Can WHERE and HAVING be used interchangeably?

No; WHERE filters rows before grouping and HAVING filters groups afterwards, so aggregate conditions belong in HAVING.

Why does a LEFT JOIN produce rows full of NULLs?

Every left-table row is kept, and unmatched right-table columns are padded with NULL.

DSE ICT | Relational Databases and SQL Practice Paper 2 (Aggregation and Joins)

2026-11-04

Ten items from aggregate functions to the division of labour between GROUP BY and HAVING, then row counts for INNER and LEFT JOIN on a twelve-row, four-class table.

Attempt first, mark afterwards　Allow about thirty-five minutes; note the row count of every intermediate result before deciding between WHERE and HAVING.

01　Paper details (check answers afterwards)

Item	Detail
Questions	10 items
Suggested time	About 35 minutes
Difficulty mix	Foundation 4 · Intermediate 3 · Challenge 2 · Error redo 1
Chapter	Chapter: DSE ICT \| Relational Databases and SQL
Level	S5 (Secondary 5) ICT

02　Questions (10 items)

FoundationOf twelve students (total 902 marks) five are in form 4. What does `SELECT COUNT(*) FROM Students WHERE form = 4;` give, and what percentage of the table is that?
FoundationWith the same scores, the highest 95 and lowest 54, what are `MAX(score)` and `MIN(score)`, and what is the range?
FoundationFor classes A (five students), B (four) and C (three), which class has the highest average in `SELECT class, AVG(score) FROM Students GROUP BY class;`, and what is it?
FoundationThe top three scores are 95, 91 and 88. What score appears on the first row of `SELECT name, score FROM Students ORDER BY score DESC, name ASC LIMIT 1;`?
IntermediateThe forms hold five, four and three students. How many groups does `SELECT form FROM Students GROUP BY form HAVING COUNT(*) >= 4;` return?
IntermediateStudents has twelve rows and Classes holds A, B, C and D. How many rows come back from an `INNER JOIN` of the two tables on class?
IntermediateThe same tables written as `Classes LEFT JOIN Students`. Class D has no students, so how many rows return?
ChallengeClass totals are A 358, B 307 and C 237. What is the first row of `SELECT class, SUM(score) FROM Students GROUP BY class ORDER BY SUM(score) DESC;`?
ChallengeThe three form 6 students score 88, 54 and 66. What is their `AVG(score)`, and how far is it from the table average of 75.17?
Error redoError drill: a student writes `SELECT class, AVG(score) FROM Students WHERE COUNT(*) > 3 GROUP BY class;` to drop small classes. What is wrong?

03　Answers and one-line notes

#	Answer	One-line note
1	COUNT(*) = 5, which is 41.7 percent, since 5 divided by 12 is 41.666…	COUNT(*) counts rows only, and the percentage uses the full table row count as its denominator.
2	MAX = 95 and MIN = 54, a range of 41 marks	Aggregate functions collapse a whole column to one value, and the range is the maximum minus the minimum.
3	Class C, at 79.0, since 237 divided by 3 is 79	GROUP BY gathers rows of the same class before averaging within each group; A averages 71.6 and B 76.75.
4	95	ORDER BY sorts by score descending first, LIMIT 1 keeps only the first row, and the second sort key never comes into play.
5	Two groups, form 4 and form 5	HAVING filters after grouping and tests the group count, so form 6 with only three students is dropped.
6	Twelve rows	Every student class exists in Classes, so all twelve rows are kept one to one; class D has no students and never appears.
7	Thirteen: the twelve matched rows plus one row for class D with NULL student fields	A LEFT JOIN keeps every row of the left table and pads NULLs where the right table has no match.
8	Class A with 358 marks	SUM adds within each group and the result is sorted by total descending; A has one more student than B, so its total is 51 higher even though its average is lower.
9	69.3, which is 5.83 marks below the table average, since 75.166… minus 69.333…	Average within the group first (208 divided by 3), then subtract from the table average; rounding early changes the difference.
10	WHERE runs before grouping and cannot use aggregates; it must be `GROUP BY class HAVING COUNT(*) > 3`	The order of execution is WHERE, then GROUP BY, then HAVING, so any condition on a group belongs in HAVING.

04　What to do after marking

After marking, redo only the wrong items and write down which step went wrong (formula, unit, or misread number).
Repeat the same item two days later; move to the next paper only when both attempts are correct.
Parents only need the answer and note columns; there is no need to explain every item.

Need a tutor for this chapter? Register here Tutor Registration →

Questions and answers are written originally by the TutorZone team and are not taken from any school, publisher or examination authority paper. Every value is checked by computer.