> For the complete documentation index, see [llms.txt](https://docs.tibero.com/tibero-manuals/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.tibero.com/tibero-manuals/7.2.6.manuals/sql-reference-guide/sql-queries/select/select.md).

# SELECT 예제

SELECT, WITH, FLASHBACK, PIVOT 관련 예제를 정리합니다.

## SELECT 예제

다음은 **SELECT**를 사용하는 예입니다.

```sql
-- SAMPLE TABLES
CREATE TABLE DEPT (
    DEPTNO NUMBER,
    DNAME  VARCHAR2(20),
    LOC    VARCHAR2(20)
);

CREATE TABLE EMP (
    EMPNO    NUMBER,
    ENAME    VARCHAR2(100),
    SALARY   NUMBER,
    DEPTNO   NUMBER,
    STATUS   NUMBER,
    MGRNAME  VARCHAR2(100),
    JOB      VARCHAR2(20)
);

INSERT INTO DEPT VALUES (5, 'Accounting', 'BUSAN');
INSERT INTO DEPT VALUES (7, 'Research',   'DAEGU');
INSERT INTO DEPT VALUES (9, 'IT',         'SEOUL');

INSERT INTO EMP VALUES (1, 'KING',   5000, 9, 0, NULL,    'CEO');
INSERT INTO EMP VALUES (2, 'CLARK',  3000, 5, 0, 'KING',  'ACC_MGR');
INSERT INTO EMP VALUES (3, 'FORD',   2800, 7, 0, 'KING',  'RES_MGR');
INSERT INTO EMP VALUES (4, 'ALLEN',  1700, 5, 0, 'CLARK', 'ACC_STAFF');
INSERT INTO EMP VALUES (5, 'SCOTT',  2500, 7, 0, 'FORD',  'RES_STAFF');

COMMIT;

SELECT * FROM EMP;

SELECT ENAME, SALARY * 1.05 FROM EMP WHERE DEPTNO = 5;

SELECT E.ENAME, E.SALARY, D.* FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

SELECT ENAME, SALARY, LOC FROM EMP NATURAL JOIN DEPT;

SELECT ENAME FROM EMP WHERE DEPTNO = 5
    UNION SELECT ENAME FROM EMP WHERE DEPTNO = 7;

SELECT DEPTNO, MAX(SALARY) FROM EMP GROUP BY DEPTNO HAVING DEPTNO >= 5;

SELECT DISTINCT DEPTNO FROM EMP;

SELECT * FROM EMP WHERE DEPTNO = 5 FOR UPDATE;

SELECT E.ENAME, D.* FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO FOR UPDATE OF LOC;
```

다음은 비순환 구조에서 **WITH** 절을 재귀적으로 사용하는 예입니다.

```sql
COLUMN RNAME     FORMAT A20
COLUMN NAME_TREE FORMAT A40
COLUMN P_NAME    FORMAT A15
COLUMN ISCYCLE   FORMAT A7

SET LINESIZE 180
SET PAGESIZE 100

WITH RECURSIVE (RECURSIVE_LEVEL, ID, NAME, NAME_TREE, P_NAME, POST_TEAM) AS (
    SELECT 1,
           EMPNO,
           ENAME,
           ENAME,
           MGRNAME,
           JOB
      FROM EMP
     WHERE EMPNO = 1
    UNION ALL
    SELECT R.RECURSIVE_LEVEL + 1,
           E.EMPNO,
           E.ENAME,
           R.NAME_TREE || '/' || E.ENAME,
           E.MGRNAME,
           E.JOB
      FROM EMP E, RECURSIVE R
     WHERE E.MGRNAME = R.NAME
)
SEARCH DEPTH FIRST BY NAME SET IDX
CYCLE P_NAME SET ISCYCLE TO 'Y' DEFAULT 'N'
SELECT
    RECURSIVE_LEVEL AS LV,
    LPAD(' ', 2 * (RECURSIVE_LEVEL - 1), ' ') || NAME AS RNAME,
    P_NAME,
    NAME_TREE,
    ISCYCLE,
    IDX
FROM RECURSIVE;

LV  RNAME   P_NAME  NAME_TREE              ISCYCLE  IDX
--- -------- ------ ---------------------- ------- ----
 1  KING            KING                   N        1
 2  CLARK    KING   KING/CLARK             N        2
 3  ALLEN    CLARK  KING/CLARK/ALLEN       N        3
 2  FORD     KING   KING/FORD              N        4
 3  SCOTT    FORD   KING/FORD/SCOTT        N        5

5 rows selected.
```

다음은 순환 구조에서 **WITH** 절을 재귀적으로 사용하는 예입니다.

```sql
-- make cycle
INSERT INTO EMP VALUES (6, 'FORD',   999, 7, 0, 'ALLEN', 'CYCLE_A');
INSERT INTO EMP VALUES (7, 'ALLEN',  999, 5, 0, 'SCOTT', 'CYCLE_B');
COMMIT;

COLUMN RNAME     FORMAT A20
COLUMN NAME_TREE FORMAT A40
COLUMN P_NAME    FORMAT A15
COLUMN ISCYCLE   FORMAT A7

SET LINESIZE 180
SET PAGESIZE 100

WITH RECURSIVE (RECURSIVE_LEVEL, ID, NAME, NAME_TREE, P_NAME, POST_TEAM) AS (
    SELECT 1,
           EMPNO,
           ENAME,
           ENAME,
           MGRNAME,
           JOB
      FROM EMP
     WHERE EMPNO = 1
    UNION ALL
    SELECT R.RECURSIVE_LEVEL + 1,
           E.EMPNO,
           E.ENAME,
           R.NAME_TREE || '/' || E.ENAME,
           E.MGRNAME,
           E.JOB
      FROM EMP E, RECURSIVE R
     WHERE E.MGRNAME = R.NAME
)
SEARCH DEPTH FIRST BY NAME SET IDX
CYCLE P_NAME SET ISCYCLE TO 'Y' DEFAULT 'N'
SELECT
    RECURSIVE_LEVEL AS LV,
    LPAD(' ', 2 * (RECURSIVE_LEVEL - 1), ' ') || NAME AS RNAME,
    P_NAME,
    NAME_TREE,
    ISCYCLE,
    IDX
FROM RECURSIVE;

LV  RNAME   P_NAME  NAME_TREE                                   ISCYCLE  IDX
--- ------- ------ ------------------------------------------- ------- ----
 1  KING            KING                                        N        1
 2  CLARK   KING    KING/CLARK                                  N        2
 3  ALLEN   CLARK   KING/CLARK/ALLEN                            N        3
 4  FORD    ALLEN   KING/CLARK/ALLEN/FORD                       N        4
 5  SCOTT   FORD    KING/CLARK/ALLEN/FORD/SCOTT                 N        5
 6  ALLEN   SCOTT   KING/CLARK/ALLEN/FORD/SCOTT/ALLEN           N        6
 7  FORD    ALLEN   KING/CLARK/ALLEN/FORD/SCOTT/ALLEN/FORD      Y        7
 2  FORD    KING    KING/FORD                                   N        8
 3  SCOTT   FORD    KING/FORD/SCOTT                             N        9
 4  ALLEN   SCOTT   KING/FORD/SCOTT/ALLEN                       N       10
 5  FORD    ALLEN   KING/FORD/SCOTT/ALLEN/FORD                  N       11
 6  SCOTT   FORD    KING/FORD/SCOTT/ALLEN/FORD/SCOTT            Y       12
 
12 rows selected.
```

다음은 시간을 사용해 **FLASHBACK** 쿼리를 수행한 예입니다.

```sql
CREATE TABLE TODO (ITEM VARCHAR2(20), DUEDATE DATE);

INSERT INTO TODO VALUES ('WEDDING_ATTEND', '2012-04-01');
INSERT INTO TODO VALUES ('GROCERY', '2011-12-28');
COMMIT;

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
-----------------------------------------------------------
2011/12/28 18:41:33.854889

1 row selected.

SELECT * FROM TODO;

ITEM                 DUEDATE
-------------------- ----------
WEDDING_ATTEND       2012/04/01
GROCERY              2011/12/28

2 rows selected.

INSERT INTO TODO VALUES ('NEWSPAPER_PAY', '2011-12-31');
COMMIT;

SELECT * FROM TODO;

ITEM                 DUEDATE
-------------------- ----------
WEDDING_ATTEND       2012/04/01
GROCERY              2011/12/28
NEWSPAPER_PAY        2011/12/31

3 rows selected.

SELECT * FROM TODO AS OF TIMESTAMP '2011/12/28 18:41:33.854889';

ITEM                 DUEDATE
-------------------- ----------
WEDDING_ATTEND       2012/04/01
GROCERY              2011/12/28

2 rows selected.
```

다음은 TSN 정보를 사용해 **FLASHBACK** 쿼리를 수행한 예입니다. 동적 뷰 V$TSN\_TIME을 이용하면 TSN과 시간의 매핑 정보를 확인할 수 있습니다.

```sql
CREATE TABLE TODO (ITEM VARCHAR2(20), DUEDATE DATE);

INSERT INTO TODO VALUES ('WEDDING_ATTEND', '2012-04-01');
INSERT INTO TODO VALUES ('GROCERY', '2011-12-28');
COMMIT;

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
------------------------------------------------------------------
2011/12/29 09:48:35.494024

1 row selected.

SELECT * FROM TODO;

ITEM               DUEDATE
------------------ ------------------------------
WEDDING_ATTEND     2012/04/01
GROCERY            2011/12/28

2 rows selected.

INSERT INTO TODO VALUES ('NEWSPAPER_PAY', '2011-12-31');
COMMIT;

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
------------------------------------------------------------------
2011/12/29 09:49:05.635549

1 row selected.

SELECT * FROM TODO;

ITEM               DUEDATE
------------------ ------------------------------
WEDDING_ATTEND     2012/04/01
GROCERY            2011/12/28
NEWSPAPER_PAY      2011/12/31

3 rows selected.

SELECT *
FROM V$TSN_TIME
WHERE TIME >= '2011/12/29 09:48:35.494024'
  AND TIME <= '2011/12/29 09:49:05.635549';

     TSN TIME
------------------------------------------------------------------
12347 2011/12/29 09:48:36.018269
12347 2011/12/29 09:48:37.019113
12348 2011/12/29 09:48:38.020786
12348 2011/12/29 09:48:39.021757
12348 2011/12/29 09:48:40.022545
12349 2011/12/29 09:48:42.023222
12350 2011/12/29 09:48:43.722855
12350 2011/12/29 09:48:45.023568
12350 2011/12/29 09:48:46.024370
12351 2011/12/29 09:48:48.024938
12351 2011/12/29 09:48:49.025834
12352 2011/12/29 09:48:50.026523
12352 2011/12/29 09:48:51.027325
12352 2011/12/29 09:48:52.028122
12353 2011/12/29 09:48:54.027811
12353 2011/12/29 09:48:55.027850
12353 2011/12/29 09:48:56.028738
12354 2011/12/29 09:48:58.029017
12354 2011/12/29 09:48:59.030678
12355 2011/12/29 09:49:00.031466
12357 2011/12/29 09:49:02.033270
12357 2011/12/29 09:49:03.033251
12358 2011/12/29 09:49:04.034311

     TSN TIME
------------------------------------------------------------------
12358 2011/12/29 09:49:05.034333

24 rows selected.

SELECT * FROM TODO AS OF SCN 12350;

ITEM               DUEDATE
------------------ ------------------------------
WEDDING_ATTEND     2012/04/01
GROCERY            2011/12/28

2 rows selected.
```

다음은 **PIVOT**과 **UNPIVOT**을 사용하는 예입니다.

```sql
SELECT *
FROM (
    SELECT DEPTNO, JOB, SALARY
    FROM EMP
)
PIVOT (
    SUM(SALARY) AS SALARY_SUM
    FOR DEPTNO IN (1 DEPT1, 5 DEPT5, 7 DEPT7)
);

JOB                  DEPT1_SALARY_SUM DEPT5_SALARY_SUM DEPT7_SALARY_SUM
-------------------- ---------------- ---------------- ----------------
ACC_MGR                               3000
ACC_STAFF                             1700
CEO
RES_MGR                                                  2800
RES_STAFF                                                2500

5 rows selected.

CREATE VIEW PIVOTED_EMP AS
SELECT *
FROM (
    SELECT DEPTNO, JOB, SALARY
    FROM EMP
)
PIVOT (
    SUM(SALARY) AS SALARY_SUM
    FOR DEPTNO IN (1 DEPT1, 5 DEPT5, 7 DEPT7)
);

SELECT *
FROM PIVOTED_EMP
UNPIVOT (
    SALARY_SUM
    FOR DEPARTMENT
    IN (DEPT1_SALARY_SUM AS 1,
        DEPT5_SALARY_SUM AS 5,
        DEPT7_SALARY_SUM AS 7)
);

JOB          DEPARTMENT  SALARY_SUM
------------ ----------  ----------
ACC_MGR               5        3000
ACC_STAFF             5        1700
RES_MGR               7        2800
RES_STAFF             7        2500

4 rows selected.

SELECT *
FROM PIVOTED_EMP
UNPIVOT INCLUDE NULLS (
    SALARY_SUM
    FOR DEPARTMENT
    IN (DEPT1_SALARY_SUM AS 1,
        DEPT5_SALARY_SUM AS 5,
        DEPT7_SALARY_SUM AS 7)
);

JOB                  DEPARTMENT  SALARY_SUM
-------------------- ----------  ----------
ACC_MGR                       1
ACC_MGR                       5        3000
ACC_MGR                       7
ACC_STAFF                     1
ACC_STAFF                     5        1700
ACC_STAFF                     7
CEO                           1
CEO                           5
CEO                           7
RES_MGR                       1
RES_MGR                       5
RES_MGR                       7        2800
RES_STAFF                     1
RES_STAFF                     5
RES_STAFF                     7        2500

15 rows selected.
```