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?

Yes. Only courses that are enrolled by ALL automative engineering students. — draculaxx1, Jun 14 '20 at 21:56
As a tip for future questions, if you can add sample data as DDL/DML statements it makes for a much higher quality question. And then have expected results as formatted text. — Dale K, Jun 14 '20 at 22:27
@DaleK thank you i am trying now. If i can't solve it i will do as you said. Thanks. — draculaxx1, Jun 14 '20 at 22:32
@saarp your proposed edit is for a different RDBMS than tagged by OP which is a fundamental change to the question. Please be more careful in future! — Dale K, Jun 15 '20 at 00:33

score 1 · Accepted Answer · answered Jun 14 '20 at 22:00

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.

score 0 · Answer 2 · answered Jun 14 '20 at 22:38

For a slightly different approach you can apply 2 logic checks:

At least 1 member of 'Automotive Engineering' is enrolled in this course
No member of 'Automotive Engineering' is not enrolled in this course

declare @Department varchar(64) = 'Automotive Engineering';

select C.CourseName
from Courses C
-- A student from the selected department is enrolled in this course
where exists (
    select 1
    from Enroll E
    inner join Students S on S.StudentId = E.StudentId
    where S.Department = @Department and E.CourseID = C.CourseID
)
-- And no student from the selected department is not enrolled in this course.
and not exists (
    select 1
    from Students S
    where S.Department = @Department
    and not exists (
        select 1
        from Enroll E
        where E.StudentId = S.StudentId and E.CourseId = C.CourseID
    )
);

I checked the first one and it works. Can u tell me the difference from the below code `select c.* from courses c where (select count(*) from enroll e inner join students s on s.StudentID = e.StudentID where e.CourseID = c.CourseID and s.department = 'Hukuk' ) >= 1` — draculaxx1, Jun 14 '20 at 22:45
Your query is just checking that **one** member of the selected department is enrolled in a course, the 2 answers here are checking that **every** member of the department is enrolled in a course. — Dale K, Jun 14 '20 at 22:47

Query to find courses that all members are enrolled in

2 Answers2