HKDSE ICT｜SQL and Database Queries Lesson-Prep Guide (for tutors)｜Clause order, WHERE versus HAVING, four joins, GROUP BY and aggregates

2026-11-03

Tutor lesson-prep guides · Secondary ICT

Students can write SELECT * FROM student, but the moment GROUP BY appears they scatter columns, and asked the difference between WHERE and HAVING they go quiet. This lesson fixes one clause order and one 120-row table so every query can be checked by arithmetic instead of recalled as a pattern.

Objectives — Students can: (1) write the 6 clauses in order (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY) and state that written order differs from execution order; (2) use a table of 120 students in 5 classes (24 per class, mean mark 62) to write aggregate queries for class size, mean and total, applying WHERE to rows and HAVING to groups correctly; (3) distinguish the preserved side of each of the 4 join types and explain the roles of primary and foreign keys in linking two tables.

01 Prerequisites (3-minute check)

If any of these fails, fix it before the lesson starts.

Skill	Quick check	Pass mark
Tables	What does a row and a column each represent?	A row is a record, a column is a field
Primary keys	Can two rows share a primary key value?	No, it must be unique
Basic SELECT	What does SELECT * mean?	Return every column
Comparison operators	How is not-equal written in SQL?	As <> or !=

02 Lesson flow (60 minutes)

Time	Stage	Tutor does	Student does
0–6 min	One table, three facts	Fields, records, primary key	Draw the table first
6–16 min	Six clauses in order	6 clauses, fixed positions	Written order is not run order
16–30 min	WHERE versus HAVING	Filter rows versus filter groups	Aggregate conditions go to HAVING
30–44 min	Aggregates and grouping	120 / 5 = 24	Only grouped columns after GROUP BY
44–54 min	Four joins	4 preserved sides	INNER needs both sides
54–60 min	Check the numbers and exit	Verify with row counts and totals	If it does not add up, it is wrong

03 Teaching script (read it out)

Scene 1: Draw the table before writing the query

Put a five-row student table and a class table on the board, mark the primary and foreign keys, and only then write the first SELECT. The reason is practical: most errors are not syntax, they are a missing mental picture, which is why a student still selects student_name after GROUP BY class. With the table visible, names are clearly per row while classes are per group.

Ask: after GROUP BY class, why is student_name invalid? (Names exist per row, not per group)

Scene 2: Teach written order apart from run order

The 6 clauses have a fixed written order, but the engine reads FROM first, then WHERE to filter rows, then GROUP BY, then HAVING to filter groups, then SELECT to pick columns, then ORDER BY to sort. That single sequence explains two rules: WHERE cannot hold an aggregate because grouping has not happened, and HAVING cannot filter raw rows because they are already collapsed. Say it once and most errors disappear.

Ask: does COUNT(*) > 20 belong in WHERE or HAVING? (HAVING)

Scene 3: Finish with arithmetic checking

Give 120 students, 5 classes and a mean of 62, so the grand total is 7440. After writing a query, students check two anchors: 24 per class from 120 divided by 5, and class totals summing to 7440. If the sum overshoots, a join has duplicated rows, which is the classic join trap and is caught instantly by arithmetic.

Ask: why would a join inflate the total? (One-to-many duplication)

04 Question types and worked steps

Type	Example	Steps	Answer
Single-table filter	SELECT name FROM student WHERE form = ‘S6’	Choose columns, name the table, add the condition, quote strings	Only matching rows return
Aggregate and group	Class size and mean mark per class	SELECT class, COUNT(*), AVG(mark) … GROUP BY class	Five groups of 24
Filter after grouping	Classes with a mean above 60	Write the aggregate condition in HAVING, never WHERE	HAVING AVG(mark) > 60
Join two tables	Get the form tutor from the class table	Write the ON condition and name the preserved side	INNER keeps only matches

Sanity check (teach this): Clause order is fixed and a misplaced clause is a syntax error; WHERE runs before grouping so it cannot hold aggregates; HAVING runs after grouping so COUNT and AVG are legal there; every non-aggregate column in SELECT must appear in GROUP BY; 120 divided by 5 gives 24, the row-count anchor; the total of 7440 is the second anchor, and a join that inflates it has duplicated rows; a join without ON becomes a Cartesian product; strings take single quotes while numbers do not; COUNT(*) counts rows whereas COUNT(column) skips NULLs.

05 Independent practice (3 questions)

Question	Answer	What to watch
Write: classes with a mean above 65, with size	SELECT class, COUNT(*) FROM student GROUP BY class HAVING AVG(mark) > 65;	The aggregate condition belongs in HAVING
Judge: is WHERE COUNT(*) > 10 valid?	No	WHERE runs before grouping, so COUNT does not exist yet
Calculate: a join returns 240 rows instead of 120, why?	A one-to-many relationship duplicated rows	Use DISTINCT or aggregate first

06 Common mistakes and what to say

Mistake	Student writes	Say this
Aggregate inside WHERE	A student writes WHERE COUNT(*) > 20	Tutor line: “WHERE runs while rows still exist and no groups do, so COUNT is not there yet. Counting groups is HAVING.”
Per-row column after GROUP BY	A student writes SELECT name, class, COUNT(*) … GROUP BY class	Tutor line: “After grouping there is one row per class. Which name would it print? Names only exist per row.”
Join without ON	A student writes FROM student JOIN class	Tutor line: “Without ON the engine pairs every row with every row. The row count explodes and the answer cannot be right.”
Confusing COUNT(column) with COUNT(*)	A student counts people with COUNT(phone) and gets fewer	Tutor line: “COUNT(column) skips NULL, so anyone without a phone vanishes. Count people with COUNT(*).”

07 Homework (paper, section and question numbers)

Secondary ICT chapter index — Review the database chapters and copy the fixed clause order
S5 ICT: system development life cycle — Use the requirements step there to list the fields a student table needs
Secondary Maths chapter index — Use the statistics chapter to recheck the mean calculation on this page

08 Exit check (last 5 minutes)

Question	Answer	Decision
Which clause holds an aggregate condition?	HAVING	Answering WHERE means execution order is missing
What may be selected after GROUP BY?	Grouped columns and aggregates	Answering any column confuses rows with groups
Which rows does INNER JOIN keep?	Only rows matched on both sides	Answering all rows means the preserved side is unclear

09 Tutor FAQ

Which year group?

S6 ICT databases; S5 can start with single-table SELECT in the summer term.

How much syntax must be memorised?

Six clause positions and four aggregates; the rest follows from execution order.

Are answers provided?

Three practice items with answers and explanations, plus three linked chapters.

Find a tutor — free Tutor Registration →

A TutorZone lesson-prep guide. Written by the TutorZone team; questions and lesson flow are original and are not taken from any school or publisher material.