SQL DML — SELECT, INSERT, UPDATE, DELETE: Worked examples

Everything below runs on the same running table from the parent, employees, freshly loaded with these four rows every time an example says "start fresh":

id	name	dept	salary
1	Asha	Sales	50000
2	Ben	Eng	70000
3	Cira	Eng	90000
4	Dia	Sales	40000

See the parent topic for the table's CREATE statement.

The scenario matrix

Before working examples, let's list every case class a beginner can hit. A "case class" is a distinct type of surprise — the sign flips, the empty results, the edge conditions. Think of each row as a cell we must cover.

#	Case class	The trap it hides
C1	Normal filtered SELECT	baseline — a predicate that keeps some rows
C2	Empty result (predicate matches nothing)	0 rows is a valid answer, not an error
C3	The `NULL` degenerate case	`= NULL` never matches; you need `IS NULL`
C4	Aggregate over an empty / grouped set	`COUNT` returns 0, but `AVG`/`SUM` return `NULL`
C5	UPDATE that references the old value	right-hand side = value before the write
C6	Order-of-operations chain (UPDATE then DELETE)	the second statement sees the new data
C7	The missing-WHERE catastrophe	one statement changes every row
C8	DELETE vs TRUNCATE limiting behaviour	filtered+undoable vs all+fast
C9	Real-world word problem	translate English → predicate
C10	Exam twist: alias in WHERE / logical order	why the query errors

Each example below is tagged with the cell(s) it covers. Between them, every cell is hit.

Example 1 — Normal filtered SELECT `[C1]`

Worked example Baseline: who earns more than 60k in Eng?

SELECT name, salary
FROM employees
WHERE dept = 'Eng' AND salary > 60000;

Forecast: Cover the code and guess the output rows before reading on. How many rows? Which names?

Steps:

FROM employees → grab all 4 rows. Why this step? The logical execution order (parent's mnemonic "Fat Wizards…") always starts with FROM: you can't filter rows you haven't fetched.
WHERE dept = 'Eng' AND salary > 60000 → test each row's predicate. Asha (Sales) fails first clause. Dia (Sales) fails. Ben (Eng, 70000) → TRUE AND TRUE. Cira (Eng, 90000) → TRUE AND TRUE. Why this step? AND is TRUE only when both sides are TRUE; this narrows to the intersection.
SELECT name, salary → project only those two columns of the survivors. Why this step? SELECT runs after WHERE, so it only ever sees rows that already passed.

Result: Ben 70000, Cira 90000.

Verify: Two survivors, both salaries strictly greater than 60000 ✓, both dept = 'Eng' ✓. No Sales row leaked through.

Example 2 — Empty result set `[C2]`

Worked example A predicate no row satisfies

SELECT name FROM employees WHERE salary > 100000;

Forecast: Does this error, return zero rows, or return a row with a blank name?

Steps:

WHERE salary > 100000 → test each: 50000, 70000, 90000, 40000. The maximum is 90000, which is not greater than 100000. Why this step? We need to see that no row makes the predicate TRUE.
Since zero rows survived, SELECT projects zero rows. Why this step? An empty result is a legitimate, successful answer — SQL does not treat "nothing matched" as an error.

Result: 0 rows (an empty table, no error).

Verify: Count of returned rows $= 0$ . Compare: our max salary is $90000 < 100000$ , so emptiness is correct.

Example 3 — The `NULL` degenerate case `[C3]`

Worked example A missing salary refuses to compare

Start fresh, then insert a fifth employee whose salary is unknown:

INSERT INTO employees (id, name, dept, salary)
VALUES (5, 'Eve', 'Sales', NULL);
 
SELECT name FROM employees WHERE salary = NULL;      -- (A)
SELECT name FROM employees WHERE salary IS NULL;     -- (B)

Forecast: Which query returns Eve — (A), (B), both, or neither?

Steps:

NULL means "unknown / no value" — it is not zero and not an empty string. Why this step? You must know what the object is before you compare it.
Query (A) salary = NULL: any comparison with NULL using = yields the third truth value, UNKNOWN — never TRUE. WHERE keeps only TRUE rows. Why this step? SQL uses three-valued logic (TRUE/FALSE/UNKNOWN). "Is an unknown value equal to unknown?" is itself unknown.
So (A) returns 0 rows — Eve is not selected, which surprises everyone. Why this step? WHERE only keeps rows whose predicate is TRUE; since Eve's row evaluated to UNKNOWN (not TRUE), it is discarded — we must trace the row all the way to its removal to trust the surprising outcome.
Query (B) salary IS NULL: the special operator IS NULL tests the state "has no value" and returns real TRUE/FALSE. Why this step? This is the only correct way to catch missing values.

Result: (A) → 0 rows. (B) → Eve.

Verify: The distinguishing fact: NULL = NULL evaluates to UNKNOWN, not TRUE. Only IS NULL returns TRUE for a missing value, so exactly (B) catches Eve.

Example 4 — Aggregates over groups and emptiness `[C4]`

The figure below shows the mechanism as a left-to-right pipeline with three labelled stages. Stage 1 (left): the four raw rows exactly as the table holds them — each row's dept and salary visible. Stage 2 (middle arrows): GROUP BY dept routes each row into its box — follow the black arrow that carries Asha 50000 and Dia 40000 into the Sales box, and the red arrows that carry Ben 70000 and Cira 90000 into the Eng box. Stage 3 (right boxes): each box lists its two salaries and then the single AVG number they collapse to. The red Eng box is the key object — watch its two inputs (70000, 90000) become the one output 80000.

Figure — SQL DML — SELECT, INSERT, UPDATE, DELETE

Worked example COUNT vs AVG on empty and on NULLs

Start fresh (the original 4 rows, no Eve). Run:

SELECT dept, COUNT(*) AS n, AVG(salary) AS avg_pay
FROM employees
GROUP BY dept;                                  -- (A)
 
SELECT COUNT(*) AS n, AVG(salary) AS avg_pay
FROM employees
WHERE dept = 'HR';                              -- (B) no HR exists

Forecast: In (B), the HR filter matches nothing. Is n equal to 0 or NULL? Is avg_pay equal to 0 or NULL?

Steps:

(A) GROUP BY dept buckets rows into Sales = {Asha 50000, Dia 40000} and Eng = {Ben 70000, Cira 90000} — exactly the two right-hand boxes in the figure, fed by the routing arrows of Stage 2. Why this step? Aggregates summarize each bucket, so we must form the buckets first (the figure's middle stage).
AVG(Sales) = (50000+40000)/2 = 45000; AVG(Eng) = (70000+90000)/2 = 80000. COUNT(*) = 2 each — this is the figure's Stage 3 "two salaries collapse to one number" step (red box for Eng). Why this step? AVG = sum ÷ number of rows, computed per group.
(B) WHERE removes every row (no HR). Now the aggregate runs over an empty set — imagine Stage 3 with an empty box: no salaries feed in. Why this step? This is the limiting case: what does a summary of nothing return?
COUNT(*) of nothing is 0 (a real number — you can count zero things). But AVG of nothing is NULL (there is no value to average — dividing by zero rows is undefined, not 0). Why this step? This is the single most-tested aggregate edge case. COUNT always returns a number; AVG/SUM/MIN/MAX return NULL on an empty input.

Result: (A) → Sales 2 45000, Eng 2 80000. (B) → n = 0, avg_pay = NULL.

Verify: Sales avg $45000$ , Eng avg $80000$ . Empty-set COUNT(*) = 0 and AVG = NULL. See Aggregate Functions & GROUP BY for the full family.

Example 5 — UPDATE that references the old value `[C5]`

Worked example A 10% raise for Engineering

Start fresh. Run:

UPDATE employees
SET salary = salary * 1.10
WHERE dept = 'Eng';

Forecast: After this, what are Ben's and Cira's salaries? Does Asha change?

Steps:

WHERE dept = 'Eng' selects Ben (70000) and Cira (90000). Asha and Dia are Sales → untouched. Why this step? The predicate decides which rows the SET touches. (Parent's rule: write WHERE first!)
For each matched row, evaluate the right-hand side using the current value: salary * 1.10. Why this step? On the right of =, salary means "this row's value before the update" — this is what lets you compute a raise relative to the old pay.
Ben: $70000 \times 1.10 = 77000$ . Cira: $90000 \times 1.10 = 99000$ .

Result: Ben 77000, Cira 99000; Asha 50000, Dia 40000 unchanged.

Verify: $70000 \times 1.10 = 77000$ ✓, $90000 \times 1.10 = 99000$ ✓. Sum of Eng salaries went $160000 \to 176000$ , exactly a $10%$ increase ✓.

Example 6 — Order-of-operations chain: UPDATE then DELETE `[C6]`

Worked example The second statement sees the first's result

Start fresh. Run in order:

UPDATE employees SET salary = salary + 5000 WHERE dept = 'Sales';   -- step A
DELETE FROM employees WHERE salary < 60000;                          -- step B
SELECT name, salary FROM employees ORDER BY name;                    -- step C

Forecast: Which names survive to the final SELECT? Watch out: does the DELETE see the old Sales salaries or the new ones?

Steps:

Step A raises both Sales rows by 5000: Asha $50000 \to 55000$ , Dia $40000 \to 45000$ . Eng untouched. Why this step? Statements run sequentially and are committed to the working state in order — the UPDATE finishes completely before DELETE begins.
Step B deletes rows with salary < 60000. Using the new values: Asha 55000 < 60000 → deleted; Dia 45000 → deleted; Ben 70000, Cira 90000 → kept. Why this step? Because A already ran, B's predicate reads the updated salaries. This is the whole point of the example — order matters.
Step C SELECT + ORDER BY name (alphabetical) of the survivors. Why this step? ORDER BY runs last, sorting whatever rows remain.

Result: Ben 70000, Cira 90000.

Verify: Survivors both have salary $\geq 60000$ ✓. If the DELETE had (wrongly) used the old salaries, Asha's original 50000 and Dia's 40000 would still both be $< 60000$ and deleted anyway — so here the answer coincidentally matches, but the reasoning (new values) is what you must state. Final names sorted: Ben < Cira ✓.

Example 7 — The missing-WHERE catastrophe `[C7]`

Worked example One line, the whole table gone wrong

Start fresh. A tired engineer means to zero out one salary but forgets the WHERE:

UPDATE employees SET salary = 0;      -- 😱 no WHERE

Forecast: How many rows change — 1, 2, or all 4?

Steps:

There is no WHERE clause, so the implicit predicate is "match every row". Why this step? Absence of a filter is not "match nothing" — it is "match all". This is the parent's most important fact: no WHERE → everywhere.
SET salary = 0 applies to all 4 rows. Why this step? SET runs once per matched row, and step 1 matched every row — so the same assignment is written into all four, wiping the whole payroll.

Result: All 4 salaries become 0. Total payroll $= 0$ .

Verify: Rows affected $= 4$ (the full table). Sum of salaries after $= 0$ .

Common mistake Preview before you commit

The fix (parent's safety drill):

BEGIN;
SELECT * FROM employees WHERE id = 1;   -- confirm the 1 row you mean
UPDATE employees SET salary = 0 WHERE id = 1;
-- inspect, then either:
ROLLBACK;   -- undo the whole thing, or
COMMIT;     -- lock it in

Wrapping in a transaction makes a bad DML statement recoverable — see Transactions — ACID, COMMIT, ROLLBACK.

Example 8 — DELETE vs TRUNCATE: the limiting behaviour `[C8]`

Worked example Remove some rows vs empty the whole table

Start fresh. Compare:

DELETE FROM employees WHERE dept = 'Sales';   -- (A)
-- vs, on a fresh table:
TRUNCATE TABLE employees;                       -- (B)

Forecast: After (A), how many rows remain? After (B)? Which one can you ROLLBACK?

Steps:

(A) DELETE … WHERE dept = 'Sales' removes only the two Sales rows (Asha, Dia), row-by-row and logged. Why this step? DELETE is DML: it respects the filter and each removal is written to the transaction log, so the database remembers how to undo each one.
Remaining after (A): Ben, Cira → 2 rows. Why this step? DELETE only touches rows the predicate matched; the two Eng rows never satisfied dept = 'Sales', so they are left completely intact — the survivors are exactly the non-matching rows.
(B) TRUNCATE ignores any filter (it takes none) and deallocates all data pages in one operation → 0 rows. Why this step? TRUNCATE is the "empty everything" blunt instrument. Instead of visiting rows one by one, it throws away the storage the rows lived in — that is why it is faster than DELETE but also why it cannot report or undo individual row removals.
Rollback behaviour: (A) sits inside a transaction and is fully reversible with ROLLBACK (see the safety drill in Example 7). (B) is usually not rollback-able — once committed by the engine it is gone, because there is no per-row log to replay. Why this step? This is the deciding factor in real life: choose DELETE when you need a filter and an undo; choose TRUNCATE only when you truly want the whole table emptied fast and accept there is no going back.

Result: (A) leaves 2 rows (Ben, Cira) and is rollback-able. (B) leaves 0 rows and is usually not rollback-able.

Verify: After (A): row count $= 2$ . After (B): row count $= 0$ . Only (A) is rollback-able. See SQL DDL — CREATE, ALTER, DROP for TRUNCATE's DDL-ish nature.

Example 9 — Real-world word problem `[C9]`

Worked example "Give everyone in Sales earning under 45k a bump to exactly 45k, then list the raised people."

Start fresh. Translate the English into SQL.

Forecast: Who gets bumped? What are the final Sales salaries? What exact rows does the final list show?

Steps:

Identify the filter words: "in Sales" → dept = 'Sales'; "earning under 45k" → salary < 45000. Why this step? Every English condition maps to one clause of a predicate (WHERE clause and Predicates).
Identify the action: "bump to exactly 45k" → SET salary = 45000 (a fixed value, not a formula). Why this step? "to exactly X" is an absolute assignment, unlike Example 5's relative * 1.10.
Write the UPDATE:
```
UPDATE employees SET salary = 45000
WHERE dept = 'Sales' AND salary < 45000;
```
Sales rows: Asha 50000 (not < 45000 → skip), Dia 40000 (< 45000 → bumped to 45000). Why this step? The AND narrows to Sales rows that are also below 45k — only Dia qualifies.
"List the raised people" → write the SELECT that reports exactly those who now sit at 45000:
```
SELECT name, salary FROM employees
WHERE dept = 'Sales' AND salary = 45000
ORDER BY name;
```
Sales rows after the update: Asha 50000 (not = 45000 → excluded), Dia 45000 (= 45000 → listed). Why this step? The task's second half ("list the raised people") is its own query; the predicate salary = 45000 picks out precisely the bumped employee, and ORDER BY name gives a stable output.

Result: Only Dia is bumped ( $40000 \to 45000$ ). The final list → Dia 45000 (a single row; Asha stays 50000, above the threshold, and is not listed).

Verify: Rows updated $= 1$ (Dia only). Asha unchanged at 50000 because $50000 \neq < 45000$ . Final SELECT returns exactly one row: Dia 45000 ✓.

Example 10 — Exam twist: alias in WHERE, and logical order `[C10]`

Worked example Why does this query ERROR?

Start fresh. A student writes:

SELECT name, salary * 12 AS annual
FROM employees
WHERE annual > 600000;      -- ✗ error: unknown column 'annual'

Forecast: Will this run? If not, where does it break, and how do you fix it?

Steps:

Recall the logical execution order (parent mnemonic "Fat Wizards Generally Have Super Odd Luck"): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Why this step? The alias annual is created in the SELECT step, so we must locate when it comes into existence.
WHERE runs before SELECT. At the moment WHERE is evaluated, annual does not exist yet → the DB reports "unknown column". Why this step? This derives the rule instead of memorizing it: aliases from SELECT are invisible to WHERE because WHERE is evaluated earlier in the pipeline.
Fix: repeat the expression in WHERE (it can't use the alias), so it evaluates on real columns:
```
SELECT name, salary * 12 AS annual
FROM employees
WHERE salary * 12 > 600000;
```
Test each row: Asha $50000 \times 12 = 600000$ (not >, skip), Ben $70000 \times 12 = 840000$ ✓, Cira $90000 \times 12 = 1080000$ ✓, Dia $40000 \times 12 = 480000$ skip. Why this step? salary is a real table column available in WHERE, so recomputing salary * 12 there sidesteps the missing alias.

Result: The broken query errors ("unknown column 'annual'"). The fixed query → Ben (annual 840000), Cira (annual 1080000).

Verify: $50000 \times 12 = 600000$ (excluded, since > is strict), $70000 \times 12 = 840000 > 600000$ ✓, $90000 \times 12 = 1080000 > 600000$ ✓, $40000 \times 12 = 480000 < 600000$ ✗. Two survivors ✓.

Coverage check

Recall Did we hit every matrix cell?

C1 ::: Example 1 (filtered SELECT) C2 ::: Example 2 (empty result set) C3 ::: Example 3 (= NULL vs IS NULL) C4 ::: Example 4 (COUNT=0 but AVG=NULL on empty; grouped averages) C5 ::: Example 5 (UPDATE using old value, * 1.10) C6 ::: Example 6 (UPDATE-then-DELETE chain sees new data) C7 ::: Example 7 (missing-WHERE hits all rows) C8 ::: Example 8 (DELETE filtered+undoable vs TRUNCATE all+fast) C9 ::: Example 9 (English word problem → predicate) C10 ::: Example 10 (alias in WHERE errors; logical order)

Active Recall

What truth value does salary = NULL produce, and which rows does WHERE keep?

It produces UNKNOWN; WHERE keeps only TRUE rows, so = NULL matches nothing — use IS NULL.

On an empty input set, what do COUNT(*) and AVG(x) each return?

COUNT(*) returns 0; AVG (and SUM/MIN/MAX) return NULL.

In SET salary = salary * 1.10, which value does the right-hand salary use?

The row's current value before the update.

In an UPDATE-then-DELETE sequence, does the DELETE see old or new values?

New values — statements run in order, so DELETE reads what UPDATE already wrote.

Why does WHERE annual > 600000 fail when annual is a SELECT-list alias?

WHERE runs before SELECT, so the alias doesn't exist yet.

DELETE with a WHERE vs TRUNCATE — rows affected and reversibility?

DELETE removes only filtered rows and is rollback-able; TRUNCATE empties all rows fast and is usually not rollback-able.

Connections

4.4.04 SQL DML — SELECT, INSERT, UPDATE, DELETE (Hinglish) — the parent this deep-dive extends.
WHERE clause and Predicates — the filter behind Examples 1, 3, 9.
Aggregate Functions & GROUP BY — the empty-set edge case of Example 4.
Transactions — ACID, COMMIT, ROLLBACK — the recovery drill for Example 7.
SQL DDL — CREATE, ALTER, DROP — TRUNCATE's DDL nature in Example 8.
SQL Joins · Indexes — where these SELECT patterns scale to many tables.