Exercises — SQL clauses — WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

(a) WHERE — a per-row test on salary. (b) LIMIT — caps the number of returned rows. (c) ORDER BY salary DESC — sorting is ORDER BY's only job. (d) HAVING — "average salary" is an aggregate of a group, so it must run after GROUP BY. WHERE cannot see averages.

TRUE. WHERE is step 2, GROUP BY is step 3. WHERE filters raw rows first; then the survivors get grouped. This is exactly why WHERE cannot mention COUNT(*) — no groups exist at step 2.

SELECT dept, COUNT(*) AS n
FROM employees
WHERE salary > 40000
GROUP BY dept;

Why: salary > 40000 is a row fact → WHERE (step 2), cheaply shrinking the data before grouping. Note WHERE salary > 40000 also silently discards NULL salaries, because NULL > 40000 evaluates to NULL (not TRUE), and WHERE keeps only TRUE rows. Then GROUP BY partitions the survivors by dept, and COUNT(*) counts each pile.

SELECT dept, COUNT(*) AS headcount
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 5
ORDER BY headcount DESC;

Why each clause: No per-row filter needed, so no WHERE. GROUP BY makes the piles. COUNT(*) >= 5 is a group fact → HAVING (step 4). headcount is the alias born in SELECT (step 5), and ORDER BY (step 6) runs after SELECT, so the alias is legal there.

Why it errors: annual is defined in SELECT (step 5), but WHERE is step 2 — it runs before SELECT, so the alias does not exist yet. Fix A — repeat the expression in WHERE:

SELECT name, salary * 12 AS annual
FROM employees
WHERE salary * 12 > 600000;

Fix B — wrap in a subquery (compute the alias first, filter outside):

SELECT * FROM (
  SELECT name, salary * 12 AS annual FROM employees
) t
WHERE annual > 600000;

Fix A is simpler; Fix B (Subqueries and CTEs) is handy when the expression is long.

Query A first removes rows with salary <= 50000 (or NULL), then counts what's left per department. So n = number of high earners per dept, and departments with zero high earners simply vanish (they produce an empty group, which disappears). Query B keeps all rows, groups by dept, then removes whole piles smaller than 4 people. n = the full department size, shown only for departments of size $>3$. Key contrast: WHERE changes which rows enter the count; HAVING changes which finished groups survive. They filter at different stages, so they answer different questions.

Incorrect. LIMIT (step 7) just keeps the first 3 rows of whatever order it received. With no ORDER BY, SQL gives no guarantee about order — it may hand back rows in storage/index order, which can look sorted by luck. The correct "top 3 by pay":

SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;

Now ORDER BY (6) sorts before LIMIT (7) trims — the two steps are adjacent in the pipeline, which is exactly why "top-N" works. (Relates to Pagination patterns: LIMIT n OFFSET m only makes sense on a stable ORDER BY.)

NULL means "unknown". In SQL's three-valued logic, salary = NULL evaluates to NULL, not TRUE. WHERE keeps a row only when its predicate is exactly TRUE, so every row is dropped. The correct test uses the special operator:

SELECT name FROM employees WHERE salary IS NULL;

IS NULL returns TRUE/FALSE (never NULL), so it actually finds the missing values.

SELECT dept, ROUND(AVG(salary)) AS avg_sal
FROM employees
WHERE salary > 30000            -- per-row filter (also drops NULLs)
GROUP BY dept                   -- one pile per department
HAVING COUNT(*) > 2             -- keep piles with 3+ qualifiers
ORDER BY avg_sal DESC, dept ASC -- sort, alias legal here
LIMIT 3;                        -- top 3 after sorting

Reading the word problem into the pipeline (in run order):

• "salary above 30000 / known" → WHERE salary > 30000 (NULL fails the > test, so unknowns are excluded automatically).
• "each department" → GROUP BY dept.
• "more than 2 qualifying employees" → HAVING COUNT(*) > 2 (a group count → HAVING, not WHERE).
• "average salary rounded" → SELECT ROUND(AVG(salary)).
• "top ... highest first, tie-break by name" → ORDER BY avg_sal DESC, dept ASC (alias visible because ORDER BY runs after SELECT).
• "top 3" → LIMIT 3 (last).

Step WHERE salary > 30000: row 3 (20000) drops, row 7 (NULL) drops. Survivors: 1,2,4 (Sales), 5,6 (Eng), 8,9 (HR). Step GROUP BY dept:

• Sales = {40000, 60000, 50000}, count 3.
• Eng = {90000, 70000}, count 2.
• HR = {45000, 55000}, count 2.

Step HAVING COUNT(*) > 2: only Sales (count 3) survives; Eng and HR (count 2) are removed. Step SELECT ROUND(AVG(salary)): Sales avg $=(40000+60000+50000)/3=50000$. Step ORDER BY / LIMIT 3: one row remains — no reordering needed.

Output:

The interesting lesson: the NULL and the low salary quietly vanished at WHERE, and both other departments fell at HAVING — so a large-looking table produced a single row.

(a) Row-level average. AVG ignores NULLs, so it averages the 8 known salaries: $\frac{40000+60000+20000+50000+90000+70000+45000+55000}{8}=\frac{430000}{8}=53750.$ (b) Average of averages.

• Sales avg $=(40000+60000+20000+50000)/4=42500$.
• Eng avg (NULL ignored) $=(90000+70000)/2=80000$.
• HR avg $=(45000+55000)/2=50000$.
• Mean of the three $=(42500+80000+50000)/3=172500/3=57500$.

Why different: $53750\ne 57500$. The overall average weights every person equally; the average-of-averages weights every department equally, so the small 2-person Eng dept (high pay) counts as much as the 4-person Sales dept. Averaging averages is not the same as the overall average unless all groups are the same size.

Eng rows are ids 5 (90000), 6 (70000), 7 (NULL). (a) COUNT(*) = 3 — counts rows, NULLs included. (b) COUNT(salary) = 2 — counts non-NULL values of the column, so the NULL (id 7) is skipped. Lesson: COUNT(*) answers "how many rows?"; COUNT(col) answers "how many have a value here?". (See aggregate functions.) Choosing the wrong one silently over- or under-counts.

What goes wrong: With a non-unique sort key, the relative order of the two 60000 rows is not defined. Between page 1 and page 2 requests the engine may order them differently, so one row can appear on both pages or neither — a classic pagination bug. Fix: Add a unique tiebreaker to make the ordering total:

SELECT name, salary FROM employees
ORDER BY salary DESC, id ASC
LIMIT 5 OFFSET 5;

Now (salary, id) is unique per row, so every page boundary is deterministic and stable across requests. (Foundation of Pagination patterns; keyset pagination goes further by filtering WHERE (salary, id) < (...).)

Every exercise reduces to "which numbered stage can see this thing?" — rows at WHERE (2), groups at HAVING (4), aliases only from SELECT (5) onward, and any "top-N" needs a total ORDER BY before LIMIT.