Students and Examinations

Students and Examinations

What is this problem about?

"Students and Examinations" is a SQL problem that requires you to generate a report showing how many times each student attended each exam. You are typically given three tables: Students, Subjects, and Examinations. The goal is to produce a list where every student is paired with every subject, even if they never took the exam for that subject. For each pair, you must display the student ID, student name, subject name, and the count of exams attended.

Why is this asked in interviews?

This question is widely used by companies like Apple, Microsoft, and Amazon to test a candidate's knowledge of CROSS JOIN and LEFT JOIN. It is specifically designed to see if you know how to handle "missing data"—situations where you need to show a zero count for entries that don't exist in the activity table. It's a fundamental reporting task that demonstrates your ability to combine disparate tables into a cohesive summary.

Algorithmic pattern used

The key pattern here is creating a Cartesian Product followed by a Left Join.

1. Use a CROSS JOIN between the Students and Subjects tables to create every possible combination of student and subject.
2. LEFT JOIN this result with the Examinations table on both StudentID and SubjectName.
3. Use GROUP BY on the StudentID and SubjectName.
4. Use COUNT(Examinations.subject_name) (or similar) to count the actual exam appearances. Crucially, counting a column from the right side of a LEFT JOIN will correctly return 0 for missing rows, whereas COUNT(*) would incorrectly return 1.

Example explanation (use your own example)

Students: [Alice, Bob] Subjects: [Math, Physics] Exams: [Alice took Math, Alice took Math]

1. CROSS JOIN gives: (Alice, Math), (Alice, Physics), (Bob, Math), (Bob, Physics).
2. LEFT JOIN with Exams:
   • (Alice, Math) matches twice. Count = 2.
   • (Alice, Physics) matches zero times. Count = 0.
   • (Bob, Math) matches zero times. Count = 0.
   • (Bob, Physics) matches zero times. Count = 0. Final report shows all 4 combinations with their respective counts.

Common mistakes candidates make

The most common mistake is using an INNER JOIN or a simple LEFT JOIN without the CROSS JOIN first. This results in the final report only showing students who actually took at least one exam, or subjects that were actually taken. Another mistake is using COUNT(*) in the aggregation, which counts the row created by the LEFT JOIN itself, turning zeros into ones. Candidates also sometimes forget to order the results as specified in the problem statement.

Interview preparation tip

When solving the "Students and Examinations" database problem, remember the "Cross Join then Left Join" strategy. It is the standard way to ensure all possible pairs are represented in a report. Always double-check your COUNT() logic to ensure that students with zero exams are correctly identified as such. This is the part of the query that interviewers watch most closely.