Drill 9 of 12 in Education questions
0

Interview Question Bank · Relational division · HAVING with FILTER · NULLs in bool_and

9. Students who passed every course they took

HardEducationQuestion #225

Concept: Relational division · HAVING with FILTER · NULLs in bool_andshow

'Every' questions are relational division: group each person's rows and keep the group only if the rows meeting the condition are all of them, as in HAVING COUNT(*) FILTER (WHERE ok) = COUNT(*). Watch NULLs: bool_and and NOT EXISTS (... failed ...) both treat an unknown outcome as harmless, so rows with no result yet slip through. A person with no rows at all passes NOT EXISTS vacuously, so start from the rows, not the people.

Example (different data):

SELECT supplier_id, COUNT(*) AS shipments
FROM shipments
GROUP BY supplier_id
HAVING COUNT(*) FILTER (WHERE arrived_on <= promised_on) = COUNT(*);

Your task

Brightpath Academy offers an 'all clear' scholarship to students who passed every course they enrolled in. A course is passed with a final_score of 60 or more; an enrollment still in progress or withdrawn (no final_score) doesn't count as passed.

For every student with at least one enrollment where every enrollment has final_score >= 60, return student (name) and courses_passed (their number of enrollments). Sort by student.

Starting Postgres in your browser…

Checking your access…