Division: Students Who Completed All Required Courses

Hardsql

required_courses(course_id) lists every course a program requires. enrollments(student_id, course_id) records what each student has taken. Write a query returning the student_id of every student who has completed every required course (they may have taken other courses too), ordered ascending.

sql
CREATE TABLE enrollments (student_id INTEGER NOT NULL, course_id INTEGER NOT NULL);
CREATE TABLE required_courses (course_id INTEGER NOT NULL);

Sample data (this is what your query runs against when you press Run):

sql
INSERT INTO required_courses (course_id) VALUES (101), (102), (103);
INSERT INTO enrollments (student_id, course_id) VALUES
(1, 101), (1, 102), (1, 103),
(2, 101), (2, 102),
(3, 101), (3, 102), (3, 103), (3, 104);

Example 1

Input

(none)

Output

student_id
1
3

This is relational Division: filter enrollments to required courses, group by student, and keep only students whose distinct-required-course count equals the total number required. Reference: SELECT student_id FROM enrollments WHERE course_id IN (SELECT course_id FROM required_courses) GROUP BY student_id HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM required_courses) ORDER BY student_id;

Input (stdin)

Output

Run your code to see output here...