DDL and DML
INSERT, UPDATE, DELETE
These commands form the core Data Manipulation Language (DML) suite used to modify the actual records stored within your database tables. Understanding these operations is essential for maintaining data integrity and ensuring that the information retrieved by queries remains accurate and current. You reach for these operations whenever you need to add new entities, modify existing state, or remove obsolete information from your system.
Inserting New Data
The INSERT operation is the mechanism for populating your database with new records, allowing you to transition from an empty schema to a functional state. When you execute an insert, the database engine validates the provided values against the table schema, checking data types, constraints, and mandatory fields before committing the transaction. It is crucial to understand that SQL treats every insert as a logical atomic operation, meaning either all columns are persisted correctly or the entire request is rejected if a constraint, such as a primary key duplication or a null violation, occurs. By explicitly defining the target columns, you insulate your code from future schema changes, such as adding default values or optional columns, which might otherwise break positional inserts that rely on a fixed structure. Always think of an insertion as a request to create a new row identity within the storage engine's structure.
-- Inserting a new customer record
INSERT INTO customers (name, email, created_at)
VALUES ('Alice Johnson', 'alice@example.com', CURRENT_TIMESTAMP);
-- SQL engine maps these values to the schema's defined types.Updating Existing Records
The UPDATE operation is inherently more hazardous than an INSERT because it modifies existing state rather than appending new information. When you execute an update, the database locates the specific rows matching your criteria and replaces the old values with new ones, marking the old data for deletion or overwriting the storage blocks. The most vital component here is the WHERE clause; without it, the engine will modify every single row in the table, potentially causing catastrophic data loss or corruption. Conceptually, an update is a multi-step process where the engine first reads existing values to determine if criteria are met, then performs the write operation. Because this can lock rows or tables, performance considerations arise; updating indexed columns or large ranges can cause significant transaction log growth and blocking. Always design updates to affect the smallest possible subset of data to maintain optimal system performance and transactional safety.
-- Updating a specific user's email address
UPDATE customers
SET email = 'new.alice@example.com'
WHERE name = 'Alice Johnson'; -- Crucial: WHERE limits the scopeRemoving Data with DELETE
DELETE removes one or more rows from a table, effectively severing those entities from the database records permanently. Similar to the update, a delete operation relies heavily on the WHERE clause to isolate the records being purged. When you run this command, the database engine must traverse the table structure—often utilizing indexes—to identify the row identifiers, then remove the pointers and free the associated storage space. This process is complex because the database must also check for referential integrity; if other tables rely on these records through foreign key constraints, the operation will fail to prevent orphaned data. Understanding that delete is a logged operation is critical, as every row deletion is recorded in the transaction log to allow for potential rollbacks or replication to standby servers. Always ensure that your WHERE clause is tested in a SELECT query first to verify exactly which rows will be affected before executing the destructive command.
-- Deleting a record that is no longer needed
DELETE FROM customers
WHERE email = 'old.user@example.com'; -- Targets only the specific recordTransactions and Atomic Safety
Transactions provide a safety net for DML operations by ensuring the ACID properties: Atomicity, Consistency, Isolation, and Durability. When performing multiple inserts, updates, or deletes that are logically tied together, you should wrap them in a transaction block. This allows the database to treat a sequence of operations as a single unit of work. If an error occurs halfway through, the database can roll back the entire batch to the state existing before the transaction began, preventing partial data updates that could break your application's logic. By using COMMIT and ROLLBACK commands, you gain granular control over when changes are made permanent. This is particularly useful in complex systems where updating one table necessitates updating another simultaneously. Understanding this ensures that even in the face of system crashes or unexpected constraints, your database remains in a valid and reliable state, preventing data divergence between related entities.
BEGIN TRANSACTION;
-- Multiple DML operations here
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- All changes saved as one atomic unitFiltering for Precision
Precise targeting is the bedrock of safe DML operations. Whether updating or deleting, the WHERE clause acts as a filter that defines exactly which subset of data is affected by the operation. A poorly constructed filter can result in unintended modifications to thousands of rows, which are difficult to reverse without a full backup. To reason about these operations, consider the database as a set of records; your command is a function applied only to the subset returned by the filter. It is best practice to always write your filter using a unique identifier, such as a primary key, to ensure that only the intended record is modified. By combining precise conditions and logical operators like AND/OR, you gain fine-grained control over the modification process. Always treat your DML operations with extreme caution, verifying the scope of your filters repeatedly to avoid common pitfalls that arise from selecting overly broad or incorrect data targets.
-- Using a primary key for surgical precision
DELETE FROM customers
WHERE customer_id = 502; -- Using PK is the safest way to limit impactKey points
- INSERT adds new records while ensuring adherence to schema constraints and data types.
- The UPDATE operation relies strictly on the WHERE clause to define its scope.
- Omitting the WHERE clause in an UPDATE or DELETE command will affect every row in the table.
- DELETE operations must respect foreign key constraints to maintain database referential integrity.
- Transactions allow multiple DML operations to be grouped into a single atomic unit of work.
- The COMMIT command saves changes to the database, while ROLLBACK reverts the transaction state.
- Using primary keys in your filters ensures that DML operations affect only the intended records.
- Transaction logs ensure that operations are durable even in the event of a system crash.
Common mistakes
- Mistake: Forgetting the WHERE clause in UPDATE or DELETE statements. Why it's wrong: Without a WHERE clause, the operation applies to every single row in the table, leading to massive data loss. Fix: Always write the WHERE clause before the UPDATE or DELETE statement, or run a SELECT first to verify the target rows.
- Mistake: Using single quotes for numeric values or no quotes for strings. Why it's wrong: SQL enforces strict data typing; strings must be enclosed in single quotes while numbers should not, or queries will trigger syntax errors or unnecessary type casting. Fix: Use single quotes for all character, date, and time data types, and use no quotes for numeric types.
- Mistake: Assuming an INSERT statement handles multiple rows in one command. Why it's wrong: While some dialects support it, developers often fail to use bulk insert syntax when performing mass data migrations, resulting in poor performance. Fix: Use comma-separated value sets in a single INSERT statement to reduce transaction overhead.
- Mistake: Neglecting to commit or rollback after DML operations. Why it's wrong: In many database environments, changes are held in a transaction buffer and are not permanent until explicitly committed, causing confusion about data persistence. Fix: Always issue a COMMIT command after verifying the changes, or use ROLLBACK if an error occurs.
- Mistake: Ignoring foreign key constraints when deleting records. Why it's wrong: Attempting to delete a record that is referenced by another table will trigger a foreign key violation error, crashing the process. Fix: Delete the dependent records first, use ON DELETE CASCADE, or set the references to NULL before removing the parent record.
Interview questions
How do you add a new row to a table using the INSERT statement?
To add a new row, you use the INSERT INTO syntax followed by the table name and a set of parentheses containing the column names. Then, you use the VALUES keyword followed by another set of parentheses containing the actual data you want to store. For example: INSERT INTO employees (id, name) VALUES (1, 'Alice'). It is crucial to specify the columns to ensure data integrity and avoid issues if the table schema changes later.
How would you modify an existing record using the UPDATE statement and why is the WHERE clause so important?
The UPDATE statement is used to modify existing data by specifying the table name, the SET clause to define the column-to-value assignments, and a WHERE clause. The WHERE clause is absolutely critical because, without it, the database will apply the update to every single row in the entire table. For instance: UPDATE employees SET salary = 50000 WHERE id = 1. Omitting the WHERE clause causes a catastrophic loss of original data across the whole table.
What is the syntax for removing rows from a table, and what is the difference between DELETE and TRUNCATE?
To remove specific rows, you use the DELETE FROM statement combined with a WHERE clause, such as DELETE FROM employees WHERE id = 5. A TRUNCATE statement, conversely, removes all rows from a table much faster by deallocating the pages used by the table. Unlike DELETE, TRUNCATE is a DDL operation that cannot be rolled back easily in some systems and does not log individual row deletions, making it significantly more efficient for clearing large datasets.
Compare the approach of using an INSERT INTO ... SELECT statement versus inserting values one by one. When would you prefer one over the other?
The INSERT INTO ... SELECT approach is superior for bulk data migration or copying records between tables because it minimizes the number of round-trips to the database engine. Instead of executing multiple individual INSERT statements, you perform a single set-based operation that processes the entire result set of the query at once. This significantly reduces network overhead and transaction logging volume, whereas individual inserts are better for inserting small, user-defined values where the data source is external or non-tabular.
How can you use a subquery within an UPDATE statement, and what are the potential risks of doing so?
You can use a subquery within the SET clause of an UPDATE statement to pull data from a different table to populate the target column. For example, you might update a customer's total_spent column by summing up their orders from an orders table using a correlated subquery. The main risk is performance; if the subquery is not optimized with indexes, it can lead to massive overhead because the subquery might execute for every single row being updated in the target table.
How do you handle transactional safety when performing a large-scale DELETE or UPDATE operation?
For large-scale operations, you should wrap your statements in a transaction using BEGIN TRANSACTION and COMMIT. This ensures that if the operation is interrupted or hits an error, you can issue a ROLLBACK to return the table to its previous state. Additionally, to avoid locking the entire table for too long, it is best practice to perform updates or deletes in smaller, batched increments using a loop or a limited WHERE clause, which helps maintain system performance and concurrency for other users.
Check yourself
1. You need to update the email address for a specific user. Which approach best ensures data integrity?
- A.Run the UPDATE statement without any conditions to reset the table.
- B.Use a SELECT query with the unique ID first to confirm the exact row, then execute the UPDATE.
- C.Perform a DELETE and then an INSERT to replace the record.
- D.Update the entire table and hope the database engine keeps the original values.
Show answer
B. Use a SELECT query with the unique ID first to confirm the exact row, then execute the UPDATE.
Verifying the target with a SELECT statement is the safest practice. The first and fourth options will overwrite all data, leading to catastrophe. The third is inefficient and risks data loss if the insert fails.
2. What is the result of executing 'DELETE FROM Employees' without a WHERE clause?
- A.It deletes only the first row of the table.
- B.It removes the table structure itself.
- C.It deletes all records from the table but keeps the structure.
- D.It throws an error and performs no action.
Show answer
C. It deletes all records from the table but keeps the structure.
In standard SQL, a DELETE statement without a WHERE clause acts on all rows in the table. It does not drop the table structure. Option 1 is incorrect because it lacks a filter, and option 4 is incorrect because the statement is syntactically valid.
3. Which statement correctly adds a new employee named 'John' with ID 101?
- A.INSERT INTO Employees VALUES ('John', 101);
- B.INSERT Employees (Name, ID) VALUES (John, 101);
- C.INSERT INTO Employees (Name, ID) VALUES ('John', 101);
- D.UPDATE Employees SET Name = 'John' WHERE ID = 101;
Show answer
C. INSERT INTO Employees (Name, ID) VALUES ('John', 101);
Option 3 correctly specifies column names and uses quotes for the string literal. Option 1 assumes column order which is risky. Option 2 fails to quote the string 'John'. Option 4 is an update, not an insertion.
4. When performing an UPDATE on multiple columns, which syntax is correct?
- A.UPDATE Table SET Col1 = 'A', Col2 = 'B' WHERE Id = 1;
- B.UPDATE Table SET Col1 = 'A' AND SET Col2 = 'B' WHERE Id = 1;
- C.UPDATE Table SET Col1 = 'A'; SET Col2 = 'B' WHERE Id = 1;
- D.UPDATE Table WHERE Id = 1 SET Col1 = 'A', Col2 = 'B';
Show answer
A. UPDATE Table SET Col1 = 'A', Col2 = 'B' WHERE Id = 1;
Standard SQL requires comma separation for multiple columns in a single SET clause. Option 1 follows this. Options 2 and 3 use invalid syntax (AND/semicolon), and Option 4 has the WHERE clause in the wrong sequence.
5. Why is it important to use a transaction when performing a large DELETE operation?
- A.It makes the deletion process run faster than a single statement.
- B.It allows you to undo the deletion if you realize you targeted the wrong rows.
- C.It automatically adds a WHERE clause to your statement.
- D.It converts the DELETE operation into an UPDATE operation.
Show answer
B. It allows you to undo the deletion if you realize you targeted the wrong rows.
Transactions provide atomicity and the ability to rollback. Option 1 is false (transactions often add overhead), Option 3 is false, and Option 4 is logically incorrect as DELETE and UPDATE perform different functions.