Drill 6 of 12 in General questions
0

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…