Interview Question Bank · CROSS JOIN · NOT EXISTS · NULL scores
6. Exams each student missed
MediumGeneralQuestion #198
Concept: CROSS JOIN · NOT EXISTS · NULL scoresshow
To find what didn't happen, first build every combination that could have happened with a CROSS JOIN, then remove the ones that did with NOT EXISTS. Starting from the facts table can't work: a missing fact has no row to start from. Be clear about what counts as happening: a row with a NULL measurement may mean the event never took place.
Example (different data):
SELECT v.name, s.shift_date FROM volunteers v CROSS JOIN shifts s WHERE NOT EXISTS ( SELECT 1 FROM shift_signups x WHERE x.volunteer_id = v.id AND x.shift_id = s.id );
Your task
Fernbrook Academy is scheduling resits. Every student is expected to sit all four end-of-year exams, and a result with no score means the student registered but didn't turn up.
Return student (the student's name) and subject for every exam a student did not sit, meaning there is no result for that student and exam with a score. Sort by student, then exam_date.
Starting Postgres in your browser…
Checking your access…