국비 TIL(Today I Learned) 20220707

1. 실습문제

-- 급여등급 중 짝수등급과 홀수등급의 급여합계 차이를 구하시오.
-- 이거는 하다가 못함, 짝수등급, 홀수 등급 이어주는거를 못함
-- 조건 없을때 ON 1=1
SELECT SUM(E.SAL) AS HOL
FROM EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL AND MOD(GRADE, 2) = '0';

SELECT SUM(E.SAL) AS HOL
FROM EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL AND MOD(GRADE, 2) = '1';


SELECT J.JJACK, H.HOL, J.JJACK - H.HOL AS CHA
FROM (SELECT SUM(E.SAL) AS JJACK
FROM EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL AND MOD(GRADE, 2) = 0) J
INNER JOIN (SELECT SUM(E.SAL) AS HOL
FROM EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL AND MOD(GRADE, 2) = 1) H
ON 1 = 1;

-- UNION 사용, 홀짝을 따로 볼수는 없어서 활용도 떨어짐
SELECT MAX(V.VAL) - MIN(V.VAL) AS CHA
FROM (SELECT SUM(E.SAL) AS VAL
FROM EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL AND MOD(GRADE, 2) = 0
UNION
SELECT SUM(E.SAL) AS VAL
FROM EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL AND MOD(GRADE, 2) = 1) V;

-- DECODE 활용
SELECT SUM(DECODE(MOD(SG.GRADE, 2), 0, E.SAL, NULL)) AS JJACK,
        SUM(DECODE(MOD(SG.GRADE, 2), 1, E.SAL, NULL)) AS HOL,
        ABS(SUM(DECODE(MOD(SG.GRADE, 2), 0, E.SAL, NULL)) -
                SUM(DECODE(MOD(SG.GRADE, 2), 1, E.SAL, NULL))) AS CHA
FROM EMP E INNER JOIN SALGRADE SG ON E.SAL BETWEEN SG.LOSAL AND SG.HISAL;




-- 전체 급여평균보다 급여가 낮은 사원의 급여를 40% 인상하여, 인상 급여 기준으로 급여등급을 출력하시오.
-- 과정--
SELECT * FROM EMP INNER JOIN (SELECT AVG(SAL) AS AVG FROM EMP) E ON 1=1;

-- 과정--
SELECT ENAME, SAL,
        CASE WHEN SAL < AVG
            THEN SAL * 1.4
            ELSE SAL
        END AS RSAL, AVG
FROM EMP INNER JOIN (SELECT AVG(SAL) AS AVG FROM EMP) E ON 1=1;

-- 과정--
SELECT E.ENAME, E.SAL, SG.GRADE, E.RSAL, SG.LOSAL, SG.HISAL
FROM (SELECT ENAME, SAL,
        CASE WHEN SAL < AVG
            THEN SAL * 1.4
            ELSE SAL
        END AS RSAL, AVG
FROM EMP INNER JOIN (SELECT AVG(SAL) AS AVG FROM EMP) E ON 1=1) E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL ;

-- 마지막 코드--
SELECT E.ENAME, E.SAL, E.RSAL, ROUND(AVG) AS AVG_SAL, SG.GRADE AS OLD_GRADE, S.GRADE AS NEW_GRADE
FROM (SELECT ENAME, SAL,
        CASE WHEN SAL < AVG
            THEN SAL * 1.4
            ELSE SAL
        END AS RSAL, AVG
FROM EMP INNER JOIN (SELECT AVG(SAL) AS AVG FROM EMP) E ON 1=1) E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL INNER JOIN SALGRADE S ON 
E.RSAL BETWEEN S.LOSAL AND S.HISAL;


-- 강사님 풀이--
-- 과정 --
SELECT ROUND(AVG(SAL)) AS AVG_SAL FROM EMP;
-- 과정 --
SELECT E.ENAME, E.SAL,
        CASE WHEN E.SAL < A.AVG_SAL
            THEN E.SAL * 1.4
            ELSE E.SAL
        END AS RSAL, A.AVG_SAL
FROM EMP E INNER JOIN (SELECT ROUND(AVG(SAL)) AS AVG_SAL FROM EMP) A ON 1=1;
-- 결과 --
SELECT E.ENAME, E.SAL, E.RSAL, E.AVG_SAL, OSG.GRADE AS OLD_GRADE, NSG.GRADE AS NEW_GRADE
FROM (SELECT E.ENAME, E.SAL,
        CASE WHEN E.SAL < A.AVG_SAL
            THEN E.SAL * 1.4
            ELSE E.SAL
        END AS RSAL, A.AVG_SAL
FROM EMP E INNER JOIN (SELECT ROUND(AVG(SAL)) AS AVG_SAL FROM EMP) A ON 1=1) E
INNER JOIN SALGRADE OSG ON 
E.SAL BETWEEN OSG.LOSAL AND OSG.HISAL INNER JOIN SALGRADE NSG ON 
E.RSAL BETWEEN NSG.LOSAL AND NSG.HISAL;

-- 쿼리 작성 요령
-- 1. 무엇을 구하고자 하는가?
-- 2. 조건 찾기
-- 3. 요소 찾기(필요한 데이터 선정)
-- 4. 사용 테이블 선정
-- 5. 안쪽부터 쿼리 작성
-- 6. 검증

 

2. 팀이 낸 문제 -> 내일 문제풀 예정

-- MARTIN 사원 부서의 평균 급여보다 많고, 부서 이름이 'RESEARCH'인 사원들의 평균 급여보다 적은 사원들의 급여를 30%인상하여 해당 사원들의 이름, 부서번호, 급여, 직무를 출력하여라.
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')
;

 

오늘은 셤이 있었고,

오라클 실습하고, 셤보고, 팀 프로젝트 화면기획하니 하루가 가버림