o 예시 데이터 [SM-G981N]갤럭시 S20 [SM-G991N]갤럭시 S21 [SM-S901N]갤럭시 S22
[쿼리]
SELECT SUBSTRING(A.PRODUCT FROM '\[(.*?)\]') AS PRODUCT_CODE,
A.PRODUCT
FROM
(
SELECT '[SM-G981N]갤럭시 S20' AS PRODUCT
UNION ALL
SELECT '[SM-G991N]갤럭시 S21'
UNION ALL
SELECT '[SM-S901N]갤럭시 S22'
) A
WITH EMPP AS
(
SELECT 10 AS DEPTNO, 'CLERK' AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200 AS BONUS, 3 AS CHLDN FROM DUAL UNION ALL
SELECT 10 AS DEPTNO, 'MANAGER' AS JOB, 7782 AS EMPNO, 'CLARK' AS ENAME, 2450 AS SAL, 100 AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING' AS ENAME, 5000 AS SAL, 0 AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'ANALYST' AS JOB, 7902 AS EMPNO, 'FORD' AS ENAME, 3000 AS SAL, 300 AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'ANALYST' AS JOB, 7788 AS EMPNO, 'SCOTT' AS ENAME, 3000 AS SAL, 600 AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'CLERK' AS JOB, 7876 AS EMPNO, 'ADAMS' AS ENAME, 1100 AS SAL, 200 AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'CLERK' AS JOB, 7369 AS EMPNO, 'SMITH' AS ENAME, 800 AS SAL, 200 AS BONUS, 4 AS CHLDN FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'MANAGER' AS JOB, 7566 AS EMPNO, 'JONES' AS ENAME, 2975 AS SAL, 300 AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'CLERK' AS JOB, 7900 AS EMPNO, 'JAMES' AS ENAME, 950 AS SAL, 300 AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'MANAGER' AS JOB, 7698 AS EMPNO, 'BLAKE' AS ENAME, 2850 AS SAL, 400 AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7521 AS EMPNO, 'WARD' AS ENAME, 1250 AS SAL, 500 AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0 AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7499 AS EMPNO, 'ALLEN' AS ENAME, 1600 AS SAL, 300 AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS, 3 AS CHLDN FROM DUAL
),
/* NON-PIVOT 결과 */
A AS (
SELECT DEPTNO,
JOB,
SUM(CHLDN) AS CHLDN
FROM EMPP
GROUP BY DEPTNO, JOB
),
/* PIVOT 결과 */
B AS
(
SELECT *
FROM
(
SELECT
DEPTNO, -- GROUP BY
JOB, -- GROUP BY (JOIN 불가)
JOB J, -- JOIN용
SAL, -- SUM(SAL)
BONUS -- SUM(BONUS)
FROM EMPP
)
PIVOT
(
SUM(SAL) AS SAL,
SUM(BONUS) AS BNS
FOR JOB IN ( 'CLERK' AS C,
'ANALYST' AS A,
'PRESIDENT' AS P,
'SALESMAN' AS S,
'MANAGER' AS M
)
)
) /* End of B */
SELECT A.DEPTNO AS A_DEPTNO,
A.JOB AS A_JOB,
A.CHLDN,
B.*
FROM A, /* NON-PIVOT 결과 */
B /* PIVOT 결과 */
WHERE A.DEPTNO = B.DEPTNO
AND A.JOB = B.J
ORDER BY A_DEPTNO, A_JOB;
[실습] 부서 & 직함별로 급여 합, 보너스 합, 인원 수 출력 ※ 급여 합은 행을 열로 전환한 결과 포함
WITH EMP AS
(
SELECT 10 AS DEPTNO, 'CLERK' AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 10 AS DEPTNO, 'MANAGER' AS JOB, 7782 AS EMPNO, 'CLARK' AS ENAME, 2450 AS SAL, 100 AS BONUS FROM DUAL UNION ALL
SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING' AS ENAME, 5000 AS SAL, 0 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'ANALYST' AS JOB, 7902 AS EMPNO, 'FORD' AS ENAME, 3000 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'ANALYST' AS JOB, 7788 AS EMPNO, 'SCOTT' AS ENAME, 3000 AS SAL, 600 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'CLERK' AS JOB, 7876 AS EMPNO, 'ADAMS' AS ENAME, 1100 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'CLERK' AS JOB, 7369 AS EMPNO, 'SMITH' AS ENAME, 800 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'MANAGER' AS JOB, 7566 AS EMPNO, 'JONES' AS ENAME, 2975 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'CLERK' AS JOB, 7900 AS EMPNO, 'JAMES' AS ENAME, 950 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'MANAGER' AS JOB, 7698 AS EMPNO, 'BLAKE' AS ENAME, 2850 AS SAL, 400 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7521 AS EMPNO, 'WARD' AS ENAME, 1250 AS SAL, 500 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7499 AS EMPNO, 'ALLEN' AS ENAME, 1600 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS FROM DUAL
)
SELECT DEPTNO,
JOB,
SUM(SAL) AS SUM_SAL, /* (부서 & 직함별) 급여 합 */
SUM(CASE WHEN JOB = 'ANALYST' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_ANALYST,
SUM(CASE WHEN JOB = 'CLERK' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_CLERK,
SUM(CASE WHEN JOB = 'MANAGER' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_MANAGER,
SUM(CASE WHEN JOB = 'SALESMAN' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_SALESMAN,
SUM(CASE WHEN JOB = 'PRESIDENT' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_PRESIDENT,
SUM(BONUS) AS SUM_BONUS, /* (부서 & 직함별) 보너스 합 */
COUNT(*) AS CNT /* (부서 & 직함별) 인원 수 */
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB
WITH절의 EMP 데이터
쿼리 결과
CASE 문을 DECODE로 변경 (CASE문 방식과 결과는 동일)
WITH EMP AS
(
...
...
...
)
SELECT DEPTNO,
JOB,
SUM(SAL) AS SUM_SAL, /* (부서 & 직함별) 급여 합 - NON-PIVOT */
SUM(DECODE(JOB, 'ANALYST', SAL)) AS SUM_SAL_ANALYST, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'CLERK', SAL)) AS SUM_SAL_CLERK, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'MANAGER', SAL)) AS SUM_SAL_MANAGER, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'SALESMAN', SAL)) AS SUM_SAL_SALESMAN, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'PRESIDENT', SAL)) AS SUM_SAL_PRESIDENT, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(BONUS) AS SUM_BONUS, /* (부서 & 직함별) 보너스 합 - NON-PIVOT */
COUNT(*) AS CNT /* (부서 & 직함별) 인원 수 - NON-PIVOT */
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB
GROUP BY 조건을 변경(JOB을 그룹핑 제외)하여 쿼리 수행
WITH EMP AS
(
...
...
...
)
SELECT DEPTNO,
-- JOB, /* JOB을 그룹핑 제외 */
SUM(SAL) AS SUM_SAL,
SUM(CASE WHEN JOB = 'ANALYST'
THEN SAL
END) AS SUM_SAL_ANALYST,
...
...
...
SUM(BONUS) AS SUM_BONUS,
COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO --, JOB /* JOB을 그룹핑 제외 */
ORDER BY DEPTNO --, JOB /* JOB을 그룹핑 제외 */
[실습] STUDENT 테이블에서 학년 또는 학과별로 인원 수, 키의 합계, 몸무게의 합계를 동시에 출력
STUDENT 테이블 데이터
쿼리
SELECT GRADE AS "학년",
DEPTNO1 AS "학과",
COUNT(*) AS /* (학년 or 학과별) */ "인원 수",
SUM(HEIGHT) AS /* (학년 or 학과별) */ "키의 합계",
SUM(WEIGHT) AS /* (학년 or 학과별) */ "몸무게의 합계"
FROM STUDENT
GROUP BY GROUPING SETS(GRADE, DEPTNO1)
ORDER BY GRADE, DEPTNO1
-- 1. 사용자 생성
CREATE USER asl IDENTIFIED BY [비밀번호]
DEFAULT tablespace USERS
TEMPORARY tablespace TEMP;
-- 2. 권한부여
GRANT RESOURCE, CONNECT TO asl
2.1. 경기결과 테이블 생성
-- ASL."RESULT" definition
CREATE TABLE "RESULT"
("SEASON" NUMBER NOT NULL ENABLE,
"ROUND" NUMBER NOT NULL ENABLE,
"GAME" NUMBER(1,0) NOT NULL ENABLE,
"PLAYER_1_ID" VARCHAR2(100) NOT NULL ENABLE,
"PLAYER_1_NAME" VARCHAR2(100),
"PLAYER_2_ID" VARCHAR2(100) NOT NULL ENABLE,
"PLAYER_2_NAME" VARCHAR2(100),
"WINNER_ID" VARCHAR2(100),
"DATE" DATE,
"UPPER_SRG" VARCHAR2(100),
CONSTRAINT "RESULT_PK" PRIMARY KEY ("SEASON", "ROUND", "GAME")
);
COMMENT ON COLUMN "RESULT"."SEASON" IS '시즌';
COMMENT ON COLUMN "RESULT"."ROUND" IS '강';
COMMENT ON COLUMN "RESULT"."GAME" IS '경기';
COMMENT ON COLUMN "RESULT"."PLAYER_1_ID" IS '선수1 아이디';
COMMENT ON COLUMN "RESULT"."PLAYER_1_NAME" IS '선수1 이름';
COMMENT ON COLUMN "RESULT"."PLAYER_2_ID" IS '선수2 아이디';
COMMENT ON COLUMN "RESULT"."PLAYER_2_NAME" IS '선수2 이름';
COMMENT ON COLUMN "RESULT"."WINNER_ID" IS '승자 아이디';
COMMENT ON COLUMN "RESULT"."DATE" IS '경기 날짜';
COMMENT ON COLUMN "RESULT"."UPPER_SRG" IS '상위_게임유일값(SEASON_ROURND_GAME)';
COMMENT ON TABLE "RESULT" IS '경기결과';
/*
* 1. WITH절에서 경기별 고유값을 지정
*/
WITH BASE AS (
SELECT SEASON, -- 시즌(9)
ROUND, -- 라운드(8강, 4강, 결승)
SEASON || ROUND || GAME AS SRG, -- 경기별 고유값(시즌 + 라운드 + 경기 )
UPPER_SRG, -- 상위 경기 고유값
GAME,
NVL2(UPPER_SRG,
ROUND || '강 ' || GAME || '경기 - ' || PLAYER_1_NAME || ' VS ' || PLAYER_2_NAME,
'결승 - ' || PLAYER_1_NAME || ' VS ' || PLAYER_2_NAME
) AS TITLE,
PLAYER_1_NAME,
PLAYER_2_NAME
FROM RESULT
)
/*
* 2.경기별 고유값(SRG)와 상위 경기고유값(UPPER_SRG)으로 게층 구현
*/
SELECT SEASON,
ROUND,
SRG,
UPPER_SRG,
GAME,
LPAD(' ', 2*(LEVEL-1)) || TITLE AS LPAD_TITLE, --레벨별 들여쓰기
LEVEL
FROM BASE
START WITH UPPER_SRG IS NULL -- 결승은 상위 경기고유값이 NULL
CONNECT BY PRIOR SRG = UPPER_SRG -- 경기별 고유값과 상위 경기고유값으로 연결
ORDER BY GAME
SELECT 'A' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'C' T1_COL1 FROM DUAL
2번 테이블
SELECT 'A' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'D' T2_COL1 FROM DUAL
1번 테이블과 2번테이블 INNER-JOIN
SELECT T1.T1_COL1, T2.T2_COL1
FROM ( SELECT 'A' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'C' T1_COL1 FROM DUAL ) T1,
( SELECT 'A' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'D' T2_COL1 FROM DUAL ) T2
WHERE T1.T1_COL1 = T2.T2_COL1
