《Database System Concept》习题SQL查询：我的写法是否符合要求？

云原生阿岚

2026-5-25

Is the Multi-Column Subquery Required Here?

No, you don’t strictly have to use the exact subquery structure from the参考答案, but your current approach won’t give you the correct result. Here’s why:

The Problem With Your Query

Your query uses course_id IN (SELECT course_id FROM teaches WHERE ID=10101), which would count all students who took any section of courses that teacher 10101 has ever taught—including sections taught by other teachers in different semesters or years. That’s not what the question asks for; we need students who took sections specifically taught by teacher 10101.

Why the参考答案 Works

The参考答案 uses the combination of (course_id, sec_id, semester, year) because these four columns together uniquely identify a single course section in both the teaches and takes tables. This ensures you’re only matching students who enrolled in sections that teacher 10101 actually led.

Equivalent Alternatives (Not Just the Exact Form)

You can achieve the same correct result with other SQL structures, as long as you match all four section-identifying columns:

Using a JOIN (Often More Readable)

SELECT COUNT(DISTINCT t.ID)
FROM takes t
JOIN teaches te 
  ON t.course_id = te.course_id 
  AND t.sec_id = te.sec_id 
  AND t.semester = te.semester 
  AND t.year = te.year
WHERE te.ID = 10101;

Using an EXISTS Subquery

SELECT COUNT(DISTINCT ID)
FROM takes t
WHERE EXISTS (
    SELECT 1 
    FROM teaches te
    WHERE te.ID = 10101
      AND te.course_id = t.course_id
      AND te.sec_id = t.sec_id
      AND te.semester = t.semester
      AND te.year = t.year
);

Key Takeaway

The critical thing is that you can’t rely solely on course_id to link the two tables—you need to match all columns that define a unique course section. The参考答案’s form is one valid way to do this, but there are other equally correct approaches.