1280 Students and Examinations

Description

Table: Students

• student_id is the primary key (column with unique values) for this table.
• Each row of this table contains the ID and the name of one student in the school.

Table: Subjects

• subject_name is the primary key (column with unique values) for this table.
• Each row of this table contains the name of one subject in the school.

Table: Examinations

• There is no primary key (column with unique values) for this table. It may contain duplicates.
• Each student from the Students table takes every course from the Subjects table.
• Each row of this table indicates that a student with ID student_id attended the exam of subject_name.

Problem Statement

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The result format is in the following example.

Example 1:

Input:

• Students table:

• Subjects table:

• Examinations table:

Output:

Explanation:

• The result table should contain all students and all subjects.
• Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time.
• Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam.
• Alex did not attend any exams.
• John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.

Solution

Here, the problem is asking to output all students with all subjects. This can be done with CROSS JOIN to get combination of student_id and student_name.

1SELECT s.student_id, s.student_name, sub.subject_name
2    FROM Students s
3    CROSS JOIN Subjects sub;

From Examinations table, you can also get the number of exams attended by each student in each subject. If the same student takes the same subject twice, you will have two combination of student_id and subject_name. You can use GROUP BY to get the count of exams attended by each student in each subject.

1SELECT student_id, subject_name, COUNT(1) AS attended_exams
2    FROM Examinations
3    GROUP BY student_id, subject_name;

Now, you can use LEFT OUTER JOIN to get the count of exams attended by each student in each subject. Finally, you order the results by student_id and subject_name. The final solution looks like this.

 1SELECT s.student_id, s.student_name, sub.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams
 2    FROM Students s
 3    CROSS JOIN Subjects sub
 4    LEFT OUTER JOIN (
 5        SELECT student_id, subject_name, COUNT(1) AS attended_exams
 6            FROM Examinations
 7            GROUP BY student_id, subject_name
 8    ) grouped 
 9    ON s.student_id = grouped.student_id AND sub.subject_name = grouped.subject_name
10    ORDER BY s.student_id, sub.subject_name;