-- ● 문제
-- 서브쿼리를 활용하여 TBL_SAWON 테이블을 다음과 같이 조회할 수 있도록 한다.
/*
------------------------------------------------------------------
사원명 성별 현재나이 급여 나이보너스
------------------------------------------------------------------
단, 나이보너스는 현재 나이가 40세 이상이면 급여의 70%
30세 이상 40 세 미만이면 급여의 50%
20세 이상 30세 미만이면 급여의 30%
또한, 완성된 조회 구문을 기반으로
VIEW_SAWON2 라는 이름의 VIEW 를 생성한다.
*/
CREATE OR REPLACE VIEW VIEW_SAWON2
AS
SELECT T2.사원명, T2.성별, T2.현재나이, T2.급여
, CASE WHEN T2.현재나이>=40 THEN T2.급여*0.7
WHEN T2.현재나이>=30 THEN T2.급여*0.5
WHEN T2.현재나이>=20 THEN T2.급여*0.3
ELSE 0
END "나이보너스"
FROM
(
SELECT *
FROM VIEW_SAWON
)T2;
--==>>View VIEW_SAWON2이(가) 생성되었습니다.
-- VIEW는 순간의 사진을 저장한다는 개념이 아니라
--음,, 서식을 저장해두는 느낌. 사진을 찍어서 저장하는 것이 아니라
--사진을 찍는 '방법'을 저장해두는 느낌이다
--어떻게 표의 데이터를 정리해서 볼 것인지에 대한 방식? 서식은아니고...
SELECT SANAME "사원명"
, CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성'
WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성'
ELSE '성별확인불가'
END "성별"
, CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)
WHEN SUBSTR(JUBUN,7,1) IN ('3','4')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999)
ELSE 0
END "현재나이"
, SAL "급여"
FROM TBL_SAWON;
-- ● 생성된 VIEW 확인
SELECT *
FROM VIEW_SAWON2;
/*
김가영 여성 28 100 30
김서현 여성 28 2000 600
김아별 여성 29 5000 1500
이유림 여성 26 4000 1200
정주희 여성 25 2000 600
한혜림 여성 25 2000 600
이하이 여성 18 1000 0
아이유 여성 21 3000 900
정준이 남성 24 4000 1200
이이제 여성 20 2000 600
선동열 남성 47 3000 2100
선우선 여성 23 2000 600
선우용녀 여성 51 1000 700
남주혁 남성 20 2000 600
남궁선 남성 17 1000 0
남이 남성 52 2000 1400 */
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-- ● RANK() → 등수(순위)를 반환하는 함수
SELECT EMPNO"사원번호", ENAME"사원명", DEPTNO"부서번호", SAL"급여"
FROM EMP;
/*
7369 SMITH 20 800
7499 ALLEN 30 1600
7521 WARD 30 1250
7566 JONES 20 2975
7654 MARTIN 30 1250
7698 BLAKE 30 2850
7782 CLARK 10 2450
7788 SCOTT 20 3000
7839 KING 10 5000
7844 TURNER 30 1500
7876 ADAMS 20 1100
7900 JAMES 30 950
7902 FORD 20 3000
7934 MILLER 10 1300*/
SELECT EMPNO"사원번호", ENAME"사원명", DEPTNO"부서번호", SAL"급여"
, RANK() OVER(ORDER BY SAL DESC)"전체급여순위" ---★
FROM EMP;
/* --전체급여순위
7839 KING 10 5000 1
7902 FORD 20 3000 2
7788 SCOTT 20 3000 2
7566 JONES 20 2975 4
7698 BLAKE 30 2850 5
778 CLARK 10 2450 6
7499 ALLEN 30 1600 7
7844 TURNER 30 1500 8
7934 MILLER 10 1300 9
752 WARD 30 1250 10
7654 MARTIN 30 1250 10
7876 ADAMS 20 1100 12
790 JAMES 30 950 13
7369 SMITH 20 800 14*/
SELECT EMPNO"사원번호", ENAME"사원명", DEPTNO"부서번호", SAL"급여"
, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)"부서내급여순위"
, RANK() OVER(ORDER BY SAL DESC)"전체급여순위" ---★
FROM EMP;
/*
7839 KING 10 5000 1 1
7902 FORD 20 3000 1 2
7788 SCOTT 20 3000 1 2
7566 JONES 20 2975 3 4
7698 BLAKE 30 2850 1 5
7782 CLARK 10 2450 2 6
7499 ALLEN 30 1600 2 7
7844 TURNER 30 1500 3 8
7934 MILLER 10 1300 3 9
7521 WARD 30 1250 4 10
7654 MARTIN 30 1250 4 10
7876 ADAMS 20 1100 4 12
7900 JAMES 30 950 6 13
7369 SMITH 20 800 5 14*/
-- ● DENSE_RANK() → 서열을 반환하는 함수
SELECT EMPNO"사원번호", ENAME"사원명", DEPTNO"부서번호", SAL"급여"
, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)"부서내급여순위"
, DENSE_RANK() OVER(ORDER BY SAL DESC)"전체급여순위" ---★
FROM EMP;
/*
7839 KING 10 5000 1 1
7902 FORD 20 3000 1 2
7788 SCOTT 20 3000 1 2
7566 JONES 20 2975 2 3 ※ RANK()의 경우 공동1위 뒤에는 3위가 오며
7698 BLAKE 30 2850 1 4 DENSE_RANK()는 공동 1위 뒤에 2위가 온다.
7782 CLARK 10 2450 2 5
7499 ALLEN 30 1600 2 6
7844 TURNER 30 1500 3 7
7934 MILLER 10 1300 3 8
7521 WARD 30 1250 4 9
7654 MARTIN 30 1250 4 9
7876 ADAMS 20 1100 3 10
7900 JAMES 30 950 5 11
7369 SMITH 20 800 4 12
*/
-- ● EMP 테이블의 사원 정보를
-- 사원명, 부서번호, 연봉, 부서내연봉순위, 전체연봉순위 항목으로 조회한다.
SELECT *
FROM EMP;
SELECT ENAME"사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"--, 부서내연봉순위, 전체연봉순위
, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0) DESC) "부서내연봉순위"
, DENSE_RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC)"전체연봉순위"
FROM EMP;
/*-- 부서내연봉순위 전체연봉순위
KING 10 60000 1 1
FORD 20 36000 1 2
SCOTT 20 36000 1 2
JONES 20 35700 2 3
BLAKE 30 34200 1 4
CLARK 10 29400 2 5
ALLEN 30 19500 2 6
TURNER 30 18000 3 7
MARTIN 30 16400 4 8
MILLER 10 15600 3 9
WARD 30 15500 5 10
ADAMS 20 13200 3 11
JAMES 30 11400 6 12
SMITH 20 9600 4 13*/
SELECT ENAME"사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"--, 부서내연봉순위, 전체연봉순위
, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0) DESC)
, DENSE_RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC)
FROM EMP
ORDER BY DEPTNO;
SELECT T.*
, RANK() OVER(PARTITION BY T.부서번호 ORDER BY T.연봉 DESC) "부서내연봉순위"
, RANK() OVER(ORDER BY T.연봉 DESC) "전체연봉순위"
FROM
(
SELECT ENAME "사원명", DEPTNO "부서번호" , SAL*12+NVL(COMM,0) "연봉"
FROM EMP
)T
ORDER BY 2,3 DESC;
/*KING 10 60000 1 1
CLARK 10 29400 2 6
MILLER 10 15600 3 10
FORD 20 36000 1 2
SCOTT 20 36000 1 2
JONES 20 35700 3 4
ADAMS 20 13200 4 12
SMITH 20 9600 5 14
BLAKE 30 34200 1 5
ALLEN 30 19500 2 7
TURNER 30 18000 3 8
MARTIN 30 16400 4 9
WARD 30 15500 5 11
JAMES 30 11400 6 13
*/
-- ● EMP 테이블에서 전체 연봉 순위가 1등부터 5등까지만...
-- 사원명, 부서번호, 연봉, 전체연봉순위 항목으로 조회한다.
--[첫 번째 방법]
SELECT ENAME"사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP
WHERE RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC)<=5;
--===>> 에러발생 "window functions are not allowed here"
-- ※ 위의 내용은 RANK() OVER() 함수를 WHERE 조건절에서 사용한 경우이며
-- 이 함수는 WHERE 조건절에서 사용할 수 없기 때문에 발생하는 에러이다.
-- 이 경우, 우리는 INLINE VIEW를 활용하여 풀이해야 한다.
-- 따라서 [첫 번째 방법]은 안됨.
--[두 번째 방법]
SELECT T.사원명, T.부서번호, T.연봉, T.전체연봉순위
FROM
(
SELECT ENAME"사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP
)T
WHERE T.전체연봉순위 <=5;
/*
KING 10 60000 1
SCOTT 20 36000 2
FORD 20 36000 2
JONES 20 35700 4
BLAKE 30 34200 5
*/
-- ● EMP테이블에서 각 부서별 연봉 등수가 1등 부터 2등 까지만 조회한다,
-- 사원번호, 사원명, 부서번호, 연봉, 부서내연봉등수, 전체연봉등수
SELECT *
FROM
(
SELECT EMPNO "사원번호", ENAME"사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0) DESC) "부서내연봉순위"
, RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP
)T
WHERE T.부서내연봉순위 IN (1,2);
/*
7839 KING 10 60000 1 1
7902 FORD 20 36000 1 2
7788 SCOTT 20 36000 1 2
7698 BLAKE 30 34200 1 5
7782 CLARK 10 29400 2 6
7499 ALLEN 30 19500 2 7*/
SELECT T.*
FROM
(
SELECT EMPNO "사원번호", ENAME"사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"
, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0) DESC) "부서내연봉순위"
, DENSE_RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP
)T;
--WHERE T.부서내연봉순위 IN (1,2);
--ORDER BY 3,4 DESC;
/*
7839 KING 10 60000 1 1
7902 FORD 20 36000 1 2
7788 SCOTT 20 36000 1 2
7566 JONES 20 35700 2 3
7698 BLAKE 30 34200 1 4
7782 CLARK 10 29400 2 5
7499 ALLEN 30 19500 2 6
*/
/*--WHERE T.부서내연봉순위 <=2; --딱히 데이터의 순서가 의미있는 것은 아니다만
7839 KING 10 60000 1 1
7782 CLARK 10 29400 2 5
7902 FORD 20 36000 1 2
7788 SCOTT 20 36000 1 2
7566 JONES 20 35700 2 3
7698 BLAKE 30 34200 1 4
7499 ALLEN 30 19500 2 6*/
--------------------------------------------------------------------------------------
