SQL 기본 및 활용 (1)

조인의 형태

INNER JOIN

ON 절

SELECT A.SUBWAY_STATN_NO
     , A.LN_NM
     , A.STATN_NM
     , B.BEGIN_TIME
     , B.END_TIME
     , CASE WHEN B.TK_GFF_SE_CD = 'TGS001' THEN '승차'
            WHEN B.TK_GFF_SE_CD = 'TGS002' THEN '하차'
             END TK_GFF_SE_NM
     , B.TK_GFF_CNT
  FROM TB_SUBWAY_STATN A
 INNER JOIN TB_SUBWAY_STATN_TK_GFF B
         ON (A.SUBWAY_STATN_NO = B.SUBWAY_STATN_NO)
 WHERE A.SUBWAY_STATN_NO = '000001' --1호선 서울역
   AND B.STD_YM = '202010'
   AND B.BEGIN_TIME = '0800'
   AND B.END_TIME = '0900'
 ORDER BY B.TK_GFF_CNT DESC;

• ON절, WHERE절, SELECT절에 존재하는 모든 조인 칼럼에 대해서 앨리어스를 기재해야 함.

NATURAL JOIN

SELECT DEPT_CD
     , A.DEPT_NM
     , B.EMP_NO
     , B.EMP_NM
  FROM TB_DEPT_6_1_6 A NATURAL JOIN TB_EMP_6_1_6 B
 ORDER BY DEPT_CD
;

• TB_DEPT_6_1_6 과 TB_EMP_6_1_6에 둘다 DEPT_CD 컬럼이 존재하여 그 칼럼을 기준으로 NATURAL JOIN을 실시함. SELECT 문에서 A.DEPT_CD, B.DEPT_CD 와 같은 형태로 조회 불가 ⇒ SQL 에러 발생함

USING 절

SELECT SUBWAY_STATN_NO
     , A.LN_NM
     , A.STATN_NM
     , B.BEGIN_TIME
     , B.END_TIME
     , CASE WHEN B.TK_GFF_SE_CD = 'TGS001' THEN '승차'
            WHEN B.TK_GFF_SE_CD = 'TGS002' THEN '하차'
             END TK_GFF_SE_NM
     , B.TK_GFF_CNT
  FROM TB_SUBWAY_STATN A JOIN TB_SUBWAY_STATN_TK_GFF B
 USING (SUBWAY_STATN_NO)
 WHERE SUBWAY_STATN_NO = '000001' --1호선 서울역
   AND B.STD_YM = '202010'
   AND B.BEGIN_TIME = '0800'
   AND B.END_TIME = '0900'
 ORDER BY B.TK_GFF_CNT DESC
;

• USING 절에 기재하는 조인 칼럼에는 앨리어스를 붙일 수 없음. A.SUBWAY_STATN_NO, B.SUBWAY_STATN_NO 라고 하면 SQL 문법 에러가 발생
• 마찬가지로 SELECT 나 WHERE 절에도 앨리어스 사용 못함

OUTER JOIN

CROSS JOIN

SELECT ROWNUM AS RNUM
     , NVL(A.DEPT_CD, '(Null)') AS A_DEPT_CD
     , NVL(A.DEPT_NM, '(Null)') AS A_DEPT_NM
     , NVL(B.EMP_NO , '(Null)') AS B_EMP_NO
     , NVL(B.EMP_NM , '(Null)') AS B_EMP_NM
     , NVL(B.DEPT_CD, '(Null)') AS B_DEPT_CD
  FROM TB_DEPT_6_1_10 A
 CROSS JOIN TB_EMP_6_1_10 B
 ORDER BY RNUM;

• CROSS JOIN을 하면 두 테이블의 데이터의 곱집합의 결과가 나오게 됨. 한 테이블 데이터 5개, 다른 테이블 데이터 9개면 5*9 = 45 개의 결과값이 나오게 됨

• CROSS JOIN은 특정 테이블의 데이터를 복제할 때 많이 사용함

집합연산자

UNION과 UNION ALL의 결과집합

• UNION 연산은 중복된 행에 대해 중복을 제거해서 출력하며 중복을 제거하는 과정에서 정렬을 수행하기도 함(DBMS 내부에서 작동). 하지만 SQL문 수행 시 집합의 정렬을 보장하기 위해서는 반드시 ORDER BY 절을 사용해야 함

• UNION 및 UNION ALL 연산 시 ORDER BY 절에 기재하는 칼럼에는 앨리어스 사용할 수 없음

SELECT A.INDUTY_CL_CD
     , A.INDUTY_CL_NM
  FROM TB_INDUTY_CL A
 WHERE INDUTY_CL_SE_CD = 'ICS002' --중
   AND A.INDUTY_CL_CD LIKE 'N%' --관광/여가/오락
UNION ALL
SELECT B.INDUTY_CL_CD AS 업종분류코드, B.INDUTY_CL_NM AS 업종분류명
  FROM TB_INDUTY_CL B
 WHERE B.INDUTY_CL_SE_CD = 'ICS002' --중
   AND B.INDUTY_CL_CD LIKE 'O%' --숙박
 ORDER BY INDUTY_CL_CD
;

• UNION/UNION ALL 연산 시에 출력되는 집합의 헤더 값은 첫 번째 SQL문(맨 위의 SQL문)의 SELECT절에 기재한 칼럼명(혹은 앨리어스명)을 따르게 됨

계층형 질의와 셀프 조인

계층형 질의

• 테이블에 계층형 데이터를 입력할 수 있고, 이러한 계층형 구조의 데이터를 SQL 문으로 표현하는 것을 계층형 질의 혹은 계층형 SQL 이라고 함

• 또한 동일한 테이블끼리 조인 연산을 수행하는 조인 방식인 셀프 조인으로도 계층형 구조로 이루어진 데이터를 조회할 수 있음

SELECT A.INDUTY_CL_CD
     , A.INDUTY_CL_NM
     , B.INDUTY_CL_SE_CD
     , B.INDUTY_CL_SE_NM
     , LEVEL LVL
     , LPAD(' ', 4*(LEVEL-1))|| A.INDUTY_CL_CD || '(' || A.INDUTY_CL_NM || ')' AS "업종분류코드(명)"
     , CONNECT_BY_ISLEAF AS CBI
  FROM TB_INDUTY_CL A
     , TB_INDUTY_CL_SE B
 WHERE A.INDUTY_CL_SE_CD = B.INDUTY_CL_SE_CD
 START WITH A.UPPER_INDUTY_CL_CD IS null
 CONNECT BY PRIOR A.INDUTY_CL_CD = A.UPPER_INDUTY_CL_CD
 ORDER SIBLINGS BY A.INDUTY_CL_CD
;

• START WITH 조건 : 계층 구조 전개의 시작 위치를 지정하는 구문. 즉 루트데이터를 지정함

• CONNECT BY [NOCYCLE] [PRIOR] A AND B : CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문임
• PRIOR PK(자식) = FK(부모) 형태를 사용하면 계층 구조에서 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 순방향 전개를 한다.
• PRIOR FK(부모) = PK(자식) 형태를 사용하면 반대로 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 역방향 전개를 한다.
• NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.

• ORDER SIBLINGS BY 칼럼 : 형제 노드(동일 LEVEL)사이에서 정렬을 수행

• CONNECT_BY_ROOT : 현재 행의 최상위 노드를 구함

• SYS_CONNECT_BY_PATH : 계층형의 경로를 간편하게 출력하는 데 이용

계층형 SQL에서 제공하는 가상 칼럼

• LEVEL : 루트 데이터면 1. 1부터 시작

• CONNECT_BY_ISLEAF: 전개과정에서 해당 데이터가 리프 데이터면 1이고 아니면 0

• CONNECT_BY_ISCYCLE : 전개과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1이고 그렇지 않으면 0이다.

SELF JOIN의 활용

SELECT A.INDUTY_CL_CD
     , NVL(A.INDUTY_CL_NM, '(Null)') AS INDUTY_CL_NM
     , NVL(A.UPPER_INDUTY_CL_CD, '(Null)') AS "상위업종분류코드"
     , NVL(B.UPPER_INDUTY_CL_CD, '(Null)') AS "차상위업종분류코드"
  FROM TB_INDUTY_CL A
     , TB_INDUTY_CL B
 WHERE A.UPPER_INDUTY_CL_CD = B.INDUTY_CL_CD
   AND A.INDUTY_CL_CD LIKE 'Q12%' --커피점/카페
ORDER BY A.INDUTY_CL_CD
;

서브 쿼리

• 서브 쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인 쿼리는 서브쿼리의 칼럼을 사용할 수 없음.

서브 쿼리 사용 시 주의점

• 서브쿼리는 서브쿼리를 소괄호 “()”로 감싸서 사용

• 서브쿼리는 단일행 또는 복수행 비교 연산자와 함께 사용 가능하다.

• 단일행 비교 연산자는 서브 쿼리의 결과가 반드시 1건 이하여야 한다.

• 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관없다. 1건 혹은 여러 건도 가능하다.

• 서브 쿼리 내에서는 ORDER BY 절을 사용하지 못한다. ORDER BY 절은 전체 SQL 문 내에서 오직 1개만 올 수 있기 때문에 ORDER BY 절은 메인쿼리의 맨 마지막 SQL문 아래에 위치해야 한다.

서브 쿼리 사용이 가능한 위치

• SELECT절 : SELECT 절에 위치한 서브쿼리를 스칼라 서브쿼리라고 함

• FROM 절 : FROM 절에 위치한 서브쿼리를 인라인뷰 서브쿼리라고 함

• WHERE 절 : WHERE 절에 위치한 서브쿼리를 서브쿼리라고 함

• HAVING 절 : HAVING 절에 위치한 서브쿼리를 서브쿼리라고 함

• INSERT 문의 VALUES 절 : 서브쿼리라고 부름

• UPDATE 문의 set 절 : 서브쿼리라고 부름

동작 방식에 따른 서브쿼리 분류

• 비연관 서브쿼리 : 서브쿼리가 메인쿼리의 칼럼을 가지고 있지 않은 형태의 서브쿼리. 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용

• 연관 서브쿼리 : 서브쿼리가 메인쿼리의 값을 가지고 있는 형태의 서브쿼리. 일반적으로 메인쿼리가 먼저 수행되어 읽혀 진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용함

반환 형태에 따른 서브쿼리 분류

SELECT A.TK_GFF_CNT
     , A.SUBWAY_STATN_NO
     , B.LN_NM
     , B.STATN_NM
  FROM TB_SUBWAY_STATN_TK_GFF A
     , TB_SUBWAY_STATN B
 WHERE A.STD_YM = '202010' --2020년 10월
   AND A.BEGIN_TIME = '0800' --출근시간대
   AND A.END_TIME = '0900' --출근시간대
   AND A.SUBWAY_STATN_NO = B.SUBWAY_STATN_NO
   AND TK_GFF_SE_CD = 'TGS002' --하차
   AND TK_GFF_CNT > (
                      SELECT K.TK_GFF_CNT
                        FROM TB_SUBWAY_STATN_TK_GFF K
                       WHERE K.SUBWAY_STATN_NO = '000615' --9호선 여의도역
                         AND K.STD_YM = '202010' --2020년 10월
                         AND K.BEGIN_TIME = '0800' --출근시간대
                         AND K.END_TIME = '0900' --출근시간대
                         AND K.TK_GFF_SE_CD = 'TGS002' --하차
                    )
ORDER BY A.TK_GFF_CNT desc
;

SELECT B.SUBWAY_STATN_NO
     , B.LN_NM
     , B.STATN_NM
  FROM TB_SUBWAY_STATN B
 WHERE B.SUBWAY_STATN_NO IN (
                              SELECT K.SUBWAY_STATN_NO
                                FROM TB_SUBWAY_STATN_TK_GFF K
                               WHERE K.STD_YM = '202010' --2020년 10월
                                 AND K.BEGIN_TIME = '0800' --출근시간대
                                 AND K.END_TIME = '0900' --출근시간대
                                 AND K.TK_GFF_SE_CD = 'TGS002' --하차
                                 AND K.TK_GFF_CNT >= 250000
                            )
ORDER BY B.SUBWAY_STATN_NO
;

SELECT A.ADSTRD_CD
     , B.ADSTRD_NM
     , A.STD_YM
     , A.POPLTN_SE_CD
     , A.AGRDE_SE_CD
     , A.POPLTN_CNT
  FROM TB_POPLTN A
     , TB_ADSTRD B
 WHERE A.STD_YM = '202010'
   AND A.POPLTN_SE_CD = 'T'
   AND (A.AGRDE_SE_CD, A.POPLTN_CNT) IN
                                       (
                                         SELECT K.AGRDE_SE_CD
                                              , MAX(K.POPLTN_CNT) AS POPLTN_CNT
                                           FROM TB_POPLTN K
                                          WHERE K.STD_YM = '202010' --2020년 10월 기준
                                            AND K.POPLTN_SE_CD = 'T' --인구구분코드 : 전체
                                          GROUP BY K.AGRDE_SE_CD
                                       )
   AND A.ADSTRD_CD = B.ADSTRD_CD
 ORDER BY A.AGRDE_SE_CD
;

EXISTS 문 서브쿼리 사용

SELECT A.SUBWAY_STATN_NO
     , A.LN_NM
     , A.STATN_NM
  FROM TB_SUBWAY_STATN A
 WHERE EXISTS ( SELECT 1
                  FROM TB_SUBWAY_STATN_TK_GFF K
                 WHERE K.SUBWAY_STATN_NO = A.SUBWAY_STATN_NO
                   AND K.STD_YM = '202010'
                   AND TK_GFF_CNT >= 250000
              )
ORDER BY A.SUBWAY_STATN_NO
;

• EXISTS 문은 서브쿼리의 결과가 참이라면 결과집합에 포함시킴

• SELECT 1 인 이유는 해당 조건에 해당 하는 값이 있는지 체크만 하면 되기에 칼럼 값 프린트가 아닌 그냥 1을 출력시킨것

인라인뷰 서브쿼리

SELECT B.SUBWAY_STATN_NO
     , B.LN_NM
     , B.STATN_NM
     , A.STD_YM
     , A.BEGIN_TIME
     , A.END_TIME
     , A.TK_GFF_SE_CD
     , A.TK_GFF_CNT
  FROM
     (
       SELECT A.SUBWAY_STATN_NO
            , A.STD_YM
            , A.BEGIN_TIME
            , A.END_TIME
            , A.TK_GFF_SE_CD
            , A.TK_GFF_CNT
         FROM TB_SUBWAY_STATN_TK_GFF A
        WHERE A.STD_YM = '202010'
          AND A.BEGIN_TIME = '1800'
          AND A.END_TIME = '1900'
          AND A.TK_GFF_SE_CD = 'TGS002'
          AND A.TK_GFF_CNT > 150000
     ) A
     , TB_SUBWAY_STATN B
 WHERE A.SUBWAY_STATN_NO = B.SUBWAY_STATN_NO
 ORDER BY A.TK_GFF_CNT DESC
;

HAVING 절의 서브쿼리 사용

SELECT A.SUBWAY_STATN_NO
     , (SELECT L.STATN_NM || '(' || L.LN_NM ||')'
          FROM TB_SUBWAY_STATN L
         WHERE L.SUBWAY_STATN_NO = A.SUBWAY_STATN_NO
       ) AS STATN_INFO
     , ROUND(MAX(A.TK_GFF_CNT), 2) AS TK_GFF_CNT
  FROM TB_SUBWAY_STATN_TK_GFF A
 WHERE A.STD_YM = '202010'
   AND A.BEGIN_TIME = '1800'
   AND A.END_TIME = '1900'
   AND A.TK_GFF_SE_CD = 'TGS002'
 GROUP BY A.SUBWAY_STATN_NO
HAVING ROUND(MAX(A.TK_GFF_CNT), 2) > (
                                       SELECT ROUND(AVG(A.TK_GFF_CNT), 2) AS AVG_TK_GFF_CNT
                                         FROM TB_SUBWAY_STATN_TK_GFF A
                                        WHERE A.STD_YM = '202010'
                                          AND A.BEGIN_TIME = '1800'
                                          AND A.END_TIME = '1900'
                                          AND A.TK_GFF_SE_CD = 'TGS002'
                                     )
ORDER BY TK_GFF_CNT DESC
;

UPDATE 문의 SET 절에 위치하는 서브쿼리

UPDATE TB_POPLTN A
   SET A.ADSTRD_NM = ( SELECT K.ADSTRD_NM
                         FROM TB_ADSTRD K
                        WHERE K.ADSTRD_CD = A.ADSTRD_CD
                     );

INSERT 문에 사용되는 서브쿼리

INSERT INTO TB_SUBWAY_STATN_TK_GFF_SUM
     VALUES ('000615',
              (SELECT SUM(TK_GFF_CNT)
                 FROM TB_SUBWAY_STATN_TK_GFF
                WHERE SUBWAY_STATN_NO = '000615' --9호선 여의도역
              )
            )
;

뷰 사용하기

• 뷰는 SQL문(SELECT문)을 뷰로 생성해 놓고, 해당 뷰만 호출하면 뷰 내부에 있는 SQL문(SELECT문)을 호출할 수 있는 역할을 함

• 뷰를 사용함으로써 특정 업무에서 어떤 데이터를 조회할 때는 특정 뷰를 사용하는 것으로 지정할 수 있음

• 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.

• 편리성 : 복잡한 질의를 뷰로 생성 함으로써 관련 질의를 단순하게 작성할 수 잇다.

• 보안성 : 개인정보와 같은 숨기고 싶은 민감한 정보가 존재한다면, 뷰를 생성할 때 해당 정보(칼럼)을 제외시키고 생성함으로써 사용자들에게 정보 노출을 하지 않을 수 있다.

CREATE OR REPLACE VIEW V_STARBUCKS_POPLTN_INFO AS
SELECT A.BSSH_NO
     , A.CMPNM_NM
     , A.BHF_NM
     , A.ADSTRD_CD
     , B.ADSTRD_NM
     , SUM(C.POPLTN_CNT) AS SUM_POPLTN_CNT
  FROM TB_BSSH A
     , TB_ADSTRD B
     , TB_POPLTN C
 WHERE ( CMPNM_NM LIKE '%스타벅스%'
         OR
         UPPER(CMPNM_NM) LIKE '%STARBUCKS%'
       )
   AND A.ADSTRD_CD = B.ADSTRD_CD
   AND B.ADSTRD_CD = C.ADSTRD_CD
   AND C.STD_YM = '202010'
   AND C.POPLTN_SE_CD = 'T'
 GROUP BY A.BSSH_NO, A.CMPNM_NM, A.BHF_NM, A.ADSTRD_CD, B.ADSTRD_NM
 ORDER BY A.BSSH_NO, A.CMPNM_NM, A.BHF_NM, A.ADSTRD_CD, B.ADSTRD_NM;

SELECT A.BSSH_NO
     , A.CMPNM_NM
     , A.BHF_NM
     , A.ADSTRD_CD
     , A.ADSTRD_NM
     , A.SUM_POPLTN_CNT
  FROM V_STARBUCKS_POPLTN_INFO A
;

그룹 함수

그룹함수의 종류

• ROLLUP : 소그룹 간의 소계를 계산하는 기능

• CUBE : 다차원적인 소계를 계산하는 기능. 결합 가능한 모든 값에 대하여 다차원 집계를 생성함

• GROUPING SETS: 특정 항목에 대한 소계를 계산하는 기능

ROLLUP 함수를 이용한 합계 데이터 출력

SELECT A.AGRDE_SE_CD
     , SUM(A.POPLTN_CNT) AS POPLTN_CNT
  FROM TB_POPLTN A
 WHERE A.STD_YM = '202010'
   AND A.POPLTN_SE_CD = 'T'
 GROUP BY ROLLUP(A.AGRDE_SE_CD)
 ORDER BY A.AGRDE_SE_CD
;

000	4005030
010	4818481
020	6802784
030	6900752
040	8312221
050	8655292
060	6669780
070	3687451
080	1714191
090	250335
100	21699
	51838016 -- 합계 데이터

SELECT A.AGRDE_SE_CD
     , A.POPLTN_SE_CD
     , SUM(A.POPLTN_CNT) AS POPLTN_CNT
  FROM TB_POPLTN A
 WHERE A.STD_YM = '202010'
   AND A.POPLTN_SE_CD IN ('M', 'F')
 GROUP BY ROLLUP(A.AGRDE_SE_CD, A.POPLTN_SE_CD)
 ORDER BY A.AGRDE_SE_CD
;
---
000	F	1951273
000	M	2053757
000		4005030
010	F	2325821
010	M	2492660
010		4818481

• 위와 같이 쿼리하면 연령대별 인구수 합계, 연령대별 + 인구구분별 인구수 합계, 전체인구수 합계 가 출력됨

ROLLUP + GROUPING 함수를 이용한 합계 데이터 출력

• ROLLUP 함수에 기재한 칼럼을 GROUPING 함수에 인자로 넣으면 합계를 표현하는 행에 대해서는 1이 출력됨. 이를 이용하여 CASE WHEN 문으로 합계/소계인 행에 대해 해당 합계가 어떤 합계인지 표현할 수 있음

SELECT CASE WHEN GROUPING(A.AGRDE_SE_CD) = 0
            THEN A.AGRDE_SE_CD
            ELSE '전체합계' END AS AGRDE_SE_CD
     , CASE WHEN GROUPING(A.POPLTN_SE_CD) = 0
            THEN A.POPLTN_SE_CD
            ELSE '연령대별남녀합계' END AS POPLTN_SE_CD
     , SUM(A.POPLTN_CNT) AS POPLTN_CNT
  FROM TB_POPLTN A
 WHERE A.STD_YM = '202010'
   AND A.POPLTN_SE_CD IN ('M', 'F')
 GROUP BY ROLLUP(A.AGRDE_SE_CD, A.POPLTN_SE_CD)
 ORDER BY A.AGRDE_SE_CD;

CUBE 함수를 이용한 합계 데이터 출력

SELECT CASE WHEN GROUPING_AGRDE_SE_CD = 1 AND GROUPING_POPLTN_SE_CD = 1
            THEN '전체합계'
            WHEN GROUPING_AGRDE_SE_CD = 1 AND GROUPING_POPLTN_SE_CD = 0
            THEN '연령대별합계'
            WHEN GROUPING_AGRDE_SE_CD = 0 AND GROUPING_POPLTN_SE_CD = 1
            THEN '성별합계'
            WHEN GROUPING_AGRDE_SE_CD = 0 AND GROUPING_POPLTN_SE_CD = 0
            THEN '연령대+성별합계' ELSE '' END AS 합계구분
      , NVL(AGRDE_SE_CD, '연령대합계') AS AGRDE_SE_CD
      , NVL(POPLTN_SE_CD, '성별합계') AS POPLTN_SE_CD
      , POPLTN_CNT
   FROM
      (
        SELECT A.AGRDE_SE_CD
             , GROUPING(A.AGRDE_SE_CD) AS GROUPING_AGRDE_SE_CD
             , A.POPLTN_SE_CD
             , GROUPING(A.POPLTN_SE_CD) AS GROUPING_POPLTN_SE_CD
             , SUM(A.POPLTN_CNT) AS POPLTN_CNT
          FROM TB_POPLTN A
         WHERE A.STD_YM = '202010'
           AND A.POPLTN_SE_CD IN ('M', 'F')
         GROUP BY CUBE(A.AGRDE_SE_CD, A.POPLTN_SE_CD)
         ORDER BY A.AGRDE_SE_CD
      ) A
;

• CUBE 함수를 이용하면 “연령대구분별(AGRDE_SE_CD)”, “연령대구분별(AGRDE_SE_CD) + 인구구분별(POPLTN_SE_CD)”, “인구구분별(POPLTN_SE_CD)”, “전체”의 총 4개 기준의 합계를 구함

UNION ALL + GROP BY 를 이용한 합계 데이터 출력

SELECT A.AGRDE_SE_CD AS AGRDE_SE_CD
     , '성별전체' AS POPLTN_SE_CD
     , SUM(POPLTN_CNT) POPLTN_CNT
  FROM TB_POPLTN A
 WHERE A.STD_YM = '202010'
   AND A.POPLTN_SE_CD IN ('M', 'F')
 GROUP BY AGRDE_SE_CD
UNION ALL
SELECT '연령대별전체' AS AGRDE_SE_CD
     , A.POPLTN_SE_CD AS POPLTN_SE_CD
     , SUM(POPLTN_CNT) POPLTN_CNT
  FROM TB_POPLTN A
 WHERE A.STD_YM = '202010'
   AND A.POPLTN_SE_CD IN ('M', 'F')
 GROUP BY POPLTN_SE_CD
UNION ALL
SELECT  '연령대별전체' AS AGRDE_SE_CD
      , '성별전체' AS POPLTN_SE_CD
      , SUM(POPLTN_CNT) POPLTN_CNT
  FROM TB_POPLTN A
 WHERE A.STD_YM = '202010'
   AND A.POPLTN_SE_CD IN ('M', 'F')
 ORDER BY AGRDE_SE_CD, POPLTN_SE_CD, POPLTN_CNT
;

• 위 SQL 문은 연령대구분코드별, 인구구분코드별, 전체 기준으로 인구수 합계를 구한 후 UNION ALL로 합친 모습임

• 위의 SQL에서 나오는 결과를 GROUPING SETS를 이용하면 한번에 가능함

SELECT NVL(AGRDE_SE_CD, '연령대별전체') AS AGRDE_SE_CD
     , NVL(POPLTN_SE_CD, '성별전체') AS POPLTN_SE_CD
     , SUM(POPLTN_CNT) POPLTN_CNT
  FROM TB_POPLTN A
 WHERE A.STD_YM = '202010'
   AND A.POPLTN_SE_CD IN ('M', 'F')
 GROUP BY GROUPING SETS(AGRDE_SE_CD, POPLTN_SE_CD, ()) -- ()는 전체합계를 의미
 ORDER BY AGRDE_SE_CD, POPLTN_SE_CD, POPLTN_CNT
;

• GROUPING SETS는 함수 인자의 순서가 서로 바뀌어도 동일한 결과집합을 출력함

DCL(Data Control Language)

• DDL 문의 일종으로서 커밋 혹은 롤백이 필요없이 실행하는 순간 적용됨. 권한을 줄 때는 GRANT, 권한 회수할 때는 REVOKE 문을 사용

CREATE USER DCL_TEST IDENTIFIED BY 1234; -- DCL_TEST 유저를 만드는 DCL
GRANT CREATE SESSION TO DCL_TEST; -- 접속 권한 부여
GRANT CREATE TABLE TO DCL_TEST; -- 테이블 생성 권한 부여
GRANT SELECT ON SQLD.TB_ADRES_CL_SE TO DCL_TEST; -- 테이블 조회 권한 부여

GRANT INSERT, DELETE, UPDATE ON SQLD.TB_ADRES_CL_SE TO DCL_TEST; -- DCL_TEST 계정
-- 에게 SQLD 계정이 소유한 테이블의 DML 권한 주기

-- 테이블 DML 권한 회수
REVOKE SELECT ON SQLD.TB_ADRES_CL_SE FROM DCL_TEST;
REVOKE INSERT, DELETE, UPDATE ON SQLD.TB_ADRES_CL_SE TO DCL_TEST;
REVOKE CREATE TABLE FROM DCL_TEST; 

ROLE을 이용한 권한 부여

CREATE ROLE CREATE_SESSION_TABLE; -- ROLE 생성

GRANT CREATE SESSION, CREATE TABLE TO CREATE_SESSION_TABLE; -- ROLE에 권한 추가

GRANT CREATE_SESSION_TABLE TO DCL_TEST; -- 사용자에게 ROLE 권한 부여

오라클 DBMS에서 일반적으로 부여하는 ROLE

• CONNECT : 데이터베이스에 접속할 수 있는 CREATE SESION 권한 존재
• CREATE SESSION

• RESOURCE : 각종 데이터베이스의 객체를 생성할 수 있는 권한이 주어짐
• CREATE CLUSTER
• CREATE PROCEDURE
• CREATE TYPE
• CREATE SEQUENCE
• CREATE TRIGGER
• CREATE OPERATOR
• CREATE TABLE
• CREATE INDEXTYPE

DCL_TEST 계정 제거 및 재생성, 기본 ROLE 부여

DROP USER DCL_TEST CASCADE; -- 유저가 가지고 있는 모든 오브젝트 및 유저를 제거하는 SQL 문

CREATE USER DCL_TEST IDENTIFIED BY 1234;
GRANT CONNECT, RESOURCE TO DCL_TEST; -- 대표적인 Role 2개를 부여