S6 ICT: SQL grouping and filtering
- 2026-11-20
S6 ICT: SQL grouping and filtering
GROUP BY with HAVING plus LIKE, BETWEEN and IN, checking counts before and after grouping.
Intermediate: grouping and conditions This paper focuses on grouping and conditions. GROUP BY Class splits the eight rows into four and four, SUM gives 341 for class A and 208 for class B, AVG gives 85.25 and 69.33, and the divisor of 7 rather than 8 is explained. The condition section checks two names starting with L under LIKE, three rows for BETWEEN 70 and 90, six rows for IN with two courses, and course 101 surviving HAVING at least 4. It closes with the error of placing an aggregate condition in WHERE.
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 | Intermediate (10 items) |
02 Questions (10 items)
- FoundationIn SELECT Class, COUNT(*) FROM Student GROUP BY Class, how many rows fall in each group?
- FoundationWhat does SELECT COUNT(*) FROM Student WHERE Name LIKE ‘L%’ return?
- FoundationWhat does SELECT COUNT(*) FROM Student WHERE Score BETWEEN 70 AND 90 return?
- IntermediateIn SELECT Class, SUM(Score) FROM Student GROUP BY Class, what is each class total?
- IntermediateGrouping Enrol by CourseID with HAVING COUNT(*) at least 4 leaves which course?
- IntermediateIn SELECT Class, AVG(Score) FROM Student GROUP BY Class, what is each average to two decimals?
- ChallengeWhat divisor does AVG(Score) use over the whole table, and why is it not 8?
- ChallengeWhat does SELECT COUNT(*) FROM Enrol WHERE CourseID IN (101, 104) return?
- ChallengeIn SELECT DISTINCT Score FROM Student ORDER BY Score DESC, what is the second value?
- Error redoError rework: a student writes SELECT Class FROM Student WHERE COUNT(*) at least 4. Why does it fail?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | Class A has 4 rows and class B has 4 rows. | GROUP BY splits the eight rows by class and counts inside each group, giving four and four. |
| 2 | It returns 2 rows, Lee and Lam. | The percent sign matches any number of characters, and two names start with L. |
| 3 | It returns 3 rows. | BETWEEN includes both ends, so 82, 77 and 88 qualify while 91 lies above the upper bound. |
| 4 | Class A totals 341 and class B totals 208. | All four class A scores are real while class B has a NULL that is skipped, so only three scores are added. |
| 5 | Only course 101 survives, with 4 enrolment rows. | HAVING filters after grouping, and the other courses hold 3, 3 and 2 rows, so they are dropped. |
| 6 | Class A is 85.25 and class B is 69.33. | Class A divides 341 by 4 and class B divides 208 by 3, so the divisors differ. |
| 7 | The divisor is 7. | AVG ignores NULL and uses only real scores, while the table row count 8 applies to COUNT(*) alone. |
| 8 | It returns 6 rows. | Course 101 holds 4 rows and course 104 holds 2, and IN combines both sets of matches. |
| 9 | The second value is 88. | After duplicates are removed the order runs 91, 88, 82, 77, 65, 55, so the second value is 88. |
| 10 | An aggregate cannot appear in WHERE, so GROUP BY with HAVING must be used instead. | WHERE tests rows before grouping, and at that moment COUNT(*) does not exist yet. |
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.



