/en-tutor-prep-s6-ict-sql-database-queries-practice-4/
/en-tutor-prep-s6-ict-sql-database-queries-practice-4/
General Inquiry: info@TutorZone.com.hk
Office Hours: Monday to Saturday - 9:00 AM to 6:00 PM
WhatsApp Monday to Sunday - 24 hours
Hotline: +852 6828 1809
Instant Support: +852 9061 3106

S6 ICT: SQL error rework

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)

  1. FoundationError rework: COUNT(*) and COUNT(Score) differ by one row on the same table. Why?
  2. FoundationError rework: WHERE Score = NULL returns 0 rows. What is the correct form?
  3. FoundationError rework: why does putting COUNT(*) at least 4 in WHERE fail?
  4. IntermediateError rework: SELECT Class, COUNT(*) omits GROUP BY. What is the result?
  5. IntermediateError rework: ORDER BY Score gives no ASC or DESC. Which score comes first?
  6. IntermediateError rework: how do NULL values affect SUM and COUNT differently?
  7. ChallengeError rework: a JOIN without ON gives 96 rows. What is the correct count?
  8. ChallengeError rework: why does LIKE ‘%a%’ match three names?
  9. ChallengeError rework: how do DISTINCT Class and GROUP BY Class differ in result?
  10. 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.

Join as a tutor or coach — academic, music, sports, arts, STEM. Free to register. See how it works →