DSE ICT | Databases and SQL Tutor Prep Guide | Keys, SELECT, JOIN and normalisation

2026-11-01

Tutor lesson-prep guides · Secondary ICT

Students can recite that a primary key is unique, yet drop the WHERE clause, confuse COUNT with AVG, and give up on JOIN. This lesson splits a query into four parts so an error points at one part.

Objectives — Students can: identify primary and foreign keys and explain referential integrity; write a query from a requirement using SELECT, FROM, WHERE and ORDER BY; and use COUNT, AVG and GROUP BY with JOIN to merge two tables into one result.

01 Prerequisites (3-minute check)

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

Skill	Quick check	Pass mark
Reading tables	What one column of a table is called	A field
Sorting	Is ORDER BY ascending or descending by default	Ascending
Averages	How to average 5 marks	Total divided by the count

02 Lesson flow (60 minutes)

Time	Stage	Tutor does	Student does
0 to 10 min	Tables and records	The basic units of a relational database	Distinguishes the three terms
10 to 22 min	Keys	Unique, not empty, pointing at another table	Names both keys
22 to 36 min	Four parts of SELECT	SELECT, FROM, WHERE, ORDER BY	Writes a filter with sorting
36 to 48 min	Aggregates	COUNT, AVG, one row per group	Counts 2 in class 4A
48 to 56 min	JOIN and normalisation	Matching with ON, the cost of repeated data	5 rows after the join
56 to 60 min	Exit check	Three questions collected and marked	Independent

03 Teaching script (read it out)

Scene 1: Keys first: draw two tables before any rule

A table is made of fields and records. A primary key identifies one row uniquely, so it can never repeat or be blank. A foreign key is the primary key of another table, carried here to show what a row belongs to.
This lesson uses Students with number, name, class and mark, holding 5 records, plus Classes with class and form teacher, holding 3. The student number is the primary key of Students, the class is a foreign key pointing at the class primary key in Classes, so Students may not mention a class that Classes does not contain. That rule is referential integrity.

Ask the student what goes wrong if the form teacher name is stored in Students instead (one name repeats, and one change means many edits).

Scene 2: SELECT in four parts, each with its own kind of mistake

SELECT chooses the columns to output, FROM chooses the table, WHERE filters the rows and ORDER BY sorts them. Each part fails differently, so the first diagnostic question is whether the filter or the sort is wrong.
SELECT Name FROM Students WHERE ClassID equals 4A ORDER BY Name returns 2 rows, Chan Tai Man and Lee Ka Yan, in ascending name order.
Adding Mark greater than 70 gives 3 rows. Adding AND ClassID equals 4B gives one row, Ng Yuk. Text values take quotes and numbers do not.

Ask the student to write the query for the top mark in class 4B, where Wong Hoi has 58 and Ng Yuk has 90.

Scene 3: Aggregates and JOIN: from many rows to one number

COUNT counts rows, AVG averages and MAX finds the largest, and with GROUP BY each group produces one row. SELECT ClassID and COUNT star FROM Students GROUP BY ClassID gives 2 in 4A, 2 in 4B and 1 in 5A.
The class average is the total divided by 5, giving 73.2, with the top mark 90 held by Ng Yuk.
JOIN lines two tables up on the foreign key, so selecting the student name and the teacher with JOIN ON ClassID gives the same 5 rows, now with Ms Cheung over 2, Mr Lau over 2 and Dr Yip over 1.

Reminder: leaving out the ON condition produces the Cartesian product, five rows times three rows, 15 rows, the commonest S6 loss.

04 Question types and worked steps

Type	Example	Steps	Answer
Design	Design two tables for a tutorial centre	Pick the unique column as key, move repeated data out	Students plus Classes
Query	List class 4A names from highest mark down	SELECT with WHERE and ORDER BY Mark descending	2 rows
Aggregate	Count and average per class	GROUP BY with COUNT and AVG	4A 2, 4B 2, 5A 1
Explain	Why normalise at all	Remove repetition, avoid update anomalies	One edit instead of many

Sanity check (teach this): A query can never return more rows than the table holds, 5, so anything above that means a missing ON condition. A COUNT is always a whole number, so 73.2 means AVG was used by accident. An average must fall between the lowest mark 58 and the highest 90, otherwise rows were added wrongly.

05 Independent practice (3 questions)

Question	Answer	What to watch
Number of students in 4B	2	WHERE ClassID equals 4B then COUNT
Average mark of the table	73.2	AVG of Mark
Rows after the join	5	Each student belongs to exactly one class

06 Common mistakes and what to say

Mistake	Student writes	Say this
Assuming the table is already filtered	Runs SELECT without WHERE and reports only 4A	WHERE does the filtering; without it you get the whole table.
Swapping COUNT and AVG	Writes AVG star to count people	COUNT counts rows, AVG averages values, never swap them.
Leaving out ON in a JOIN	Multiplies five rows by three and reports 15	No ON means a Cartesian product and the row count explodes.
Storing the teacher inside Students	Repeats one teacher name on many rows	Repeated data means many edits, which is why the table is split.

07 Homework (paper, section and question numbers)

Secondary ICT chapter index — All chapters
Prep guide hub — Every subject

08 Exit check (last 5 minutes)

Question	Answer	Decision
Students in class 4A	2	All three right: move to normalisation
Rows after the join	5	Answering 15 means ON was missed
Average mark of the table	73.2	Two wrong: restart scene two

09 Tutor FAQ

Can a name be a primary key?

It is a poor choice, since names repeat and change. An invented number is safer.

Can WHERE be used after GROUP BY?

WHERE filters rows before grouping, and HAVING filters groups afterwards, so the order matters.

Is more normalisation always better?

No. Third normal form is usually enough, and splitting further forces constant joins that slow reading down.

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.