Wsrtjtyk

I've been trying to do a "Not exist" approach to isolate the answer, but I think I've gone a bit too deep. I'm limited to essentially only using like really basic stuff for my intro to database class. So, basically only INNER JOIN, UNION, and CASE and maybe a few other stuff, but probably best assume like the bare bones stuff. We're not allowed to use coalesce yet.

Not sure if this is possible with just INNER JOIN, UNION, and CASE -> but I found a solution with an outer join (still quite simple).

Lets try to solve this in two steps.

1) Lets see which students have NOT finished all their classes

select s.code

  from  programs p inner join students s on (p.degree = s.degree)

                   left outer join exams e on (e.course = p.course and e.student = s.code)

  where e.course is null

We join programs with students and then left outer join the exams and filter only those rows where no exam row could be matched (which means the student has not taken the exam yet)

2) We get all students that are NOT returned by this query (meaning they have finished all required courses)

select

code, name

from student where code not in (



  select s.code

  from  programs p inner join students s on (p.degree = s.degree)

                   left outer join exams e on (e.course = p.course and e.student = s.code)

  where e.course is null



)

I like using aggregation for these things.

select s.code, s.name, s.degree

from students s join

     programs p

     on p.degree = s.degree join

     exams e

     on e.student = s.code and e.course = p.course

group by s.code, s.name, s.degree

having count(distinct e.course) = (select count(*) from programs p2 where p2.degree = p.degree);

Note that this includes the degree as well as the student. After all, students could double major.

In a similar vein to Preli's answer, I'd use a left join to highlight exams that had not been taken:

SELECT s.code, s.name, p.course

FROM

  students s

  INNER JOIN

  programs p 

  ON (p.degree = s.degree)

This is effectively the list of exams a student must take for the course

Now add on the exams they have taken, and leave nulls for those they havent:

SELECT s.code, s.name, p.course, e.course

FROM

  students s

  INNER JOIN

  programs p 

  ON (p.degree = s.degree)



  LEFT OUTER JOIN

  exams e

  ON e.student = s.code AND e.course = p.course

The results would look like:

Code   | Name | p.Course | e.Course

stu001 | John | Calc A   | Calc A

stu001 | John | Calc B   | Calc B

stu002 | Doe  | Calc A   | <null>

stu002 | Doe  | Calc B   | <null>

Now reduce it to a list of just students that have taken an exam for every course. We can do this by checking that the COUNT(e.course) is the same as the COUNT(p.course) because NULL isn't counted by COUNT() so any occurrences of null (no exam) in e.course reduce the count, compared to counting the overall occurrences of p.course (i could also have used count(*)):

SELECT s.code, s.name

FROM

  students s

  INNER JOIN

  programs p 

  ON (p.degree = s.degree)



  LEFT OUTER JOIN

  exams e

  ON e.student = s.code AND e.course = p.course



GROUP BY s.code, s.name

HAVING COUNT(p.course) = COUNT(e.course)

John's COUNT(p.Course) would be 2 and COUNT(e.course) would also be 2, so he shows (just once, because he's grouped. Doe's COUNT(p.course) is 2 but COUNT(e.course) is 0, because all values are null, and 2 != 0 so he is hidden