핵심정리

5. 외워야 할 SQL 문법

1. Hirarchical SQL (계층형 질의)

root에서 시작해서 주어진 조건에 맞게 전개하는 방식으로 Query

SELECT ...
  FROM table_name
 WHERE ...
 START WITH (root condition)
 CONNECT BY [NOCYCLE] PRIOR (root id) = (child's root id)
 [ORDER SIBLINGS BY (columns...)]

• 가상 Coluns
  • LEVEL : root = 1, leaf 방향으로 1씩 증가
  • CONNECT_BY_ISLEAF : leaf면 1 (자식이 있으면 0, 없으면 1)
  • CONNECT_BY_ISCYCLE : leaf가 아니면서 조상 중에 자기자신이 있으면 1 (cycle에 속해 있으면 1, 아니면 0)
• 전용 함수
  • SYS_CONNECT_BY_PATH(column, separator) : root부터 현재까지 경로
  • CONNECT_BY_ROOT column : root 데이터의 컬럼을 표시
• Oracle SCOTT.EMP Table에서 MANAGER = NULL부터 전개

  SELECT EMP.*,
       LPAD(' ',LEVEL - 1) || LEVEL "LEVEL",
       CONNECT_BY_ISLEAF ISLEAF,
       SUBSTR(SYS_CONNECT_BY_PATH(ENAME,'-'),2) PATH,
       CONNECT_BY_ROOT ENAME ROOT
  FROM EMP
   START WITH MGR IS NULL
   CONNECT BY PRIOR EMPNO = MGR
   ORDER SIBLINGS BY HIREDATE;
  

• 반대로 MILLER(7934)로 부터 직속상관들을 전개

  SELECT EMP.*,
       LPAD(' ', LEVEL - 1) || LEVEL "LEVEL",
       SUBSTR(SYS_CONNECT_BY_PATH(ENAME, '-'),2) NAME_PATH,
       SUBSTR(SYS_CONNECT_BY_PATH(JOB,'-'),2) JOB_PATH
  FROM EMP
   START WITH EMPNO = 7934
   CONNECT BY PRIOR MGR = EMPNO
  

2. ROLLUP (Group Function)

각 단계별 소계를 계산

SELECT ... FROM ... WHERE ...
GROUP BY (소계를 적용하지 않을 column...), ROLLUP (소계 적용할 columns...)
-- ROLLUP 안에서 column들을 괄호로 묶을시 계층구조가 아닌 같은 level로 소계를 구분

• GROUPING(column) 함수 : 해당 소계에 속한 column에 대해서 1을 출력

 SELECT DECODE(GROUPING(DNAME), 1,'Total DEPT', DNAME) DNAME,
        DECODE(GROUPING(JOB), 1,'Toal JOB', JOB) JOB,
        COUNT(*) TOTAL_EMP,
        SUM(SAL) TOTAL_SAL
   FROM EMP e, DEPT d
  WHERE e.DEPTNO = d.DEPTNO
  GROUP BY ROLLUP (DNAME, JOB);

이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)