SQL Interview Questions – Students and Examinations

Problem Description –

Write an SQL query to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The query result format is in the following example.

Difficulty Level – Easy

Problem Link – Students and Examinations

Solution –

with student_subject as (
SELECT
    student_id,
    student_name,
    subject_name
FROM Students, Subjects
ORDER BY 1
), student_exam as (
SELECT
    student_id,
    subject_name,
    COUNT(*) as attended_exams
FROM Examinations
GROUP BY 1, 2 
ORDER BY 1
) 
SELECT
    ss.student_id,
    ss.student_name,
    ss.subject_name,
    IFNULL(se.attended_exams,0) as attended_exams
FROM student_subject as ss LEFT JOIN student_exam as se
ON ss.student_id = se.student_id
AND ss.subject_name = se.subject_name
ORDER BY 1, 3

To solve this problem first we have to cross join of students and subjects table.

SELECT
    student_id,
    student_name,
    subject_name
FROM Students, Subjects

Next we need to find the number of times each student attended each exam.

SELECT
    student_id,
    subject_name,
    COUNT(*) as attended_exams
FROM Examinations
GROUP BY 1, 2 
ORDER BY 1

Now, all we need to do is join these two tables using a left join and replace null values in attended exams with zeros as Alex did not appear for any exams.