[SQL][LeetCode] 570. Managers with at Least 5 Direct Reports - INNER JOIN + GROUP BY + HAVING ORACLE

https://leetcode.com/problems/managers-with-at-least-5-direct-reports/?envType=study-plan-v2&envId=top-sql-50

코드설명

INNER JOIN + GROUP BY + HAVING을 활용합니다.

 

문제에서 매니저로써 5명의 EMPLOYEE를 관리하고 있는 사람의 NAME을 출력하는 문제입니다.

ORACLE 코드

INNER JOIN을 활용한 코드입니다.

SELECT E1.NAME
FROM EMPLOYEE E1, EMPLOYEE E2
WHERE E1.ID = E2.MANAGERID
GROUP BY E1.ID, E1.NAME
HAVING COUNT(E2.ID) >= 5;

 

SUBQUERY + IN 절로도 처리가능합니다.

SELECT NAME
FROM EMPLOYEE
WHERE ID IN (
    SELECT MANAGERID
    FROM EMPLOYEE
    GROUP BY MANAGERID
    HAVING COUNT(*) >= 5
);