SQLD 2과목

2한나·2025년 12월 30일

sql

SQLD

목록 보기

3/3

2과목

1장 SQL 기본

DUAL 테이블

특성

사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다.
일종의 DUMMY 테이블
DUMMY라는 문자열 유형의 칼럼에 ‘X’라는 값이 들어 있는 행을 1건 포함한다.

CASE문

#1.
CASE 
	WHEN LOC='NEW YORK' THEN 'A'
	WHEN LOC='KOREA' THEN 'B'
	ELSE 'C'
END

#2.
CASE LOC
	WHEN 'NEW YORK' THEN 'A'
	WHEN 'KOREA' THEN 'B'
	ELSE 'C'
END

#3.
DECODE(LOC, 'NEW YORK', 'A', 'KOREA', 'B', 'C')

ELSE 지정 안하면 ELSE인 경우 NULL 반환

단일행 NULL 관련 함수

NVL(표현식1, 표현식2): 표현식1이 NULL이면 표현식2 출력, 아니면 표현식1 출력
*SQL SERVER는 ISNULL
NULLIF(표현식1, 표현식2): 표현식1과 표현식2가 같으면 NULL 출력, 다르면 표현식1 출력
COALESCE(표현식1, 표현식2, …): NULL이 아닌 최초의 표현식 출력, 다 NULL이면 NULL 출력

JOIN

N개의 테이블로부터 필요한 칼럼을 조회하려면 최소 N-1개의 JOIN이 필요하다.

특징

일반적으로 조인은 PK와 FK값의 연관성에 의해 성립된다.
어떤 경우에는 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 조인이 성립됨
DBMS 옵티마이저는 FROM 절에 나열된 테이블들을 항상 2개로 묶어서 조인을 처리한다.
EQUI JOIN은 ‘=’ 연산자에 의해서만 수행되며, 그 이외의 비교 연산자를 사용하면 모두 NON EQUI JOIN이다.
대부분 NON EQUI JOIN을 수행할 수 있지만, 설계상의 이유로 수행이 불가능한 경우도 있다.

JOIN시 USING에 이용한 건 ALIAS 사용 X

SELECT STADIUM_ID, #T.STADIUM_ID X
FROM TEAM T INNER JOIN STADIUM S
		 USING (STADIUM_ID); #(T.STADIUM_ID = S.STADIUM_ID) X

순수 관계 연산자

SELECT(시그마) → WHERE절로 구현
PROJECT(파이) → SELECT절로 구현
JOIN(조인) → JOIN으로 구현
DIVIDE → 현재 사용 X
CARTESIAN PRODUCT(카티션곱) → CROSS JOIN으로 구현
UNION, SET DIFFERENCE, INTERSECTION 등

2장 SQL 응용

집합 연산자

UNION

UNION 연산자를 사용한 SQL은 각각의 집합에 ORDER BY절 사용 X (GROUP BY절은 사용 ㄱㄴ)
→ 최종 결과를 정렬하므로 가장 마지막 줄에 한번만 가능하다.
합집합 후 중복 데이터를 ‘모두’ 지운다.

계층형 질의

문법

START WITH: 시작 위치
CONNECT BY
PRIOR
순방향: PRIOR 자식 = 부모
역방향: PRIOR 부모 = 자식
ORDER SIBLINGS BY: 형제 노드 사이에서의 정렬

특징

로트 노드의 LEVEL 값은 1
START WITH 절에서 나온 시작 데이터는 CONNECT BY 절과 무관하게 결과에 항상 포함!
→ WHERE절에서 필터링해야지 포함 X
SQL SERVER
- 계층형 질의문은 CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개
- 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.
오라클에서
- 계층형 질의문에서 WHERE절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용된다.
- 계층형 질의문에서 PRIOR 키워드는 CONNECT BY, SELECT, WHERE절에서 사용할 수 있다.

서브 쿼리

특징

서브쿼리는 GROUP BY절에서 사용 X

종류

단일행 서브쿼리: 단일행 비교 연산자 사용 + 다중행 비교 연산자도 사용 ㄱㄴ
다중행 서브쿼리: 다중행 비교 연산자 + 단일행 비교 연산자 사용 불가
다중 칼럼 서브쿼리: 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야한다.
→ 오라클은 지원, SQL SERVER는 지원 X
연관 서브쿼리: 서브쿼리가 메인 칼럼을 포함하고있는 형태의 서브쿼리
비연관 서브쿼리: 주로 메인 쿼리에 값을 제공하기 위한 목적으로 사용
스칼라 서브쿼리: 하나의 값을 반환하는 서브쿼리
인라인 뷰: FROM절에서 사용되는 서브쿼리
→ SQL이 실행될 때만 임시적으로 생성되는 동적뷰

뷰

특징

독립성: 테이블 구조가 변경되어도 응용 프로그램은 변경하지 않아도 된다.
편리성, 보안성
단지 정의만 가지고 있으며, 실행 시점에 질의를 재작성하여 수행

윈도우 함수

특징

윈도우 함수 처리 후 결과 건수는 줄어들지 X
GROUP BY절과 함께 사용 시, GROUP BY 절의 집합을 원본으로 하는 데이터를 WINDOW FUNCTION과 함께 사용하면 오류 X

윈도우 함수 구조

SELECT WINDOW_FUNCTION(인수) OVER ([PARTITION BY 칼럼] [ORDER BY 칼럼] [WINDOWING절])
FROM 테이블 명;

WINDOWING절
- ROWS UNBOUNDED PRECENDING
  → 현재 파티션의 첫 행부터 현재 행까지
- ROWS CURRENT ROW
  → 현재 행부터 현재 행까지 → 현재행

1. 순위 윈도우 함수

1) RANK 함수

동일한 값에는 동일한 순위 부여
1등, 1등 → 3등

2) DENSE_RANK 함수

동일한 값에는 동일한 순위 부여
1등,1등 → 2등

3) ROW_NUMBER

동일한 값에 다른 순위 부여
1등,2등,3등

2. 행 순서 윈도우 함수

1) FIRST_VALUE (or LAST_VALUE) 함수

각 파티션 내에서 가장 먼저 (또는 나중) 나온 값

2) LAG 함수

각 파티션에서 해당 행의 몇 번째 이전(또는 이후) 행의 값을 가져옴
LAG(SAL, 2, 0)
→ 2번째 앞의 행을 가져오고, 가져올 행이 없는 경우 처음 두 행의 값은 0으로 채움

‘0’을 생략하면 NULL로 채워짐

3. 비율 윈도우 함수

1) RATIO_TO_REPORT 함수

파티션 내 전체 SUM(칼럼) 값에 대한 행별 백분율

2) PERCENT_RANK 함수

행의 순서별 백분율

3) CUME_DIST

현재 행에 대해, 현재 행보다 작거나 같은 건수에 대한 누적 백분율

3) NTILE 함수

파티션별 전체 건수를 N등분한 결과를 구함

PIVOT 과 UNPIVOT

PIVOT

LONG 데이터를 WIDE 데이터로 바꾸는 것

UNPIVOT

WIDE 데이터를 LONG 데이터로 바꾸는 것

정규 표현식

REGEXP_REPLACE

정규식 표현을 사용한 문자열 치환 가능
(대상, 찾을 문자열, [바꿀 문자열], [검색위치], [발견 횟수], [옵션])

REGEXP_SUBSTR

정규식 표현식을 사용한 문자열 추출
(대상, 패턴, [검색위치], [발견 횟수], [옵션], [추출그룹])

REGEXP_INSTR

주어진 문자열에서 특정 패턴의 시작 위치 반환
(원본, 찾을 문자열, [시작 위치], [발견 횟수])

REGEXP_COUNT

주어진 문자열에서 특정 패턴의 횟수 반환
(원본, 찾을 문자열, [시작위치], [옵션])

3장 관리 구문

SQL 명령어 종류

DML (데이터 조작어)

SELECT, INSERT, UPDATE, DELETE

DDL (데이터 정의어)

CREATE, ALTER, DROP, RENAME

DCL (데이터 제어어)

GRANT, REVOKE

TCL (트랜잭션 제어어)

COMMIT, ROLLBACK, SAVEPOINT

DML

SELECT 문장 실행 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

WHERE 절

WHERE절에는 집계함수를 사용 X. (ex. COUNT, SUM, AVG, MIN. MAX 등)

GROUP BY절과 HAVING절의 특성

GROUP BY절에는 ALIAS명 사용 X
GROUP BY절 사용 후 ORDER BY절을 사용할 때, GROUP BY 표현식이 아닌 값은 기술 X
= SELECT 절에 없는 표현식 기술 X
GROUP BY절 사용하지 않고 HAVING절 사용 ㄱㄴ → 전체 기준으로 집계함수 실행

ORDER BY 절

오라클: NULL값이 가장 큰 값
SQL SERVER: NULL값이 가장 작은 값

INSERT

‘ ’를 저장하면
- 오라클: NULL로 저장됨
- SQL SERVER: ‘ ’로 저장됨
DATE에 INSERT 하려면 문자열 형태로 삽입해야함 → ‘2024.08.24’

DDL

테이블 생성시 주의사항

테이블명은 단수형 권고
다른 테이블 명과 중복 X
한 테이블 내에서 칼럼명 중복 X
칼럼 뒤 데이터 유형 꼭 지정
테이블명, 칼럼명은 A-z, 0-9, _ , $, # 만 사용 가능, 반드시 문자로 시작, 벤더별로 길이에 한계 있음, 벤더에서 사전에 정의한 예약어 사용 X
표준 데이터 타입: TEXT 적절 X → CHAR, VARCHAR2로 표현
- EX) CHAR, VARCHAR2, NUMERIC

외래키

외래키 값 NULL 가능
외래키 값은 참조 무결성 제약을 받을 수 있음

제약 조건 추가

CREATE TABLE PRODUCT(
...,
CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID));

ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID)

ALTER

테이블 칼럼에 대한 정의 변경시

오라클

# MODIFY, 여러 칼럼 동시 수정 가능
ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터유형 [DEFAULT] [NOT NULL], 칼럼명2 ...);

SQL SERVER

# ALTER, 여러 칼럼 동시 수정 불가능
ALTER TABLE 테이블명 ALTER (칼럼명1 데이터유형 [DEFAULT] [NOT NULL]);

DROP

CASCADE: 연관된 객체 모두 삭제
RESTRICT 스키마가 공백인 경우에만 삭제

Delete( or Modify) Action: 부서-사원
1. Cascade: 부모 삭제 시 자식 같이 삭제
2. Set Null: 부모 삭제 시 자식 해당 필드 Null
3. Set Default: 부모 삭제 시 자식 해당 필드 Default 값으로 설정
4. Restrict: 자식 테이블에 PK 값이 없는 경우만 삭제 허용
5. No Action (default): 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음
Insert: 부서-사원
1. Automatic: 부모 테이블에 PK가 없는 경우 부모 PK를 생성 후 자식 입력
2. Set Null: 부모 테이블에 PK가 없는 경우 자식 외부키를 NULL로 설정
3. Set Default: 부모 테이블에 PK가 없는 경우 자식 외부키를 기본값으로 설정
4. Dependent: 부모 테이블에 PK가 존재할 때만 자식 입력 허용
5. No Action (default): 참조 무결성을 위반하는 입력 액션을 취하지 않음

TRUNCATE는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제시 DELETE보다 빠름

DCL

WITH GRANT OPTION: 다른 사용자에게도 해당 권한 부여 가능한 능력
→ 권한 취소 시 이를 통해 다른 사용자에게 허가했던 권한들 연쇄적으로 모두 취소됨
ROVOKE문을 사용해 권한 취소 시 그 권한을 허가한 사용자가 권한 취소 ㄱㄴ

TCL

트랜잭션의 특성

원자성, 일관성, 고립성
지속성: 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

ROLLBACK

COMMIT 이전에는 ROLLLBACK을 사용해 변경을 취소할 수 있음
→ COMMIT 되지 않은 상위 모든 트랜잭션을 모두 ROLLBACK
ROLLBACK 이후 관련 행에 대한 장금이 풀려 다른 사용자들이 데이터를 변경할 수 있음

BEGIN TRANSACTION

트랜잭션 시작
COMMIT 또는 ROLLBACK으로 트랜잭션 종료

오라클 VS SQL SERVER

오라클
```
SAVEPOINT SVP1;
...
ROLLBACK TO SVP1;
```