-
-1)도시별 급여를 2)가장 많이 받는 상급자를 구하시오.
.
--2) 조건
--1) 주 테이블 EMPLOYEES(SALARY,MANAGER_ID)
--2) 부 테이블 LOCATIONS(CITY),
--
--
--CITY를 가져오려면 DEPARTMENT LOCATION-ID, LOCATION의 LOCATION-ID
--급여를 가장 많이 받는 상급자 EMPLOYEES MANAGER_ID- EMPLOYEE ID와 연결
--가장 많이 받는 상급자( 자가참조를 해야하는데 너무 데이터가 많으므로 매니저ID를 그룹으로 지어서 구한다.
SELECT * FROM EMPLOYEES;
SELECT A.EMPLOYEE_ID
FROM
(SELECT EMPLOYEE_ID
FROM EMPLOYEES)A INNER JOIN
(SELECT MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID) B
ON A.EMPLOYEE_ID = B.MANAGER_ID
;
--도시별 급여를
SELECT
*
FROM
(SELECT B.FIRST_NAME,B.SALARY,L.CITY,
RANK() OVER(PARTITION BY L.CITY ORDER BY B.SALARY DESC) AS RNK
FROM
(SELECT MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID) A INNER JOIN EMPLOYEES B ON A.MANAGER_ID = B.EMPLOYEE_ID
INNER JOIN DEPARTMENTS D ON B.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID)
WHERE RNK =1
;
-- 전체 부서장의 급여평균을 구하고, 부서장의 급여가 구한 평균 이상인 부서의 지역을 구하시오.
부서장의 급여평균을 구하려면
부서의 매니저id와 EMPLOYEES의 EMPLOYEE_ID와 조인해야 한다.
SELECT AVG(E.SALARY) AS AVG_SAL
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.EMPLOYEE_ID = D.MANAGER_ID
;
--부서장의 급여
SELECT A.DEPARTMENT_NAME, A.CITY
FROM
(SELECT D.DEPARTMENT_NAME, L.CITY, E.SALARY
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.EMPLOYEE_ID = D.MANAGER_ID
INNER JOIN LOCATIONS L ON D.LOCATION_ID=L.LOCATION_ID
)A
INNER
JOIN
(SELECT AVG(E.SALARY) AS AVG_SAL
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.EMPLOYEE_ID = D.MANAGER_ID)B ON A.SALARY >= B.AVG_SAL;
