S6 ICT: SQL joins and subqueries
- 2026-11-20
S6 ICT: SQL joins and subqueries
From INNER JOIN through LEFT JOIN, subqueries and HAVING, checking row counts and aggregate results.
Challenge: joins and subqueries This paper works with two tables. Joining Student and Enrol on the identifier gives 12 rows and 8 distinct students; two class A students take course 101 and course 101 has the most takers at 4; a LEFT JOIN shows 0 students without enrolment; a subquery using the overall average of 78.43 keeps 4 students; the three students in course 102 average 83.33; 4 students take exactly two courses; HAVING at least 3 leaves three courses; and it closes with the Cartesian product of 96 rows caused by a missing ON.
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 | Challenge (10 items) |
02 Questions (10 items)
- FoundationStudent and Enrol are joined on StudentID with INNER JOIN. How many rows result?
- FoundationAfter the join, how many values does SELECT DISTINCT StudentID return?
- FoundationHow many class A students take CourseID 101?
- IntermediateGrouping Enrol by CourseID, which course has the most students and how many?
- IntermediateAfter Student LEFT JOIN Enrol, how many students have no enrolment?
- IntermediateThe three students taking CourseID 102 have what average score, to two decimals?
- ChallengeUsing a subquery for students scoring above the overall average of 78.43, how many qualify?
- ChallengeHow many students take exactly two courses?
- ChallengeGROUP BY CourseID with HAVING COUNT(*) at least 3 leaves how many courses?
- Error redoError rework: a student writes INNER JOIN without ON and gets 96 rows. Why?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | 12 rows. | Enrol holds 12 records and each pairs with its student, so the joined row count equals the Enrol row count. |
| 2 | It returns 8 values. | Twelve enrolment rows come from eight students, so de-duplication leaves eight identifiers. |
| 3 | Two students, with identifiers 1 and 2. | Filtering by class and then by course leaves only the two records that satisfy both conditions. |
| 4 | Course 101 with 4 students. | The per-course counts are 4, 3, 3 and 2, and the maximum belongs to course 101. |
| 5 | None, that is 0 students. | LEFT JOIN keeps all eight left rows, and every student appears in Enrol, so no row has an empty match. |
| 6 | The average is 83.33. | Their scores are 82, 77 and 91, so 250 divided by 3 gives 83.33. |
| 7 | Four students qualify. | The inner query returns 78.43 and the outer query compares each row, so 82, 91, 88 and 91 pass. |
| 8 | Four students, with identifiers 1, 2, 5 and 7. | Grouping by StudentID and counting gives four groups of size two, while the rest take a single course. |
| 9 | Three courses remain: 101, 102 and 103. | The per-course counts are 4, 3, 3 and 2, so only course 104 with two students is filtered out. |
| 10 | The tables form a Cartesian product, and 8 times 12 gives 96. | Without a matching condition every row pairs with the whole other table, so the row count becomes the product. |
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.



