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

SELECT * FROM EMP;
-- 산업경제팀
-- 사업과 각 상사의 급여 차이를 구하고 급여 차이에 따른 랭크를 구하시오
-- 내 풀이
SELECT E1.ENAME, E1.SAL, E2.ENAME AS MGR_ENAME, E2.SAL AS MGR_SAL, E2.SAL - E1.SAL AS CHA,
DENSE_RANK() OVER(ORDER BY E2.SAL - E1.SAL DESC) AS RNK
FROM EMP E1 INNER JOIN EMP E2  
ON E1.MGR = E2.EMPNO;

-- 강사님 풀이
SELECT E1.ENAME, E1.SAL, E2.ENAME AS MGR_ENAME, E2.SAL AS MGR_SAL, ABS(E2.SAL - E1.SAL) AS CHA,
DENSE_RANK() OVER(ORDER BY ABS(E2.SAL - E1.SAL) DESC) AS RNK
FROM EMP E1 INNER JOIN EMP E2  
ON E1.MGR = E2.EMPNO;


-- 교육팀
-- DALLAS에서 근무하는 사원 중 전체 평균 급여보다 급여가 적은 사원에게 급여를 50%인상 하고 이름, 근무지, 전체 급여 평균, 기존 급여, 바뀐 급여를 출력하시오
-- 내 풀이
SELECT E.ENAME, D.LOC, S.AVG,  E.SAL,
 CASE WHEN E.SAL <S.AVG
            THEN E.SAL * 1.5
            ELSE E.SAL
        END AS RSAL
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND LOC ='DALLAS' INNER JOIN (SELECT ROUND(AVG(SAL)) AS AVG FROM EMP) S ON 1=1;

-- 강사님 풀이
SELECT E.ENAME, D.LOC, A.AVG_SGL, E.SAL,
 CASE WHEN E.SAL < A.AVG_SGL
            THEN E.SAL * 1.5
            ELSE E.SAL
        END AS RSAL
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND LOC ='DALLAS' INNER JOIN (SELECT ROUND(AVG(SAL)) AS AVG_SGL FROM EMP) A ON 1=1;


-- 환경팀
-- 시카고에 일하는 세일즈맨 중 연봉 등급이 2등급인 직원들의 연봉을 20% 증가시키고 아니면 20%감소시키시오.
-- 내 풀이
SELECT E.ENAME, SG.GRADE, D.LOC, E.JOB, E.SAL,  DECODE(SG.GRADE ,'2', SAL * 1.2, SAL * 0.8) AS RSAL
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND LOC ='CHICAGO' AND JOB = 'SALESMAN' INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL
ORDER BY SG.GRADE;

-- 강사님 풀이
SELECT E.ENAME, SG.GRADE, D.LOC, E.JOB, E.SAL,  DECODE(SG.GRADE ,'2', SAL * 1.2, SAL * 0.8) AS RSAL
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND LOC ='CHICAGO' INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL
WHERE E.JOB = 'SALESMAN';


-- 보건팀
-- NEW YORK에서 일하고 있는 사원들의 월급 평균을 구하고, 사원들의 급여가 뉴욕평균보다 낮으면 20% 인상 높으면 20% 삭감
-- 내 풀이
SELECT E.ENAME AS 이름, E.SAL AS 뉴욕급여, 뉴욕평균,
 CASE WHEN E.SAL < 뉴욕평균
            THEN E.SAL * 1.2
            ELSE E.SAL * 0.8
        END AS 바뀐급여
FROM EMP E INNER JOIN (SELECT FLOOR(AVG(SAL)) AS 뉴욕평균
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND LOC ='NEW YORK') D ON 1=1;

-- 강사님 풀이
-- 용어만 다르고 같음


-- 각 부서에 대한 급여 평균값의 순위를 지정해서, 해당 부서 사원들의 급여를 1순위는 10%, 2순위는 20%, 3순위는 30% 인상을 하시오. 
-- 내 풀이
SELECT E.RNK, E.DEPTNO, E.ENAME, E.SAL, E.GRADE, NSG.GRADE
FROM (SELECT S.RNK, E.DEPTNO, E.ENAME, E.SAL, OSG.GRADE,
CASE WHEN S.RNK = '1' 
            THEN E.SAL * 1.1
            WHEN S.RNK = '2' 
            THEN E.SAL * 1.2
            WHEN S.RNK = '3' 
            THEN E.SAL * 1.3
            ELSE E.SAL  -- 디폴트
        END AS NEWSAL
FROM EMP E INNER JOIN SALGRADE OSG ON 
E.SAL BETWEEN OSG.LOSAL AND OSG.HISAL INNER JOIN (SELECT E.DEPTNO, D.LOC, ROUND(AVG(SAL)) AS AVG,
RANK() OVER(ORDER BY ROUND(AVG(SAL)) DESC) AS RNK
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
GROUP BY D.LOC, E.DEPTNO) S ON E.DEPTNO = S.DEPTNO
ORDER BY S.RNK) E INNER JOIN SALGRADE NSG ON 
NEWSAL BETWEEN NSG.LOSAL AND NSG.HISAL;

-- 강사님은 CASE 대신 DECODE


-- 교통팀(우리)
SELECT ENAME, SAL*1.3, JOB 
FROM EMP
WHERE SAL > (SELECT ROUND(AVG(E.SAL))
                FROM EMP E INNER JOIN DEPT D 
                ON E.DEPTNO = D.DEPTNO
                AND D.DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='MARTIN'))
AND SAL < (SELECT ROUND(AVG(E.SAL))
                FROM EMP E
                INNER JOIN DEPT D
                ON E.DEPTNO = D.DEPTNO
                AND D.DNAME='RESEARCH');
                
-- 마틴이 속한 부서의 평균! 강사님 풀이
SELECT FLOOR(AVG(E.SAL)) AS AVG_SAL
                FROM EMP E INNER JOIN EMP E2
                ON E.DEPTNO = E2.DEPTNO
                AND E2.ENAME = 'MARTIN';

 

 

-- 시애틀에 근무하는 사원들의 급여 평균을 구하시오.
SELECT 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 AND L.CITY ='Seattle';


-- FIRST_NAEM과 LAST_NAME을 이용하여 EMAIL데이터를 만드시오.
-- FIRST_NAEM에서 대문자 LAST_NAME을 공백제거 합쳐서 8글자
-- 모두 대문자: UPPER(값), 모두 소문자: LOWER(값)
SELECT FIRST_NAME, LAST_NAME, CONCAT(SUBSTR(FIRST_NAME,1,1),UPPER(SUBSTR(REPLACE(LAST_NAME,' ',''),1,7))) AS EMAIL
FROM EMPLOYEES;

SELECT FIRST_NAME, UPPER(SUBSTR(SUBSTR(FIRST_NAME, 1, 1) ||
DECODE(INSTR(FIRST_NAME, ' '),0,'', -- FIRST_NAME Jose manuel 때문에 써줌
SUBSTR(FIRST_NAME, INSTR(FIRST_NAME, ' ') + 1, 1)) 
|| REPLACE(LAST_NAME, ' ', ''),1,8)) AS EMAIL
FROM EMPLOYEES;

-- 직속 부하직원이 가장 많은 상급자를 구하시오. 
-- 내 풀이
-- 과정
SELECT E2.MANAGER_ID, COUNT(*),
RANK() OVER(ORDER BY  COUNT(*) DESC ) AS RNK 
FROM EMPLOYEES E1 INNER JOIN EMPLOYEES E2 ON E1.EMPLOYEE_ID = E2.MANAGER_ID
GROUP BY E2.MANAGER_ID
;

-- 결과
SELECT E.FIRST_NAME
FROM EMPLOYEES E INNER JOIN(SELECT E2.MANAGER_ID, COUNT(*),
RANK() OVER(ORDER BY  COUNT(*) DESC ) AS RNK 
FROM EMPLOYEES E1 INNER JOIN EMPLOYEES E2 ON E1.EMPLOYEE_ID = E2.MANAGER_ID
GROUP BY E2.MANAGER_ID) D ON E.EMPLOYEE_ID = D.MANAGER_ID
WHERE RNK ='1'
;

-- 강사님 풀이
-- 과정
SELECT MANAGER_ID, COUNT(*) AS CNT, 
RANK() OVER(ORDER BY  COUNT(*)DESC ) AS RNK 
FROM EMPLOYEES GROUP BY MANAGER_ID;
-- 결과
SELECT E.FIRST_NAME
FROM EMPLOYEES E INNER JOIN (SELECT MANAGER_ID, COUNT(*) AS CNT, 
RANK() OVER(ORDER BY  COUNT(*)DESC ) AS RNK 
FROM EMPLOYEES GROUP BY MANAGER_ID) M
ON E.EMPLOYEE_ID = M.MANAGER_ID
AND RNK ='1';


-- 국가별 사원수와 급여평균을 구하시오.
-- 내 풀이
SELECT COUNTRY_ID, COUNTRY_NAME, COUNT(*), FLOOR(AVG(SALARY))
FROM (SELECT C.COUNTRY_ID, C.COUNTRY_NAME, E.SALARY
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 COUNTRY_NAME, COUNTRY_ID;

-- 강사님 풀이
SELECT C.COUNTRY_ID, C.COUNTRY_NAME, COUNT(*), FLOOR(AVG(SALARY))
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_NAME, C.COUNTRY_ID;


-- 나 왜 자꾸 쿼리문 한개씩 더 쓰는것 같지.......

 

오라클 SCOTT, HR 계정에 있는 테이블로 실습