Data Manipulation Language (DML) Flashcards

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

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.

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?

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.

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?

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.

What is the primary purpose of the `INSERT INTO ... SELECT` statement in SQL?

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.

You are tasked with increasing the salary of all employees in the 'Sales' department by 5%. Which DML query correctly performs this action?

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.

When using the `DELETE` statement with a `WHERE` clause, what is the effect on the table?

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.