Visual walkthrough — SQL DML — SELECT, INSERT, UPDATE, DELETE

We will slowly follow one query through the machine. Here it is — don't try to understand it yet, we'll earn every word:

SELECT dept, AVG(salary) AS avg_pay
FROM employees
WHERE salary > 40000
GROUP BY dept
HAVING AVG(salary) > 60000
ORDER BY avg_pay DESC
LIMIT 1;

Our data is the same 4-row table from the parent note:

Step 1 — The problem: written order ≠ run order

WHAT: We notice that the very first word you write — SELECT — is almost the last thing the database does.

WHY it matters: Every confusing SQL rule ("why can't I filter on my alias?", "why does WHERE reject AVG()?") comes from this mismatch. If you know which stage runs first, you can derive the rule instead of memorizing it.

PICTURE: The left column is the order your fingers type; the right column is the order the machine runs. The crossing arrows are exactly where the confusion lives.

Look at the red arrow: SELECT (typed 1st) executes 5th. That single crossing is the whole reason this page exists.

Step 2 — FROM: put the raw rows on the table

WHAT: The engine loads all 4 rows of employees. Nothing is filtered, nothing is grouped.

WHY first: You cannot filter, group, or sort rows you haven't fetched yet. FROM is the only stage that produces rows out of thin air (technically, off the disk); every later stage only shrinks or reshapes what FROM gave it.

PICTURE: All four rows sit on the conveyor belt, in the blue "input" tray. This is the material the rest of the machine sculpts.

$\underset{\text{stage}}{\underbrace{\text{FROM}}}\underset{\text{the table it reads}}{\underbrace{\text{employees}}}$

• FROM — the verb: "fetch rows".
• employees — the noun: which pile of rows to fetch. Result = all 4 rows.

Step 3 — WHERE: keep only rows that pass a per-row test

WHAT: We test each row against salary > 40000.

WHY here, before grouping: WHERE looks at individual rows. It runs before any grouping because grouping should only bucket the rows we actually want to keep — filtering first means less work later, and it lets us throw out rows that would pollute a group's average.

WHY WHERE cannot say AVG(salary): an average is a property of a group of rows, but at this stage groups don't exist yet — we're still looking at one lonely row. That's the derivation of the rule "no aggregates in WHERE."

PICTURE: Each row is checked. Dia (40000) fails > 40000 (40000 is not greater than 40000 — watch that strict >), so she is dropped in red. The other three continue in green.

$\text{WHERE}\underset{\text{this row’s value}}{\underbrace{\text{salary}}}\underset{\text{strict, 40000 fails}}{\underbrace{>}}\underset{\text{the threshold}}{\underbrace{40000}}$

Survivors: Asha (50000), Ben (70000), Cira (90000).

Step 4 — GROUP BY: collapse rows into buckets

WHAT: We bucket the 3 survivors by dept. Sales → {Asha}. Eng → {Ben, Cira}.

WHY: We want one answer per department, not per person. Grouping is the moment "3 rows" becomes "2 groups". This is why aggregate functions like AVG only make sense from here on — now there's a pile to average.

PICTURE: The three green rows fall into two labelled bins. Notice Eng holds two rows; Sales holds one.

$\text{GROUP BY}\underset{\text{the bucket key}}{\underbrace{\text{dept}}}\Rightarrow \{\text{Sales}\},\{\text{Eng}\}$

Step 5 — HAVING: filter the buckets (this is not WHERE)

WHAT: We compute each bucket's AVG(salary) and keep only buckets over 60000.

• Sales bucket: just Asha → $\text{AVG}=50000$. Is $50000>60000$? No → drop.
• Eng bucket: Ben + Cira → $\text{AVG}=\frac{70000+90000}{2}=80000$. Is $80000>60000$? Yes → keep.

WHY HAVING exists at all: WHERE already left; it filtered rows and couldn't see averages. We need a second filter that runs after grouping to filter groups. That second filter is HAVING. Same idea (keep the TRUE ones), different unit (groups, not rows).

PICTURE: Two buckets sit on a scale marked at 60000. Sales (50000) tips below and is dropped in red; Eng (80000) clears the bar in green.

$\text{HAVING}\underset{\text{a group’s average}}{\underbrace{\text{AVG(salary)}}}>\underset{\text{group threshold}}{\underbrace{60000}}$

Step 6 — SELECT: now, finally, choose the columns

WHAT: From the one surviving group (Eng), we output dept and AVG(salary), naming the average avg_pay.

WHY this is 5th, not 1st: You can only pick from what survived filtering and grouping. Choosing columns first would be meaningless — there'd be nothing chosen-from yet. This late position is exactly why an alias defined here cannot be used back in WHERE (Step 3): WHERE already finished four stages ago, before the name avg_pay was ever born.

PICTURE: The Eng bucket enters the SELECT box; out comes a clean one-row result with the new column heading avg_pay.

$\text{SELECT dept,}\underset{\text{computed}}{\underbrace{\text{AVG(salary)}}}\text{AS}\underset{\text{nickname born here}}{\underbrace{\text{avg\_pay}}}$

Result so far: Eng | 80000.

Step 7 — ORDER BY then LIMIT: sort, then cut

WHAT: ORDER BY avg_pay DESC sorts high→low; LIMIT 1 keeps the top row.

WHY last: Sorting and cutting only make sense on the final shape of the output. Also note ORDER BY can use the alias avg_pay — because ORDER BY runs after SELECT, so by now the nickname exists. Contrast that with WHERE, which ran before SELECT and therefore cannot.

PICTURE: Our result has only one row, so sorting is trivial and LIMIT 1 keeps it. The figure also shows the general case with several rows to make the sort-then-cut visible.

$\text{ORDER BY}\underset{\text{alias usable here!}}{\underbrace{\text{avg\_pay}}}\text{DESC}\text{LIMIT}1$

Final answer of the whole query: Eng | 80000.

Step 8 — The degenerate cases (what happens at the edges)

Never leave a reader to hit an untested scenario. Three edges:

PICTURE: The full pipeline with the two optional gears (WHERE, GROUP BY) greyed out, showing rows sliding straight through.

The one-picture summary

Everything above, compressed: the query text on top, the 7 execution stages as a numbered assembly line below, and our 4 rows narrowing to the single final answer Eng | 80000. The crossing arrow (write-order ↔ run-order) is drawn in red — the source of every rule we derived.

Connections

• Parent: SQL DML overview — the four verbs this pipeline serves.
• WHERE clause and Predicates — the per-row test in Step 3.
• Aggregate Functions & GROUP BY — the bucketing and HAVING of Steps 4–5.
• SQL Joins — extend the FROM stage across multiple tables.
• Indexes — how FROM/WHERE fetch rows fast.
• Transactions — ACID, COMMIT, ROLLBACK — the safety net around any changes.