190417

-- 모든 직원의 현재 직종 및 이전 직종을 사원 아이디, 직종 아이디로 표현하기
-- 각 사원에 대해 동일한 직종은 한 번만 조회
SELECT EMPLOYEE_ID, JOB_ID, 'C'
FROM EMPLOYEES
UNION
SELECT EMPLOYEE_ID, JOB_ID, 'P'
FROM JOB_HISTORY;
 
-- 모든 직원의 현재 직종 및 이전 직종을 사원 아이디, 직종 아이디로 표현하기
SELECT EMPLOYEE_ID, JOB_ID
FROM EMPLOYEES
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID
FROM JOB_HISTORY
ORDER BY EMPLOYEE_ID;
 
-- 50번 부서에서 근무중이거나 근무했던 사원의 아이디, 직종을 조회하기
SELECT EMPLOYEE_ID, JOB_ID, HIRE_DATE AS START_DATE, NULL AS END_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID, START_DATE, END_DATE
FROM JOB_HISTORY
WHERE DEPARTMENT_ID = 50;
 
-- 현재 50번 부서에 근무 중인 직원 중에서 이전에 50번 부서에 근무 경험이 있는 직원 조회하기
SELECT A.EMPLOYEE_ID, B.FIRST_NAME, B.SALARY
FROM (SELECT EMPLOYEE_ID
         FROM EMPLOYEES
         WHERE DEPARTMENT_ID = 50
         INTERSECT 
         SELECT EMPLOYEE_ID
         FROM JOB_HISTORY
         WHERE DEPARTMENT_ID = 50) A, EMPLOYEES B
WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID;
 
-- 90번 부서에 근무 중인 사원 중에서 업무가 변경된 적이 한 번도 없는 사원의 아이디를 조회하기
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
MINUS
SELECT EMPLOYEE_ID
FROM JOB_HISTORY;
 
-- 90번 부서에 근무 중인 사원 중에서 업무가 변경된 적이 한 번도 없는 사원의 정보를 조회하기
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID
                                FROM EMPLOYEES
                                WHERE DEPARTMENT_ID = 90
                                MINUS
                                SELECT EMPLOYEE_ID
                                FROM JOB_HISTORY);
 
-- 직원 아이디가 174 사원과 관리자 및 부서가 동일한 사원을 조회하기
SELECT *
FROM EMPLOYEES
WHERE (MANAGER_ID, DEPARTMENT_ID) IN (SELECT MANAGER_ID, DEPARTMENT_ID -- 앞부분 괄호 필수, 순서 지켜야
                                                        FROM EMPLOYEES
                                                        WHERE EMPLOYEE_ID = 174);
 
-- 부서별 최고 급여를 조회했을 때
SELECT EMPLOYEE_ID, FIRST_NAME, NVL(TO_CHAR(DEPARTMENT_ID), '없음'), SALARY
FROM EMPLOYEES
WHERE (NVL(DEPARTMENT_ID, 0), SALARY) IN (SELECT NVL(DEPARTMENT_ID, 0), MAX(SALARY)
                                                 FROM EMPLOYEES
                                                 GROUP BY DEPARTMENT_ID);
 
-- 스칼라 서브쿼리 사용하기
-- 아이디, 이름, 급여, 평균 급여와의 차
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY - (SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES) AVG_SALARY
FROM EMPLOYEES;
 
-- 전체 평균 급여보다 10000달러 이상 많이 받는 사원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES
WHERE SALARY - (SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES) >= 10000;
 
-- 모든 사원의 급여를 인상시키기
-- 평균 급여보다 적은 급여를 받는 사원의 급여 인상시키기
-- (평균급여액 - 급여)/4
UPDATE EMPLOYEES
SET
    SALARY = SALARY + TRUNC(((SELECT AVG(SALARY) FROM EMPLOYEES) - SALARY)/4)
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID
                                FROM EMPLOYEES
                                WHERE SALARY < (SELECT AVG(SALARY)
                                FROM EMPLOYEES));
 
SELECT * FROM EMPLOYEES;
ROLLBACK;
 
-- 급여별 급여 인상액 추정해보기
-- 15000달러 이상 10% 인상, 10000달러 이상 15% 인상, 10000달러 미만 20% 인상
-- 평균 급여 대비 인상률임
SELECT
    EMPLOYEE_ID,
    FIRST_NAME,
    SALARY,
    CASE
        WHEN SALARY >= 15000 THEN TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.1)
        WHEN SALARY >= 10000 THEN TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.15)
        ELSE TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.2)
    END INCREMENT_SALARY
FROM EMPLOYEES;
 
-- 부서 아이디, 부서 이름, 부서별 사원수 계산하기(상호연관 서브쿼리)
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME,
          (SELECT COUNT(*) FROM EMPLOYEES X WHERE X.DEPARTMENT_ID = A.DEPARTMENT_ID)
FROM DEPARTMENTS A
WHERE A.MANAGER_ID IS NOT NULL;
 
-- 일반 서브쿼리와 상호 연관 서브쿼리
-- 일반 서브쿼리
-- 전체 사원의 평균급여보다 급여를 많이 받는 사원 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT TRUNC(AVG(SALARY))
                        FROM EMPLOYEES);  -- 서브쿼리는 단 한 번 실행되고, 실행 결과가 조건식에 사용된다.
 
-- 상호연관 서브쿼리
-- 소속부서의 평균급여보다 급여를 많이 받는 사원 조회하기
SELECT A.FIRST_NAME, A.DEPARTMENT_ID, A.SALARY
FROM EMPLOYEES A
WHERE A.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES B WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID);
 
 
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'yyyy') >= '2003';
AND TO_CHAR(HIRE_DATE, 'mm') in ('01','02','03') 
UNION
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
where TO_CHAR(HIRE_DATE, 'yyyymm') >= 200501
AND TO_CHAR(HIRE_DATE, 'yyyymm') <= 200506;
 
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'yyyy') = 2005;
 
SELECT a.department_id, a.department_name, (SELECT count(*) FROM EMPLOYEES x where x.DEPARTMENT_ID = a.department_id) as 사원수
FROM DEPARTMENTS a;