SQL | Students and Examinations, Managers with at Least 5 Direct Reports

https://leetcode.com/problems/students-and-examinations/description/

 

---

Students and Examinations

# 학생별 시험에 참가한 횟수 조회
# 학생 아이디 기준 정렬
SELECT
    st.student_id,
    st.student_name,
    su.subject_name,
    COUNT(ex.subject_name) AS attended_exams
FROM 
    students st 
CROSS JOIN		# 학생별로 시험과목 다 조회해야하기 때문에 CROSS JOIN
    subjects su
LEFT JOIN
    examinations ex
    ON st.student_id = ex.student_id
    AND su.subject_name = ex.subject_name
GROUP BY 
    1,2,3
ORDER BY 
    1 ;

💡 CROSS JOIN : 모든 경우의 수를 다 도출 -> ON 필요없음

---

https://leetcode.com/problems/managers-with-at-least-5-direct-reports/description/

---

Managers with at Least 5 Direct Reports

# 직속 보고자 5명 이상인 관리자
# 1) 직속 보고자 5명인 관리자 아이디 찾기
SELECT
	managerId,
    COUNT(*) AS cnt_managerId
FROM
    Employee
GROUP BY
    managerId
HAVING COUNT(*) >= 5)

# 2) 관리자 이름과 매칭
WITH a AS
    (SELECT
        managerId,
        COUNT(*) AS cnt_managerId
    FROM
        Employee
    GROUP BY
        managerId
    HAVING COUNT(*) >= 5)
SELECT
    e.name		# 관리자 이름조회
FROM
    Employee e JOIN a ON e.id = a.managerId