[SQLD] SQLD 요약본 아카이브

프롤로그:

내가 SQLD를 준비하면서 그때 그때 모아뒀던 자료들 백업!!

진짜 내가 보면서 공부하는 용도로 만든거라 엄청 두서없음

링크들은 출처!! 자세한 설명이 보고 싶다면 들어가서 보면 됨

(하지만 이정도 알면 시험 합격할 수 있다)

 

엔터티: 업무에 필요한 데이터를 저장 및 관리하는 테이블

속성: 엔터티의 한 부분, 업무에 필요한 최소 값의 단위. 컬럼.

도메인: 가질 수 있는 값의 범위, 데이터타입, 크기, 제약사항 지정

 

-도메인의 특징

엔터티 내에서 속성에 대한 데이터 타입과 크기를 지정 ex) unique,not null, check 등 조건 설정

check: 직접 써주는 제약조건. where 절에 써주는 방식과 같다. 

 

PK: 테이블의 모든 데이터를 유일하게 식별해주는 컬럼.(주식별자)

FK: 테이블간의 관계를 의미.

 

=> 엔터티와 테이블의 차이?

엔터티는 DB, SQL상에서 실제로 존재하지 않음, 그냥 일종의 개념.

테이블은 실제로 존재하며 물리적인 구조를 가지고 있음

 

논리 - 물리 (용어 차이)

엔터티-테이블

속성-칼럼

관계-관계

키 그룹-인덱스

 

https://velog.io/@keywookim/We.TIL-35-엔티티와-테이블의-차이

 

도메인 무결성 제약: 타입이 다른 것을 방지

엔터티 무결성 제약: PK(중복, null 방지)

참조 무결성 제약: FK(여러 테이블의 데이터 무결성을 깨뜨리지 않기 위해 제약 필요)

 

https://blog.naver.com/www104567/221113528833

 

-식별자의 종류

 

대표성의 여부: 주식별자, 보조식별자

스스로 생성 여부: 내부식별자, 외부식별자

속성의 수: 단일식별자, 복합식별자

업무적으로 의미가 있던 식별자 속성: 본직실별자

일련번호같이 새롭게 만든 식별자를 구분하기 위해 만든 식별자: 인조 식별자 (대체여부)

 

-주식별자의 특징

 

유일성: 모든 인스턴스들을 유일하게 구분

최소성: 유일성을 만족하는 최소의 수가 되어야 함

불변성: 한 번 지정된 식별자의 값은 변하지 않아야 함

존재성: 반드시 값이 존재해야 함

 

-반정규화 적용

 

테이블 반정규화

속성(칼럼)의 반정규화

관계의 반정규화

 

-테이블의 반정규화 기법

 

테이블 병합(1:1, 1:M 관계, 슈퍼/서브타입)

테이블 분할(수직, 수평)

테이블 추가(중복,  통계, 이력, 부분)

 

-칼럼의 반정규화 기법

 

중복칼럼 추가

파생칼럼 추가

이력테이블 칼럼 추가

PK에 의한 칼럼 추가

응용시스템 오작동을 위한 칼럼 추가

 

-관계의 반정규화

 

중복관계 추가

ex)비식별자 관계를 식별자 관계로 

데이터를 처리하기 위한 여러 경로를 거쳐 조인이 가능하지만, 성능저하를 예방하기 위해 추가적인 관계를 맺음

비식별자는 느슨한 관계인데 식별자 관계로 바꿈 => 추가적인 관계를 맺음

 

오답인 이유: FK에 대한 속성을 추가…

=> PK에 의한 칼럼 추가.  (칼럼과 속성은 같은 라인, 다만 PK를 고치는 것이지 FK가 아님.

 

-반정규화를 하는 이유(무결성이 깨질 수 있는 위험을 무릅쓰고 데이터를 중복하여 적용하는 이유)

 

데이터를 조회할 때 디스크 I/O량이 많아서 성능 저하가 발생하는 경우

경로가 너무 멀어 조인으로 인한 성능 저하가 발생하는 경우

칼럼을 계산하여 읽을 때 성능 저하가 발생하는 경우

 

 

 

 

 

 

 

 

-속성의 특성에 따른 분류

 

기본속성 (업무로부터 추출한 일반적인 속성) ex) 원금, 예치기간, 이자율

설계속성 (업무상 필요 데이터 외의 업무를 규칙화하기 위해 변형하여 정의) ex) 예금분류

파생속성 (데이터 조회시 빠른 성능을 위해 원래 속성값을 계산하여 저장할 수 있도록 만듬) ex) 이자

 

-속성: 더 이상 분리되지 않는 최소의 데이터 단위 (엔터티에서 한 분야를 담당

 

엔터티에 대한 구체적인 정보 제공

한 개의 엔터티는 두 개 이상의 인스턴스의 집합

한 개의 엔터티는 두 개 이상의 속성을 가짐

한 개의 속성은 한 개의 속성값을 가짐

속성도 집합

 

https://coding-log.tistory.com/70?category=1007649

 

-이상현상

 

삭제이상: 원하는 값만 삭제하고 싶지만 하나의 튜플이 삭제를 원하지 않는 속성 값도 갖고 있기 때문에 같이 지워져서 발생하는 문제. (튜플: RDB의 테이블에서 한 행에 해당. 파일 시스템의 레코드)

삽입이상: 원하는 값만 삽입하고 싶으나 테이블에 필요치 않은 필드들 때문에 원치 않는 필드의 값도 삽입해야 하는 경우.

갱신이상: 어떤 값을 업데이트 했을 때 그 속성의 다른 속성값들과의 불일치가 발생하는 현상

 

https://simsimjae.tistory.com/244

 

엔터티-인스턴스-속성의 개념

 

엔터티: 테이블의 이름

인스턴스: 데이터베이스에 저장된 데이터 내용의 전체 집합을 의미

 

https://blog.naver.com/clsrnclsrn95/222069240916

 

 

TRUNCATE: 기존에 사용하던 테이블의 모든 로우를 제거하기 위한 명령어

모든 로우를 제거하기 위한 것이기 때문에 특정 로우를 선택하여 지울 수 없다.

빠르다

삭제 후 되돌릴 수 없음

=> TRUNCATE TABLE 테이블 이름

 

DROP TABLE과의 차이점

DROP TABLE은 테이블의 존재 자체가 없어져 구조가 남지 않음.

TRUNCATE TABLE은 테이블은 존재하면서 데이터의 내용만 제거, 구조는 남음.

 

DELETE TABLE과의 차이점

TRUNCATE TABLE보다 DELETE TABLE이 시스템 부하가 더 크다

삭제 후 복귀 가능

DELETE는 테이블을 삭제 하는게 아니라 자료만 삭제 (DROP, TRUNCATE는 삭제)

 

DELETE FROM 테이블 이름

(FROM은 삭제 가능)

 

https://blog.naver.com/weekamp/221595976817

 

-TRUNCATE, DROP, DELETE의 차이

https://blog.naver.com/kji9653/222036578325

 

ORDER BY는 정렬하고자 하는 열의 이름을 쓰는 것. 이름을 쓴 이후에 어떻게 정렬할 것인지 옵션 추가.

ex) order by n1 desc;

 

기본은 asc 오름차순, desc은 내림차순 옵션

 

열의 숫자를 사용해서도 정렬가능

ex) select * from emp order by 3;

 

but, select 절에 없는 컬럼의 자리수를 사용할 수는 없다.

 

PROCEDURE vs TRIGGER

EXECUTE - 자동 실행

커밋, 롤백 가능 - 커밋, 롤백 불가

+ 둘다 CREATE 명령어로 생성

 

프로시저: 프로시저는 미리 DB 서버에 일련의 SQL 명령을 해놓고 프로시저를 실행하여 SQL 명령을 간단하게 실행할 수 있다. 프로시저 안에는 SQL문 뿐만 아니라 if, while문 등의 제어 명령이나 반복 명령을 기술 할 수 있기 때문에 프로그램도 만들 수 있다.

 

트리거: 테이블에 작성. 어떤 테이블에 행 삽입, 변경, 삭제시 트리거가 설정되어 있으면 SQL 문장이 자동으로 실행

 

https://keumjae.tistory.com/131

 

 

-계층형 쿼리: START WITH ~ CONNECT BY

 

계층형 구조는 상하 수직관계의 트리형태의 구조로 이루어진 형태를 말합니다. 예를 들자면 특정회사의 부서, 특정학교의 학과등이 있습니다. 계층형 쿼리는 테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 말합니다. 

 

START WITH [최상위 조건] 

CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건]  -> 부모노드와 자식노드 연결 

 

ex)

START WITH parent_cd IS NULL

CONNECT BY PRIOR dep_cd = parent_cd;

 

https://coding-factory.tistory.com/461

 

CONNECT_BY_ISLEAF: 계층형 쿼리에서 해당하는 로우가 자식노드가 있는지 없는지 여부를 체크

자식노드가 있을 경우 0, 자식노드가 없을 경우 1

 

https://devjhs.tistory.com/171

 

계층형 쿼리의 진행방향

 

PRIOR 자식 = 부모 | 부모 -> 자식 방향으로 전개 

PRIOR 부모 = 자식 | 자식 -> 부모 방향으로 전개 

 

start with 부모 connect by 자식 = 부모 (순방향) 부모 to 자식

start with 자식 connect by 부모 = 자식 (역방향) 자식 to 부모

 

https://devdhjo.github.io/sqld/2019/11/26/database-sqld-020.html

[SQLD] 020# SQL 활용 ⑶ 계층형 질의와 셀프 조인

 

 

 

 

 

 

 

 

 

-SQL 실행순서

from -> on -> join -> where -> group by -> cube | rollup -> having -> select -> distict -> order by -> top

 

-JOIN 종류

 

 

inner join

교집합의 값을 가져오는… 키값이 있는 테이블의 컬럼 값을 비교 후 조건에 맞는 값을 가져오는 것.

 

ex)

select *

from emp inner join dep

on emp.dep_id = dep.dep_id;

 

select *

from emp, dep

where emp.dep_id = dep.dep_id;

 

cross join

테이블 두 개를 합치는 것. a 튜플이 6개, b 튜플이 4개면 6 * 4 = 24

 

outer join

조인하는 여러 테이블에서 한 쪽에는 데이터가 있고 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블의 내용을 전부 출력. 즉, 조인 조건에 만족하지 않아도 해당 행을 출력하고 싶을 때 사용할 수 있음.

 

=> left, right outer join

 

조인문의 각 방향에 있는 테이블의 모든 결과를 가져온 후 그 반대 방향의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 null 표시

 

ex)

 

select *

from emp e left outer join dep d

on e.dep_id = d.dep_id;

 

oracle ver)

select *

from emp e, dep d

where e.dep_id = d.dep_id(+);

 

//left의 경우 right에 (+) 붙여줌

 

full outer join: right, left outer join을 합친 것. 양쪽 모두 조건이 일치하지 않는 것들까지 모두 결합하여 출력

 

https://clairdelunes.tistory.com/22

 

-JOIN 연산 

 

Nested loop join

두 개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 row를 결합해 조인하는 방식 

for문을 여러 개 돌리는 것과 같은 논리.

좁은 범위에 유리한 성능. 실행속도 = 선행 테이블 사이즈 * 후행 테이블 접근 횟수

데이터를 랜덤으로 액세스하기 때문에 결과 집합이 많으면 느려짐.

 

Sort merge join

조인의 대상범위가 넓을 경우 랜덤 액세스를 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 사용

연결을 위해 랜덤엑세스를 하지 않고 스캔을 수행

Nested loop join과 달리 선행집합 개념이 없음

정렬을 위한 영역에 따라 효율에 큰 차이 발생

조인 연산자가 ‘=‘가 아닌 경우 Nested loop join 보다 유리.

두 결과 집합의 크기 차이가 많이 나는 경우 비효율적

 

Hash join

해싱함수 기법을 활용하여 조인을 수행하는 방식 (직접 연결이 아니라 연결될 대상을 특정 파티션에 모아두는 역할만 함)

대용량 처리의 선결조건인 랜덤 액세스와 정렬에 대한 부담을 해결

해시 테이블 생성후 Nested loop처럼 순차적인 처리 형태로 수행

 

https://needjarvis.tistory.com/162

 

SQL 문에서 SUM 연산할 때 NULL이 있으면 NULL값을 제외하고 계산해준다.

 

-NULL 처리

 

NVL: NULL 값을 다른 값으로 바꿀 때 사용. 

ex) nvl(mgr, 0)

 

NVL2: NVL + DECODE, null이 아닐 경우와 null일 경우 반환할 값을 지정해줌.

ex)nvl2(mgr, 1, 0)

 

NULLIF: 매개변수 두 개의 값이 동일하면 NULL 아니면 첫 번째 매개변수 값을 반환

ex) NULLIF(exp1, exp2)

=> CASE WHEN exp1 = exp2 THEN null ELSE exp1 END

 

COALESCE

NVL함수와 비슷

coalesce(exp1, exp2, exp3, …)

exp1이 null이 아니면 exp1 값을 아니면 나머지 값을 반환

 

http://www.gurubee.net/lecture/1880

 

-ROLL UP, CUBE, GROUPING

 

 

ROLL UP

group by절에 의해 그룹 지어진 집합 결과에 대해 좀 더 상세한 정보를 반환하는 기능 수행

=> 누적에 대한 총계를 구할 때 씀… sum쓸 때. 그래서 그 누적 합만 나오고 나머지 row에는 null이라고 뜸

계층구조, 인수 순서가 바뀌면 수행 결과도 바뀜

 

ex) group by rollup(id, dept_nm);

 

CUBE

roll up과 비슷한데 roll up은 total 이라면 cube는 item total값과 column total 값을 나타낼 수 있음.

=> total도 가능하고 한 변수를 골라서 그 그룹별로도 total을 구하는 것도 가능

결합 가능한 모든 값에 대해 다차원 집계 생성

CUBE는 ROLL UP에 비해 시스템에 많은 부담을 줌.

 

ex) group by cube(id, dept_nm);

GROUPING

의사칼럼(실제로 존재하지 않으나 특수 목적으로 사용

rollup, cube 모두 사용가능

해당 row가 group by에 의해 산출된 row인 경우에는 0을 rollup, cube에 의해 산출된 row인 경우에는 1을 반환

 

https://androphil.tistory.com/166

 

-그룹내 순위결정하는 함수, ROW_NUMBER(), RANK(), DENSE_RANK()

 

row_number(): 1등이 두 명이어도 1,2등으로 나눈다.

rank(): 1등이 두 명이면 그 다음 순위는 3등이 된다.

dense_rank(): 1등이 두 명이면 그 다음 순위는 2등이 된다.

 

row_number(), rank(), dense_rank() over(partition by [그룹핑할 컬럼] order by [정렬할 컬럼])

 

 

 

 

 

 

 

 

 

 

 

 

-commit에 관한 oracle과 sql server의 차이

 

oracle은 사용자가 임의로 commit, rollback을 해줘야 트랜잭션이 종료됨

sql server는 기본적으로 auto commit, auto commit이 꺼져있는 경우 rollback하면 begin 지점까지 rollback

 

exists(서브쿼리): 한 건이라도 존재하면 true, 아니면 false

 

like는 대소문자를 구분함 ‘A%’라고 했으면 A만되고 a는 안됨

대소문자 구분없이 하려면 where upper(컬럼명) like upper(검색명)

 

-숫자관련 함수

 

round: 반올림

exp: e^값

ceil: 무조건 반올림

trunc: 소수점 절삭

 

-order by에 대한 sql server와 oracle의 차이

oracle: null 값이 맨 마지막으로 정렬

sql server: null 값이 맨 앞으로 정렬

 

옵티마이저: SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부 핵심엔진

 

where 1 = 1 => 참이라는 의미

 

-집합 연산자 UNION, UNION ALL, MINUS, INTERSECT

 

합집합

*UNION ALL: 중복을 포함한 두 개의 쿼리문의 값을 하나로 묶어 검색

*UNION: 중복을 제거한 두 개의 쿼리문의 결과값을 하나로 묶어 검색

 

교집합

*INTERSECT: 양쪽 모두에서 포함된 행 검색, 중복된 행을 하나의 행으로 표시

 

차집합

*MINUS: 첫 번째 검색결과에서 두 번째 검사결과를 제외

 

-윈도우 함수(Window Function): 행과 행간의 관계를 정의하기 위해서 제공되는 함수

순위, 합계, 평균, 행 위치 등을 조작

group by와 병행하여 사용 불가

윈도우 함수로 인해 결과 건수가 줄어들지 않음

sum, max, min 등과 같은 집계 윈도우 함수를 사용할 때 윈도우 절과 함께 사용하면 집계의 대상이 되는 레코드 범위를 지정할 수 있다.

윈도우 함수의 partiotion 구문은 group by와 비슷한 역할을 한다.

종류: 순위함수, 집계함수, 비율함수

 

-비율함수

 

RATIO_TO_REPORT: 전체 합계 대비 비율 또는 백분율을 구하는 그룹 함수

ex)

select sal, ratio_to_report(sal) over() as sal_ratio

from emp

where job = ‘manager’

 

NTILE: 파티션 별로 전체 건수를 argument 값으로 n등분한 결과를 조회

ex)

select deptno, ename, sal, ntile(4) over(order by sal desc) as n_tile 

from emp;

 

=> 4등분으로 분할하는 의미, 급여가 높은 순으로 1~4등분으로 분할, 급여가 가장 높은 등급에 속하면 1, 가장 낮은 등급에 속하면 4 (비율로 따짐)

만약 10명이라고 쳤을때 4등분을 한다면 2.5씩이므로 3,3,2,2 이렇게 등분됨… 어렵다

 

https://velog.io/@yewon-july/Window-Function

 

order siblings by: 계층내에서 정렬하기

ex) order siblings by code desc;

=>code행에 같은 계층 별로 역순으로 정렬

 

-with: 서브 쿼리가 여러 번 다시 사용될 때 편리하게 사용

with [with절 명칭] as (select [컬럼명] from [테이블명])

select [컬럼명] from [with절 명칭]

 

with 문제가 나왔을 때 union all + 최상위에 null 어쩌고,, 계층 문제

재귀문제임

재귀하면서 위의 레벨로 올라가서 계속 값을 더해서 구함,,

 

http://gurubee.net/lecture/2223

 

-where절 우선순위

 

1 : () 괄호

2 : +, - * / 산술 연산자

3 : || 연결 연산자

4 : > , < , >= , <= , != , <> , =  비교조건

5 : IS [NOT] NULL , [NOT] LIKE , [NOT] IN 

6 : [NOT] BETWEEN

7 : NOT 논리 조건

8 : AND 논리 조건

9 : OR 논리 조건

 

or가 제일 마지막 and가 제일 높음

 

 

https://dahye0811.tistory.com/entry/WHERE절-우선순위

 

-LAG, LEAD 함수

LAG: 이전 행의 값을 리턴

LAG(대상 컬럼명 [값을 가져올 행의 위치(생략가능)] [값이 없을 경우 기본값(생략가능)]) OVER([그룹 컬럼명(생략가능)] 정렬 컬럼명)

 

LEAD: 다음 행의 값을 리턴

LEAD(대상 컬럼명 [값을 가져올 행의 위치(생략가능)] [값이 없을 경우 기본값(생략가능)]) OVER([그룹 컬럼명(생략가능)] 정렬 컬럼명)

 

가져올 값이 없으면 null처리됨. null의 숫자가 즉 값을 가져올 행의 위치 부분,,, ㅎ