NUHMAN.com

📋 Problem Description:

You're given three tables from a school database:

1. Students Table
2. Stores student IDs and their names.

sql

+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
| 13         | John         |
| 6          | Alex         |
+------------+--------------+

1. Subjects Table
2. Contains a list of all subjects offered in the school.

sql

+--------------+
| subject_name |
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+

1. Examinations Table
2. Records each time a student attended an exam. It can have duplicate entries, indicating a student took the same subject exam multiple times.

sql

+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1          | Math         |
| 1          | Physics      |
| 1          | Programming  |
| 2          | Programming  |
| 1          | Physics      |
| 1          | Math         |
| 13         | Math         |
| 13         | Programming  |
| 13         | Physics      |
| 2          | Math         |
| 1          | Math         |
+------------+--------------+

🧠 Objective:

Write an SQL query that returns a comprehensive table with the number of times each student attended each subject's exam.

The result should:

• Include all students and all subjects, even if a student didn’t attend a particular exam (count as 0).
• Be ordered by student_id and subject_name.

✅ Expected Output:

sql

+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 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              |
+------------+--------------+--------------+----------------+

🧾 SQL Solution:

sql

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 s.student_id = e.student_id AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;

🔍 Explanation:

• CROSS JOIN ensures all possible combinations of students and subjects.
• LEFT JOIN pulls in matching rows from Examinations, if any.
• COUNT(e.subject_name) gives the number of times a student attended an exam for that subject. If there’s no match, it counts as 0.
• GROUP BY is used to aggregate exam counts per student-subject pair.
• ORDER BY sorts the final result as required.