Students and Examinations
Microsoft
Problem Statement
Academic administrators need a comprehensive overview of exam attendance. The report must show every student paired with every subject, even if the student never attended an exam for that specific subject.
Write an SQL query to find the number of times each student attended each exam.
Rules:
- The result must include every student from the Students table and every subject from the Subjects table.
- For student-subject pairs with no attendance, the count should be 0.
- Return the student_id, student_name, subject_name, and attended_exams.
- Results must be sorted by student_id and subject_name in ascending order.
Tests your understanding of
Basic SQL, CROSS JOIN, LEFT JOIN, Aggregation and NULL Handling
Input Tables
students
| student_id(INTEGER) | student_name(VARCHAR) |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 13 | John |
| 6 | Alex |
subjects
| subject_name(VARCHAR) |
|---|
| Math |
| Physics |
| Programming |
examinations
| student_id(INTEGER) | subject_name(VARCHAR) |
|---|---|
| 1 | Math |
| 1 | Physics |
| 1 | Programming |
| 1 | Physics |
| 1 | Math |
| 1 | Math |
| 2 | Programming |
| 1 | Physics |
| 1 | Math |
| 13 | Math |
| 13 | Programming |
| 13 | Physics |
| 2 | Math |
| 1 | Math |
Expected Output
| student_id(INTEGER) | student_name(VARCHAR) | subject_name(VARCHAR) | attended_exams(INTEGER) |
|---|---|---|---|
| 1 | Alice | Math | 5 |
| 1 | Alice | Physics | 3 |
| 1 | Alice | Programming | 1 |
| 2 | Bob | Math | 1 |
| 2 | Bob | Physics | 0 |
| 2 | Bob | Programming | 1 |
| 6 | Alex | Math | 0 |
| 6 | Alex | Physics | 0 |
| 6 | Alex | Programming | 0 |
| 13 | John | Math | 1 |
| 13 | John | Physics | 1 |
| 13 | John | Programming | 1 |
Tags
EasyBasic SQLCROSS JOINLEFT JOINAggregationNULL Handling
15-20 min
68%