국비 TIL(Today I Learned) 20220713 오라클 실습

오전 

어제 학생들이 낸 문제 어제꺼 이어서 풀고, 2개 못 품(문제 8번 17번)

강사님 풀이는 8번, 13번, 15번, 17번, 19번만 있음

-- Seattle에서 근무하는 사원들의 부서별 급여 1위를 구하시오.(내가 낸 문제)
-- 출력: 부서이름, 연봉, 도시
SELECT S.DEPARTMENT_NAME ,S.SALARY, S.CITY FROM(
SELECT E.SALARY, D.DEPARTMENT_NAME, L.CITY,
RANK() OVER(PARTITION BY D.DEPARTMENT_NAME ORDER BY E.SALARY DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE L.CITY = 'Seattle') S WHERE S.RNK= '1';


-- 문제1
-- 2001년에 입사한 사람의 나라이름
-- 출력: 이름, 나라이름
SELECT FIRST_NAME, C.COUNTRY_NAME
FROM EMPLOYEES 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 TO_CHAR(E.HIRE_DATE, 'YYYY') = '2001';

-- 문제2
-- 도시별 사원들의 급여에서 최저 급여와 최고 급여의 차이가 가장 큰 도시와 그 차이를 구하시오
SELECT S.CITY, S.CHA
FROM(
SELECT S.CITY, MAX(S.SALARY),  MIN(S.SALARY),  MAX(S.SALARY) -  MIN(S.SALARY) AS CHA,
RANK() OVER(ORDER BY  MAX(S.SALARY) -  MIN(S.SALARY) DESC) AS RNK
FROM (SELECT E.SALARY, L.CITY
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID)S
GROUP BY S.CITY ) S WHERE S.RNK='1'
;

-- 문제3
-- PHONE_NUMBER 맨 뒷자리가 4인 사원의 LAST_NAME과 COUNTRY_ID를 뽑고,
-- COUNTRY_ID가 UK인 사람은 50%인상 US인 사람은 10%인상하여 그들만의 월급 순위를 구하시오.
-- 출력 = LAST_NAME, PHONE_NUMBER, COUNTRY_ID, 원래 월급, 인상 후 월급, 랭킹
SELECT  S.LAST_NAME, S.PHONE_NUMBER, S.COUNTRY_ID, S.SALARY, S.RSAL,
RANK() OVER(ORDER BY S.RSAL DESC)
FROM (SELECT E.LAST_NAME, E.PHONE_NUMBER, C.COUNTRY_ID, E.SALARY,
          CASE WHEN C.COUNTRY_ID = 'UK'
            THEN E.SALARY * 1.5
            WHEN C.COUNTRY_ID = 'US'
            THEN E.SALARY * 1.1
            ELSE E.SALARY
        END AS RSAL
FROM EMPLOYEES 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 PHONE_NUMBER LIKE '%4') S;

-- 문제4
-- 급여가 3000 미만의 사원들을 구하고 급여 순위를 구하세요
-- 출력 : FIRST_NAME + LAST_NAME , SALARY, COUNTRY_NAME, RNK
SELECT CONCAT(FIRST_NAME, LAST_NAME), SALARY, C.COUNTRY_NAME,
RANK() OVER(ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEES 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 SALARY < 3000;

-- 문제 4.5
-- 나라별 사원들의 평균 급여를 구하고, 
-- 평균 급여가 가장 낮은 1순위 나라의 평균급여를 5000인상,  
-- 2순위는 3천, 3순위는 1천 인상후, 
-- 인상전의 평균급여, 인상전 순위를 오름차순 
-- 인상후의 평균급여과 인상후의 순위를 내림차순으로 구하시오.
SELECT  D.AVG_SAL AS "인상 전 평균급여", D.RNK AS "인상 전 순위", D.RSAL AS "인상 후 평균급여",
RANK() OVER(ORDER BY D.RSAL DESC) AS "인상 후 순위", D.COUNTRY_ID AS "나라"
FROM (
SELECT S.COUNTRY_ID, S.AVG_SAL, S.RNK,
     CASE WHEN S.RNK = 1
            THEN AVG_SAL + 5000
            WHEN S.RNK = 2
            THEN AVG_SAL + 3000
            WHEN S.RNK = 3
            THEN AVG_SAL + 1000
            ELSE AVG_SAL
        END AS RSAL
FROM(
SELECT C.COUNTRY_ID, FLOOR(AVG(E.SALARY)) AS AVG_SAL, 
RANK() OVER(ORDER BY FLOOR(AVG(E.SALARY)) ASC) AS RNK
FROM EMPLOYEES 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
GROUP BY C.COUNTRY_ID) S) D
;




-- 문제5
-- 급여가 4000에서 5000사이인 사원의 이름과 소속부서명과 주소, 급여, 순위를 출력하시오.
SELECT FIRST_NAME,D.DEPARTMENT_NAME, L.STREET_ADDRESS, SALARY,
RANK() OVER(ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEES  E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID WHERE SALARY BETWEEN 4000 AND 5000;

-- 문제6
-- FIRST_NAME이 Bruce 인 사람보다 나중에 입사한 사원들의 FIRST_NAME, HIRE_DATE 를 조회해라.
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= (SELECT HIRE_DATE FROM EMPLOYEES WHERE FIRST_NAME = 'Bruce') 
ORDER BY HIRE_DATE
;

​
-- 문제7
-- 도시별 사원 수와 급여 평균을 출력하세요 (사원 수 오름차순 정렬)
SELECT L.CITY, COUNT(*) AS CNT, FLOOR(AVG(E.SALARY)) AS AVG_SAL
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.CITY
ORDER BY CNT
;

-- 문제8 => 하는중
-- 유럽과 아시아에서 입사자가 가장 많은 부서의 2007년에 입사한 사원 중
-- 연봉이 가장 낮은 사원의 상급자 정보를 출력
-- 출력 : 부하직원의 이름, 상급자의 이름, 상급자의 연봉, 상급자의 업무이름

-- 내가 풀다가 만거-
SELECT *  FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE TO_CHAR(E.HIRE_DATE,'YYYY') = '2007' AND D.DEPARTMENT_NAME = (
SELECT S.DEPARTMENT_NAME
FROM (
SELECT D.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 INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID 
INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
INNER JOIN REGIONS R ON C.REGION_ID = R.REGION_ID INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID
WHERE R.REGION_ID IN ('2','3')
GROUP BY D.DEPARTMENT_NAME) S
WHERE S.RNK = '1'
); -- 나 REGION_ID 잘못 줬구나, 유럽과 아시아는 REGION_ID 1,3임

-- 유럽과 아시아에서 사원이 가장 많은 부서의 2007년에 입사한 사원 중
-- 연봉이 가장 낮은 사원의 상급자 정보를 출력
-- 출력 : 부하직원의 이름, 상급자의 이름, 상급자의 연봉, 상급자의 업무이름
-- 강사님 풀이
SELECT A.FIRST_NAME AS "부하직원", E.FIRST_NAME AS "상급자",
E.SALARY, J.JOB_TITLE
FROM EMPLOYEES E
INNER JOIN (
SELECT E.MANAGER_ID, E.FIRST_NAME,
RANK() OVER(ORDER BY E.SALARY ASC) AS RNK
FROM EMPLOYEES E
INNER JOIN (
SELECT E.DEPARTMENT_ID, COUNT(*) AS CNT,
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES 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
INNER JOIN REGIONS R
ON C.REGION_ID = R.REGION_ID
AND R.REGION_NAME IN ('Europe', 'Asia')
GROUP BY E.DEPARTMENT_ID) R
ON E.DEPARTMENT_ID = R.DEPARTMENT_ID
AND R.RNK = 1
WHERE TO_CHAR(E.HIRE_DATE, 'YYYY') = 2007) A
ON E.EMPLOYEE_ID = A.MANAGER_ID
AND A.RNK = 1
INNER JOIN JOBS J
ON E.JOB_ID = J.JOB_ID;









-- 문제9
-- S로 시작하는 도시별 평균급여와 그 도시에서 일하는 직원들의 개별급여를 구하고
-- 도시의 평균급여보다 낮은 직원들의 급여를 10% 인상하시오.
-- 출력: S로 시작하는 도시들의 평균급여, 각 직원 급여,도시명, 사원이름, 변경된 급여
SELECT E.FIRST_NAME, L.CITY, E.SALARY, S.AVG_SAL, 
         CASE WHEN E.SALARY  < S.AVG_SAL
            THEN E.SALARY * 1.1
            ELSE E.SALARY
        END AS RSAL
FROM EMPLOYEES 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 (
SELECT L.CITY, FLOOR(AVG(E.SALARY)) AS AVG_SAL
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE L.CITY LIKE 'S%'
GROUP BY L.CITY
)S ON L.CITY= S.CITY;



-- 문제10
-- 업무 인원이 가장 많은 부서를 출력하세요
SELECT S.CITY, S.DEPARTMENT_NAME
FROM (
SELECT L.CITY, D.DEPARTMENT_NAME, COUNT(*),
RANK() OVER(PARTITION BY L.CITY ORDER BY COUNT(*) DESC ) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.CITY, D.DEPARTMENT_NAME) S
WHERE S.RNK = 1
;


-- 문제11
-- 사원수 1등 2등의 부서들의 급여 평균의 차이를 구하시오
SELECT ABS(MAX(DECODE(S.RNK, 1, S.AVG_SAL, NULL)) -MIN(DECODE(S.RNK, 2, S.AVG_SAL, NULL))) AS CHA
FROM (
SELECT D.DEPARTMENT_NAME, COUNT(*), FLOOR(AVG(E.SALARY)) AS AVG_SAL,
RANK() OVER(ORDER BY COUNT(*) DESC ) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
)S 
WHERE S.RNK IN(1,2)
;

-- 문제12
-- 'COUNTRY_ID'가 'US'인 2004년 입사자들의 연봉을 20% 인상시키고, 2003년 입사자들은 20% 감소시켜라. (나머지는 그대로)
-- 위를 바탕으로,부서 ID별 바뀐 연봉 순위를 매기고 1순위만 출력하여라.
-- 출력) 2004,2003년 입사자의 FIRST_NAME, 입사자들의 원래 연봉, COUNTRY_ID, 바뀐 연봉, 부서별 연봉 1순위 
SELECT M.FIRST_NAME, M.SALARY, M.COUNTRY_ID, M.DEPARTMENT_ID, M.RNK FROM (
SELECT S.FIRST_NAME, S.SALARY, S.COUNTRY_ID, S.DEPARTMENT_ID, S.HIRE_DATE,
RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY RSAL) AS RNK
FROM (
SELECT E.FIRST_NAME, E.SALARY, L.COUNTRY_ID, E.DEPARTMENT_ID, E.HIRE_DATE,
 CASE WHEN TO_CHAR(E.HIRE_DATE,'YYYY') = '2004'
            THEN E.SALARY * 1.2
            WHEN TO_CHAR(E.HIRE_DATE,'YYYY') = '2003'
            THEN E.SALARY * 0.8
            ELSE E.SALARY
        END AS RSAL
FROM EMPLOYEES  E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE L.COUNTRY_ID = 'US' AND  TO_CHAR(HIRE_DATE, 'YYYY') IN ('2004', '2003')
) S ) M WHERE M.RNK = '1';


-- 문서13 => 평균값 DEPARTMENT_ID = '80' 으로 찾기
-- 판매부서 직업별 최고연봉자의 연봉과, 판매부서 직원들의 평균연봉을 비교해서 그 차이를 구하시오
-- 출력 : 직업, 최고연봉자 FIRSTNAME, 최고연봉, 판매부서평균연봉, 차이 

SELECT H.JOB_TITLE, H.FIRST_NAME, H.SALARY, S1.AVG_SAL, H.SALARY - S1.AVG_SAL
FROM (
SELECT S.SALARY, S.JOB_ID, S.JOB_TITLE, S.FIRST_NAME FROM( 
SELECT E.FIRST_NAME, E.SALARY, J.JOB_ID, J.JOB_TITLE,
RANK() OVER(PARTITION BY J.JOB_ID ORDER BY E.SALARY DESC) AS RNK
FROM EMPLOYEES E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID
WHERE J.JOB_TITLE LIKE 'Sales%') S
WHERE S.RNK = '1' ) H INNER JOIN (SELECT FLOOR(AVG(E.SALARY)) AS AVG_SAL
FROM EMPLOYEES E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID
WHERE E.DEPARTMENT_ID = 80) S1 ON 1=1
;

-- 강사님 풀이
SELECT A.JOB_TITLE ,A.FIRST_NAME, A.SALARY, A.AVG_SAL, A.CHA
FROM (
SELECT J.JOB_TITLE, E.FIRST_NAME, E.SALARY, A.AVG_SAL, 
E.SALARY - A.AVG_SAL AS CHA, 
    RANK() OVER(PARTITION BY E.JOB_ID
                    ORDER BY E.SALARY DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
        ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
        AND D.DEPARTMENT_NAME = 'Sales'
    INNER JOIN JOBS J
        ON E.JOB_ID = J.JOB_ID
    INNER JOIN (SELECT FLOOR(AVG(E.SALARY)) AS AVG_SAL
                FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
                    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
                    AND D.DEPARTMENT_NAME = 'Sales')A
                    ON 1=1) A
                    WHERE A.RNK = 1
;

-- 문제14 
-- 소속한 부서의 평균 연봉보다 낮은 연봉인 사원들의 ENAME, SALARY, DEPARTMENT_ID, 각 부서 평균 연봉 AS AVG_SALARY 을 조회해라.
SELECT CONCAT(E.FIRST_NAME, E.LAST_NAME), E.SALARY, E.DEPARTMENT_ID, S.AVG_SAL FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
INNER JOIN (SELECT FLOOR(AVG(E.SALARY))AS AVG_SAL, D.DEPARTMENT_ID FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID GROUP BY D.DEPARTMENT_ID) S ON D.DEPARTMENT_ID=S.DEPARTMENT_ID
WHERE E.SALARY < S.AVG_SAL
ORDER BY S.AVG_SAL
;

-- 문제15
-- 나라별 사원 평균을 구하고 1,4 순위는 20%인상 후 차이 값  2, 3 순위는 20%삭감 후 합계 값을 구하시오.
-- RANK 로 순위 구할때 PARTITION BY 사용하지 않음
-- 출력 : 1 , 2 , 3 , 4 등 ,
-- 1 , 4등의 인상 후 값  ,
-- 1 , 4등 인상후 차이 값 ,
-- 2 , 3등 삭감 값 ,
-- 2 , 3등 삭감 후 값
-- 내 풀이
SELECT E1.* , E2.*, E1."1등" * 1.2 AS "1등 인상", E2."4등" * 1.2 AS "4등 인상", 
ABS(E1."1등" * 1.2 - E2."4등" * 1.2) AS "1,4등차이값" , E1."2등" * 0.8  AS "2등 삭감", E2."3등" * 0.8 AS "3등 삭감",  E1."2등" *0.8 + E2."3등" * 0.8 AS "2,3등 합계값"
FROM (
SELECT MAX(DECODE(E.RNK, 1, E.AVG_SAL, NULL)) AS "1등", MIN(DECODE(E.RNK, 2, E.AVG_SAL, NULL)) AS "2등"
FROM (SELECT AVG_SAL,
RANK() OVER(ORDER BY AVG_SAL ASC) AS RNK
FROM(
SELECT L.COUNTRY_ID, FLOOR(AVG(E.SALARY)) AS AVG_SAL FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.COUNTRY_ID)) E) E1 INNER JOIN 
(SELECT MAX(DECODE(E.RNK, 3, E.AVG_SAL, NULL)) AS "3등", MIN(DECODE(E.RNK, 4, E.AVG_SAL, NULL)) AS "4등"
FROM (SELECT AVG_SAL,
RANK() OVER(ORDER BY AVG_SAL ASC) AS RNK
FROM(
SELECT L.COUNTRY_ID, FLOOR(AVG(E.SALARY)) AS AVG_SAL FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.COUNTRY_ID)) E) E2 ON 1=1
;

-- 강사님 풀이
SELECT MAX(DECODE(A.RNK, 1, A.AVG_SAL, NULL)) AS "1등",
MAX(DECODE(A.RNK, 2, A.AVG_SAL, NULL)) AS "2등",
MAX(DECODE(A.RNK, 3, A.AVG_SAL, NULL)) AS "3등",
MAX(DECODE(A.RNK, 4, A.AVG_SAL, NULL)) AS "4등",
MAX(DECODE(A.RNK, 1, A.AVG_SAL * 1.2, NULL)) AS "1등 인상",
MAX(DECODE(A.RNK, 4, A.AVG_SAL * 1.2, NULL)) AS "4등 인상",
MAX(DECODE(A.RNK, 1, A.AVG_SAL * 1.2, NULL)) -
MAX(DECODE(A.RNK, 4, A.AVG_SAL * 1.2, NULL)) AS "1,4등 인상 차이",
MAX(DECODE(A.RNK, 2, A.AVG_SAL * 0.8, NULL)) AS "2등 삭감",
MAX(DECODE(A.RNK, 3, A.AVG_SAL * 0.8, NULL)) AS "3등 삭감",
MAX(DECODE(A.RNK, 2, A.AVG_SAL * 0.8, NULL)) +
MAX(DECODE(A.RNK, 3, A.AVG_SAL * 0.8, NULL)) AS "2,3등 합계값"
FROM (SELECT L.COUNTRY_ID, FLOOR(AVG(E.SALARY)) AS AVG_SAL,
RANK() OVER(ORDER BY AVG(E.SALARY) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.COUNTRY_ID) A;


-- 문제16
-- Seattle'에서 근무하는 사원들이 제일 많이 하고 있는 업무의 최대, 최소 급여를 구하라
SELECT J.JOB_TITLE, J.MAX_SALARY, J.MIN_SALARY FROM JOBS J INNER JOIN 
(SELECT J.JOB_TITLE, COUNT(*),
RANK() OVER (ORDER BY COUNT(*) DESC) AS CNT
FROM EMPLOYEES 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 JOBS J ON E.JOB_ID =J.JOB_ID 
WHERE L.CITY = 'Seattle'
GROUP BY J.JOB_TITLE) S ON J.JOB_TITLE = S.JOB_TITLE
WHERE S.CNT = '1'
;

-- 문제 17  => 해아함
-- 2005, 2006년에 입사한 사원들의 도시별 업무들의 평균 급여를 구하고,
-- 평균 급여가 2등인 도시와 업무에 속한 사원들의 정보를 출력하세요.
-- 출력 내용 -> 입사년도, LAST_NAME, CITY , JOB_TITLE,SALARY, 2등인 평균급여

-- 나는 못 풀음, 강사님 풀이
SELECT A.YY, E.LAST_NAME, A.CITY, J.JOB_TITLE, E.SALARY, A.AVG_SAL
FROM EMPLOYEES E INNER JOIN (SELECT TO_CHAR(E.HIRE_DATE, 'YYYY') AS YY, L.CITY, E.JOB_ID, FLOOR(AVG(E.SALARY)) AS AVG_SAL,
RANK() OVER(PARTITION BY TO_CHAR(E.HIRE_DATE, 'YYYY')
ORDER BY AVG(E.SALARY) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
WHERE TO_CHAR(E.HIRE_DATE, 'YYYY') IN (2005, 2006)
GROUP BY TO_CHAR(E.HIRE_DATE, 'YYYY'), L.CITY, E.JOB_ID) A
ON E.JOB_ID = A.JOB_ID
AND A.RNK = 2
INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
AND L.CITY = A.CITY
INNER JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
WHERE TO_CHAR(E.HIRE_DATE, 'YYYY') IN (2005, 2006);





-- 문제 18
-- Oxford에서 근무하는 사원들 중 급여가 전체 평균 보다 높으면, 20% 인하하고
-- 바뀐 급여의 순위를 구하시오
-- 출력 : 이름, 도시, 전체 평균 급여, 급여, 바뀐급여, 순위
SELECT S.EMPLOYEE_ID, S.LAST_NAME, S.CITY, S.AVG_SAL, S.SALARY, S.RSAL,
RANK() OVER(ORDER BY S.RSAL DESC) AS CNT
FROM (
SELECT E.EMPLOYEE_ID, E.LAST_NAME, L.CITY, S.AVG_SAL, E.SALARY,
    CASE WHEN E.SALARY >  S.AVG_SAL
            THEN E.SALARY * 0.8
            ELSE E.SALARY
        END AS RSAL
FROM EMPLOYEES 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 JOBS J ON E.JOB_ID =J.JOB_ID  INNER JOIN (SELECT FLOOR(AVG(SALARY)) AS AVG_SAL FROM EMPLOYEES) S ON 1=1
WHERE L.CITY = 'Oxford'
) S
;


-- 문제 19
-- PHONE_NUMBER가 ***.124.****인 사원들의 상사의 직업별 급여 1순위가 사는 도시를 구하시오.
-- 출력 상사의 이름, 상사의 직업, 상사의 급여, 상사의 급여 순위, 상사가 사는 도시(급여를 오름차순으로)
SELECT * FROM (
SELECT DISTINCT  E1.FIRST_NAME, J.JOB_ID, L.CITY, E1.SALARY,
RANK() OVER(PARTITION BY JOB_ID ORDER BY E1.SALARY DESC) AS CNT
FROM EMPLOYEES E1 INNER JOIN (SELECT * FROM EMPLOYEES WHERE PHONE_NUMBER LIKE '%.124.%') E2 ON E1.EMPLOYEE_ID = E2.MANAGER_ID  
INNER JOIN DEPARTMENTS D ON E1.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN JOBS J ON E1.JOB_ID =J.JOB_ID 
) S WHERE S.CNT =1
ORDER BY S.SALARY
;

-- 강사님 풀이
SELECT E.FIRST_NAME, J.JOB_TITLE, E.SALARY, E.RNK, L.CITY
FROM (SELECT E.FIRST_NAME, E.JOB_ID, E.DEPARTMENT_ID, E.SALARY,
RANK() OVER(PARTITION BY E.JOB_ID ORDER BY E.SALARY DESC) AS RNK
FROM EMPLOYEES E INNER JOIN (SELECT DISTINCT MANAGER_ID
FROM EMPLOYEES
WHERE PHONE_NUMBER LIKE '%.124.%') M
ON E.EMPLOYEE_ID = M.MANAGER_ID) E
INNER JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
WHERE E.RNK = 1
ORDER BY E.SALARY ASC;

 

팀별 과제 확인받고,

 

오후 쿼리 셤

-- SCOTT
-- 업무(JOB)별 총급여(급여 + 성과급) 순위를 나타내시오.
-- 출력 : 사원명, 업무, 급여, 성과급, 총급여, 순위
-- 내 풀이
SELECT ENAME, JOB, SAL, COMM, SAL + NVL(COMM,0) AS TOT_SAL,
RANK() OVER(PARTITION BY JOB ORDER BY SAL + NVL(COMM,0) DESC) AS RNK
FROM EMP;

 
 
-- 각 부서별 급여순위 1위 ~ 3위를 출력하시오.
-- 출력 : 부서명, 사원명, 급여, 순위
-- 내 풀이
SELECT S.* FROM
(SELECT D.DNAME, E.ENAME, E.SAL,
RANK() OVER(PARTITION BY D.DNAME ORDER BY E.SAL DESC) AS RNK
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO) S 
WHERE S.RNK <=3
;


-- HR
-- 상급자들의 업무들 중 가장 많은 것은 어떤 것인가?
-- 출력 : 업무명(JOB_TITLE), 상급자 수
-- 내 풀이
SELECT H.JOB_TITLE, H.CNT FROM (
SELECT COUNT(*) AS CNT, S.JOB_TITLE, 
RANK() OVER( ORDER BY COUNT(*) DESC) AS RNK
FROM
(SELECT DISTINCT  E1.* , J.JOB_TITLE
FROM EMPLOYEES E1 INNER JOIN EMPLOYEES E2 ON E1.EMPLOYEE_ID = E2.MANAGER_ID INNER JOIN JOBS J ON E1.JOB_ID = J.JOB_ID) S
GROUP BY S.JOB_TITLE) H
WHERE H.RNK = '1'
;

 

1번 문제 강사님 풀이

 

2번 문제 강사님 풀이

 

3번 문제 강사님 풀이

 

시험은 강사님 쿼리랑 비슷함, 100점 

 

 

erd 테이블 구조

회사에 가면 요청해야함!! 없으면 이상한데래

https://www.google.com/search?q=erd&oq=erd&aqs=chrome..69i57j0i131i433i512j0i512l8.2837j0j15&sourceid=chrome&ie=UTF-8 

erd - Google 검색