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

==SELECT==. It is the only read-only verb — it looks, it never writes.

SELECT name FROM employees WHERE dept = 'Sales';

What this does: FROM grabs the table, WHERE dept = 'Sales' keeps only Sales rows (Asha, Dia), SELECT name picks the name column → returns Asha, Dia.

All 4 rows. There is no WHERE, so the predicate is effectively "always true" — every row matches. That is the "No WHERE → everywhere" rule from the parent note. All salaries become 0.

• INSERT → add a row
• DELETE → remove a row
• UPDATE → change a value in a row
• SELECT → read rows

Mnemonic from the parent: Select, Insert, Update, Delete.

Name the columns explicitly (order-independent, future-proof):

INSERT INTO employees (id, name, dept, salary)
VALUES (5, 'Eli', 'Eng', 65000);

Why name columns: if a hire_date column is added later, INSERT INTO employees VALUES (...) (no names) would break because the value count no longer matches. Naming columns keeps this statement valid.

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

Walkthrough: Eng rows are Ben (70000) and Cira (90000); both clear 60000. ORDER BY salary DESC sorts high→low → Cira 90000, Ben 70000.

UPDATE employees
SET salary = salary + 5000
WHERE dept = 'Sales';

What the right-hand salary means: the row's current value before this update. Asha 50000 → 55000, Dia 40000 → 45000. Eng rows untouched (they fail the WHERE).

SELECT dept, AVG(salary) AS avg_pay, COUNT(*) AS n
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 2;

Bucketing logic: GROUP BY dept makes two buckets — Sales {50000, 40000} and Eng {70000, 90000}. Aggregates summarise each bucket: Sales avg $=(50000+40000)/2=45000$, n $=2$; Eng avg $=(70000+90000)/2=80000$, n $=2$. HAVING COUNT(*) >= 2 filters buckets — both pass. Result: Sales 45000 (n=2), Eng 80000 (n=2).

It fails because WHERE is evaluated before SELECT and before grouping, so the alias avg_pay does not exist yet — and you cannot filter on an aggregate in WHERE at all (there are no groups yet). Recall the execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Fix: filter groups with HAVING:

SELECT dept, AVG(salary) AS avg_pay
FROM employees
GROUP BY dept
HAVING AVG(salary) > 46000;

Now only Eng (avg 80000) survives; Sales (45000) is dropped.

Step 1 (UPDATE runs first): Asha 50000 → 55000, Dia 40000 → 45000. Table now: Asha 55000, Ben 70000, Cira 90000, Dia 45000. Step 2 (DELETE sees the new values): salary < 60000 matches Asha (55000) and Dia (45000) → both removed. Final rows: Ben 70000, Cira 90000. The key insight: the DELETE filter operates on the updated salaries, not the originals — order of statements changes the outcome.

UPDATE employees SET dept = 'Retail' WHERE dept = 'Sales';
INSERT INTO employees (id, name, dept, salary)
VALUES (6, 'Faye', 'Sales', 48000);

Trace: Asha and Dia move Sales→Retail (2 rows). Then Faye added to Sales (1 row). Counts per dept:

• Eng: Ben, Cira → 2
• Retail: Asha, Dia → 2
• Sales: Faye → 1

Total rows = 5.

UPDATE employees SET salary = salary * 1.10 WHERE dept = 'Eng';
 
SELECT name, salary FROM employees
WHERE dept = 'Eng'
ORDER BY salary ASC;

Compute the raises: Ben $70000\times 1.10=77000$; Cira $90000\times 1.10=99000$. Sorted low→high: Ben 77000, Cira 99000. (Note: salary is an INT; $77000$ and $99000$ are whole numbers, so no rounding surprise here.)

BEGIN;                                   -- open a transaction (undoable)
 
-- 1. Preview the EXACT rows the WHERE will hit:
SELECT id, name, salary FROM employees WHERE id = 3;   -- Cira, 90000
 
-- 2. Do the change with the SAME where:
UPDATE employees SET salary = salary / 2 WHERE id = 3; -- 90000 -> 45000
 
-- 3. Re-check:
SELECT id, name, salary FROM employees WHERE id = 3;   -- Cira, 45000
 
COMMIT;                                  -- lock it in; or ROLLBACK; to undo

Why the ritual works: BEGIN starts a transaction so nothing is permanent until COMMIT. Previewing with the same WHERE guarantees you change exactly the rows you inspected. If the preview showed 4 rows instead of 1, you'd catch a missing/wrong WHERE before committing. Cira: $90000/2=45000$.

Gus does NOT appear. Any comparison with NULL (which means unknown) yields UNKNOWN, never TRUE. WHERE keeps a row only when the predicate is TRUE, so NULL < 60000 → UNKNOWN → row dropped. Rows returned: Asha (50000), Dia (40000). (Ben 70000, Cira 90000, Gus NULL all excluded.) To include unknown-salary rows you must ask explicitly:

SELECT name FROM employees WHERE salary < 60000 OR salary IS NULL;

Note you must use IS NULL, not = NULL — because salary = NULL is itself UNKNOWN and matches nothing.

Both leave the same rows, but the intermediate steps differ.

• Script A: first DELETE removes Sales (Asha, Dia) → left with Ben 70000, Cira 90000. Second DELETE (salary < 60000) matches none → 2 rows (Ben, Cira).
• Script B: first DELETE (salary < 60000) removes Asha (50000) and Dia (40000) → left with Ben 70000, Cira 90000. Second DELETE (dept = 'Sales') matches none → 2 rows (Ben, Cira).

Final answer: both end with 2 rows (Ben, Cira). Here order didn't change the result because the two conditions happened to overlap on the same victims. Contrast this with Exercise 3.3, where an UPDATE between operations did change outcomes. Lesson: order matters when an earlier statement changes the data a later filter reads — pure deletes on the starting set may or may not, so always trace it.

Use ==DELETE FROM employees;== (no WHERE — intentionally, to hit all rows) inside a transaction:

BEGIN;
DELETE FROM employees;   -- all rows gone, but...
-- changed your mind:
ROLLBACK;                -- all rows restored

Why not TRUNCATE: TRUNCATE TABLE employees is faster (it deallocates data pages instead of logging each row) but is usually not rollback-able in most engines and does not fire row triggers. If undo-ability matters, DELETE is the safe choice; if you need a fast blunt wipe and don't care about undo, TRUNCATE.

Eng rows now: Ben 70000, Cira 90000, Gus NULL (3 rows).

• COUNT(*) counts rows → 3.
• COUNT(salary) counts non-NULL values → 2 (Gus's NULL is skipped).
• AVG(salary) ignores NULLs, averaging only the 2 present values → $(70000+90000)/2=80000$.

The subtlety: AVG divides by the count of non-NULL salaries (2), not by COUNT(*) (3). So the NULL neither counts as zero nor drags the average down — it's simply excluded.