/en-tutor-prep-s6-ict-sql-database-queries-practice-3/
/en-tutor-prep-s6-ict-sql-database-queries-practice-3/
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 joins and subqueries

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)

  1. FoundationStudent and Enrol are joined on StudentID with INNER JOIN. How many rows result?
  2. FoundationAfter the join, how many values does SELECT DISTINCT StudentID return?
  3. FoundationHow many class A students take CourseID 101?
  4. IntermediateGrouping Enrol by CourseID, which course has the most students and how many?
  5. IntermediateAfter Student LEFT JOIN Enrol, how many students have no enrolment?
  6. IntermediateThe three students taking CourseID 102 have what average score, to two decimals?
  7. ChallengeUsing a subquery for students scoring above the overall average of 78.43, how many qualify?
  8. ChallengeHow many students take exactly two courses?
  9. ChallengeGROUP BY CourseID with HAVING COUNT(*) at least 3 leaves how many courses?
  10. 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.

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