[LeetCode] SQL 50 - 1280. Students and Examinations (MySQL)

문제 설명

- 첫 번째 테이블 명 : Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+

- 테이블 설명 :

student_id 는 이 테이블의 기본 키 입니다.
이 테이블의 각 행에는 학교에 다니는 한 학생의 ID와 이름이 포함되어 있습니다.


- 두 번째 테이블 명 : Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+

- 테이블 설명 : 

subject_name은 이 테이블의 기본 키 입니다.
이 테이블의 각 행에는 학교의 한 과목의 이름이 포함되어 있습니다.


- 세 번째 테이블 명 : Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+

- 테이블 설명 :

이 테이블에는 기본 키가 없습니다. 중복된 내용이 포함되어 있을 수 있습니다.
Students 테이블의 각 학생은 Subjects 테이블의 모든 강좌를 수강합니다.
이 테이블의 각 행은 ID가 Student_id인 학생이 subject_name의 시험에 참석했음을 나타냅니다.


- 문제 :

각 학생이 각 시험에 참석한 횟수를 구하는 풀이를 작성하세요.
student_id  와 subject_name 으로 정렬된 결과 테이블을 반환합니다.

 

입출력 예시

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              |
+------------+--------------+--------------+----------------+

- 설명 :

결과 테이블에는 모든 학생과 모든 과목이 포함되어야 합니다.
Alice는 수학 시험에 3번, 물리학 시험에 2번, 프로그래밍 시험에 1번 참석했습니다.
Bob은 수학 시험에 1번, 프로그래밍 시험에 1번 참석했지만 물리학 시험에는 참석하지 않았습니다.
Alex는 어떤 시험에도 참석하지 않았습니다.
John은 수학 시험 1회, 물리학 시험 1회, 프로그래밍 시험 1회에 참석했습니다.

 

풀이

-- cross join, left join 사용 runtime 1028 ms

select st.student_id, st.student_name, sub.subject_name, count(ex.subject_name) as attended_exams
from Students st
cross join Subjects sub
left join Examinations ex
on st.student_id = ex.student_id
and sub.subject_name = ex.subject_name
group by student_id, student_name, subject_name
order by student_id, subject_name
;

 

노트 (CROSS JOIN)

출처 : https://hongong.hanbit.co.kr/sql-%ea%b8%b0%eb%b3%b8-%eb%ac%b8%eb%b2%95-joininner-outer-cross-self-join/

CROSS JOIN

한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능

조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 수이다.
카티션 곱(CARTESIAN PRODUCT)라고도 함

출처 : https://hongong.hanbit.co.kr/sql-%ea%b8%b0%eb%b3%b8-%eb%ac%b8%eb%b2%95-joininner-outer-cross-self-join/

SQL 기본 문법: JOIN(INNER, OUTER, CROSS, SELF JOIN)

 

출처 : https://leetcode.com/problems/students-and-examinations/description/?envType=study-plan-v2&envId=top-sql-50