Students and Examinations - Problem

You have three tables: Students, Subjects, and Examinations.

The Students table contains student information with student_id as the primary key. The Subjects table contains all available subjects with subject_name as the primary key. The Examinations table records each exam attendance (may contain duplicates).

Write a SQL query to find the number of times each student attended each exam. The result should include all students and all subjects, even if a student never attended a particular exam (showing 0 count).

Return the result ordered by student_id and subject_name.

Table Schema

Students
Column Name Type Description
student_id PK int Unique identifier for each student
student_name varchar Name of the student
Primary Key: student_id
Subjects
Column Name Type Description
subject_name PK varchar Name of the subject
Primary Key: subject_name
Examinations
Column Name Type Description
student_id int Student who attended the exam
subject_name varchar Subject of the exam attended

Input & Output

Example 1 — Basic Student Exam Attendance
Input Tables:
Students
student_id student_name
1 Alice
2 Bob
13 John
Subjects
subject_name
Math
Physics
Programming
Examinations
student_id subject_name
1 Math
1 Physics
1 Programming
2 Programming
1 Physics
1 Math
13 Math
13 Programming
13 Physics
Output:
student_id student_name subject_name attended_exams
1 Alice Math 2
1 Alice Physics 2
1 Alice Programming 1
2 Bob Math 0
2 Bob Physics 0
2 Bob Programming 1
13 John Math 1
13 John Physics 1
13 John Programming 1
💡 Note:

The query creates all possible student-subject combinations using CROSS JOIN, then uses LEFT JOIN to match actual exam attendance. Alice attended Math twice and Physics twice, Bob only attended Programming once, and John attended each subject once. Notice Bob has 0 attendance for Math and Physics.
Example 2 — Student With No Exam Attendance
Input Tables:
Students
student_id student_name
1 Alice
2 Bob
Subjects
subject_name
Math
Physics
Examinations
student_id subject_name
1 Math
Output:
student_id student_name subject_name attended_exams
1 Alice Math 1
1 Alice Physics 0
2 Bob Math 0
2 Bob Physics 0
💡 Note:

This example shows how LEFT JOIN handles cases where students haven't attended certain exams. Bob never attended any exams, so all his counts are 0. Alice only attended Math once, so her Physics count is 0.

Constraints

  • 1 ≤ student_id ≤ 1000
  • 1 ≤ student_name.length ≤ 20
  • 1 ≤ subject_name.length ≤ 20
  • Each row in Examinations represents one exam attendance

Visualization

Tap to expand
Students and Examinations: Count AttendanceInput TablesStudentsidname1Alice2BobSubjectssubjectMathPhysicsExaminationsstudent_idsubject1Math1MathCROSS JOINLEFT JOINOutput: Attendance Countstudent_idstudent_namesubject_nameattended_exams1AliceMath21AlicePhysics02BobMath02BobPhysics0
Understanding the Visualization
1
Cross Join
Generate all student-subject pairs
2
Left Join
Match with actual exam records
3
Count & Group
Count attendance per combination
Key Takeaway
🎯 Key Insight: Use CROSS JOIN to ensure all combinations appear, then LEFT JOIN to count actual occurrences

Related Problems

Asked in
Amazon 12 Google 8 Microsoft 6
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen