SQLD 취득기

시작하며

SQLD(SQL Developer) 시험을 준비하면서 공부한 핵심 내용을 정리한 글이다. 데이터 모델링의 개념부터 SQL 문법, 윈도우 함수, 성능 최적화까지 전반적인 내용을 다룬다.

SQLD 핵심 개념 정리

모델링이란

현실 세계를 추상화, 단순화, 명확화하여 데이터베이스로 표현하는 과정이다.

데이터 모델링의 특징

추상화(Abstraction) 2. 단순화(Simplification) 3. 명확화(Clarity)

데이터 모델링의 관점

데이터 관점 2. 프로세스 관점 3. 데이터와 프로세스의 상관 관점

데이터 모델링의 단계

개념적 데이터 모델링 : 전사적 데이터 모델링 수행, 추상화 레벨이 가장 높다.
논리적 데이터 모델링 : 데이터베이스 모델에 대한 Key, 속성, 관계 등을 모두 표현한다.
물리적 데이터 모델링 : 실제 데이터베이스로 구현할 수 있도록 성능이나 가용성 등 물리적 성격을 고려하여 표현한다.

데이터 독립성

DB 사용자의 관점과 DB가 실제로 표현되는 물리적인 방식을 분리하기 위해 존재한다.

3단 스키마 구조

외부 스키마(External Schema) : 각 사용자가 보는 DB 스키마를 정의한다.
개념 스키마(Conceptual Schema) : 모든 사용자가 보는 DB 스키마를 통합하여 조직 전체 관점의 정의
- 논리적 독립성 : 개념 스키마가 변경되어도 외부 스키마(응용 프로그램)는 영향 받지 않는다.
내부 스키마(Internal Schema) : 물리적인(Physical Representation) 단계, 실질적인 저장 구조, 인덱스 등
- 물리적 독립성 : 내부 스키마가 변경되어도 외부/개념 스키마는 영향 받지 않는다.

엔티티의 분류

유/무형 : 유형(물리적 실체 있음), 개념(추상적 개념), 사건(행위를 함으로 발생)
발생 시점 : 기본(독립적으로 생성), 중심(기본에서 파생, 행위 엔티티 생성), 행위(2개 이상 엔티티로부터 파생)

SQL 명령문 개괄

from > where > group by > having > select > order by
DML : select, insert, update, delete, merge
- merge : insert, update를 한 번에 수행한다.
DDL : alter, create, modify, truncate, drop
- DELETE vs TRUNCATE vs DROP
- DELETE(DML) : 데이터는 지워지지만 테이블 용량은 줄어들지 않는다. 원하는 데이터만 지울 수 있고, 삭제 후 잘못 삭제한 것을 되돌릴 수 있다.
- TRUNCATE(DDL) : 테이블을 제외하고 용량이 줄어들며, 인덱스, 데이터 등을 삭제한다. 삭제 후 절대 되돌릴 수 없다.
- DROP(DDL) : 테이블 전체를 삭제, 공간, 객체를 삭제한다. 삭제 후 절대 되돌릴 수 없다.
TCL : rollback, commit
DCL : grant, revoke, role

SELECT

distinct(col1, col2) 라 한다면 col1, col2를 동시에 본다.
ALIAS
- select 절에서 쓰는 경우: as 생략 가능, “컬럼명에 띄어쓰기”
- from 절에서 쓰는 경우 : as 사용 불가
CONCAT
- CONCAT 인수는 무조건 2개다.
- 때문에 ORACLE에서는 CONCAT이 중첩되는 것을 피하고자 || 연산을 사용한다. MySQL에서는 OR 연산에 해당된다.

논리 연산자

연산 순위 : not, and, or

SQL 연산자

between 1 and 2
in (1,2,3)
like
- _ : 미지의 한 글자
- % : 0 이상 글자
- escape : 와일드카드인 _, %를 문자로 취급하는 방법
ROWNUM : where 조건절에서 rownum=1인 경우 포함한다.
TOP : select top(n) 컬럼명 : 출력할 때 상위 n개

NULL

NULL의 정의 : 부재, 모르는 값
NULL 산술 연산값 [ex) NULL + 2] : NULL
NULL의 비교 연산값 [ex) NULL = 2] : FALSE
정렬 순서
- Oracle : NULL은 최대값(무한대)
- SQL Server : NULL은 최소값(-무한대)
NVL(v1, v2) : v1이 NULL이면 v2, 아니면 v1
NVL2(v1, v2, v3) : v1이 NULL이면 v3, 아니면 v2
ISNULL(v1, v2) : v1이 NULL이면 v2, 아니면 v1
NULLIF(v1, v2) : 같으면 NULL, 아니면 v1
COALESCE : NULL 아닌 첫 번째 값

정렬

특성 : 정렬은 가장 마지막에 실행된다.
컬럼 번호 정렬 : 출력되는 컬럼 수보다 큰 값이 안 된다.
인수 두 개 정렬 : A desc B asc : A가 같으면 B 오름차순
select 인수에 없어도 정렬 가능하다.

숫자 함수

ROUND(13.89) : ROUND(“값”, “자리수”), “자리수” 만큼 표현한다.
CEIL/CEILING : 올림

문자열 함수

UPPER, LOWER
LPAD(123, 5, ‘0’) : 00123
RPAD(123, 5, ‘0’) : 12300
LTRIM, RTRIM : 문자열 왼쪽 혹은 오른쪽 공백을 제거한다.
SUBSTR/SUBSTRING(A, B, C) : A 컬럼의 B번째 문자부터 C번째 문자까지
INSTR : 찾고자 하는 문자열이 몇 번째 위치에 있는지 위치를 알려준다.

날짜 함수

TO_CHAR, TO_DATE : TO_CHAR(SYSDATE, ‘YYYYMMDD’)
SYSDATE(oracle)
GETDATE(sql server)
날짜 데이터 + 100에서 숫자는 day를 의미한다.

DECODE / CASE

CASE : CASE WHEN 조건 전부 안 맞는 경우 NULL이 출력된다.
DECODE : DECODE(gender, ‘M’, ‘남자’, ‘F’, ‘여자’, ‘기타’)

집계 함수

NULL과의 관계 - sum : sum()의 경우는 NULL 전부 제외한다.
NULL과의 관계 - count : count(*)의 경우는 전부 포함한다.
SUM(A) + SUM(B) + SUM(C) != SUM(A + B + C)

GROUP BY

집약 기능
그룹 수준으로 정보를 바꾼다.

JOIN

natural join, using : 중복된 컬럼이 제일 앞에 등장하고, 중복 컬럼은 하나만 나온다.
- using의 경우 alias 사용 불가
left outer join
- a left outer join b == a.col1 = b.col1(+)
- 뒷쪽 join key 컬럼에 붙인다.

서브쿼리

scalar subquery : select, order by 절에서 사용된다. 하나의 행을 반환한다.
inline view : from 절에서 사용된다. 하나의 임시 테이블이다.
subquery : where 절, having 절에서 사용된다.
group by에서는 서브쿼리를 사용할 수 없다.

집합 연산자

union : 합집합
union all : 중복 데이터 있고, 정렬 작업 없어 빠르다.
intersect : 교집합
minus : 여집합

제약 조건

pk : unique + not null
unique
not null

VIEW

독립성
편리성
보안성

그룹 함수

rollup : 각 소그룹의 합계 + 전체 합계를 각 그룹 마지막에 나타내 준다.
- GROUP BY ROLLUP(상품ID, 월) 하면 상품ID(첫 컬럼) 기준으로만 합계를 구한다.
cube : ROLLUP과 달리 GROUP BY절에 명시한 모든 컬럼에 대해 소그룹 합계를 계산한다.
- GROUP BY CUBE(상품ID, 월) 하면 상품ID 합계도 구하고 월별 합계도 구한다.
groupingsets : 항목에 대한 소계만 계산해 출력한다.
- GROUP BY GROUPING SETS(상품ID, 월) 하면 각 컬럼별 합계만 컴팩트하게 보여 준다.
grouping : 집계가 계산된 결과에 대해서는 1의 값을 갖고 그렇지 않은 결과에 대해서는 0을 가진다.

윈도우 함수

ROWS, RANGE :
- ROWS : 조회된 데이터를 물리적 위치(ROWNUM)로 구분하여 모든 행이 1개의 행으로 인식된다.
- RANGE : 집합으로 묶인 그룹이 1개의 행으로 인식된다.
RANK(중복 건너뛰고 순위 매김) vs DENSE_RANK(중복 건너뛰지 않고 순위 매김)
PARTITION BY :
- 분석함수([칼럼]) OVER(PARTITION BY 칼럼1, 칼럼2... [ORDER BY 절] [WINDOWING 절])

계층형 질의

prior 자식 데이터 = 부모 데이터
부모에서 자식으로 가면 순방향

절차형 PL/SQL

EXCEPTION :
procedure, trigger, user defined function의 차이점 존재

속성

기본 속성 : 업무 프로세스 분석을 통해 바로 정의가 가능한 속성이다.
설계 속성 : 업무에 존재하지는 않지만 설계하다 보니 도출된 속성이다.
파생 속성 : 다른 속성의 속성값을 계산하거나 특정한 규칙으로 변형하여 생성한 속성이다.
→ 기설파

도메인

데이터 유형, 크기, 제약 조건

관계

IE 표기법
- 식별자(강한 관계), 비식별자(약한 관계)
BARKER 표기법

식별자, 비식별자 관계

식별자 : 각각의 인스턴스를 구분 가능하게 만들어 주는 대표 격인 속성을 의미한다.
- 주식별자 (Primary Key) ⇐> 보조식별자 (주식별자와 같이 식별은 가능하나 대표 식별자는 아니다)
  1. 유일성 2. 최소성 3. 불변성 4. 존재성(not null)
- 내부 식별자(내부에서 만들어진 것) ⇐> 외부 식별자(외부에서 온 것)
- 단일 식별자(1) ⇐> 복합 식별자(n)
- 원조 식별자(가공되지 않는 날것의 식별자) ⇐> 인조 식별자(주식별자 속성이 두 개 이상인 경우 하나로 묶은 것)
식별자(강한 관계, 부모 엔티티 필수, 실선) | 비식별자(약한 관계, 부모 엔티티 선택, 점선)

ERD 작성 순서

엔티티를 그린다.
엔티티를 적절하게 배치한다.
엔티티 간의 관계를 나타낸다.
관계명을 정의한다.
관계의 참여도를 나타낸다.
관계의 필수 여부를 나타낸다.
→ 그배관명참필

정규화

제1 정규형 : 모든 속성은 하나의 값만 가져야 한다.
2차 부분 함수 종속 : 모든 속성은 반드시 주식별자의 일부가 아닌 전체에 종속되어야 한다.
3차 이행 함수 종속 : 주식별자가 아닌 모든 속성 간에는 서로 종속될 수 없다.

반정규화

데이터 무결성을 해친다.
대량 범위, 범위 처리, 통계 처리에서 주로 고려한다.

대량 데이터에 따른 성능

row migration, chaining
list partitioning
range partitioning : 관리 쉬움
hash partitioning : 관리 어려움

슈퍼/서브타입

용량이 작은 경우 : 개별 테이블 유지
용량이 큰 경우 :
- 트랜잭션 공통, 차이 : plus type
- 트랜잭션 전체 통합 : single

분산 데이터베이스

데이터 무결성을 해친다.

조인 수행 원리

NL JOIN : 랜덤 액세스, 대용량 소트 작업 유지
Sort Merge : 조인 키 정렬
Hash : 등가(=)만 가능, 선행 테이블 작음, Hash 처리를 위한 별도 공간 필요

옵티마이저

CBO : cost based optimizer - 경로 기반
RBO : rule based optimizer - 규칙 기반

인덱스

언제 사용되는지
- 부정형, 형변환, LIKE 등에서는 쓰지 않는다.
인덱스 사용 시 성능이 감소하는 경우
- DML (insert, delete, update) 을 사용하는 경우

실행 순서

들여쓰기와 순서로 판단한다.
1. 들여쓰기는 안 할수록 순서 뒤다.
2. 들여쓰기가 동일한 경우 뭉터기로 순서대로 처리하되, 내부 순서는 1번과 동일하다.

정리하며

SQLD 시험은 데이터 모델링 이론과 SQL 문법 모두를 다룬다. 정규화 단계, JOIN 종류, NULL 처리, 윈도우 함수 등 자주 출제되는 개념을 중심으로 반복 학습하면 어렵지 않게 합격할 수 있다.

탐색기