inblog logo
|
silver
    SQL문제풀기

    [SQL문제풀기] Students and Examinations

    silver's avatar
    silver
    Oct 18, 2025
    [SQL문제풀기] Students and Examinations
    Contents
    문제MySQL

    문제

    Students and Examinations - LeetCode
    Can you solve this real interview question? Students and Examinations - Table: Students +---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+ 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 +--------------+---------+ | Column Name | Type | +--------------+---------+ | subject_name | varchar | +--------------+---------+ 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 +--------------+---------+ | Column Name | Type | +--------------+---------+ | student_id | int | | subject_name | varchar | +--------------+---------+ 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.   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: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | | 13 | John | | 6 | Alex | +------------+--------------+ Subjects table: +--------------+ | subject_name | +--------------+ | Math | | Physics | | Programming | +--------------+ Examinations table: +------------+--------------+ | 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 | +------------+--------------+ Output: +------------+--------------+--------------+----------------+ | 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 | +------------+--------------+--------------+----------------+ 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.
    Students and Examinations - LeetCode
    https://leetcode.com/problems/students-and-examinations/description/?envType=study-plan-v2&envId=top-sql-50
    Students and Examinations - LeetCode

    MySQL

    내가 작성한 정답

    select s.student_id, s.student_name, s.subject_name,
        sum(case when e.subject_name is null then 0 
		        else 1 end) attended_exams
from (select st.student_id, st.student_name,s.subject_name
        from Students st
        cross join Subjects s) s
left join Examinations e 
on s.student_id = e.student_id and s.subject_name = e.subject_name
group by s.student_id, s.student_name, s.subject_name
order by 1, 3;
    Share article

    silver

    RSS·Powered by Inblog