COMP9. DATA MANIPULATION LANGUAGE

You have a table Employees with columns EmployeeID, FirstName, LastName, and Position. How would you insert a new employee with the following details: EmployeeID = 101, FirstName = 'John', LastName = 'Doe', Position = 'Manager'?

• INSERT INTO Employees (EmployeeID, FirstName, LastName, Position) VALUES (101, 'John', 'Doe', 'Manager');
• SELECT * FROM Employees WHERE EmployeeID = 101;
• DELETE FROM Employees WHERE EmployeeID = 101;
• UPDATE Employees SET FirstName = 'John' WHERE EmployeeID = 101;

If a table Orders has columns OrderID, ProductName, and Quantity and you only want to insert a value for ProductName and Quantity, which command is correct?

• INSERT INTO Orders (OrderID, ProductName) VALUES (1, 'ProductA');
• INSERT INTO Orders (Quantity) VALUES (10);
• INSERT INTO Orders (ProductName, Quantity) VALUES ('ProductA', 10);
• INSERT INTO Orders (ProductName) VALUES ('ProductA', 10);

What happens if you execute the following command and the StudentID column in the Students table is set to auto-increment?

• The StudentID will be set to a random value.
• The StudentID will reset to zero.
• The StudentID will remain unchanged.
• The StudentID will automatically increment to the next available value.

You have a table Products with columns ProductID, ProductName, Price, and Stock. Which of the following statements would add a new product named "Notebook" with a price of 15.99 and stock of 100?

• INSERT INTO Products (ProductName, Price, Stock) VALUES ('Notebook', 15.99, 100);
•     INSERT INTO Products VALUES (NULL, 'Notebook', 15.99, 100);

• DELETE FROM Products WHERE ProductName = 'Notebook';
• UPDATE Products SET Price = 15.99 WHERE ProductName = 'Notebook';

What would happen if you attempt to execute the following command on a table where EmployeeID is the primary key and already contains the value 201?

INSERT INTO Employees (EmployeeID, FirstName, LastName) VALUES (201, 'Jake', 'Brown');

• The insertion will fail due to a primary key constraint violation.
• The command will create a duplicate entry without any error.
• The command will update the existing record with EmployeeID 201.
• The insertion will succeed and add a new record.

You are inserting data into a table with a column that allows NULL values. Which statement will successfully insert a NULL into that column?

• INSERT INTO Sales (Product, Price) VALUES ('Pen', NULL);

• INSERT INTO Sales (Product, Price) VALUES ('Pen', 'NULL');

• INSERT INTO table_name (column_name) VALUES (DEFAULT);
• INSERT INTO table_name (column_name) VALUES (0);

You have a table Customers with columns CustomerID, Name, and Email. If you want to add a new customer with only their Name and Email, leaving CustomerID to be auto-generated, which command should you use?

• UPDATE Customers SET Name = 'Customer Name', Email = '[e-mail]' WHERE CustomerID = 1;
• SELECT * FROM Customers WHERE Name = 'Customer Name';
• DELETE FROM Customers WHERE Name = 'Customer Name';
• INSERT INTO Customers (Name, Email) VALUES ('Customer Name', '[e-mail]');

You are working with a table Orders that has a column OrderDate with a default value set to the current date. Which command correctly inserts a new order with default OrderDate?

• INSERT INTO Orders (OrderDate) VALUES (DEFAULT);

• INSERT INTO Orders SET OrderDate = DEFAULT;
• INSERT INTO Orders (OrderDate) VALUES (CURRENT_DATE);
• INSERT INTO Orders (OrderDate) VALUES (NOW());

A table Products has columns ProductID, ProductName, and Price. Which statement correctly inserts a product with a price of 0?

• INSERT INTO Products (ProductID, ProductName) VALUES (1, 'Sample Product');
• INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Sample Product', 10);
• INSERT INTO Products VALUES (1, 'Sample Product', 0, 'Extra Column');
• INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Sample Product', 0);

You have a table Students with columns StudentID, FirstName, LastName, and Grade. If you want to insert a student named "Emily Clark" with a grade of 85, what is the correct SQL command assuming StudentID is auto-incremented?

• INSERT INTO Students (FirstName, LastName, Grade) VALUES ('Emily', 'Clark', 85);
• INSERT INTO Students (FirstName, LastName) VALUES ('Emily', 'Clark');
• INSERT INTO Students (StudentID, FirstName, LastName, Grade) VALUES (1, 'Emily', 'Clark', 85);
• INSERT INTO Students (FirstName, LastName, Grade) VALUES ('Clark', 'Emily', 85);