1280. Students and Examinations
SQL 50 · Basic Joins (day 2). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.
Your work
Hints
Open one at a time. Try to finish with as few as you can.
1. What does one output row represent?
One (student, subject) pair, all pairs.
2. Approach
CROSS JOIN Students and Subjects, LEFT JOIN Examinations on both keys, GROUP BY both, COUNT(e.subject_name).
3. The trap
COUNT(*) gives 1 for pairs with no exam. Order by student_id, subject_name.
Reference solution
Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.
Show the MySQLPostgreSQL solution
SELECT s.student_id, s.student_name, sub.subject_name,
COUNT(e.subject_name) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e
ON e.student_id = s.student_id
AND e.subject_name = sub.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;