190418

-- 전체 직원의 평균 급여보다 적은 급여를 받은 사원들의 급여 - 평균 급여를 구하기
SELECT EMPLOYEE_ID, FIRST_NAME, (SELECT avg(salary) from EMPLOYEES) - salary
from EMPLOYEES
where salary < (select avg(salary)
                   from EMPLOYEES);
 
-- with 절
-- SELECT문에서만 사용 가능하다.
-- SQL문에서 여러 번 사용되는 같은 형태의 쿼리를 with절을 사용해서 결과를 검색하고 임시로 저장한다.
-- select 문에서 with절의 결과를 이용할 수 있다.
-- 쿼리의 실행 성능이 향상된다.
-- with
-- 임시 테이블명 as (
--        select ~
--        from ~
--        where ~
-- )
-- SELECT
-- FROM 임시테이블명
-- where ~
with 
temp_avg as (
    select avg(salary) avg_salary
    from EMPLOYEES
)
select EMPLOYEE_ID, FIRST_NAME, TRUNC((SELECT avg_salary from temp_avg) - salary) salary_gap
from EMPLOYEES
WHERE SALARY < (SELECT avg_salary
                        from temp_avg);
                        
-- 부서별 총 급여를 계산했을 때, 부서별 총 급여의 평균보다 총급여를 많이 받는 부서의 이름, 총급여 조회
SELECT DEPARTMENT_NAME, TOTAL_DEPT_SALARY
FROM (SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
         FROM EMPLOYEES A, DEPARTMENTS B
         WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
         GROUP BY B.DEPARTMENT_NAME) A
WHERE A.TOTAL_DEPT_SALARY > (SELECT SUM(TOTAL_DEPT_SALARY)/COUNT(*)
                                           FROM (SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
                                                    FROM EMPLOYEES A, DEPARTMENTS B
                                                    WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
                                                    GROUP BY B.DEPARTMENT_NAME));
 
-- WITH절을 사용해서 부서별 총 급여의 평균보다 총급여를 많이 받는 부서의 이름, 총 급여 조회하기
WITH
    DEPT_SALARY AS (
        SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
        FROM EMPLOYEES A, DEPARTMENTS B
        WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
        GROUP BY B.DEPARTMENT_NAME),
    DEPT_AVG AS (
        SELECT SUM(TOTAL_DEPT_SALARY)/COUNT(*) AVG_DEPT_SALARY
        FROM DEPT_SALARY
    )
SELECT *
FROM DEPT_SALARY
WHERE TOTAL_DEPT_SALARY > (SELECT AVG_DEPT_SALARY
                                         FROM DEPT_AVG);
                                         
-- 계층형 쿼리
-- employee에서 Neena를 시작점으로 지정하고, 상위에서 하위로 검색하기
SELECT level, employee_id, first_name, manager_id
FROM EMPLOYEES
start with first_name = 'Neena'
-- 검색방향: 상위에서 하위로
-- connect by prior 부모키 = 자식키
CONNECT BY PRIOR employee_id = manager_id;
 
-- employee에서 206번 사원을 시작점으로 지정하고, 하위에서 상위로 검색하기
SELECT EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
start With  EMPLOYEE_id= 206
-- connect by prior 자식행 키 = 부모행의 키
connect by prior manager_id = EMPLOYEE_id;
 
-- employees 테이블의 계층 구조를 level과 LPAD를 사용해서 표현하기.
SELECT level, lpad(' ', (level * 5), ' ') || FIRST_NAME
FROM EMPLOYEES
START WITH EMPLOYEE_id = 100
CONNECT by PRIOR EMPLOYEE_id = MANAGER_id;
 
-- employee에서 Neena를 시작점으로 지정하고, 상위에서 하위로 검색하기
SELECT level, lpad(' ', (level * 5)-5, ' ') || FIRST_NAME
from EMPLOYEES
-- where FIRST_NAME != 'Nancy' -- Nancy만 안 나옴
start with first_name = 'Neena'
connect by EMPLOYEE_id = manager_id    -- 상위 -> 하위: prior 부모행의 키 = 자식행의 키
and FIRST_NAME != 'Nancy';                  -- Nanacy와 Nancy의 하위 행이 모두 나오지 않음