ITPEC FE Morning October 2021 Question 26

Correlated Subquery with COUNT — find courses with exactly one section in a given year.

The query uses a correlated subquery that counts sections per course for year 2019:

• •For each course C, the subquery runs: SELECT COUNT(cid) FROM Section S WHERE C.cid = S.cid AND S.year = 2019
• •The outer query keeps only courses where that count equals 1

Count of 2019 sections per course:
- CSE101 → 2 sections (Spring, Fall) → excluded
- CSE102 → 0 sections → excluded
- CSE103 → 2 sections (Spring, Fall) → excluded
- EEE101 → 1 section (Spring) → included
- EEE102 → 2 sections (Spring, Fall) → excluded

Result: Electrical Circuits I → (d)

Why not others:
- (a) Null — EEE101 satisfies the condition, so the result is not empty
- (b) — includes courses with count ≠ 1
- (c) — includes courses with 2 sections in 2019

Key rule: A correlated subquery re-executes for each row of the outer query, using the outer row's values in its WHERE clause.

AI-generated — may contain errors