SQLスクリプト

※コピーすると改行が抜ける場合はソースをダブルクリックして強調表示を外してからコピーしてください

--10%のブロックをサンプルとして抽出
SELECT * FROM EMP SAMPLE BLOCK (10);

--階層問い合わせ
--PRIORは親側の条件
COLUMN ENAME FORMAT A15
COLUMN ENAME2 FORMAT A25
SELECT
    LEVEL,
    EMPNO,
    LPAD(' ',2*(LEVEL)) || ENAME ENAME,
    SYS_CONNECT_BY_PATH(ENAME, '>') ENAME2,
    MGR
FROM
    SCOTT.EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR;

     LEVEL      EMPNO ENAME           ENAME2                           MGR
---------- ---------- --------------- ------------------------- ----------
         1       7839   KING          >KING
         2       7566     JONES       >KING>JONES                     7839
         3       7902       FORD      >KING>JONES>FORD                7566
         4       7369         SMITH   >KING>JONES>FORD>SMITH          7902
         2       7698     BLAKE       >KING>BLAKE                     7839
         3       7499       ALLEN     >KING>BLAKE>ALLEN               7698
         3       7521       WARD      >KING>BLAKE>WARD                7698
         3       7654       MARTIN    >KING>BLAKE>MARTIN              7698
         3       7844       TURNER    >KING>BLAKE>TURNER              7698
         3       7900       JAMES     >KING>BLAKE>JAMES               7698
         2       7782     CLARK       >KING>CLARK                     7839
         3       7934       MILLER    >KING>CLARK>MILLER              7782

--WITH句を使用したクエリ
WITH
 A AS
(SELECT * FROM DEPT),
 C AS
(SELECT * FROM SALGRADE)
SELECT ENAME,DNAME,SAL,GRADE FROM EMP B,A,C
WHERE B.DEPTNO=A.DEPTNO 
  AND B.SAL > C.LOSAL AND B.SAL <= HISAL ;

ENAME                DNAME                               SAL      GRADE
-------------------- ---------------------------- ---------- ----------
KING                 ACCOUNTING                         5000          5
FORD                 RESEARCH                           3000          4
JONES                RESEARCH                           2975          4
BLAKE                SALES                              2850          4
CLARK                ACCOUNTING                         2450          4
ALLEN                SALES                              1600          3
TURNER               SALES                              1500          3
MILLER               ACCOUNTING                         1300          2
MARTIN               SALES                              1250          2
WARD                 SALES                              1250          2
JAMES                SALES                               950          1
SMITH                RESEARCH                            800          1

--分析関数(WINDOWファンクション)
SELECT
    ENAME,JOB,SAL,
    MAX(SAL) OVER (PARTITION BY JOB) JOB_MAX_SAL,
    LAG(ENAME,1,'NONE') OVER (PARTITION BY JOB ORDER BY SAL) PREV_ENAME,
    ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY SAL) SAL_ORDER
FROM EMP;

ENAME                JOB                       SAL JOB_MAX_SAL PREV_ENAME            SAL_ORDER
-------------------- ------------------ ---------- ----------- -------------------- ----------
FORD                 ANALYST                  3000        3000 NONE                          1
SMITH                CLERK                     800        1300 NONE                          1
JAMES                CLERK                     950        1300 SMITH                         2
MILLER               CLERK                    1300        1300 JAMES                         3
CLARK                MANAGER                  2450        2975 NONE                          1
BLAKE                MANAGER                  2850        2975 CLARK                         2
JONES                MANAGER                  2975        2975 BLAKE                         3
KING                 PRESIDENT                5000        5000 NONE                          1
WARD                 SALESMAN                 1250        1600 NONE                          1
MARTIN               SALESMAN                 1250        1600 WARD                          2
TURNER               SALESMAN                 1500        1600 MARTIN                        3
ALLEN                SALESMAN                 1600        1600 TURNER                        4

マニュアル

SQL リファレンス 10g リリース2（10.2）
リファレンス 10g リリース2（10.2）