Query to find courses that all members are enrolled in

Question

I have a database which contains 5 tables.

CREATE TABLE COURSES 
(
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Credit  NUMBER(10,2)
);

CREATE TABLE TEACHERS 
(
    SSN VARCHAR(100)
    Name VARCHAR(100)
);

CREATE TABLE OFFER 
(
    CourseID NUMBER(10), 
    SSN VARCHAR(100)
);

CREATE TABLE STUDENTS 
(
    StudentID NUMBER(10),
    Name VARCHAR(100),
    Department NUMBER(10)
);

CREATE TABLE ENROLL 
(
    StudentID NUMBER(10),
    CourseID NUMBER(10),
    Semester VARCHAR(100)
);

And I want to find the names of courses that are enrolled by all “Automotive Engineering” students.

I tried the below statements but it shows nothing.

SELECT C.CourseID
FROM COURSES C
WHERE NOT EXISTS (SELECT S.StudentID
                  FROM STUDENTS S
                  WHERE NOT EXISTS (SELECT E.CourseID
                                    FROM ENROLL E
                                    WHERE S.Department = 'Automotive Engineering'
                                      AND E.CourseID = C.CourseID 
                                      AND E.StudentID = S.StudentID)
                 )

I execute the above query but it shows nothing. Can someone help me from here?

Answer 1

This is a kind of relational division problem. Here is one approach using correlated subqueries for filtering

select c.*
from courses c
where 
    (
        select count(*) 
        from enroll e 
        inner join students s on s.student_id = e.student_id
        where e.course_id = c.course_id and s.department = 'Automotive Engineering'
    ) = (
        select count(*)
        from students s
        where s.department = 'Automotive Engineering'
    )

The first subquery computes how many Automative Engineering students enroll for the given course; we then ensure that this gives the same count as the total number of sudents in the department.