[SQL/코드카타] 모든 조합의 CROSS JOIN과 참여한 시험을 LEFT JOIN

문제

 

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.

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.

• 각 학생들이 참석한 각각의 시험의 횟수를 구해라
• student_id와 subject_name 으로 정렬해라
• 결과는 모든 학생들과 모든 과목을 포함해야만 한다.

▼ Output

 

최초쿼리

SELECT 
    s.student_id, 
    s.student_name,
    e.subject_name,
    COUNT(e.subject_name) as attended_exams
FROM students s
LEFT JOIN examinations e ON s.student_id=e.student_id
LEFT JOIN subjects j ON e.subject_name=j.subject_name
GROUP BY s.student_id, s.student_name, e.subject_name
ORDER BY s.student_id, e.subject_name;

▼ 출력결과

🚨 학생별로 모든 과목들이 표시되지 않고 있음
-> 시험을 보지 않았더라도 0으로 표시하고 보여줘야 함

 

정답쿼리

SELECT 
    s.student_id, 
    s.student_name,
    j.subject_name,
    COUNT(e.subject_name) AS attended_exams
FROM students s
CROSS JOIN subjects j 
LEFT JOIN examinations e 
    ON e.student_id=s.student_id
    AND j.subject_name=e.subject_name
GROUP BY s.student_id, s.student_name, j.subject_name
ORDER BY s.student_id, j.subject_name;

 

적용 핵심 함수

• COUNT(col)
• CROSS JOIN
• LEFT JOIN
• GROUP BY

쿼리문 설명

- CROSS JOIN을 사용해서 students 테이블의 student_id, student_name과 subjects 테이블의 subject_name의 모든 조합을 생성해야 함!!!
- 다음 LEFT JOIN으로 모든 조합에 examinations를 가져다 붙임! 
- 조인 조건을 모두 걸어줘야 함.
- 집계합수 COUNT를 사용하므로, SELECT절의 컬럼들은 GROUP BY절에서 묶어줘야 함
- 출력하는 subject_name은 모든 과목을 보여줘야 하기에 SELECT절에서 j.subject_name 을 가져다 사용하고,
- 각 학생이 참여한 과목의 시험 수는 LEFT JOIN으로 가져다 붙인 e.subject_name으로 COUNT를 해야함!!!

 

끝.