Identifying courses with student enrollment that includes all students from a specific course

Courses
1

I’m working on a problem related to a student database and need help with an SQL query. The database has three tables:

student (sid, sname)
course (cid, cname)
enrol (sid, cid)

I’m trying to find all courses where the group of enrolled students includes all the students from a particular course C. In other words, I want to identify courses that have a superset of students compared to course C.

Can anyone help me figure out how to write this query? I’m not sure how to approach the comparison between sets of students in different courses. Any tips or suggestions would be really helpful!

2

hey there! i’ve dealt with similar queries before. you’ll wanna use subqueries and the NOT EXISTS clause for this. basically, check if there’s any student in course C who’s not in the other courses. if there isn’t, that course has all of C’s students. hope this helps point you in the right direction!

3

I’ve dealt with a similar problem previously. In my experience, the trick is to ensure that every student enrolled in course C is also present in any candidate course. You can do this by verifying that there is no student from course C missing in the other course using a NOT EXISTS construct.

For example:

SELECT DISTINCT e2.cid
FROM enrol e2
WHERE e2.cid <> 'C'
  AND NOT EXISTS (
    SELECT 1
    FROM enrol e3
    WHERE e3.cid = 'C'
      AND e3.sid NOT IN (
        SELECT e4.sid
        FROM enrol e4
        WHERE e4.cid = e2.cid
      )
  );

This method reliably filters courses that have a complete set of students from course C.

4

Hey ExploringStars! That’s an interesting problem you’ve got there. :thinking: I’m curious, have you tried any approaches so far?

I’m thinking we could tackle this with a clever use of set operations. What if we compared the set of students in each course to the set of students in course C? We’d be looking for courses where there’s no student in C that’s not also in that course.

Something like this might work:

SELECT e1.cid
FROM enrol e1
GROUP BY e1.cid
HAVING NOT EXISTS (
    SELECT e2.sid
    FROM enrol e2
    WHERE e2.cid = 'C'
    AND e2.sid NOT IN (
        SELECT e3.sid
        FROM enrol e3
        WHERE e3.cid = e1.cid
    )
)
AND e1.cid <> 'C';

What do you think about this approach? It’s basically checking if there are any students in C that aren’t in the other course, and if there aren’t any, it means that course includes all of C’s students.

Have you worked with subqueries like this before? I’d be interested to hear if you’ve tried something similar or if you have any other ideas!