SQLD 기출문제 예상문제 79번

다음 문제를 풀어보세요.

다음은 조직도 정보를 담고 있는 `EMPLOYEES` 테이블에 대한 설명이다. 주어진 테이블과 데이터를 기반으로, '사장' 직책을 가진 사람을 제외한 모든 직원의 상위 관리자 이름을 조회하는 SQL 쿼리를 작성하시오. 단, 계층 구조를 명확히 표현하기 위해 레벨 정보를 함께 출력하고, 레벨 순서대로 정렬해야 한다.

-- 테이블 구조
CREATE TABLE EMPLOYEES (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    manager_id NUMBER,
    job_title VARCHAR2(50),
    FOREIGN KEY (manager_id) REFERENCES EMPLOYEES(employee_id)
);

-- 샘플 데이터
INSERT INTO EMPLOYEES (employee_id, employee_name, manager_id, job_title) VALUES (1, '사장', NULL, '사장');
INSERT INTO EMPLOYEES (employee_id, employee_name, manager_id, job_title) VALUES (2, '부사장', 1, '부사장');
INSERT INTO EMPLOYEES (employee_id, employee_name, manager_id, job_title) VALUES (3, '팀장A', 2, '팀장');
INSERT INTO EMPLOYEES (employee_id, employee_name, manager_id, job_title) VALUES (4, '팀장B', 2, '팀장');
INSERT INTO EMPLOYEES (employee_id, employee_name, manager_id, job_title) VALUES (5, '사원A', 3, '사원');
INSERT INTO EMPLOYEES (employee_id, employee_name, manager_id, job_title) VALUES (6, '사원B', 3, '사원');
INSERT INTO EMPLOYEES (employee_id, employee_name, manager_id, job_title) VALUES (7, '사원C', 4, '사원');

SELECT LEVEL, e.employee_name, m.employee_name AS manager_name
FROM EMPLOYEES e
LEFT JOIN EMPLOYEES m ON e.manager_id = m.employee_id
WHERE e.job_title != '사장'
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id
ORDER SIBLINGS BY e.employee_name;

SELECT LEVEL, e.employee_name, m.employee_name AS manager_name
FROM EMPLOYEES e
JOIN EMPLOYEES m ON e.manager_id = m.employee_id
START WITH e.job_title != '사장' AND e.manager_id IS NOT NULL
CONNECT BY PRIOR e.employee_id = e.manager_id
ORDER BY LEVEL;

SELECT LEVEL, e.employee_name, m.employee_name AS manager_name
FROM EMPLOYEES e
JOIN EMPLOYEES m ON e.manager_id = m.employee_id
WHERE e.job_title != '사장'
START WITH e.manager_id IS NOT NULL
CONNECT BY PRIOR e.employee_id = e.manager_id
ORDER BY LEVEL;

SELECT LEVEL, e.employee_name, m.employee_name AS manager_name
FROM EMPLOYEES e
JOIN EMPLOYEES m ON e.manager_id = m.employee_id
START WITH e.manager_id IS NOT NULL
CONNECT BY PRIOR e.employee_id = e.manager_id AND e.job_title != '사장'
ORDER BY LEVEL;