(25.03.13)

문제이해

Students테이블은 student_id가 pk이다. Subjects테이블은 교과목 정보 하나만을 가지고 있다. Examinations테이블은 student가 가지는 subject_name을 가지고 있다. 그리고 중복이 가능하다.

시험 별 참석하는 학생의 수를 찾아내어라. student_id와 subject_name에 따라 정렬시켜라.

문제 접근

count가 아니라 우선 Students와 Examinations를 cross join한 결과를 얻어보았다.

SELECT st.student_id, st.student_name, ex.subject_name FROM Students as st
CROSS JOIN Examinations as ex;

---

(25.03.17)
모든 Student는 모든 Subjects에 대한 attended_exams정보를 가져야 한다. 우선 기존에 CROSS JOIN한 Examinations 대신 Subjects로 하여 결과 테이블의 토대를 만들고, ORDER BY를 이용해 정렬을 따라주자.

SELECT st.student_id, st.student_name, sb.subject_name FROM Students as st
CROSS JOIN Subjects as sb
ORDER BY st.student_id, sb.subject_name;

이제 attended_exams를 만들어보자. 우선 내장함수 COUNT를 써야하니 GROUP BY조건으로 student_id와 subject_name이 같은 경우를 조건으로 설정하자. 그런데 문제가 발생했다.

# Write your MySQL query statement below

SELECT st.student_id, st.student_name, sb.subject_name, COUNT(?) as attended_exams FROM Students as st, Examinations
CROSS JOIN Subjects as sb
GROUP BY st.student_id=Examinations.student_id and Examinations.subject_name=sb.subject_name
ORDER BY st.student_id, sb.subject_name;

어떻게 해야 count를 잘 설정할 수 있을까? 마음만 같아선 Examinations에 모든 항목들에서 student_id와 subject_name이 일치하는 것을 세면 되는데.. 여기에서 for문을 쓸 수 없으니 JOIN을 이용해 해결해야 할거 같은데..

여러모로 RIGHT JOIN 등도 시도해봤는데 방향성을 잘못잡겠어서 GPT선생의 도움이 필요해보인다.

아래는 그래도 최대한 내가 해결해보고 싶어 GPT에서 연달아 JOIN을 두번 쓴 것을 보고 다시 시도해본 코드이다

SELECT st.student_id, st.student_name, sb.subject_name, COUNT(st.student_id) as attended_exams FROM Students as st
CROSS JOIN Subjects as sb
LEFT JOIN Examinations as ex
    ON st.student_id=ex.student_id
        and sb.subject_name=ex.subject_name
GROUP BY ex.subject_name, st.student_id
ORDER BY st.student_id, sb.subject_name;

그 후 도저히 모르겠어 GPT코드와 차이를 비교해보았다.

SELECT st.student_id, st.student_name, sb.subject_name, COUNT(ex.subject_name) as attended_exams
FROM Students as st
CROSS JOIN Subjects as sb
LEFT JOIN Examinations as ex
    ON st.student_id=ex.student_id
        and sb.subject_name=ex.subject_name
GROUP BY st.student_id, st.student_name, sb.subject_name
ORDER BY st.student_id, sb.subject_name;

GROUP BY에서 ex.subject_name이 아닌 sb.subject_name을 사용하였다. 나는 LEFT JOIN 조건에서 sb.subject_name=ex.subject_name을 사용했기에 두 subject_name이 같은 값 일줄 알았지만 아니었던 것이다.

위 의문을 해결하기 위해 GROUP BY를 빼고 전체 테이블을 출력해보았다.

두 subject_name이 다른 이유는 LEFT JOIN과정에서 생긴 NULL값 때문이었다. 고로 ex.subject_name은 NULL값이 포함되어 있어 GROUP BY과정에서 데이터가 누락되는 경우가 발생하였으나 sb.subject_name은 LEFT에 해당되었기에 NULL값이 없어 정상적으로 GROUP BY가 수행되었던 것이다.

이 문제에서 알아갈 수 있는 점은 JOIN을 연달아서 사용할 수 있다는 점이나, LEFT JOIN이나 RIGHT JOIN시에 NULL값이 생기지 않는 TABLE의 값을 사용해야한다는 점이다.