medium Students Who Attended All Courses

This is relational division: “find the students whose set of taken courses contains the full set of offered courses,” and the cheap way to test set-containment is to count — if a student’s number of distinct required courses equals the total number of required courses, their set must cover all of them, because you cannot reach that count without hitting every course at least once.

The problem

Given enrollments and a master list of courses, return the students who enrolled in every course.

Enrollment

Course = { 200, 300 }  →  total required = 2

Expected output: 100, 300.

The counting solution

SELECT student_id
FROM   Enrollment
GROUP  BY student_id
HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM Course)
ORDER  BY student_id;

The right-hand side computes the divisor once (the count of all courses). For each student, HAVING keeps only those whose distinct-course count matches it. COUNT(*) FROM Course assumes course_id is the unique key of Course — if it could contain duplicates, use COUNT(DISTINCT course_id) there too so the divisor is the size of the real course set.

Traced, student by student

Divisor = COUNT(*) FROM Course = 2.

Student 200 enrolled in only course 300, so its distinct count (1) falls short of the divisor (2) and is filtered out. Students 100 and 300 each cover the whole set.

The other canonical form: double-negation with NOT EXISTS

Relational algebra has no native “divide” operator, so the textbook translation expresses division as a double negative: a student qualifies when there is no course that the student did not take.

SELECT s.student_id
FROM   (SELECT DISTINCT student_id FROM Enrollment) s
WHERE  NOT EXISTS (
         SELECT 1
         FROM   Course c
         WHERE  NOT EXISTS (
                  SELECT 1
                  FROM   Enrollment e
                  WHERE  e.student_id = s.student_id
                    AND  e.course_id  = c.course_id
         )
);

Read it inside-out: the inner NOT EXISTS means “student s did not take course c”; the outer NOT EXISTS means “there is no such missing course.” Both queries return the same answer here (100, 300). The counting form is shorter and usually faster on this schema; the NOT EXISTS form is the one that generalizes — it works unchanged when “cover the set” is defined by a join condition rather than equal counts, and it short-circuits on the first missing course instead of aggregating every row.

Why the count must be DISTINCT (the naive bug)

It is tempting to write HAVING COUNT(course_id) = (SELECT COUNT(*) FROM Course). That breaks the moment Enrollment can hold duplicate (student, course) pairs — e.g. a student who re-enrolls in course 300 twice and never touches course 200 would have COUNT(course_id) = 2 and be wrongly reported as having taken all courses. COUNT(DISTINCT course_id) counts the set of courses, not the number of enrollment rows, which is the quantity relational division actually requires.

Pitfalls

• Plain COUNT over duplicates. As above — always count DISTINCT course_id on both sides unless a uniqueness constraint guarantees no duplicates.
• Stale or extra courses in the divisor. The divisor is whatever SELECT COUNT(*) FROM Course returns. If Course contains retired courses nobody can enroll in, no one will ever match. Filter the divisor to the same population you are testing against.
• Students with zero enrollments vanish. A student who took nothing has no rows in Enrollment, so GROUP BY never produces a group for them. That is correct here (they took no courses), but if the question were “all students, with a flag,” you would need to start from a Student table and LEFT JOIN.
• Comparing counts is not the same as comparing sets. The count trick is valid only because the student’s courses are a subset of Course (foreign-key enforced). If a student could enroll in a course not listed in Course, equal counts would no longer prove coverage — you would need the NOT EXISTS form or an extra WHERE course_id IN (SELECT course_id FROM Course) guard.

Takeaways

• “All of X” problems are relational division; the count form (HAVING COUNT(DISTINCT k) = total) is the idiomatic, fast solution when the candidate set is a guaranteed subset of the target.
• The double-NOT EXISTS form is the algebraic ground truth: “no required element is missing.” Reach for it when counting cannot prove coverage or when you need early termination.
• DISTINCT is load-bearing: division is about sets, so duplicate rows must be collapsed on both sides of the equality.

Re-authored and deepened for this guide. Problem adapted from the LeetCode-style “Students Who Attended All Courses” exercise. The relational-division framing, the count-equals-cardinality theorem, and the double-negation translation follow Garcia-Molina, Ullman & Widom, Database Systems: The Complete Book (2nd ed.), and Date, An Introduction to Database Systems; the count-vs-NOT EXISTS performance trade-offs are standard SQL practitioner lore (Joe Celko, SQL for Smarties).