Interview Questions
Data Manipulation Language (DML) is a subset of SQL used to manipulate data within database objects. The main DML commands are INSERT, UPDATE, DELETE, and MERGE. These commands allow adding, modifying, removing, and combining data in database tables.
The INSERT statement adds new rows to a table. There are multiple ways to use it: INSERT INTO table_name (column1, column2) VALUES (value1, value2), or INSERT INTO table_name VALUES (value1, value2) to insert values for all columns in order.
You can insert multiple rows in a single INSERT statement by listing multiple sets of values: INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), (value5, value6). Another method is using INSERT INTO ... SELECT to insert rows from another table.
The UPDATE statement modifies existing records in a table. Its basic syntax is UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition. The WHERE clause is optional but recommended to specify which rows to update.
The DELETE statement removes one or more records from a table. Its syntax is DELETE FROM table_name WHERE condition. If no WHERE clause is specified, all rows in the table will be deleted. It's important to use a precise WHERE clause to avoid unintended data loss.
The MERGE statement performs INSERT, UPDATE, or DELETE operations in a single statement based on a condition. It's useful for synchronizing two tables. The statement allows you to compare a source table with a target table and perform different actions depending on whether a match is found.
You can insert data from one table into another using the INSERT INTO ... SELECT statement. For example: INSERT INTO target_table (column1, column2) SELECT column1, column2 FROM source_table WHERE condition.
Upsert is the process of inserting a new record or updating an existing one if it already exists. Different databases implement this differently. Some use MERGE, while others use specific syntax like INSERT ... ON DUPLICATE KEY UPDATE in MySQL.
The main risks include accidentally updating or deleting unintended records if the WHERE clause is incorrect. Always use transactions, have a backup, and test complex UPDATE or DELETE statements in a safe environment before running them on production data.
To update multiple columns, list them in the SET clause separated by commas: UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3 WHERE condition.
You can use a subquery in an UPDATE statement to set values based on conditions from another table. For example: UPDATE employees SET salary = salary * 1.1 WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York').
TRUNCATE quickly removes all rows from a table, resetting its storage. DELETE removes rows one by one and can be rolled back. TRUNCATE is faster but less flexible, as it cannot use a WHERE clause and cannot be rolled back.
To insert or update NULL values, use the NULL keyword. For example: INSERT INTO table_name (column1, column2) VALUES (value1, NULL). You can also use UPDATE table_name SET column1 = NULL WHERE condition.
You can update a table based on conditions from another table using JOIN. For example: UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id SET t1.column1 = t2.column2 WHERE condition.
Best practices include: using transactions, writing precise WHERE clauses, backing up data before major changes, using prepared statements to prevent SQL injection, and testing complex operations in a safe environment before production.
To insert rows with default values, either omit the column in the column list or explicitly use the DEFAULT keyword. For example: INSERT INTO table_name (column1) VALUES (DEFAULT), or INSERT INTO table_name DEFAULT VALUES.
The OUTPUT clause allows you to return information about the rows affected by an INSERT, UPDATE, or DELETE statement. It can capture both the old and new values of the modified rows, useful for logging or auditing purposes.
Bulk insert is a method of inserting multiple rows efficiently. Different databases have different methods, such as using INSERT with multiple VALUES, BULK INSERT command, or database-specific bulk loading utilities.
A self-update involves updating a table based on its own existing values. For example: UPDATE table_name SET column1 = column1 * 1.1 WHERE condition.
The RETURNING clause (supported in some databases like PostgreSQL) allows you to retrieve values of rows affected by INSERT, UPDATE, or DELETE statements. It's similar to the OUTPUT clause in other databases.
When working with large datasets, consider performance implications, use transactions, potentially break the operation into smaller chunks, create appropriate indexes, and be cautious of lock contention in multi-user environments.
Maintain data integrity through foreign key constraints, check constraints, transactions, using appropriate data types, and implementing validation logic. Always ensure that DML operations don't violate defined database constraints.
Parameterized queries use placeholders for values, which helps prevent SQL injection and improves query performance by allowing query plan reuse. Different databases have different syntax for parameterization.
INSERT IGNORE will skip rows that would cause errors (like duplicate key violations) instead of failing the entire insert operation. It's useful when you want to insert multiple rows and don't want the entire operation to fail if some rows have issues.
Conditional delete uses a WHERE clause to specify which rows to remove. For example: DELETE FROM table_name WHERE condition. You can also use subqueries or joins to create more complex deletion conditions.
Foreign key constraints can restrict DML operations. For example, you cannot delete a parent record if child records exist unless CASCADE delete is specified. INSERT and UPDATE must ensure that referenced values exist in the parent table.
You can copy data between tables using INSERT INTO ... SELECT, CREATE TABLE ... AS SELECT, or using database-specific bulk copy utilities. The method depends on whether you want to copy structure, data, or both.