Query help: Identifying students enrolled only in CS220

Courses
1 
SELECT e.student_id
FROM enrollments e
WHERE e.course_code = 'CS220'
  AND NOT EXISTS (
    SELECT 1
    FROM enrollments other
    WHERE other.student_id = e.student_id
      AND other.course_code != 'CS220'
  )

I’m trying to understand why this query successfully returns only those students who are exclusively enrolled in CS220. Initially, I thought the query might also list students enrolled in other courses. Could someone explain how the NOT EXISTS clause filters out students taking additional classes? I’m a bit confused on how the logic works.

Thanks for any help—I’m still getting familiar with SQL.

2

The query is quite clever in its approach as it uses a correlated subquery with the NOT EXISTS clause to effectively filter out any students who are enrolled in courses other than CS220. The main part of the query retrieves student IDs from the enrollments table where the course code is ‘CS220.’ It then checks, for each student, whether there is any record in the table showing enrollment in a course other than CS220. If such a record exists, the condition in NOT EXISTS fails and that student is excluded from the results. This ensures that only students exclusively enrolled in CS220 are returned.

3

Hey there, fellow SQL explorer! :blush:

I totally get why you’re scratching your head over this query. It’s a bit tricky at first glance, right? But don’t worry, we’ve all been there!

So, here’s the cool thing about this query - it’s like playing a game of ‘Spot the Difference’ with the enrollments. The NOT EXISTS part is basically saying, ‘Hey, show me the CS220 students, but only if you can’t find them in any other course.’

It’s kinda like looking for unicorns in a horse stable. You’re checking each CS220 student (your potential unicorn) and making sure they don’t have a ‘normal horse’ disguise (other course enrollments).

I’m curious though - what made you think it might list students in other courses too? And have you tried running this query on some sample data? Sometimes playing around with the data can really help things click!

Keep at it - you’re asking great questions, and that’s how we all learn this SQL wizardry! :man_mage::sparkles:

4

yo, that query’s pretty slick! it’s like a bouncer at a club, only letting in the CS220-only crowd. the NOT EXISTS part is checking if each CS220 student has a VIP pass (enrollment) for any other course. if they don’t, they’re in! it’s a neat trick for filtering out the multi-course party animals. have u tried tweaking it for different scenarios? might help u get a better feel for how it works