SQL - Structured Query Language Data Manipulation Language (DML) Questions and Answers 1 — Questions and Answers
Question 1: Which of the following SQL statements is NOT considered a Data Manipulation Language (DML) command?
- UPDATE
- CREATE (Correct answer)
- INSERT
- DELETE
Correct answer: CREATE
The CREATE statement is a Data Definition Language (DDL) command used to define and manage the structure of database objects, such as tables. DML commands, like UPDATE, INSERT, and DELETE, are used to manipulate the data within those objects.
Question 2: A database administrator needs to remove all rows from a large 'Log_Archive' table quickly, without the need to roll back the operation. Which SQL command is the most efficient for this task?
- DELETE FROM Log_Archive;
- DROP TABLE Log_Archive;
- TRUNCATE TABLE Log_Archive; (Correct answer)
- UPDATE Log_Archive SET IsActive = 0;
Correct answer: TRUNCATE TABLE Log_Archive;
TRUNCATE TABLE is the most efficient command for deleting all rows from a table when rollback capability is not needed. It is a DDL operation that deallocates the data pages, which is much faster and uses fewer system and transaction log resources than DELETE, a DML operation that removes rows one by one.
Question 3: A developer needs to synchronize a 'Products' table with a 'Staging_Products' table. The operation should insert new products, update existing products with new prices, and delete products that no longer exist in the staging table, all within a single atomic statement. Which DML command is best suited for this scenario?
- UPDATE
- INSERT
- MERGE (Correct answer)
- A series of INSERT, UPDATE, and DELETE statements
Correct answer: MERGE
The MERGE statement is designed specifically for this 'upsert' or synchronization scenario. It can combine INSERT, UPDATE, and DELETE operations into a single, conditional statement, making the process more efficient and atomic.
Question 4: What is the primary purpose of the `INSERT INTO ... SELECT` statement in SQL?
- To update existing records in one table based on values from another table.
- To create a new table and populate it with data from an existing table.
- To copy rows from one existing table and add them to another existing table. (Correct answer)
- To insert a single, manually specified row into a table.
Correct answer: To copy rows from one existing table and add them to another existing table.
The `INSERT INTO ... SELECT` statement is used to copy data from a source table and insert it as new rows into a destination table. Both tables must already exist.
Question 5: You are tasked with increasing the salary of all employees in the 'Sales' department by 5%. Which DML query correctly performs this action?
- INSERT INTO Employees (Salary) VALUES (Salary * 1.05) WHERE Department = 'Sales';
- ALTER TABLE Employees SET Salary = Salary * 1.05 WHERE Department = 'Sales';
- SELECT Salary * 1.05 FROM Employees WHERE Department = 'Sales';
- UPDATE Employees SET Salary = Salary * 1.05 WHERE Department = 'Sales'; (Correct answer)
Correct answer: UPDATE Employees SET Salary = Salary * 1.05 WHERE Department = 'Sales';
The UPDATE statement is the correct DML command to modify existing records in a table. The SET clause specifies the column to change and the new value, while the WHERE clause filters which rows should be affected.
Question 6: When using the `DELETE` statement with a `WHERE` clause, what is the effect on the table?
- It removes all rows from the table, but the table structure remains.
- It removes only the rows that satisfy the condition specified in the WHERE clause. (Correct answer)
- It deactivates the specified rows, but they physically remain in the table.
- It removes the entire table, including its structure and data.
Correct answer: It removes only the rows that satisfy the condition specified in the WHERE clause.
The `DELETE` command is a DML statement used to remove rows from a table. When a `WHERE` clause is included, it specifically targets and removes only those rows that meet the criteria of the clause, leaving other rows unaffected.
SQL - Structured Query Language Data Manipulation Language (DML) Questions and Answers 1
Which of the following SQL statements is NOT considered a Data Manipulation Language (DML) command?