SsY/Class
006. 그룹 함수, 분석 함수(ROLLUP / CUBE / GROUPING SETS), HAVING절, 서브상관쿼리, ROW_NUMBER() / SEQUENCE()
planet-si
2023. 3. 29. 17:52
728x90
2023.3.29 (수)
SCOTT 계정 실습
- 어제 이어 문제 풀이
-- 위에서 조회한 내용을 아래와 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
부서번호 급여합
----------- ---------
10 8750
20 10875
30 9400
인턴 8000
모든부서 37025
*/더보기
SELECT CASE DEPTNO WHEN NULL THEN '인턴'
ELSE TO_CHAR(DEPTNO)
END "부서번호"
FROM TBL_EMP;
--==>>
/*
20
30
30
20
30
30
10
20
10
30
20
30
20
10
(null)
(null)
(null)
(null)
(null)
*/
SELECT CASE WHEN DEPTNO IS NULL THEN '인턴'
ELSE TO_CHAR(DEPTNO)
END "부서번호"
FROM TBL_EMP;
--==>>
/*
20
30
30
20
30
30
10
20
10
30
20
30
20
10
인턴
인턴
인턴
인턴
인턴
*/
SELECT CASE WHEN DEPTNO IS NULL THEN '인턴'
ELSE TO_CHAR(DEPTNO)
END "부서번호"
, SAL "급여"
FROM TBL_EMP;
--==>>>
/*
20 800
30 1600
30 1250
20 2975
30 1250
30 2850
10 2450
20 3000
10 5000
30 1500
20 1100
30 950
20 3000
10 1300
인턴 1500
인턴 1000
인턴 2000
인턴 2500
인턴 1000
*/
SELECT CASE WHEN DEPTNO IS NULL THEN '인턴'
ELSE TO_CHAR(DEPTNO)
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY DEPTNO;
--==>>
/*
30 9400
인턴 8000
20 10875
10 8750
*/
SELECT CASE WHEN DEPTNO IS NULL THEN '인턴'
ELSE TO_CHAR(DEPTNO)
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
SELECT CASE WHEN DEPTNO IS NULL THEN '인턴'
ELSE TO_CHAR(DEPTNO)
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
*/
SELECT NVL(DEPTNO,'인턴') "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>> 에러 발생 // DEPTNO 숫자컬럼
/*
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
*/
SELECT NVL2(DEPTNO, TO_CHAR(DEPTNO), '인턴') "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
인턴 8000
인턴 37025
*/- GROUPING()
- 그루핑
--※ GROUPING()
SELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
8000
37025
*/
SELECT DEPTNO "부서번호", SUM(SAL) "급여합", GROUPING(DEPTNO) "그루핑결과"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750 0
20 10875 0
30 9400 0
8000 0
37025 1
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN DEPTNO
ELSE '모든부서'
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>> 에러 발생
/*
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
203행, 18열에서 오류 발생
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN TO_CHAR(DEPTNO)
ELSE '모든부서'
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
8000
모든부서 37025
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '모든부서'
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
인턴 8000
모든부서 37025
*/더보기
-- 그루핑 개념 후 내 풀이
SELECT CASE WHEN GROUPING(DEPTNO) = 0 AND DEPTNO IS NULL
THEN '인턴'
WHEN GROUPING(DEPTNO) = 1 AND DEPTNO IS NULL
THEN '모든부서'
ELSE TO_CHAR(DEPTNO)
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
인턴 8000
모든부서 37025
*/--○ TBL_SAWON 테이블을 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
--------------------------------
성별 급여합
--------------------------------
남 XXXXXX
여 XXXXXX
모든사원 XXXXXX
--------------------------------
*/더보기
--내 풀이
-- 방법1
SELECT CASE WHEN GROUPING(T.성별) = 1 THEN '모든사원'
ELSE T.성별
END "성별"
, SUM(T.급여)"급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남'
WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여'
ELSE '판별불가'
END "성별"
, SAL "급여"
FROM TBL_SAWON
) T
GROUP BY ROLLUP(T.성별);
--방법2
SELECT NVL(T.성별,'모든사원')"성별"
, SUM(T.급여)"급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남'
WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여'
ELSE '판별불가'
END "성별"
, SAL "급여"
FROM TBL_SAWON
) T
GROUP BY ROLLUP(T.성별);더보기
-- 강사님 풀이
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남'
WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여'
ELSE '확인불가'
END "성별"
, SAL "급여"
FROM TBL_SAWON;
--==>>
/*
여 100
여 2000
여 5000
여 4000
여 2000
여 2000
남 2000
남 1000
여 1000
남 1500
남 3000
여 2000
남 4000
남 3000
여 2000
남 4000
*/
SELECT T.성별 "성별"
, SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남'
WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여'
ELSE '확인불가'
END "성별"
, SAL "급여"
FROM TBL_SAWON
) T
GROUP BY ROLLUP(T.성별);
--==>>
/*
남 18500
여 20100
38600
*/
SELECT CASE GROUPING(T.성별) WHEN 0 THEN T.성별
ELSE '모든사원'
END "성별"
, SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남'
WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여'
ELSE '확인불가'
END "성별"
, SAL "급여"
FROM TBL_SAWON
) T
GROUP BY ROLLUP(T.성별);
--==>>
/*
성별 급여합
----------- ----------
남 18500
여 20100
모든사원 38600
*/- VIEW 목록 검색
- 현재 가지고 있는 VIEW 목록들을 확인하고 싶어서 추가로 확인
더보기
SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'VIEW'- 데이터 수정 , 확인 , 커밋
더보기
SELECT *
FROM VIEW_SAWON;
--○ 데이터 수정
UPDATE TBL_SAWON
SET JUBUN = '9010102234567'
WHERE SANO = 1001;
--==>> 1 행 이(가) 업데이트되었습니다.
--○ 수정된 내용 확인
SELECT *
FROM VIEW_SAWON;
--> 데이터 수정 확인
--○ 커밋
COMMIT;
--==>> 커밋 완료.--○ TBL_SAWON 테이블을 다음과 같이 연령대별 인원수 형태로
-- 조회할 수 있도록 쿼리문을 구성한다.
/*
--------------------
연령대 인원수
--------------------
10 X
20 X
30 X
40 X
50 X
전체 XX
--------------------
*/더보기
-- 내 풀이
SELECT EXTRACT(YEAR FROM SYSDATE)
- CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899
WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999
ELSE -1
END "현재 나이"
FROM TBL_SAWON;
--==>>
/*
34
25
30
25
25
28
26
28
22
19
49
53
18
19
23
51
*/
SELECT CASE WHEN T.현재나이 >=50 AND T.현재나이 <60 THEN '50'
WHEN T.현재나이 >=40 THEN '40'
WHEN T.현재나이 >=30 THEN '30'
WHEN T.현재나이 >=20 THEN '20'
WHEN T.현재나이 >=10 THEN '10'
ELSE '판별불가'
END "연령대"
FROM
(
SELECT EXTRACT(YEAR FROM SYSDATE)
- CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899
WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999
ELSE -1
END "현재나이"
FROM TBL_SAWON
)T;
--==>>
/*
30
20
30
20
20
20
20
20
20
10
40
50
10
10
20
50
*/
SELECT CASE GROUPING(S.연령대) WHEN 1 THEN '전체'
ELSE S.연령대
END "연령대"
, COUNT(*) "인원수"
FROM
(
SELECT CASE WHEN T.현재나이 >=50 AND T.현재나이 <60 THEN '50'
WHEN T.현재나이 >=40 THEN '40'
WHEN T.현재나이 >=30 THEN '30'
WHEN T.현재나이 >=20 THEN '20'
WHEN T.현재나이 >=10 THEN '10'
ELSE '판별불가'
END "연령대"
FROM
(
SELECT EXTRACT(YEAR FROM SYSDATE)
- CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899
WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999
ELSE -1
END "현재나이"
FROM TBL_SAWON
)T
)S
GROUP BY ROLLUP(S.연령대);
SELECT CASE GROUPING(S.연령대) WHEN 1 THEN '전체'
ELSE S.연령대
END "연령대"
, COUNT(S.연령대) "인원수"
FROM
(
SELECT CASE WHEN T.현재나이 >=50 AND T.현재나이 <60 THEN '50'
WHEN T.현재나이 >=40 THEN '40'
WHEN T.현재나이 >=30 THEN '30'
WHEN T.현재나이 >=20 THEN '20'
WHEN T.현재나이 >=10 THEN '10'
ELSE '판별불가'
END "연령대"
FROM
(
SELECT EXTRACT(YEAR FROM SYSDATE)
- CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899
WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999
ELSE -1
END "현재나이"
FROM TBL_SAWON
)T
)S
GROUP BY ROLLUP(S.연령대);
--==>>
/*
연령대 인원수
---------- ----------
10 3
20 8
30 2
40 1
50 2
전체 16
*/-- 방법 1. (INLINEVIEW 를 두 번 중첩)
SELECT CASE GROUPING(Q2.연령대) WHEN 0 THEN TO_CHAR(Q2.연령대)
ELSE '전체'
END "연령대"
, COUNT(Q2.연령대) "인원수"
FROM
(
-- 연령대
SELECT Q1.나이 "나이" -- 연령대 제대로 나오는지 확인
, CASE WHEN Q1.나이 >=50 AND Q1.나이 < 60 THEN 50
WHEN Q1.나이 >=40 THEN 40
WHEN Q1.나이 >=30 THEN 30
WHEN Q1.나이 >=20 THEN 20
WHEN Q1.나이 >=10 THEN 10
ELSE 0
END "연령대"
FROM
(
--나이
SELECT 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 "나이"
FROM TBL_SAWON
)
Q1
)Q2
GROUP BY ROLLUP(Q2.연령대);
--==>>
/*
연령대 인원수
---------------------------------------- ----------
10 3
20 8
30 2
40 1
50 2
전체 16
*/-- 방법 2. (INLINEVIEW 를 한 번만 사용)
SELECT CASE GROUPING(Q.연령대) WHEN 0 THEN TO_CHAR(Q.연령대)
ELSE '전체'
END "연령대"
, COUNT(Q.연령대) "인원수"
FROM
(
SELECT TRUNC(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,-1) "연령대"
FROM TBL_SAWON
)Q
GROUP BY ROLLUP(Q.연령대);
--==>>
/*
연령대 인원수
---------------------------------------- ----------
10 3
20 8
30 2
40 1
50 2
전체 16
*/- ROLLUP 활용 및 CUBE + GROUPING SETS
--※ ROLLUP() 과 CUBE() 는
-- 그룹을 묶어주는 방식이 다르다. (차이)
-- ROLLUP(A,B,C)
-- → (A,B,C) / (A,B) / (A) / (A)
-- CUBE(A,B,C)
-- → (A,B,C) / (A,B) / (A,C) / (B,C) / (A) / (B) / (C) / ()- ROLLUP()
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY 1,2;
--==>>
/*
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
*/
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
--==>>
/*
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 -- 10번 부서 모든 직종의 급여 합
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875 -- 20번 부서 모든 직종의 급여 합
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 -- 30번 부서 모든 직종의 급여 합
2902 -- 모든 부서 모든 직종의 급여 합
*/- CUBE()
- ROLLUP() 보다 더 자세한 결과를 반환
- ++ 된 부분이 ROLLUP 보다 추가 된 결과
--○ CUBE() → ROLLUP() 보다 더 자세한 결과를 반환 받을 수 있다.
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY 1,2;
--==>>
/*
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 -- 10번 부서 모든 직종의 급여 합
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875 -- 20번 부서 모든 직종의 급여 합
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 -- 30번 부서 모든 직종의 급여 합
ANALYST 6000 -- 모든 부서 ANALYST 직종의 급여합 ++
CLERK 4150 -- 모든 부서 CLERK 직종의 급여합 ++
MANAGER 8275 -- 모든 부서 MANAGER 직종의 급여합 ++
PRESIDENT 5000 -- 모든 부서 PRESIDENT 직종의 급여합 ++
SALESMAN 5600 -- 모든 부서 SALESMAN 직종의 급여합 ++
2902 -- 모든 부서 모든 직종의 급여 합
*/- GROUPING SETS
--==> 위의 처리 내용은 직접적으로 원하는 결과를 얻지 못하거나(→ROLLUP())
-- 불필요한 너무 많은 결과물을 얻게되기 때문에(→ CUBE())
-- 다음의 쿼리 형태를 더 많이 사용하게 된다.
-- 다음 작성하는 쿼리는 조회하고자 하는 그룹만 직접 지정하여 묶어주는 방식이다.
-- → 『GROUPING SETS』더보기
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '전체부서'
END "부서번호"
, CASE GROUPING(JOB) WHEN 0 THEN JOB
ELSE '전체직종'
END "직종"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
--==>>
/*
부서번호 직종 급여합
---------------------------------------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 560
30 전체직종 9400
인턴 CLERK 2500
인턴 SALESMAN 5500
인턴 전체직종 8000
전체부서 전체직종 37025
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '전체부서'
END "부서번호"
, CASE GROUPING(JOB) WHEN 0 THEN JOB
ELSE '전체직종'
END "직종"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY 1,2;
--==>>
/*
부서번호 직종 급여합
---------------------------------------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 전체직종 9400
인턴 CLERK 2500
인턴 SALESMAN 5500
인턴 전체직종 8000
전체부서 ANALYST 6000
전체부서 CLERK 6650
전체부서 MANAGER 8275
전체부서 PRESIDENT 5000
전체부서 SALESMAN 11100
전체부서 전체직종 3702
*/SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '전체부서'
END "부서번호"
, CASE GROUPING(JOB) WHEN 0 THEN JOB
ELSE '전체직종'
END "직종"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY GROUPING SETS((DEPTNO, JOB), (DEPTNO), ())
ORDER BY 1,2;
--==>> -- ROLLUP() 과 같은 결과
/*
부서번호 직종 급여합
---------------------------------------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 전체직종 9400
인턴 CLERK 2500
인턴 SALESMAN 5500
인턴 전체직종 8000
전체부서 전체직종 3702
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
ELSE '전체부서'
END "부서번호"
, CASE GROUPING(JOB) WHEN 0 THEN JOB
ELSE '전체직종'
END "직종"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY GROUPING SETS((DEPTNO, JOB) , (DEPTNO) , (JOB), ()) --// 부서번호, 직종이 같은거 묶기 1 // 부서번호 같은거 묶기 // 직종 같은거 묶기 // 전체 묶기
ORDER BY 1,2;
--==>> -- CUBE() 와 같은 결과
/*
부서번호 직종 급여합
---------------------------------------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 전체직종 9400
인턴 CLERK 2500
인턴 SALESMAN 5500
인턴 전체직종 8000
전체부서 ANALYST 6000
전체부서 CLERK 6650
전체부서 MANAGER 8275
전체부서 PRESIDENT 5000
전체부서 SALESMAN 11100
전체부서 전체직종 37025
*/ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
--==>> Session이(가) 변경되었습니다.
--○ TBL_EMP 테이블에서 입사년도별 인원수를 조회한다.
/*
------------------------------
입사년도 인원수
------------------------------
1980 1
1981 10
1982 1
1987 2
2023 5
전체 19
------------------------------
*/더보기
--내 풀이
-- ROLLUP()
SELECT CASE GROUPING(T.입사년도) WHEN 0 THEN T.입사년도
ELSE '전체'
END "입사년도"
, COUNT(*) 인원수
FROM
(
SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도"
FROM TBL_EMP
)T
GROUP BY ROLLUP(T.입사년도);
--==>>
/*
입사년도 인원수
----------- ------
1980 1
1981 10
1982 1
1987 2
2023 5
전체 19
*/
--CUBE()
SELECT CASE GROUPING(T.입사년도) WHEN 0 THEN T.입사년도
ELSE '전체'
END "입사년도"
, COUNT(*) 인원수
FROM
(
SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도"
FROM TBL_EMP
)T
GROUP BY CUBE(T.입사년도);
--==>>
/*
입사년도 인원수
----------- ------
전체 19
1980 1
1981 10
1982 1
1987 2
2023 5
*/
--GROUPING SETS()
SELECT CASE GROUPING(T.입사년도) WHEN 0 THEN T.입사년도
ELSE '전체'
END "입사년도"
, COUNT(*) 인원수
FROM
(
SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도"
FROM TBL_EMP
)T
GROUP BY GROUPING SETS((T.입사년도),());
--==>>
/*
입사년도 인원수
----------- ------
1980 1
1981 10
1982 1
1987 2
2023 5
전체 19
*/--//1. 기준이 하나여서 동일하게 처리됨 확인
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY ROLLUP(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
--==>>
/*
1980 1
1981 10
1982 1
1987 2
2023 5
19
*/
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY CUBE(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
--==>>
/*
1980 1
1981 10
1982 1
1987 2
2023 5
19
*/
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY GROUPING SETS(EXTRACT(YEAR FROM HIREDATE),())
ORDER BY 1;
--==>>
/*
1980 1
1981 10
1982 1
1987 2
2023 5
19
*/
--//2. 세가지와 관계 없이 모든 GROUP BY에 해당하는 사항
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE,'YYYY'))
ORDER BY 1;
--==>>
/*
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
936행, 26열에서 오류 발생
*/
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY CUBE(TO_CHAR(HIREDATE,'YYYY'))
ORDER BY 1;
--==>>
/*
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
950행, 26열에서 오류 발생
*/
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY GROUPING SETS(TO_CHAR(HIREDATE,'YYYY'),())
ORDER BY 1;
--==>>
/*
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
964행, 26열에서 오류 발생
*/
--> 그룹바이에서 문자 타입으로 찾는데 위의 EXTRACT 는 숫자 타입으로 가져오기 때문에
SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE,'YYYY'))
ORDER BY 1;
--==>>
/*
1980 1
1981 10
1982 1
1987 2
2023 5
19
*/
SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY ROLLUP(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
--==>>
/*
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
995행, 16열에서 오류 발생
*/
--> 즉, GROUP BY의 절의 처리방식과 SELECT 의 처리방식이 같아야한다.- HAVING
- 관찰
-■■■ HAVING ■■■--
--○ EMP 테이블에서 부서번호가 20, 30인 부서를 대상으로
-- 부서의 총 급여가 10000보다 적을 경우만 부서별 총 급여를 조회한다.
SELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM EMP
WHERE DEPTNO IN(20,30)
GROUP BY DEPTNO;
--==>>
/*
30 9400
20 10875
*/
SELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM EMP
WHERE DEPTNO IN(20,30)
AND SUM(SAL) < 10000 -- WHERE 조건 절에서 그룹함수(그룹조건)를 사용할 수 없음
GROUP BY DEPTNO;
--==>> 에러 발생
/*
ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:
1,036행, 21열에서 오류 발생
*/
--업무상 위배되는 것이 아니라면 개별조건(WHERE)로 먼저 퍼올린 데이터를 처리하는 것이 좋다.
SELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM EMP
WHERE DEPTNO IN(20,30)
GROUP BY DEPTNO
HAVING SUM(SAL) < 10000; -- 그룹에 대한 조건절은 HAVING 에.
--==>> 30 9400
SELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY DEPTNO
HAVING DEPTNO IN (20,30)
AND SUM(SAL) < 10000;
--==>> 30 9400- 중첩 그룹함수 / 분석함수
- 관찰
- RANK(), DENSE_RANK() 버전 확인 필요
--■■■ 중첩 그룹함수 / 분석함수 ■■■--
-- 그룹 함수 2 LEVEL 까지 중첩해서 사용할 수 있다.
-- 이마저도.. MS-SQL은 불가능하다.
SELECT MAX(SUM(SAL)) "결과확인" -- 이렇게 두번 중첩하는 것이 2 LEVEL
FROM EMP
GROUP BY DEPTNO;
--==>> 10875
-- RANK()
-- DENSE_RANK()
-- → ORACLE 9i 부터 적용... MS-SQL 2005 부터 적용- 서브 상관 쿼리 (상관 서브 쿼리)
--※ 하위 버전에서는 RANK() 나 DENSE_RANK() 를 사용할 수 없기 때문에
-- 이를 대체하여 연산을 수행할 수 있는 방법을 강구해야 한다.
-- 예를 들어, EMP 테이블에서 급여의 등수를 구하고자 한다면...
-- 해당 사원의 급여보다 더 큰 값이 몇개인지 확인 후
-- 그 확인한 숫자에 +1 을 추가연산해주면 그것이 곧 등수가 된다.
SELECT ENAME, SAL
FROM EMP;
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800; --SMITH 의 급여 800
--==>> 14 --SMITH 의 급여 등수
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 1600; -- ALLEN 의 급여 1600
--==>> 7 -- ALLEN 의 급여 등수--※ 서브 상관 쿼리 (상관 서브 쿼리)
-- 메인 쿼리에 있는 테이블의 컬럼이
-- 서브 쿼리의 조건절(WHERE절, HAVING절) 에 사용되는 경우
-- 우리는 이 쿼리문을 서브 상관 쿼리 하고 부른다.
SELECT ENAME "사원명", SAL "급여", (1) "급여등수"
FROM EMP;
SELECT ENAME "사원명", SAL "급여", (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800) "급여등수"
FROM EMP;
-- 해당 쿼리문을 편하게 보기 위해 개행
SELECT ENAME "사원명", SAL "급여"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800) "급여등수"
FROM EMP;
-- FROM 의 EMP 들이 동일 하기 때문에 구분하기 위해 별칭을 사용 -- 끌어다 사용할 수 있도록 만듦
SELECT ENAME "사원명", SAL "급여"
, (SELECT COUNT(*) + 1
FROM EMP E2
WHERE SAL > 800) "급여등수"
FROM EMP E1;
-- 구분하기 쉽게 별칭을 적어둠
SELECT E1.ENAME "사원명", E1.SAL "급여"
, (SELECT COUNT(*) + 1
FROM EMP E2
WHERE E2.SAL > 800) "급여등수"
FROM EMP E1;
SELECT E1.ENAME "사원명", E1.SAL "급여"
, (SELECT COUNT(*) + 1
FROM EMP E2
WHERE E2.SAL > E1.SAL) "급여등수"
FROM EMP E1;
--==>>
/*
SMITH 800 14
ALLEN 1600 7
WARD 1250 10
JONES 2975 4
MARTIN 1250 10
BLAKE 2850 5
CLARK 2450 6
SCOTT 3000 2
KING 5000 1
TURNER 1500 8
ADAMS 1100 12
JAMES 950 13
FORD 3000 2
MILLER 1300 9
*/
SELECT E1.ENAME "사원명", E1.SAL "급여"
, (SELECT COUNT(*) + 1
FROM EMP E2
WHERE E2.SAL > E1.SAL) "급여등수"
FROM EMP E1
ORDER BY 3;
--==>>
/*
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 10
ADAMS 1100 12
JAMES 950 13
SMITH 800 14
*/--○ EMP 테이블을 대상으로
-- 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다.
-- 단, RANK() 함수를 사용하지 않고, 서브 상관 쿼리를 활용할 수 있도록 한다.더보기
--○ EMP 테이블을 대상으로
-- 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다.
-- 단, RANK() 함수를 사용하지 않고, 서브 상관 쿼리를 활용할 수 있도록 한다.
SELECT ENAME "사원명"
, SAL "급여"
, DEPTNO "부서번호"
, () "부서내급여등수"
, () "전체급여등수"
FROM EMP;
SELECT E1.ENAME "사원명"
, E1.SAL "급여"
, E1.DEPTNO "부서번호"
, (SELECT COUNT(*) + 1
FROM EMP E2
WHERE E2.DEPTNO = E1.DEPTNO
AND E2.SAL > E1.SAL) "부서내급여등수"
, (SELECT COUNT(*) + 1
FROM EMP E2
WHERE E2.SAL > E1.SAL) "전체급여등수"
FROM EMP E1
ORDER BY E1.DEPTNO, E1.SAL DESC;
--==>>
/*
사원명 급여 부서번호 부서내급여등수 전체급여등수
---------- ---------- ---------- -------------- ------------
KING 5000 10 1 1
CLARK 2450 10 2 6
MILLER 1300 10 3 9
SCOTT 3000 20 1 2
FORD 3000 20 1 2
JONES 2975 20 3 4
ADAMS 1100 20 4 12
SMITH 800 20 5 14
BLAKE 2850 30 1 5
ALLEN 1600 30 2 7
TURNER 1500 30 3 8
MARTIN 1250 30 4 10
WARD 1250 30 4 10
JAMES 950 30 6 13
*/--○ EMP 테이블을 대상으로 다음과 같이 조회할 수 있도록 쿼리문을 구성한다.
/*
-----------------------------------------------------------------------
사원명 부서번호 입사일 급여 부서내입사별급여누적
-----------------------------------------------------------------------
CLARK 10 1981-06-09 2450 2450
KING 10 1981-11-17 5000 7450
MILLER 10 1981-01-23 1300 8750
SMITH 20 1980-12-17 800 800
JONES 20 1981-04-02 2975 3775
:
-----------------------------------------------------------------------SELECT ENAME "사원명", DEPTNO "부서번호", HIREDATE "입사일", SAL "급여"
, (1)"부서내입사별급여누적"
FROM EMP
ORDER BY 2, 3;
-- E1, E2 와 같이 별칭을 붙이지 않아도 조회 된다.
SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여"
, (SELECT SUM(E2.SAL)
FROM EMP E2)"부서내입사별급여누적"
FROM EMP E1
ORDER BY 2, 3;
-- 부서내 급여누적이 같아짐
SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여"
, (SELECT SUM(E2.SAL)
FROM EMP E2
WHERE E2.DEPTNO = E1.DEPTNO)"부서내입사별급여누적"
FROM EMP E1
ORDER BY 2, 3;
-- 과거가 미래보다 작고, 해당일을 포함해야하기 때문에 <= 로 표시
SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여"
, (SELECT SUM(E2.SAL)
FROM EMP E2
WHERE E2.DEPTNO = E1.DEPTNO
AND E2.HIREDATE <= E1.HIREDATE) "부서내입사별급여누적"
FROM EMP E1
ORDER BY 2, 3;
--==>>
/*
CLARK 10 1981-06-09 2450 2450
KING 10 1981-11-17 5000 7450
MILLER 10 1982-01-23 1300 8750
SMITH 20 1980-12-17 800 800
JONES 20 1981-04-02 2975 3775
FORD 20 1981-12-03 3000 6775
SCOTT 20 1987-07-13 3000 10875
ADAMS 20 1987-07-13 1100 10875
ALLEN 30 1981-02-20 1600 1600
WARD 30 1981-02-22 1250 2850
BLAKE 30 1981-05-01 2850 5700
TURNER 30 1981-09-08 1500 7200
MARTIN 30 1981-09-28 1250 8450
JAMES 30 1981-12-03 950 9400
*/--○ TBL_EMP 테이블에서 입사한 사원 수가 가장 많았을 때의
-- 입사년월과 인원수를 조회할 수 있는 쿼리문을 구성한다.
/*
------------------------
입사년월 인원수
------------------------
XXXX-XX XX
------------------------
*/SELECT ENAME, HIREDATE
FROM TBL_EMP
ORDER BY 2;
--==>>
/*
SMITH 1980-12-17
ALLEN 1981-02-20
WARD 1981-02-22
JONES 1981-04-02
BLAKE 1981-05-01
CLARK 1981-06-09
TURNER 1981-09-08
MARTIN 1981-09-28
KING 1981-11-17
FORD 1981-12-03
JAMES 1981-12-03
MILLER 1982-01-23
SCOTT 1987-07-13
ADAMS 1987-07-13
ㅇㅇㅅ 2023-03-28
ㅊㅇㅅ 2023-03-28
ㅇㅅㅇ 2023-03-28
ㅊㅎㄱ 2023-03-28
ㅇㅎㅈ 2023-03-28
*/
-- 입사년월 별 인원수
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY-MM');
--==>>
/*
1981-05 1
1981-12 2
1982-01 1
1981-09 2
2023-03 5
1981-02 2
1981-11 1
1980-12 1
1981-04 1
1987-07 2
1981-06 1
*/
--// 위에서 확인한 가장 큰 값을 넣었을 때
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY-MM')
HAVING COUNT(*) = 5 ; -- 인원 수 확인
--==>> 2023-03 5
--// 직접 확인하지 않고 가장 큰 값을 확인
SELECT MAX(COUNT(*))
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
--==>> 5 -- 가장 큰 COUNT 값 확인
--// 서브 상관 쿼리가 HAVING 절에도 들어갈 수 있음
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY-MM')
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')) ;
--==>>
/*
입사년월 인원수
--------- ----------
2023-03 5
*/- ROW_NUMBER() , SEQUENCE
- ROW_NUMBER()
관찰
더보기
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) "관찰" -- 레코드 넘버링..?
, ENAME "사원명", SAL "급여", HIREDATE "입사일"
FROM EMP;
--==>>
/*
관찰 사원명 급여 입사일
---------- ---------- ---------- ----------
1 KING 5000 1981-11-17
2 FORD 3000 1981-12-03
3 SCOTT 3000 1987-07-13
4 JONES 2975 1981-04-02
5 BLAKE 2850 1981-05-01
6 CLARK 2450 1981-06-09
7 ALLEN 1600 1981-02-20
8 TURNER 1500 1981-09-08
9 MILLER 1300 1982-01-23
10 WARD 1250 1981-02-22
11 MARTIN 1250 1981-09-28
12 ADAMS 1100 1987-07-13
13 JAMES 950 1981-12-03
14 SMITH 800 1980-12-17
*/
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) "관찰" -- 이미 퍼올렸을 때 급여를 기준으로 넘버링을 함
, ENAME "사원명", SAL "급여", HIREDATE "입사일"
FROM EMP
ORDER BY ENAME; -- 이후 다시 ORDER BY로 정렬할 때 넘버링의 숫자는 바뀌지 않음!
--==>>
/*
관찰 사원명 급여 입사일
---------- ---------- ---------- ----------
12 ADAMS 1100 1987-07-13
7 ALLEN 1600 1981-02-20
5 BLAKE 2850 1981-05-01
6 CLARK 2450 1981-06-09
2 FORD 3000 1981-12-03
13 JAMES 950 1981-12-03
4 JONES 2975 1981-04-02
1 KING 5000 1981-11-17
11 MARTIN 1250 1981-09-28
9 MILLER 1300 1982-01-23
3 SCOTT 3000 1987-07-13
14 SMITH 800 1980-12-17
8 TURNER 1500 1981-09-08
10 WARD 1250 1981-02-22
*/--※ 게시판의 게시물 번호를
-- SEQUENCE 나 IDNETITY 를 사용하게 되면
-- 게시물을 삭제했을 경우, 삭제한 게시물의 자리에
-- 다음 번호를 가진 게시물이 등록되는 상황이 발생하게 된다.
-- 이는, 보안 측면에서나... 미관상... 바람직하지 않은 상황일 수 있기 때문에
-- ROW_NUMBER() 의 사용을 고려할 수 있다.
-- 관리의 목적으로 사용할 때에는 SEQUENCE 나 IDNETITY 를 사용하지만
-- 단순히 게시물을 보기 편하도록 목록화하여 사용자에게 리스트 형식으로 보여줄 때는
-- 사용하지 않는 것이 좋다.
--※ 관찰
CREATE TABLE TBL_AAA
( NO NUMBER(10)
, NAME VARCHAR2(40)
, GRADE CHAR
);
--==>> Table TBL_AAA이(가) 생성되었습니다.
INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(1,'ㅎㅇㅎ','A');
INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(2,'ㅊㅎㄱ','B');
INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(3,'ㄱㅂㅎ','A');
INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(4,'ㅈㅅㅇ','C');
INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(5,'ㅁㅅㅎ','A');
INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(6,'ㅎㅇㅎ','B');
INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(7,'ㅎㅇㅎ','B');
--==>> 1 행 이(가) 삽입되었습니다.* 7
SELECT *
FROM TBL_AAA;
--==>>
/*
1 ㅎㅇㅎ A
2 ㅊㅎㄱ B
3 ㄱㅂㅎ A
4 ㅈㅅㅇ C
5 ㅁㅅㅎ A
6 ㅎㅇㅎ B
7 ㅎㅇㅎ B
*/
COMMIT;
--==>> 커밋 완료.
UPDATE TBL_AAA
SET GRADE='A'
WHERE NAME='ㅎㅇㅎ'
--> 모든 ㅎㅇㅎ 의 성적이 A로 변함
UPDATE TBL_AAA
SET GRADE='A'
WHERE GRADE='B'
--> 7번 ㅎㅇㅎ 도 성적이 A로 변함
UPDATE TBL_AAA
SET GRADE='A'
WHERE NAME='ㅎㅇㅎ' AND GRADE='B'
--> 둘 다 만족하는 것도 6번 7번이 동일하여, 이 경우에는 번호를 쓰지 않고서는 바꿀 수 없다.- SQUENCE()
--○ SEQUENCE 생성(시퀀스, 주문번호)
-- → 사전적인 의미 : 1.(일련의) 연속적인 사건들 2.(사건,행동 등의) 순서
CREATE SEQUENCE SEQ_BOARD; -- 시퀀스 기본 생성 구문(MS-SQL 의 IDENTITY 와 동일한 개념)
--> 이와 같이 생성하면 기본적인 시퀀스가 생성된다.
CREATE SEQUENCE SEQ_BOARD -- 시퀀스 기본 생성 구문(MS-SQL 의 IDENTITY 와 동일한 개념)
START WITH 1 -- 시작값
INCREMENT BY 1 -- 증가값
NOMAXVALUE -- 최대값 (크기지정 / NOMAXVALUE → 제한 없음)
NOCACHE; -- 캐시 사용 안함(없음)
--// 미리 시퀀스들을 발행하여 나눠줄 수 있게 뽑아두는 것
--(장점 : 사용자들이 세션(번호표뽑는줄)에 머무르는 시간이 줄어듬)
--(단점 : 사용자들이 몰리지 않으면 중간에 남은 번호들이 버려져 번호가 건너뛰어질 수 있다)
--> //기본 구문 외에 붙는것은 모두 옵션이라고 한다. // 테이블과는 별개다
--==>> Sequence SEQ_BOARD이(가) 생성되었습니다.--○ 테이블 생성(TBL_BOARD) // 사용자가 작성하는지
CREATE TABLE TBL_BOARD -- TBL_BOARD 이름의 테이블 생성 → 게시판으로 활용
( NO NUMBER -- 게시물 번호 X
, TITLE VARCHAR2(50) -- 게시물 제목 ○
, CONTENTS VARCHAR2(2000) -- 게시물 내용 ○
, NAME VARCHAR2(30) -- 게시물 작성자 △
, PW VARCHAR2(40) -- 게시물 패스워드 △
, CREATED DATE DEFAULT SYSDATE -- 게시물 작성일 X
);
--==>> Table TBL_BOARD이(가) 생성되었습니다.
--○ 데이터 입력 → 게시판에 게시물 작성
INSERT INTO TBL_BOARD VALUES
(게시물번호, 게시물제목, 게시물내용, 작성자, 패스워드, 작성일);728x90