(1)부하 직원이 가장 많은 직원 3명 의 평균급여를 구하고 그 값보다 이상인 사람은 20%감봉 아닌사람은 20%인상해서
바뀐 급여를 작성하시오
출력:사원명 급여등급 변경급여 부서명
----
SELECT E.ENAME,E.DEPTNO,
CASE
WHEN E.SAL >= F.AVG_SAL
THEN E.SAL * 0.8
ELSE E.SAL * 1.2
END AS RE_SAL
FROM
(SELECT *
FROM
(SELECT AVG(E.SAL) AS AVG_SAL
FROM
(SELECT *
FROM
(SELECT MGR,COUNT(MGR),
RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
FROM EMP
GROUP BY MGR)A
WHERE A.RNK <=3)B INNER JOIN EMP E ON B.MGR = E.EMPNO)G) F INNER JOIN EMP E ON 1=1;
--ALLEN의 상사의 급여를 20% 감소시키고 SCOTT의 급여를 10% 인상시킨 후 두 값의 차이를 구해라
ALLEN의 (MGR)는 7698이고 그 7698에 대한 EMPNO가 상사
--ENAME은 BLAKE
출력 : 두 값의 차이
--ALLEN상사의 급여를 20%감소
SELECT ENAME, SAL,
CASE
WHEN ENAME ='BLAKE'
THEN SAL * 0.8
ELSE SAL
END RE_SAL
FROM EMP
WHERE ENAME ='BLAKE'
;
--SCOTT의 급여를 10%인상
SELECT ENAME, SAL,
CASE
WHEN ENAME ='SCOTT'
THEN SAL * 1.1
ELSE SAL
END RE_SAL
FROM EMP
WHERE ENAME ='SCOTT'
;
두 차이를 구해라
SELECT ABS(A.RE_SAL -B.RE_SAL) AS CHA
FROM
(SELECT ENAME, SAL,
CASE
WHEN ENAME ='BLAKE'
THEN SAL * 0.8
ELSE SAL
END RE_SAL
FROM EMP
WHERE ENAME ='BLAKE') A
INNER JOIN
(SELECT ENAME, SAL,
CASE
WHEN ENAME ='SCOTT'
THEN SAL * 1.1
ELSE SAL
END RE_SAL
FROM EMP
WHERE ENAME ='SCOTT')B ON 1=1
3.
-- 87년에 입사한 사원의 상급자들의 급여합계를 구하고 업무별 급여합계를 구한 뒤, 두 값을 비교하여 상급자들의 급여합계(MGR_SUM)가 업무별 급여합계 보다 작은 경우 그 업무에 해당하는 사원들의 급여를
-- 50퍼센트 인상하여 변경된 급여와 전체 정보 및 변경된 급여순위를 출력하시오.
--87년에 입사한 사원의 상급자들의 급여합계를 구하고
SELECT SUM(SAL)
FROM EMP
WHERE EMPNO IN
(SELECT MGR
FROM EMP
WHERE TO_CHAR(HIREDATE,'YY') = 87);
--업무별 급여합계와 비교하여 상급자 급여합계보다 급여가 낮은 사원들의 급여를 50퍼센트 인상하여 변경순위
SELECT SUM(SAL), JOB
FROM EMP
GROUP BY JOB
;
SELECT E1.JOB, E1.ENAME,
RANK() OVER(ORDER BY CASE
WHEN E.MGR_SAL<E.JOB_SAL
THEN E1.SAL * 1.5
ELSE E1.SAL
END DESC) AS RNK,
CASE
WHEN E.JOB_SAL <E.MGR_SAL
THEN E1.SAL * 1.5
ELSE E1.SAL
END AS RE_SAL
FROM
(SELECT *
FROM (SELECT SUM(SAL) AS JOB_SAL , JOB
FROM EMP
GROUP BY JOB) E INNER JOIN (SELECT SUM(SAL) AS MGR_SAL
FROM EMP
WHERE EMPNO IN
(SELECT MGR
FROM EMP
WHERE TO_CHAR(HIREDATE,'YY') = 87))
ON 1=1)E INNER JOIN EMP E1 ON E.JOB=E1.JOB;
4.
--이름과 직업을 더하여 새로운 이름을 만들고 (중간에 스페이스 한 번 추가) 새로운이름의 길이값을 구하여 길이값이 11인 사람의 월급을 150%로 만들고,
-- 길이값이 12인 사람의 월급을 120%로 만들어 순위를 출력한 후, 사는 지역별로 총계급여합을 산출하여 1위인 지역을 구하시오.
---이름과 직업을 더하여 새로운 이름을 만들고
SELECT A.SUM_NAME
FROM
(SELECT ENAME || ' '|| JOB AS SUM_NAME
FROM EMP)A;
--새로운이름의 길이값을 구하여 길이값이 11인 사람의 월급을 150%로 만들고
SELECT DISTINCT B.SUM_NAME,B.NEW_LEN,E.SAL,
CASE
WHEN B.NEW_LEN =11
THEN E.SAL * 1.5
ELSE E.SAL
END RE_SAL
FROM
(SELECT A.SUM_NAME, LENGTH(A.SUM_NAME) AS NEW_LEN
FROM
(SELECT ENAME || ' '|| JOB AS SUM_NAME
FROM EMP)A ) B INNER JOIN EMP E ON 1=1
;
길이값이 12인 사람의 월급을 120%로 만들어 순위를 출력한 후,
SELECT DISTINCT B.SUM_NAME,B.NEW_LEN,E.SAL,
CASE
WHEN B.NEW_LEN =11
THEN E.SAL * 1.5
WHEN B.NEW_LEN = 12
THEN E.SAL * 1.2
ELSE E.SAL
END RE_SAL,
RANK() OVER (ORDER BY
CASE
WHEN B.NEW_LEN =11
THEN E.SAL * 1.5
WHEN B.NEW_LEN = 12
THEN E.SAL * 1.2
ELSE E.SAL
END DESC) AS RNK
FROM
(SELECT A.SUM_NAME, LENGTH(A.SUM_NAME) AS NEW_LEN
FROM
(SELECT ENAME || ' '|| JOB AS SUM_NAME
FROM EMP)A ) B INNER JOIN EMP E ON 1=1
;
사는 지역별로 총계급여합을 산출하여 1위인 지역을 구하시오.
SELECT *
FROM
(SELECT D.LOC, SUM(F.RE_SAL), RANK() OVER(ORDER BY SUM(F.RE_SAL) DESC ) AS RANK
FROM
(SELECT DEPTNO,ENAME || ' '|| JOB AS SUM_NAME ,
LENGTH(ENAME || ' '|| JOB) AS LEN,
CASE
WHEN LENGTH(ENAME || ' '|| JOB) =11
THEN SAL * 1.5
WHEN LENGTH(ENAME || ' '|| JOB) =12
THEN SAL * 1.2
ELSE SAL
END AS RE_SAL
FROM EMP)F INNER JOIN DEPT D ON F.DEPTNO = D.DEPTNO
GROUP BY D.LOC)G
;
