DML statements change stored data. Application ORMs generate these, but every developer should read the raw SQL—especially WHERE on updates and deletes.
INSERT
INSERT INTO customers (name, email)
VALUES ('Grace', 'grace@example.com');
INSERT INTO orders (customer_id, total, ordered_at)
VALUES (1, 25.00, '2025-03-01');Practice: Run in SQLite, DB Fiddle, or Postgres. Create the customers/orders sample from the relational-model lesson if needed.
UPDATE
UPDATE orders
SET total = total * 0.9
WHERE id = 101;
-- Always preview first:
SELECT id, total FROM orders WHERE id = 101;Rule: Never run UPDATE without WHERE unless you intend to touch every row.
DELETE
DELETE FROM orders
WHERE id = 103;
-- Safer pattern: SELECT count first
SELECT COUNT(*) FROM orders WHERE customer_id = 99;Deletes are permanent without backups or transactions. Soft deletes (status column) are common in apps.
Important interview questions and answers
- Q: INSERT vs UPSERT?
A: Plain INSERT fails on duplicate keys; UPSERT/MERGE variants update or ignore—dialect-specific. - Q: Why preview with SELECT?
A: Confirms the WHERE clause matches intended rows before irreversible changes.
Self-check
- Which DML statement removes rows?
- What happens if you UPDATE without a WHERE clause?
Pitfall: UPDATE without WHERE touches every row—use transactions in production consoles.
Interview prep
- UPDATE without WHERE?
Updates every row in the table—usually accidental.
- DELETE vs TRUNCATE?
DELETE removes rows with optional WHERE; TRUNCATE clears table fast (dialect rules vary).