[SQLD][과목 2] 제 2 장 : SQL활용

제2장 : SQL 활용

제 2 절 : 집합연산자

1. 종류

   1) UNION ALL : 중복허용

   2) UNION = UNION + DISTINCT 중복불가

   3) INTERSECT : 교집합, 중복불가

   4) EXCEPT(MINUS) 중복불가

SELECTPLAYER_NAME 선수명, BACK_NO 백넘버 # 아래와 칼럼 데이터 타입 일치해야함
FROM  PLAYER 
WHERETEAM_ID = 'K02' 

UNION 

SELECT PLAYER_NAME 선수명, BACK_NO 백넘버 #위와 칼럼 데이터 타입 일치해야함
FROM  PLAYER 
WHERETEAM_ID = 'K07' 
ORDERBY 1;  ## ORDER BY 는 맨끝에 한번만

• 위아래의 칼럼 데이터 타입이 일치해야한다

• ORDER BY 는 맨끝에 한번만 써야한다

• 최종 칼럼명은 상단 SQL문을 따른다.

제 3 절 : 계층형 질의와 셀프조인

1. 계층형 질의

   1) 구문형식

   SELECT 
   FROM
   WHERE
   START WITH 조건(전개시작)
   CONNECT BY [NOCYCLE] 조건(다음전개-PRIOR)
   [ORDER SIBLINGS BY...]

   

- START WITH
- 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
- 서브쿼리를 사용할 수도 있다.

-CONNECT BY
- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
- PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
- CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
- CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
- CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
- 서브쿼리를 사용할 수 없다.

-LEVEL Pseudocolumn
- LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

-ORDER SIBLINGS BY
- ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

-CONNECT BY의 실행순서는 다음과 같다.
- 첫째 START WITH 절
- 둘째 CONNECT BY 절
- 셋째 WHERE 절 순서로 풀리게 되어있다.

- NOCYCLE

데이터 전개 시, 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 사이클(Cycle) 형성이라함

사이클이 발생한 데이터는 런타임 오류 발생

☞ NOCYCLE 추가 : 사이클이 발생한 이후의 데이터는 전개하지 않음

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

- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)

2) 함수

1) SYS_CONNECT_BY_PATH(칼럼, 경로분리자)

: 루트, 현재까지의 경로

2) CONNECT_BY_ROOT(칼럼)

: 현재 데이터의 루트 리턴

2.  셀프조인

    ```
    SELECT EL.EMPNO, EL.MGR(관리자), E2.MGR(차상위 관리자)
    FROM EMP E1, EMP E2
    WHERE E1.MGR = E2.EMPNO;
    ```


## 제 4 절 : 서브쿼리

-   서브쿼리는 메인쿼리의 컬럼사용 가능

-   메인쿼리는 서브쿼리의 칼럼사용 불가

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

-   서브쿼리에서는 OREDR BY 사용 불가


1.  분류

    1) 동작방식에 따라

    ㄱ) 비연관 서브쿼리 : 서브쿼리가 메인쿼리의 칼럼을 사용하지 않음

    ㄴ) 연관서브쿼리 : 서브쿼리가 메인쿼리의 칼럼을 사용

    ```
    SELECT ...
    FROM A
    WHERE EXIST (SELECT
                                FROM
                                WHERE A.ID = B.ID)

    ```

    2) 변환데이터에 따라

    ㄱ) 단일행 서브쿼리

    cf) 서브쿼리의 결과를 <,≤,=,<>,>,≥ 와 같이 사용

    ㄴ) 다중행 서브쿼리

    cf) 서브쿼리의 결과를 IN, ALL, ANY, SOME, EXIST와 같이 사용

    ㄷ) 다중 칼럼 서브쿼리

    cf) IN, ALL, ANY, SOME, EXISTS 사용

    3) 위치에 따라

    ㄱ)SELECT절 → 스칼라 서브쿼리

    ㄴ) FROM절 → 인라인뷰

    ㄷ) HAVING절

    ㄹ) SET절

    ㅁ) VALUES절


2.  VIEW

-   실제데이터를 가지고 있는 테이블이 아닌 가상테이블

    1) 장점

    -   독립성 : 테이블 구조 변해도 뷰를 사용하는 어플리케이션은 똑같음

    -   편리성 : 복잡한 질의를 단순하게

    -   보안성 : 숨기고 싶은 정보를 뷰를 생성할때 출력 칼럼에서 제외할 수 있음


    2) 구문

    ㄱ) 생성

    ```
    CREATE VIEW 뷰이름 AS 
    VIEW구문;
    ```

    ㄴ) 사용

    ```
    SELECT...
    FROM 뷰이름 [별칭]
    WHERE ...;
    ```

    ㄷ) 제거

    DROP VIEW 뷰이름;


## 제 5 절 : 그룹함수

1.  ROLLUP

    1) GROUP BY절 → 집계함수 정렬기능 없음

    2) GROUP BY절 + ORDER BY절

    ```
    SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
    FROM EMP, DEPT
    WHERE DEPT.DEPTNO = EMP.DEPTNO
    GROUP BY DNAME, JOB
    ORDER BY DNAME, JOB;
    ```

    3) GROUP BY절 + ROLLUP → 집계함수 정렬기능 없음

    ```
    SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
    FROM EMP, DEPT
    WHERE DEPT.DEPTNO = EMP.DEPTNO
    GROUP BY ROLLUP (DNAME, JOB);
    ```

    [![](https://i.imgur.com/NwIQOFj.png)](https://i.imgur.com/NwIQOFj.png)

    TOTAL SAL은 정렬되지 않았음

    4) GROUP BY절 + ROLLUP + ORDER BY

    ```
    SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
    FROM EMP, DEPT
    WHERE DEPT.DEPTNO = EMP.DEPTNO
    GROUP BY ROLLUP (DNAME, JOB)
    ORDER BY DNAME, JOB ;
    ```

    5) 부분 ROLLUP

    ```
    GROUP BY DNAME, ROLLUP(JOB);
    ```

    \- 결과는 마지막 ALL DEPARTMENTS &ALL JOBS 줄만 빠짐

    ROLLUP이 JOB 칼럼에만 사용되었기 때문에 DNAME에 대한 집계는 필요하지 않기 때문

    6) 결합 ROLLUP

    ```
    GROUP BY ROLLUP (DNAME, (JOB, MGR));
    ```

    -   괄호로 묶은 JOB과 MGR의 경우 하나의 집합(JOB+MGR) 칼럼으로 간주하여 괄호 내 각 칼럼별 집계를 구하지 않음

2.  CUBE

GROUP BY CUBE (DNAME, JOB);

[![](https://i.imgur.com/SvWsRxs.png)](https://i.imgur.com/SvWsRxs.png)

-   CUBE 함수 사용으로 ROLLUP 함수의 결과에다 업무별 집계까지 추가해서 출력할 수 있음

-   UNION ALL사용도 가능하지만 CUBE함수 사용이 SQL문이 짧기 때문에 더 좋음


3\. GROUPING SETS

1) 인수2개

GROUP BY GROUPING SETS (DNAME, JOB);

-   UNION ALL을 사용한 일반 그룹함수를 사용한 SQL과 같은 결과

-   GROUPING SETS 인수들은 평등한 관계이므로 인수의 순서가 바뀌어도 **결과는 같다**


2) 인수 3개

GROUP BY GROUPING SETS ((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR));

-   괄호로 묶은 집합별로(괄호 내는 계층구조가 아닌 하나의 데이터로 간주함) 집계를 구할 수 있다

## 제 6 절 : WINDOW 함수

SELECT 함수명() OVER
[PARTION BY 칼럼]
[ORDER BY 칼럼]
[ROWS|RANGE [BETWEEN]
UNBOUNDED PRECEDING CURRENT ROW
VALUE_EXPR PRECEDING

[AND
UNBOUNDED FOLLOWING CURRENT ROW
..... FOLLOWING]
]

1.  그룹내 순위 함수

    1) RANK() OVER : 전체 데이터에 대한 순위(동일값, 동일 순위)

    ```
    SELECT JOB, ENAME, SAL, 
        RANK( ) OVER (ORDER BY SAL DESC)  ALL_RANK, 
        RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK 
    FROM EMP;

    ```

    -   ORDER BY SAL DESC 조건과 PARTITION BY JOB 조건이 충돌

    2) DENSE\_RANK()

    ```
    SELECT JOB, ENAME, SAL, 
        RANK( ) OVER (ORDER BY SAL DESC) RANK, 
        DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK 
    FROM EMP;
    ```

    3) ROW\_NUMBER()

    ```
    SELECT JOB, ENAME, SAL, 
        RANK( ) OVER (ORDER BY SAL DESC) RANK, 
        ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER 
    FROM EMP;
    ```


2\. 집계함수

1) SUM

SUM(SAL) OVER (PARTITION BY MGR)

| MGR | ENAME | SAL | MGR\_SUM |
| --- | --- | --- | --- |
| 7566 | [FORD](https://www.notion.so/FORD-2212d3c7aa474c19829c023430c1476f) | 3000 | 6000 |
| 7566 | [SCOTT](https://www.notion.so/SCOTT-2a1d9744bceb4301b68b200a7b3ebca3) | 3000 | 6000 |
| 7698 | [JAMES](https://www.notion.so/JAMES-4526c49f805a4c83a2d0ddb6daf24329) | 950 | 6550 |

SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING)

-   현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

| MGR | ENAME | SAL | MGR\_SUM |
| --- | --- | --- | --- |
| 7566 | [SCOTT](https://www.notion.so/SCOTT-f4282ef63c924a8197272a77c7b93bab) | 3000 | 6000 |
| 7566 | [FORD](https://www.notion.so/FORD-3f9473c52d114848b672c51df0167071) | 3000 | 6000 |
| 7698 | [JAMES](https://www.notion.so/JAMES-9bae3497036748e4a184d89842df98ff) | 950 | 950 |
| 7698 | [WARD \*](https://www.notion.so/WARD-97f9ec16ce624d9a902bf744beb0e0f3) | 1250 | 3450 |
| 7698 | [MARTIN \*](https://www.notion.so/MARTIN-a0b3a596fd614920aed74f17c960d0b8) | 1250 | 3450 |
| 7698 | [TURNER](https://www.notion.so/TURNER-a7106a257a1d4ee38181517491b95b92) | 1500 | 4950 |
| 7698 | [ALLEN](https://www.notion.so/ALLEN-a0e2ed662f8f4b97ac37fe9848dcac91) | 1600 | 6550 |

2) MAX, MIN., AVG

MAX(SAL) OVER (PARTITION BY MGR)
MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE)
AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

3) COUNT

#사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 출력
COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)

-50 ~ 해당행SAL값 ~ +150

3\. 그룹 내 행 순서 함수

1) FIRST\_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값

FIRST_VALUE(ENAME)
OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING)

2) LAST\_VALUE : 파티션별 윈도우에서 가장 나중에 나온 값

LAST_VALUE(ENAME)
OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

-   공동 등수를 인정하지 않고 가장 나중에 나온 행만을 처리. 공동 등수가 있을 경우를 의도적으로 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER () 내의 ORDER BY 조건에 칼럼을 추가해야함

3) LAG : 파티션별 윈도우에서 **이전** 몇번째 행의 값 가져옴

LAG(SAL) OVER (ORDER BY HIREDATE) : 가지고올 데이터 없으면 NULL
LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) : NULL값을 다른값으로 바꿈(0)

4) LEAD : 파티션별 윈도우에서 **이후** 몇번째 행의 값 가져옴

LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE)

5\. 그룹 내 비율함수

1) RATIO\_TO\_REPORT : PARTITION 내의 SUM값에서의 PERCENT

SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (),

2) PERCENT\_RANK

-   제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율

PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)

3) CUME\_DIST

-   파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율

CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)

4) NTILE

-   파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과

NTILE(4) OVER (ORDER BY SAL DESC)

## 제 7 절 DCL

1.  유저생성 / 시스템 권한

    ```
    1) 시스템 권한 얻어와서 바로 접속
    CONN /AS SYSDBA;

    2) 유저 생성 권한 부여
    GRANT CREATE USER TO ___;

    3) 유저 생성
    CREATE USER ID IDENTIFIED BY PW;

    4) 유저 로그인 권한 부여
    GRANT CREATE SESSION TO ___;

    5) 유저 로그인
    CONN ID/PW;

    6) 테이블 생성 권한 부여
    GRANT CREATE TABLE TO ___;
    ```


2.  OBJECT에 대한 권한

-   모든 사용자들은 다른사람이 만든 테이블에 접근하려면 해당테이블에 댛나 오브젝트 권한을 부여받아야함

GRANT SELECT ON EMP TO ___;

[![](https://i.imgur.com/r2UoSIV.png)](https://i.imgur.com/r2UoSIV.png)

3\. ROLE을 이용한 권한 부여

[![](https://i.imgur.com/m6LhuK3.png)](https://i.imgur.com/m6LhuK3.png)

1) 기본ROLE

[![](https://i.imgur.com/faIRItc.png)](https://i.imgur.com/faIRItc.png)

2) ROLE 만들기

CREATE ROLE 롤이름;
GRANT CREATE TABLE TO 롤이름;

3) ROLE 사용하기

GRANT CONNECT, RESOURCE TO ID
GRANT 롤이름 TO ID;

4) 권한축소

REVOKE 권한, .... FROM ID;

5) 유저 삭제

DROP USER ID [CASCADE];

-   CASCADE : 해당유저가 생성한 오브젝트를 모두 삭제한 후 유저 삭제

## 제 8 절 : 절차형 SQL

1.  Precedure

    1) 생성

    ```
    CREATE DR REPLACE Procedure 이름
    (변수명 in 타입, 변수명 out 타입)

    IS
        변수초기화

    BEGIN
        - SQL문;
        - IF ... THEN ... 
            ELSE ...
            END IF;

    EXCEPTION
        WHEN .... THEN

    END;
    ```

    2) 사용

    SQL> variable rslt varchar2(30); -----------------② SQL> EXECUTE p\_DEPT\_insert(10,'dev','seoul',:rslt);

    SQL> PRINT rslt


2.  FUNCTION

-   Procedure와 달리 retrun 값이 필수이다.

CREATE DR REPLACE FUNCTION이름
(변수명 in 타입
.....
RETURN 타입)

IS
변수초기화 ex)변수명 타입 := 0;

BEGIN
- SQL문;
- IF ... THEN ...
ELSE ...
END IF;

RETURN 변수;

END;

사용 → SELECT 함수명(칼럼명) ....

3\. Trigger

-   ROLLBACK가 일어나면 하나의 트랜잭션이 취소되는데, 그 중간에 TRIGGER가 발생하면 그것 또한 트랜잭션의 일부로 인식하여 TRIGGER안에서 일어난 내부작업도 취소

-   Procedure는 EXECUTE로 실행하고 내부에 COMMIT, ROLLBACK가 사용가능하지만, Trigger는 자동실행이며, 내부에 COMMIT, ROLLBACK 사용 불가능하다.

CREATE DR REPLACE trigger 이름
AFTER DML문
ON 테이블명
[FOR EACH ROW]
DECLARE
변수 선언;
ex) 변수명 타입;

BEGIN
- SQL문;
- IF ... THEN ...
ELSE ...
END IF;

END;

```

START WITH
- 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
- 서브쿼리를 사용할 수도 있다.
CONNECT BY
- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
- PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
- CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
- CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
- CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
- 서브쿼리를 사용할 수 없다.
LEVEL Pseudocolumn
- LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.
ORDER SIBLINGS BY
- ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.
CONNECT BY의 실행순서는 다음과 같다.
- 첫째 START WITH 절
- 둘째 CONNECT BY 절
- 세째 WHERE 절 순서로 풀리게 되어있다.

START WITH
- 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
- 서브쿼리를 사용할 수도 있다.
CONNECT BY
- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
- PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
- CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
- CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
- CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
- 서브쿼리를 사용할 수 없다.
LEVEL Pseudocolumn
- LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.
ORDER SIBLINGS BY
- ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.
CONNECT BY의 실행순서는 다음과 같다.
- 첫째 START WITH 절
- 둘째 CONNECT BY 절
- 세째 WHERE 절 순서로 풀리게 되어있다.

START WITH

• - 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
• - 서브쿼리를 사용할 수도 있다.

CONNECT BY

• - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
• -PRIOR연산자와 함께 사용하여 계층구조로 표현할 수 있다.
• -CONNECT BY PRIOR자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
• -CONNECT BYPRIOR부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
• -CONNECT BY NOCYCLE PRIOR: NOCYCLE 파라미터를 이용하여 무한루프 방지
• - 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn

• - LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY

• - ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

CONNECT BY의 실행순서는 다음과 같다.

• - 첫째START WITH절
• - 둘째CONNECT BY절
• - 세째WHERE절 순서로 풀리게 되어있다.

START WITH

• - 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
• - 서브쿼리를 사용할 수도 있다.

CONNECT BY

• - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
• -PRIOR연산자와 함께 사용하여 계층구조로 표현할 수 있다.
• -CONNECT BY PRIOR자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
• -CONNECT BYPRIOR부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
• -CONNECT BY NOCYCLE PRIOR: NOCYCLE 파라미터를 이용하여 무한루프 방지
• - 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn

• - LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY

• - ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

CONNECT BY의 실행순서는 다음과 같다.

• - 첫째START WITH절
• - 둘째CONNECT BY절
• - 세째WHERE절 순서로 풀리게 되어있다.