ITPEC FE Morning October 2021 Question 26
Tables Course and Section were created to record the course and section information of a university, respectively, as shown below; the primary keys are underlined.
Course (cid, title, credits)
Section (cid, secid, semester, year)
The current status of those tables are shown below.
Course
Section
When the SQL shown below is executed, which of the following tables is obtained as the output?
Explanation
Electrical Circuits I — only EEE101 has exactly one section in 2019.
The subquery is correlated: for each course row C it counts the Section rows with that cid in 2019, and the outer query keeps the title when the count is 1.
- •CSE101: Spring 2019 and Fall 2019, count 2
- •CSE102: both sections in 2018, count 0
- •CSE103: Spring 2019 and Fall 2019, count 2
- •EEE101: Spring 2019, count 1
- •EEE102: Spring 2019 and Fall 2019, count 2
Answer (d)
Why not others:
- •(a) Null — EEE101 satisfies the condition, so the result has a row; an empty result would show no rows, not the value Null
- •(b) — lists the courses whose sections all fall in 2019, but CSE103 and EEE102 have two 2019 sections each, so their count is 2
- •(c) — includes CSE101 and EEE102 with two 2019 sections and CSE102 with none
Key rule: Evaluate a correlated subquery once per outer row, substituting that row's values, and check its result against the outer condition.
Databases · AI-written, may contain errors
This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.