Division answers "for all" questions, and SQL has no keyword for it. Write it by counting.
What to build
A query that computes Takes ÷ Required: the students who took every required course.
Requirements
- Count only courses that are in
Required. - Count each course once per student, even if it appears twice in
Takes. - Return the students whose count equals the number of required courses, sorted by name.
Tables
Each test fills these tables with its own rows, then runs your SQL:
CREATE TABLE Takes (
student TEXT NOT NULL,
course TEXT NOT NULL
);
CREATE TABLE Required (
course TEXT PRIMARY KEY
);
Output
One column, student, in alphabetical order. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).
Constraints
Required has at least one course. A student may take courses that are not required.
Example
When a test starts with:
INSERT INTO Takes VALUES ('Rahim', 'CSE101'), ('Rahim', 'MAT101'), ('Nadia', 'CSE101'), ('Karim', 'CSE101'), ('Karim', 'MAT101'), ('Karim', 'PHY101');
INSERT INTO Required VALUES ('CSE101'), ('MAT101');
your SQL should print:
student
-------
Karim
Rahim
(2 rows)
Why it works
After the join, each student's group holds only their required courses. A student has all of them exactly when the number of different ones equals the size of Required.
Examples
CREATE TABLE Takes (
student TEXT NOT NULL,
course TEXT NOT NULL
);
CREATE TABLE Required (
course TEXT PRIMARY KEY
);
INSERT INTO Takes VALUES ('Rahim', 'CSE101'), ('Rahim', 'MAT101'), ('Nadia', 'CSE101'), ('Karim', 'CSE101'), ('Karim', 'MAT101'), ('Karim', 'PHY101');
INSERT INTO Required VALUES ('CSE101'), ('MAT101');
student ------- Karim Rahim (2 rows)
CREATE TABLE Takes (
student TEXT NOT NULL,
course TEXT NOT NULL
);
CREATE TABLE Required (
course TEXT PRIMARY KEY
);
INSERT INTO Takes VALUES ('A', 'X');
INSERT INTO Required VALUES ('X'), ('Y');
student ------- (0 rows)
Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.
Hints
Hint 1
Join `Takes` to `Required`, so that only required courses are counted.
Hint 2
Group by student and count the different courses each one took.
Hint 3
A student took them all exactly when that count equals the number of rows in `Required`.
Solution
Show one correct solution (try it yourself first)
SELECT t.student FROM Takes t JOIN Required r ON r.course = t.course GROUP BY t.student HAVING COUNT(DISTINCT t.course) = (SELECT COUNT(*) FROM Required) ORDER BY t.student;