S6 ICT: SQL error rework
- 2026-11-20
S6 ICT: SQL error rework
Ten common misunderstandings corrected: NULL comparison, COUNT differences, where aggregates belong, Cartesian products and default sort order.
Error rework: ten common slips This paper corrects real errors one by one. COUNT(Score) gives only 7 rows because one Score is NULL; WHERE Score = NULL always returns 0 rows and IS NULL is needed for 1 row; aggregate conditions must sit in HAVING; omitting GROUP BY collapses everything into one row with a count of 8; ORDER BY defaults to ASC so 55 comes first; SUM still totals 549 while COUNT drops by one; a missing ON turns 12 rows into 96; percent signs on both sides of LIKE match 3 names; DISTINCT and GROUP BY both give 2 rows but only the latter can aggregate; and the order of WHERE and HAVING leaves only course 101.
01 Paper details (check answers afterwards)
| Item | Detail |
|---|---|
| Questions | 10 |
| Suggested time | 25 |
| Difficulty mix | Foundation 3 · Intermediate 3 · Challenge 3 · Error redo 1 |
| Chapter | Chapter: S6 ICT: SQL Database Queries |
| Level | Error rework (10 items) |
02 Questions (10 items)
- FoundationError rework: COUNT(*) and COUNT(Score) differ by one row on the same table. Why?
- FoundationError rework: WHERE Score = NULL returns 0 rows. What is the correct form?
- FoundationError rework: why does putting COUNT(*) at least 4 in WHERE fail?
- IntermediateError rework: SELECT Class, COUNT(*) omits GROUP BY. What is the result?
- IntermediateError rework: ORDER BY Score gives no ASC or DESC. Which score comes first?
- IntermediateError rework: how do NULL values affect SUM and COUNT differently?
- ChallengeError rework: a JOIN without ON gives 96 rows. What is the correct count?
- ChallengeError rework: why does LIKE ‘%a%’ match three names?
- ChallengeError rework: how do DISTINCT Class and GROUP BY Class differ in result?
- Error redoError rework: when combining WHERE and HAVING, what is the correct order?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | Because one Score is NULL, so COUNT(Score) counts only 7 rows. | COUNT(*) counts rows while COUNT on a column counts only its non-NULL values. |
| 2 | Use WHERE Score IS NULL, which returns 1 row. | NULL is not equal to any value, not even itself, so an equality test never matches. |
| 3 | Aggregate conditions belong in HAVING together with GROUP BY. | WHERE runs row by row before grouping, when no aggregate value exists yet. |
| 4 | A single row whose COUNT(*) is 8. | Without grouping the whole table counts as one group, so Class shows just one of its values. |
| 5 | The first row is 55, because the default order is ascending. | When the direction is omitted the database uses ASC, and NULL is usually placed first or last. |
| 6 | SUM stays at 549 while COUNT(Score) falls from 8 to 7. | Both ignore NULL, yet SUM loses one addend with the total unchanged while COUNT loses one row. |
| 7 | It should be 12 rows, equal to the number of Enrol records. | With ON Student.StudentID = Enrol.StudentID each enrolment pairs with exactly one student. |
| 8 | Because Chan, Lam and Tsang all contain the letter a. | A percent sign on either side allows any characters around the letter, and matching ignores case. |
| 9 | Both return 2 rows, but GROUP BY can also aggregate. | DISTINCT only removes duplicates while GROUP BY additionally allows COUNT or SUM per group. |
| 10 | Filter rows with WHERE, group with GROUP BY, then filter groups with HAVING, leaving only course 101 here. | WHERE shrinks the data before grouping while HAVING removes groups that fail the condition. |
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.
Want to draw the flowchart and fill the trace table yourself? Book a TutorZone tutor and unpack all ten items in 25 minutes.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.



