JOIN 예제(hr)

-- 문제1) Steven King의 부서명을 출력하라.
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND E.LAST_NAME = 'King'
    AND E.FIRST_NAME = 'Steven';
    
-- 문제2) IT부서에서 근무하고 있는 사람들을 출력하라.    
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.FIRST_NAME, E.JOB_ID,
    D.DEPARTMENT_NAME
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
    AND D.DEPARTMENT_NAME = 'IT';
 
-- 문제3) EMPLOYEES 테이블과 DEPARTMENTS 테이블을 
-- Cartesian Product(모든 가능한 행들의 Join)하여 
-- 사원번호,이름,업무,부서번호,부서명,근무지를 출력하여라.
SELECT *
FROM EMPLOYEES A, DEPARTMENTS B;
 
-- 문제4) EMPLOYEES 테이블에서 사원번호,이름,업무, 
-- EMPLOYEES 테이블의 부서번호, DEPARTMENTS 테이블의 
-- 부서번호,부서명,근무지를 출력하여라.
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, 
    E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME,
    D.LOCATION_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
-- 문제5) EMPLOYEES 테이블과 DEPARTMENTS 테이블의 부서번호를 조인하고
-- SA_MAN 사원만의 사원번호,이름,급여,부서명,근무지를 출력하라.
-- (Alias를 사용)
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME, D.LOCATION_ID,
    E.JOB_ID
FROM EMPLOYEES E, DEPARTMENTS D 
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND E.JOB_ID = 'SA_MAN';
 
-- 문제6) EMPLOYEES 테이블과 DEPARTMENTS 테이블에서 
-- DEPARTMENTS 테이블에 있는 모든 자료를 사원번호,이름,업무, 
-- EMPLOYEES 테이블의 부서번호, DEPARTMENTS 테이블의 부서번호,부서명,근무지를 출력하여라
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID,
    D.DEPARTMENT_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID(+);
 
-- 문제7) EMPLOYEES 테이블에서 Self join하여 관리자(매니저)를 출력하여라.
SELECT A.LAST_NAME || '의 관리자는 ' || B.LAST_NAME || '입니다'
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.MANAGER_ID = B.EMPLOYEE_ID;
 
-- 문제8) EMPLOYEES 테이블에서 left join하여 관리자(매니저)를 출력하고
-- 매니저 아이디가 없는 사람은 배제하고 
-- 하향식으로 하며,    PRIOR
-- 급여는 역순으로     ORDER BY
-- 출력하라.
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.MANAGER_ID, B.EMPLOYEE_ID,
    B.FIRST_NAME
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.MANAGER_ID = B.EMPLOYEE_ID(+)
START WITH A.MANAGER_ID IS NOT NULL -- 매니저 아이디가 없는 사람은 배제
CONNECT BY A.manager_id = PRIOR A.EMPLOYEE_ID -- 하향식  
ORDER BY A.SALARY DESC;
 
-- 문제9) EMPLOYEES 테이블에서 right join하여 
-- 관리자(매니저)가 108번
-- 상향식으로 
-- 급여는 역순으로 출력하라.
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.MANAGER_ID, 
    B.EMPLOYEE_ID, B.FIRST_NAME
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.MANAGER_ID = B.EMPLOYEE_ID
   AND A.MANAGER_ID = '108' 
CONNECT BY PRIOR A.MANAGER_ID = A.EMPLOYEE_ID
ORDER BY A.SALARY DESC;