국비 TIL(Today I Learned) 20220711 오라클 문제풀이

HR 계정

-- 문제1
-- 부서별 최고 급여를 받는 사원을 구하시오
-- 내 풀이
SELECT S.DEPARTMENT_NAME, S.FIRST_NAME
FROM (SELECT E.FIRST_NAME, E.SALARY, E.DEPARTMENT_ID, D.DEPARTMENT_NAME,
RANK() OVER(PARTITION BY D.DEPARTMENT_NAME ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID) S
WHERE S.RNK = 1;

-- 강사님 풀이
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, E.SALARY
FROM (SELECT FIRST_NAME, DEPARTMENT_ID, SALARY,
RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEES) E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID WHERE E.RNK =1
ORDER BY D.DEPARTMENT_NAME;

-- 또 다른 풀이 MAX로 결과아님 과정
SELECT E.FIRST_NAME, E.DEPARTMENT_ID, E.SALARY
FROM EMPLOYEES E INNER JOIN (SELECT DEPARTMENT_ID, MAX(SALARY) AS MSAL
FROM EMPLOYEES GROUP BY DEPARTMENT_ID) A
ON E.DEPARTMENT_ID = A.DEPARTMENT_ID AND E.SALARY = A.MSAL
;

-- 문제2
-- 각 사원들의 업무별 가능한 최대 급여와 본인의 급여간 차이들을 구하고
-- 업무별로 차이가 적은 순위를 구하시오

SELECT E.FIRST_NAME, E.SALARY, E.JOB_ID, J.MAX_SALARY, (J.MAX_SALARY - E.SALARY) AS CHA FROM EMPLOYEES E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID;

SELECT JOB_ID,COUNT(*), SUM(CHA)/COUNT(*),
RANK() OVER(ORDER BY SUM(CHA)/COUNT(*) DESC) AS RNK
FROM (SELECT E.FIRST_NAME, E.SALARY, E.JOB_ID, J.MAX_SALARY, (J.MAX_SALARY - E.SALARY) AS CHA FROM EMPLOYEES E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID) S
GROUP BY JOB_ID;
-- 내 풀이
SELECT JOB_ID, SUM(CHA)/COUNT(*) AS CHAALL,
RANK() OVER(ORDER BY SUM(CHA)/COUNT(*)) AS RNK
FROM (SELECT E.FIRST_NAME, E.SALARY, E.JOB_ID, J.MAX_SALARY, (J.MAX_SALARY - E.SALARY) AS CHA FROM EMPLOYEES E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID) S
GROUP BY JOB_ID;

-- 강사님
-- 사진 참고
SELECT E.FIRST_NAME, J.JOB_ID, E.SALARY, J.MAX_SALARY, J.MAX_SALARY - E.SALARY AS CHA,
RANK() OVER(PARTITION BY E.JOB_ID ORDER BY J.MAX_SALARY - E.SALARY ASC) AS RNK
FROM  EMPLOYEES E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID
ORDER BY JOB_ID, RNK;



-- 문제3
-- 부서변동이 있었던 사원들을 구하시오
-- 단순 부서 변동의 경우 4명
SELECT DISTINCT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, JH.DEPARTMENT_ID FROM EMPLOYEES E RIGHT OUTER JOIN JOB_HISTORY JH ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID
WHERE E.DEPARTMENT_ID != JH.DEPARTMENT_ID
;
-- 입사 후 부서 변동의 경우 3명
SELECT DISTINCT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, JH.DEPARTMENT_ID FROM EMPLOYEES E RIGHT OUTER JOIN JOB_HISTORY JH ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID
WHERE E.DEPARTMENT_ID != JH.DEPARTMENT_ID AND END_DATE > HIRE_DATE; 

-- 강사님 풀이
-- 단순 부서 변동의 경우 4명
SELECT DISTINCT E.FIRST_NAME
FROM EMPLOYEES E INNER JOIN JOB_HISTORY JH ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID AND E.DEPARTMENT_ID != JH.DEPARTMENT_ID;

-- 입사 후 부서 변동의 경우 3명
SELECT DISTINCT E.FIRST_NAME
FROM EMPLOYEES E INNER JOIN JOB_HISTORY JH ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID AND E.DEPARTMENT_ID != JH.DEPARTMENT_ID AND E.HIRE_DATE < JH.END_DATE;





-- 보건팀
-- 사원수 1, 2등인 부서들의 급여 평균을 구하고, 
-- 사원 수가 1등인 부서는 급여 평균값을 40프로 인상, 2등은 30프로 삭감 후 그 차이를 구하시오
-- 출력 : 1등 평균, 2등 평균, 1등 인상 후, 2등 삭감 후, 차이
-- 내 풀이
SELECT E.AVG AS "1등의 평균", D.AVG AS "2등의 평균", E.AVG *1.4 AS "1등 인상 후", D.AVG * 0.7 AS "2등 삭감 후", ABS(E.AVG *1.4 - D.AVG * 0.7) AS 차이 FROM 
(SELECT S.AVG
FROM (
SELECT  FLOOR(AVG(E.SALARY)) AS AVG, E.DEPARTMENT_ID, DEPARTMENT_NAME, COUNT(*),
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, D.DEPARTMENT_NAME) S
WHERE RNK = 1 ) E INNER JOIN (
SELECT S.AVG
FROM (
SELECT  FLOOR(AVG(E.SALARY)) AS AVG, E.DEPARTMENT_ID, DEPARTMENT_NAME, COUNT(*),
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, D.DEPARTMENT_NAME) S
WHERE RNK = 2) D ON 1=1
;

-- 강사님 풀이
-- 과정
SELECT DEPARTMENT_ID, COUNT(*) AS CNT, FLOOR(AVG(SALARY)) AS AVG_SLA,
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
-- 과정
SELECT *
FROM (SELECT  FLOOR(AVG(SALARY)) AS AVG_SLA,
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID) E
WHERE E.RNK IN(1,2);
-- 위에서 두줄로 된거를 한줄로 변경
SELECT MAX(DECODE(E.RNK, 1, E.AVG_SAL, NULL)) AS "1등", MIN(DECODE(E.RNK, 2, E.AVG_SAL, NULL)) AS "2등",
        MAX(DECODE(E.RNK, 1, E.AVG_SAL, NULL)) * 1.4 AS "1등인상", MIN(DECODE(E.RNK, 2, E.AVG_SAL, NULL)) * 0.7 AS "2등 삭감",
        ABS(MAX(DECODE(E.RNK, 1, E.AVG_SAL, NULL)) * 1.4 - MIN(DECODE(E.RNK, 2, E.AVG_SAL, NULL)) * 0.7) AS "CHA"
FROM (SELECT  FLOOR(AVG(SALARY)) AS AVG_SAL,
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID) E
WHERE E.RNK IN(1,2);


-- 교통팀 (우리팀, 따로 풀지는 않음)
-- 각 부서별로 직원이 한명만 있는 부서만 조회하시오. 
-- 단, 직원이 없는 부서에 대해서는 ‘<신생부서>’라는 문자열이 출력되도록 하고,
-- 강사님 풀이
-- NULL도 찍어야 해서 LEFT OUTER JOIN로 썼데
SELECT DECODE(C.DEPARTMENT_ID,NULL,'<신생부서>',D.DEPARTMENT_NAME) AS UM, C.CNT 
FROM (
SELECT DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES GROUP BY DEPARTMENT_ID
HAVING COUNT(*) = 1 ) C LEFT OUTER JOIN DEPARTMENTS D ON C.DEPARTMENT_ID = D.DEPARTMENT_ID
;



-- 산업경제 팀
-- 연도별 입사자 수 1위인 부서를 뽑고 부서가 위치한 나라의 이름을 찾으시오.
-- 연도, 부서명, 그 부서의 입사자 수, 부서가 위치한 나라 이름 출력
-- (연도순 오름차순)
-- 내 풀이
SELECT E.YEAR, D.DEPARTMENT_NAME, E.CNT, C.COUNTRY_NAME FROM DEPARTMENTS D INNER JOIN (SELECT TO_CHAR(HIRE_DATE,'YYYY')AS YEAR, DEPARTMENT_ID, COUNT(*) AS CNT, 
RANK() OVER(PARTITION BY TO_CHAR(HIRE_DATE,'YYYY') ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'YYYY'), DEPARTMENT_ID) E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
WHERE E.RNK =1;

-- 강사님 풀이
SELECT E.YY, E.CNT, C.COUNTRY_NAME
FROM(
    SELECT TO_CHAR(HIRE_DATE,'YYYY') AS YY, DEPARTMENT_ID, COUNT(*) AS CNT, 
RANK() OVER(PARTITION BY TO_CHAR(HIRE_DATE,'YYYY') ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'YYYY'), DEPARTMENT_ID
)E INNER JOIN  DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
WHERE E.RNK =1
ORDER BY E.YY ASC
;



-- 교육팀
-- Canada에서 근무하는 Hartstein씨는 첫 연봉 인상 20%에 가까스로 성공하였다.
-- 따라서 인사평가에 도움을 준 자신의 상사에게 감사인사를 전하기 위해,
-- 손편지를 작성하여 상사의 근무지로 우편을 부치려고한다.
-- 현재, 인사담당자는 육아휴직 중인데, 스위스의 어느 경치좋은 호텔에서 요들송을 즐기고 있다고 한다.
-- Hartstein씨는 인사담당자의 여유로운 호캉스를 망치고 싶지 않아 스스로 상급자 근무지 주소와, 우편번호를 찾아보려고 한다
-- 본인의 인맥을 총동원하여 HR 데이터를 획득하게 된 Hartstein씨는 
-- 드디어 편지를 보낼 수 있게 되었다.
-- 이때 본인 이름과 상급자의 이름, 부서명, 근무지 주소, 우편번호, 국가를 출력하시오

-- Hartstein 상사의 주소
-- 내 풀이
SELECT E1.LAST_NAME, E2.LAST_NAME, D.DEPARTMENT_NAME, L.STREET_ADDRESS, L.POSTAL_CODE, C.COUNTRY_NAME  FROM EMPLOYEES E1 INNER JOIN EMPLOYEES E2 ON E1.MANAGER_ID = E2.EMPLOYEE_ID INNER JOIN  DEPARTMENTS D  ON D.DEPARTMENT_ID = E2.DEPARTMENT_ID  INNER JOIN LOCATIONS L ON  D.LOCATION_ID = L.LOCATION_ID
INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
WHERE E1.LAST_NAME = 'Hartstein';

-- 강사님 풀이
SELECT E.FIRST_NAME, E2.FIRST_NAME, D.DEPARTMENT_NAME, L.STREET_ADDRESS, L.POSTAL_CODE, C.COUNTRY_NAME
FROM EMPLOYEES E INNER JOIN EMPLOYEES E2 ON E.MANAGER_ID = E2.EMPLOYEE_ID INNER JOIN  DEPARTMENTS D  ON D.DEPARTMENT_ID = E2.DEPARTMENT_ID  INNER JOIN LOCATIONS L ON  D.LOCATION_ID = L.LOCATION_ID
INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID WHERE E.LAST_NAME = 'Hartstein';



-- 환경팀
-- 입사자가 가장 많은 년도에 입사한 사원 중에서 부서 별 급여 1순위 사원을 출력하라
-- 내 풀이
SELECT * FROM (
SELECT FIRST_NAME, DEPARTMENT_ID, SALARY,
RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEES 
WHERE TO_CHAR(HIRE_DATE,'YYYY') = (SELECT YEAR FROM (SELECT TO_CHAR(HIRE_DATE,'YYYY')AS YEAR, COUNT(*) AS CNT, 
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'YYYY'))
WHERE RNK = 1)
)WHERE RNK =1;

-- 강사님 풀이
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, E.SALARY, E.YY
FROM( SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, TO_CHAR(HIRE_DATE,'YYYY') AS YY,E.SALARY,
RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEES E INNER JOIN (SELECT TO_CHAR(HIRE_DATE,'YYYY') AS YY, COUNT(*) AS CNT, 
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'YYYY')) A ON TO_CHAR(E.HIRE_DATE, 'YYYY') = A.YY AND A.RNK=1) E
WHERE E.RNK =1;

 

 

SCOTT 계정

-- 보건팀
-- SCOTT 테이블
-- 급여가 1000 미만인 사원들의 직속상사 급여를 10% 삭감하시오
-- 출력 : 부하직원, 부하급여, 직속상사, 상사급여, 삭감 후 급여
-- 내 풀이
SELECT E2.ENAME AS 부하직원, E2.SAL AS 부하급여, E1.ENAME AS 직속상사, E1.SAL AS 상사급여, E1.SAL* 0.9 AS "삭감 후 급여"
FROM EMP E1 INNER JOIN (SELECT * FROM EMP WHERE SAL <1000) E2 ON E1.EMPNO = E2.MGR;

-- 강사님 풀이
SELECT DISTINCT E2.ENAME, E2.SAL, E1.ENAME, E1.SAL, E1.SAL* 0.9 AS RSAL
FROM EMP E1 INNER JOIN EMP E2 ON E1.EMPNO = E2.MGR
AND E2.SAL < 1000
; -- E1- 상급자, E2 - 부하직원



-- 교통팀 (우리팀, 따로 풀지는 않음)
-- 각 사원 별 커미션이 0 또는 NULL이고 부서위치가 ‘GO’로 끝나는 사원의 정보를 
-- 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하라.
-- 강사님 풀이
SELECT E.EMPNO, E.ENAME, E.COMM, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND D.LOC LIKE '%GO'
WHERE NVL(E.COMM, 0) = 0;





-- 산업경제팀
-- 사원 이름과 사원의 상사 이름, 상사의 부서 지역, 상사의 월급, 상사의 월급 순위 출력 
-- 내 풀이는 문제낸 팀의 답이랑 같음
-- 내 풀이
SELECT E1.ENAME, E2.ENAME AS MGR_ENAME, D.LOC AS MGR_LOC, E2.SAL AS MGR_SAL,
RANK() OVER(ORDER BY E2.SAL DESC) AS RNK
FROM EMP E1 INNER JOIN EMP E2 ON E1.MGR = E2.EMPNO INNER JOIN DEPT D ON E2.DEPTNO= D.DEPTNO;


-- 강사님 풀이
SELECT E1.ENAME, E2.ENAME, D.LOC, E2.SAL, E2.RNK
FROM EMP E1 INNER JOIN (SELECT EMPNO, ENAME, DEPTNO, SAL,
RANK() OVER(ORDER BY SAL DESC) AS RNK 
FROM EMP
) E2 ON E1.MGR = E2.EMPNO
INNER JOIN DEPT D ON E2.DEPTNO = D.DEPTNO
ORDER BY E2.SAL DESC;

-- 문제낸 팀의 답이랑 강사님 풀이 답이 다름, 강사님 쿼리문으로 보기


-- 교육팀
-- SMITH씨는 수십년간 한 회사에서 근무하고 있다
-- 최근 SMITH씨네 회사에 내부 직원의 일탈로 600억원대 횡령 사고가 발생하였는데,
-- SMITH씨도 가담한 것으로 밝혀졌다. 
-- 이에 NEW YORK으로 출장 중이던 회사대표 KING씨는 긴급 회의를 소집하였다.
-- 소속 부서와 관계없이 모든 MANAGER들은 필수적으로 참여해야하며, 회의 장소는 NEW YORK 이다.
-- 총무팀에서 항공권 예매를 일괄 구매 하기로 했는데,
-- 이때 항공권을 구매하지 않아도 되는 MANAGER의 전체정보를 출력하시오

-- 결국 뉴욕에 근무하는 MANAGER 구하는거네
-- 내 풀이
SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'NEW YORK' AND JOB = 'MANAGER'
;

-- 강사님 풀이
SELECT E.* FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND D.LOC = 'NEW YORK' INNER JOIN (SELECT DISTINCT MGR FROM EMP) E2
ON E.EMPNO = E2.MGR WHERE E.ENAME != 'KING';



-- 환경팀 하는중
-- 전체 사원 중 급여가 하위 20%면 급여를 20% 상승시켜라
-- 하위 20퍼 어떻게 구하는지 몰랐어
-- 강사님 풀이
SELECT E.ENAME, E.SAL,
        RANK() OVER(ORDER BY SAL DESC) AS RNK,
        CASE WHEN RANK() OVER(ORDER BY SAL ASC) < C.DE
            THEN E.SAL * 1.2
            ELSE E.SAL
            END AS RSAL
FROM EMP E INNER JOIN (SELECT COUNT(*) * 0.2 AS DE
FROM EMP) C ON 1=1;