SQL - Structured Query Language Data Manipulation Language (DML) Questions and Answers

Question 1

Which of the following SQL statements is NOT considered a Data Manipulation Language (DML) command?

Accepted Answer

CREATE

Answer

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?

Accepted Answer

TRUNCATE TABLE Log_Archive;

Answer

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?

Accepted Answer

MERGE

Answer

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?

Accepted Answer

To copy rows from one existing table and add them to another existing table.

Answer

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?

Accepted Answer

UPDATE Employees SET Salary = Salary * 1.05 WHERE Department = 'Sales';

Answer

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.

SQL Practice Test

SQL Practice Test

SQL - Structured Query Language Data Manipulation Language (DML) Questions and Answers