[SQL] Students and Examinations(LeetCode/Oracle)

안녕하세요!

 

이번 포스팅은 LeetCode에 있는 Students and Examinations 문제를 OracleDB로 풀어보려고 합니다!

 

(모든 문제는 Oracle로 풀이하겠습니다.)

 

1. 문제 링크 : https://leetcode.com/problems/students-and-examinations/description/

 

2. 문제

Column name	Type
student_id	int
student_name	varchar

Column name	Type
subject_name	varchar

Column name	Type
student_id	int
subject_name	varchar

[문제] 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.

각 학생이 각 시험에 참여한 횟수를 조회하는 문제입니다.

 

3. 제출 쿼리 및 설명

 

1) STUDENTS 테이블과 SUBJECTS 테이블을 CROSS JOIN 합니다. 

SELECT *
FROM STUDENTS ST CROSS JOIN SUBJECTS SU

2) 1번 쿼리에서 조인된 테이블과 EXAMINATIONS 테이블을 LEFT JOIN 합니다.

FROM (
  SELECT *
  FROM STUDENTS ST CROSS JOIN SUBJECTS SU
) S, EXAMINATIONS E
WHERE S.STUDENT_ID = E.STUDENT_ID(+)
AND S.SUBJECT_NAME = E.SUBJECT_NAME(+)

최종) STUDENT_ID, STUDENT_NAME, SUBJECT_NAME으로 그룹화한 다음 각 과목 수를 집계합니다.

SELECT S.STUDENT_ID, S.STUDENT_NAME, S.SUBJECT_NAME, COUNT(E.SUBJECT_NAME) AS ATTENDED_EXAMS
FROM (
  SELECT *
  FROM STUDENTS ST CROSS JOIN SUBJECTS SU
) S, EXAMINATIONS E
WHERE 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 S.STUDENT_ID, S.SUBJECT_NAME
;