Worked examples — SQL clauses — WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

This page is the drill hall for the parent topic. The parent told you the logical order; here we take a single tiny table and hit every case that order can produce — every filter stage, every grouping edge, NULLs, empty groups, ties, pagination, and an exam-style trap.

Every number in every answer below comes from these 7 rows. When we say "Verify", you can literally count on your fingers against this grid.

The scenario matrix

Every SQL-clause bug lives in one of these boxes. The rightmost column names the worked example that nails it.

Ten examples, ten boxes. Let's go.

Example 1 — Case A: plain per-row filter

Steps

1. FROM sales → we start with all 7 rows. Why this step? Nothing filters until we have raw rows on the table; FROM always runs first.
2. WHERE amount > 150 — test each row one at a time. This is a per-row fact, so WHERE is the correct stage (no groups involved). Why this step? amount is a value this row owns; no aggregation needed, so the cheapest, earliest filter is right.
3. Walk the grid: 100❌, 300✅, 200✅, NULL❓, 50❌, 400✅, 400✅. Why this step? NULL > 150 is not TRUE (it is UNKNOWN — the third truth value defined above), so WHERE drops it — same rule that bites us in Example 4.

Result: rows 2 (Ana 300), 3 (Bo 200), 6 (Dee 400), 7 (Dee 400) → 4 rows.

Example 2 — Case B: aggregate over groups

Steps

1. GROUP BY region partitions the 7 rows into piles (groups) by matching region. Why this step? We want a per-region summary, so we must first form the piles. All the NULL-region rows land in one "unknown" pile (grouping treats NULLs as equal for grouping only).
2. Piles: East = {1,2,5}, West = {3,4}, NULL = {6,7}. That's 3 groups. Why this step? Each distinct key (including NULL) → exactly one output row.
3. COUNT(*) counts rows in each pile; SUM(amount) adds amount, skipping NULLs. Why this step? COUNT(*) and SUM collapse a whole pile into one well-defined number — the only way the output is unambiguous.

Result:

Example 3 — Case C: HAVING vs WHERE

SELECT region, AVG(amount) AS avg_amt
FROM sales
GROUP BY region
HAVING AVG(amount) > 150;

Steps

1. There is no per-row filter needed, so WHERE is skipped. Why this step? Nothing is thrown away per row; the test is about a group's average.
2. GROUP BY region → same 3 piles (groups) as Example 2. Why this step? We need groups to exist before we can average them.
3. Compute AVG(amount) per pile: East $=450/3=150$, West $=200/1=200$, NULL $=800/2=400$. Why this step? AVG divides by the count of non-null amounts (West has 1 non-null, not 2).
4. HAVING AVG(amount) > 150 filters the groups: East $150$ ❌ (not strictly greater), West $200$ ✅, NULL $400$ ✅. Why this step? This condition uses an aggregate, which only exists after grouping — so it must be HAVING, not WHERE.

Result: West (avg 200), NULL (avg 400) → 2 rows.

Example 4 — Case D: NULL in the filter column

The two paths below are drawn side by side in the figure: the left (burnt-orange) box is = NULL collapsing to UNKNOWN and returning nothing; the right (teal) box is IS NULL correctly catching rows 6 and 7. The plum "fix" arrow between them is exactly the edit you make to go from the broken query to the working one — keep the figure in view as you read the three steps.

Steps

1. region = NULL evaluates NULL = NULL for rows 6 & 7. As defined above, that result is UNKNOWN, never TRUE. (This is the left box in the figure.) Why this step? WHERE keeps only rows that are TRUE; UNKNOWN is dropped like FALSE — so 0 rows.
2. region IS NULL is a special test that returns TRUE exactly when the value is NULL. (This is the right box, reached by following the plum "fix" arrow.) Why this step? IS NULL is the only operator built to catch the "unknown" state; = cannot.
3. Rows 6 and 7 match → 2 rows.

Result: = NULL → 0 rows; IS NULL → 2 rows (Dee, Dee).

Example 5 — Case E: NULL inside an aggregate

Steps

1. Isolate West with WHERE region = 'West' → rows {amount 200, amount NULL}. Why this step? A per-row filter is exactly WHERE's job (Case A again).
2. COUNT(*) counts rows, NULL or not → 2. Why this step? The * means "the row itself", which always exists.
3. COUNT(amount) counts non-null values of amount → 1. Why this step? Aggregates other than COUNT(*) ignore NULLs; this is the difference exam questions love.
4. SUM(amount) = 200, AVG(amount) = 200/1 = 200. Why this step? AVG = SUM / COUNT(amount), not SUM/COUNT(*). Dividing by 1, not 2, is the whole trick.

Result: COUNT(*)=2, COUNT(amount)=1, SUM=200, AVG=200.

Example 6 — Case F: alias visibility

Steps

1. The alias annual is defined in the SELECT stage, which runs after WHERE. Why this step? At the WHERE stage the alias literally does not exist yet — so this errors.
2. Fix option A — repeat the expression: WHERE amount * 12 > 3000. Why this step? WHERE can see the raw column amount, so re-derive the value inline.
3. Fix option B — order the output by the alias, which is allowed because ORDER BY runs after SELECT: ... ORDER BY annual DESC. Why this step? Same alias, opposite verdict — proving the rule is about stage order, not the alias itself.

Result of the working version SELECT amount*12 AS annual FROM sales WHERE amount*12 > 3000; — keep rows with amount > 250: 300(→3600), 400(→4800), 400(→4800) → 3 rows.

Example 7 — Case G: sorting with ties, then Top-N

SELECT id, rep, amount
FROM sales
WHERE amount IS NOT NULL
ORDER BY amount DESC, id ASC
LIMIT 3;

Steps

1. WHERE amount IS NOT NULL drops row 4 (Bo's NULL), leaving amounts: 100, 300, 200, 50, 400, 400 (ids 1,2,3,5,6,7). Why this step? A row with NULL amount cannot be ordered meaningfully against numbers; remove it first (per-row filter = WHERE).
2. ORDER BY amount DESC sorts by amount: 400 (id6), 400 (id7), 300 (id2), 200 (id3), 100 (id1), 50 (id5). The two 400s tie — that is the concrete tie from rows 6 and 7. Why this step? "Biggest first" is meaningless without a defined order; and when two rows share amount = 400, the first sort key alone leaves their relative order undefined.
3. The second key id ASC breaks the 400-tie: id 6 < id 7, so row 6 is listed before row 7. Final order: id6(400), id7(400), id2(300), ... Why this step? When the first key ties, a second key is the only thing that makes the result deterministic — otherwise the engine may return either order run-to-run.
4. LIMIT 3 keeps the first three: rows 6, 7, 2. Why this step? LIMIT trims after sorting — swap the order and "top 3" becomes "3 arbitrary rows".

Result: row 6 (Dee, 400), row 7 (Dee, 400), row 2 (Ana, 300).

Example 8 — Case H: pagination (LIMIT + OFFSET)

The figure shows the sorted list of 4 reps as a strip, with each LIMIT 2 OFFSET m window drawn as a coloured bracket sliding along it — page 1 grabs the first two cells, page 2 the next two, page 3 lands past the end and grabs nothing.

Steps

1. GROUP BY rep, ORDER BY rep ASC → sorted list: Ana, Bo, Cy, Dee (4 reps). Why this step? Pagination is only stable on a sorted set — otherwise page 2 could repeat page 1's rows, so we must fix the order before slicing.
2. Page 1: LIMIT 2 OFFSET 0 → skip 0, take 2 → Ana, Bo. Why this step? OFFSET 0 = start at the top; (1-1)*2 = 0.
3. Page 2: LIMIT 2 OFFSET 2 → skip 2, take 2 → Cy, Dee. Why this step? (2-1)*2 = 2 rows skipped; the next 2 are the answer.
4. Page 3: LIMIT 2 OFFSET 4 → skip all 4 → 0 rows (past the end). Why this step? This is the degenerate "empty last page" — the app must handle it gracefully.

Result: page1 = {Ana, Bo}, page2 = {Cy, Dee}, page3 = {} (empty).

Example 9 — Case I: degenerate / empty result

Steps

1. WHERE amount > 1000 runs first, on raw rows. Max amount is 400, so every row is dropped. Why this step? WHERE is the earliest filter; if it empties the table, nothing downstream can revive rows.
2. GROUP BY region on an empty input → zero groups. Why this step? No rows means no piles; grouping nothing yields nothing (a subtle point: GROUP BY on empty returns no rows, whereas a bare SELECT COUNT(*) with no GROUP BY would return one row of 0).
3. HAVING COUNT(*) > 0 has no groups to test → still 0 rows. Why this step? HAVING filters existing groups; with none present it changes nothing.

Result: 0 rows — a fully degenerate query.

Example 10 — Case J: real-world word problem + exam twist

Mapping English → stage

SELECT region, SUM(amount) AS total
FROM sales
WHERE amount IS NOT NULL
GROUP BY region
HAVING SUM(amount) > 250
ORDER BY (region IS NULL) ASC, total DESC;

Steps

1. WHERE amount IS NOT NULL drops row 4 (Bo's NULL amount). Why this step? "real (non-null) amounts" is a per-row fact → cheapest, earliest stage (the inverse of the IS NULL operator from Example 4).
2. GROUP BY region → East{100,300,50}, West{200}, NULL{400,400}. Why this step? "each region's total" needs one pile per region before we can sum.
3. SUM(amount): East=450, West=200, NULL-region=800. Why this step? Row 4's NULL was already removed in step 1, so West's sum is just 200 with nothing to skip.
4. HAVING SUM(amount) > 250 → East 450 ✅, West 200 ❌ (dropped), NULL-region 800 ✅. Why this step? "more than 250 in total" is a test on an aggregate → HAVING, never WHERE.
5. ORDER BY (region IS NULL) ASC, total DESC. The expression region IS NULL is a boolean: 0 (false) for real regions, 1 (true) for the unknown bucket. Ascending puts 0 before 1, so every real region sorts ahead of the unknown bucket; within each group total DESC orders by size. Why this step? "unknown bucket last regardless of total" means the unknown-flag must dominate the sort — so it becomes the first key, and raw total only breaks ties underneath it.

Result order: East (450), then NULL-region (800) — East first despite its smaller total, exactly because the unknown bucket is forced last.

Related deeper reads: SQL SELECT basics, Subqueries and CTEs, JOINs.