SQLD 개발자 자격증 요약 4. SQL 활용

 

SQLD 개발자 자격증 시험 전 본인이 보기 위해

중요하다고 생각한 부분만 정리한 요약본으로,

누락 내용이 있을 수 있다.

 

서브 쿼리

하나의 쿼리 안에 존재하는 또 다른 쿼리

ORDER BY 절

INSERT 문의 VALUE 절 등에 사용할 수 있다.

- SELECT 절: 스칼라 서브 쿼리
- FROM 절: 인라인 뷰 or 동적 뷰 (Dynamic View)
- WHERE 절, HAVING 절: 중첩 서브 쿼리

스칼라 서브 쿼리

주로 SELECT 절에 위치하나,

칼럼이 올 수 있는

대부분 위치에 사용할 수 있다.
칼럼 대신 사용되므로

반드시 하나의 값만을 반환해야 하며,

그렇지 않은 경우 에러가 발생한다.

인라인 뷰

FROM 절 등 테이블 명이 올 수 있는 위치에

사용할 수 있다.

중첩 서브 쿼리

WHERE 절과 HAVING 절에 사용 가능,

관계에 따라 연관 서브 쿼리,

비 연관 서브 쿼리로 나눔
메인 쿼리의 칼럼이 포함된 서브 쿼리를

연관 서브 쿼리,

메인 쿼리의 칼럼이 포함되지 않은

서브 쿼리를 비 연관 서브 쿼리 라고 한다.

중첩 서브 쿼리는 반환하는 데이터 형태에 따라 아래와 같이 나눌 수 있다.

① 단일 행 서브 쿼리

: 서브 쿼리가 1건 이하의 데이터를 반환, 
단일 행 비교 연산자와 함께 사용. =, , =, 

② 다중 행 서브 쿼리

: 서브 쿼리가 2건 이상의 행 데이터를 반환
다중 행 비교 연산자와 함께 사용.

IN, ALL, ANY, SOME, EXISTS

③ 다중 칼럼 서브 쿼리

서브 쿼리가 여러 칼럼의 데이터를 반환

뷰

특정 SELECT 문에 이름을 붙여서

재사용이 가능하도록 저장해놓은 오브젝트

SQL에서 테이블처럼 사용 가능.

뷰의 특징
① 보안성

보안이 필요한 칼럼을 가진 테이블일 경우,

해당 칼럼을 제외한

별도의 뷰를 생성해 보안 유지

② 독립성

테이블 스키마가 변경되었을 경우

애플리케이션은 변경하지 않고 관련 뷰만 수정

③ 편리성

복잡한 쿼리 구문을 뷰 명으로 단축해

가독성을 높이고 편리하게 사용 가능

집합 연산자
① UNION ALL: 합집합, 중복 행 출력
② UNION: 합집합, 중복 행 제거
③ INTERSECT: 교집합, 중복 제거
④ MINUS / EXCEPT: 차집합, 중복 제거

그룹 함수
① 집계 함수: COUNT, SUM, AVG, MAX, MIN 등
② 소계(총계) 함수: ROLLUP, CUBE(모든 경우의 수), GROUPING SETS(특정 항목, () 뜻은 총합계) 등

GROUPING

ROLLUP, CUBE, GROUPING SETS

등과 함께 쓰이며,

소계를 나타내는 Row를 구분할 수 있게 해줌

윈도우 함수

OVER 키워드와 함께 사용되며,

역할에 따라 아래와 같이 나눌 수 있다.

① 순위 함수

RANK (11345), DENSE_RANK (11234)

ROW_NUMBER (12345… 동일한 값이라도 각각 다른 순위)
② 집계 함수: COUNT, SUM, AVG, MAX, MIN

③ 행 순서 함수

FIRST_VALUE

파티션 별 가장 선두에 위치한

데이터를 구하는 함수

LAST_VALUE

파티션 별 가장 끝에 위치한

데이터를 구하는 함수

LAG

파티션 별로 특정수만큼

앞선 데이터를 구하는 함수

LEAE

파티션 별로 특정수만큼

뒤에 있는 데이터를 구하는 함수

④ 비율 함수

CUME_DIST

해당 파티션에서의 누적 백분율

PERCENT_RANK

해당 파티션의 맨 위 끝 행을 0,
맨 아래 끝 행을 1로 놓고

현재 행의 백분위 순위 값을 구하는 함수

NTILE

주어진 수만큼 행들을 n 등분한 후

현재 행에 해당하는 등급을 구하는 함수

RATIO_RO_REPORT

파티션 별 합계에서 차지하는 비율

UNBOUNDED PRECEDING: 위쪽 끝 행
UNBOUNDED FOLLOWING: 아래쪽 끝 행
CURRENT ROW: 현재 행
N PRECEDING: 현재 행에서 위로 n만큼 이동
N FOLLOWING: 현재 행에서 아래로 n만큼 이동

ROWS: 행 자체가 기준
RANGE: 행이 가지고 있는 데이터 값이 기준

Top-N 쿼리

ROWNUM: 실제로는 존재하지 않는 가짜 컬럼,

항상 < 조건이나 <= 조건으로 사용해야 함.

그러나 무작위로 N개를 뽑아낸 것이기 때문에,

진정한 의미의 Top-N이라고 보기는 애매함.

 

셀프 조인: 나 자신과의 조인,

FROM 절에 같은 테이블이

두 번 이상 등장하기 때문에

혼란을 막기 위해 ALIAS 별칭을 반드시 표기.

 

계층 쿼리

LEVEL: 현재의 DEPTH를 반환, 루트 노트는 1

 

SYS_CONNECT_BY_PATH (컬럼, 구분자)

루트 노드부터

현재 노드까지의 경로를 출력해주는 함수

 

START WITH

경로가 시작되는 루트 노드를 생성해주는 절

 

CONNECT BY

루트로부터 자식 노드를 생성해주는 절,

조건에 만족하는 데이터가 없을 때까지 노드를 생성

 

PRIOR: 바로 앞에 있는 부모 노드의 값을 반환

 

관리구문

DML: Data Manipulation Language,

DDL에서 정의한 대로

데이터를 입력하고

입력된 데이터를 수정, 삭제, 조회

SELECT, INSERT, UPDATE, DELETE

 

DDL: Data Definition Language,

데이터를 정의하는 SQL

CREATE, ALTER, DROP, RENAME,

TRUNCATE

 

DCL: Data Control Language,

User를 생성하고,

User에게 데이터를 컨트롤 할 수 있는 권한을

부여하거나 회수

GRANT, REVOKE, ROLE

 

TCL: Transaction Control Language,

트랜잭션을 제어하는 명령어

COMMIT, ROLLBACK, SAVEPOINT

 

트랜잭션의 특징

① 원자성: All or nothing

② 일관성: 트랜잭션 완료 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 함

③ 고립성: 고립되어 수행해야 한다

④ 지속성: 트랜잭션이 성공적으로 수행되었을 경우, 트랜잭션이 변경한 데이터가 영구적으로 저장

모든 트랜잭션이 로그에 남겨진 뒤 COMMIT 되어야 함, 시스템 장애 발생해도 복구할 수 있도록

 

제약조건의 종류

① PK: 고유성 보장, NULL 불가, UNIQUE 인덱스

 

② UNIQUE KEY: PK와 유사하게

각각의 Row에 대한 고유성을 보장,

NULL 값 허용

 

③ NOT NULL

해당 컬럼에 NULL 허용하지 않는 제약 조건

 

④ CHECK

컬럼에 저장될 수 있는 값의 범위를 제한,

DEL_UN(삭제여부) > CHK_DEL_YN

 

⑤ FK

하나의 테이블이 다른 테이블을

참조하고자 할 때 정의

 

참조 무결성 규정 관련 옵션

① CASCADE: Parent 값 삭제 시, Child 값도 삭제

 

② SET NULL: Parent 값 삭제 시,

Child의 해당 칼럼 NULL 처리

 

③ SET DEFAULT: Parent 값 삭제 시,

Child의 해당 칼럼 DEFAULT 값으로 변경

 

④ RESTRICT: Child 테이블에

해당 데이터가 PK로 존재하지 않는 경우에만,

Parent 값 삭제 및 수정 가능

 

⑤ NO ACTION

참조 무결성 제약이 걸려있는 경우

삭제 및 수정 불가