[오라클로 배우는 데이터베이스 개론과 실습]A.11 계층 검색

A.11 계층 검색

 

샘플 문제

SELECT LAST_NAME || ' reports to ' || PRIOR LAST_NAME "Walk"
FROM EMPLOYEES
START WITH LAST_NAME = 'King'
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

 

 

문제 1

SELECT PRIOR LAST_NAME || ' reports to ' ||  LAST_NAME "Walk"
FROM EMPLOYEES
START WITH LAST_NAME = 'Olson'
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID;

문제 2

SELECT LPAD('_', LEVEL-1, '_') || LAST_NAME AS "Hierarchial Query"
FROM EMPLOYEES
START WITH LAST_NAME = 'King'
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

문제 2 - 계속

SELECT LPAD('_', LEVEL-1, '_') || LAST_NAME AS "Hierarchial Query"
FROM EMPLOYEES
START WITH LAST_NAME = 'King'
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID AND LAST_NAME != 'Kochhar';

문제 3

SELECT LPAD('*', LEVEL, '*') || LAST_NAME || ' job is ' || JOB_ID
|| ', department_name is '|| D.DEPARTMENT_NAME AS "Hierarchial Query"
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
START WITH LAST_NAME = 'King'
CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID;

 

문제 4

SELECT SUBSTRB((SYS_CONNECT_BY_PATH(LAST_NAME, '-')), 2) AS "NAME_PATH"
FROM EMPLOYEES
START WITH LAST_NAME = 'King'
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;