[day13] SQLD 헷갈리는 부분 정리 ˚｡･ﾟ✧

3/8 토요일 SQLD 시험 대비

 

🥚 엔터티 / 속성특징

• 한 개의 엔터티는 2개 이상의 속성을 갖는다.
• 한 개의 엔터티는 2개 이상의 인스턴스 집합이다.
• 엔터티 하나의 인스턴스는 다른 엔터티의 인스턴스 간 관계인 pairing을 갖는다.
• 하나의 속성은 하나의 속성값만 가진다. (복합속성일경우 정규화 대상)

🥚 카디널리티 계산식 : 선택도 * 전체 레코드 수

 

🥚 절차형 SQL을 이용해서 PROCEDURE , TRIGGER, USER DEFINED FUNCTION 만들 수 있다.

• 프로시저,트리거 모두 create구로 생성 (create procedure , create trigeer)
• procedure : commit/rollback 가능, execute로 수동 실행
• trigger : commit/rollback 불가능, 자동 실행

 

🥚 SQL 

• 비절차적/구조적/선언적 질의 언어
• 데이터베이스의 모든 작업을 통제
• ❗️DML은 관점에 따라 절차형 언어로 볼 수도 있다. (프로시저) select,delete from,update set,insert into ...

🥚 성능을 고려한 데이터모델링 순서

• 1. 데이터 모델링을 할 때 정규화를 정확하게 수행
  2. 데이터베이스 용량산정 수행
  3. 데이터베이스에 발생되는 트랜잭션 유형 파악
  4. 용량과 트랜잭션의 유형에 따라 반정규화 수행
  5. 이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 수행
  6. 성능관점에서 데이터 모델 검증

🥚 NULL 처리 방식

• 기본적으로
  • Oracle에서는 NULL을 가장 큰 값으로 간주 -> 맨 뒤에 저장
  • SQL Server는 NULL을 가장 작은 값으로 간주 -> 맨 앞에 저장

• NULLS FIRST : NULL 값 맨 앞으로 / NULLS LAST :  NULL 값 맨 뒤로
• 그룹 함수에서 연산할 때 NULL은 무시함.
• not in () 에 Null 값 포함된 경우 아무 행도 출력 안 됨
• case (col) when null then a else b : 알 수 없음 -> else 에 해당하는 b 출력

✔︎ null 함수

함수	설명
NVL	NVL(data1,data2) data1 이 null 이면 data2 data1 이 null 아니면 data1
NVL2	NVL(data1,data2,data3) data1 이 null 이면 data2 data1 이 null 아니면 data3
NULLIF	NULLIF(exp1, exp2) exp1 = exp2 이면 NULL exp1 <> exp2 이면 exp1
COALESCE	COALESCE(exp1, exp2, exp3, .... ) null이 아닌 첫 번째 값

* NVL/NVL2 : null이 아닐 경우 반환 값 차이

* NVL,NVL2는 Oracle 전용

* DECODE(exp1, exp2, exp3, exp4)

exp1=exp2 이면 exp3, exp1<>exp2 이면 exp4

 

 

🥚 집계 함수

• STDDEV() : 표준편차
• VARIAN() : 분산

🥚 비율 함수

• RATIO_TO_REPORT() : 파티션별 전체 SUM값에 대한 행별 백분율
• PERCENT_RANK() : 파티션별 백분율
  • 계산방식: (파티션 내 순위 - 1) / (파티션 내 전체 행 개수 - 1)
• CUME_DIST() : 현재 행값보다 작거나 같은 건수에 대한 누적 백분율

🥚 연산자 우선순위

괄호 ( ) > NOT 연산자 > 비교연산자, SQL 비교 연산자 > AND > OR

 

🥚 LTRIM / RTRIM / TRIM (leading, trailing, both)

• 기본 구조

LTRIM / RTRIM('제거 대상 문자열','제거할 문자 혹은 공백')
TRIM(leading/trailing/both '제거할 문자 혹은 공백' FROM '제거 대상 문자열')

• 특징

제거할 문자를 따로 지정해주지 않으면 자동으로 공백으로 인식해서 공백 제거함

• 사용법 차이

	LTRIM	RTRIM	TRIM
ORACLE	문자 지정 가능		문자 지정 가능 leading, trailing 지정 가능
SQL SERVER	공백만 제거		문자 지정 가능 leading, trailing 지정 불가

 

 

 

🥚 조인 종류

1. Natural join

• 동일한 이름, 동일한 타입끼리 조인
• 조건 추가 불가
• Alias 사용 불가

2. Hash join 

• 등가 조인만 가능
• 선행 테이블이 작을수록 유리
• 해시 함수 사용하기 때문에 CPU 많이 차지함
• 조인 칼럼에 인덱스 없어도 가능

* 조인 순서 :

선행 테이블 설정하고, 조건에 해당하는 행 선택

조인 키 기준으로 해시 함수 사용하여 해시 테이블을 메인 메모리에 생성

후행 테이블에서 조건에 만족하는 행 찾기

후행 테이블의 조인 키 사용하여 해시 함수 적용 및 해당 버킷 검색

 

3. Nested loop join

• random access 발생

 

 

 

🥚 PIVOT/UNPIVOT 기본 구조

PIVOT	UNPIVOT
행 -> 열 (묶어주기)	열 -> 행 (풀어주기)
PIVOT (집계함수(집계할칼럼) FOR 피벗할칼럼 IN(열로만들행값)	UNPIVOT(집계풀어줄칼럼명 FOR 언피벗할컬럼 IN(풀어줄칼럼)
pivot(sales for month in (Jan, Feb, Mar))	unpivot(sales for month in (jan_sales, feb_sales, mar_sales))

 

🥚 Partition 기법 : 키워드

• RANGE PARTITION : 날짜/숫자처럼 연속된 데이터 값의 범위를 기준으로 
• LIST PARTITION : 특정 값을 지정해서 기준으로 삼음
• HASH PARTITION : 해시 함수 적용
• COMPOSITE PARTITION : RANGE + HASH

 

🥚 DBMS별 상위 n개 추출 방법

• oracle ➜ rownum <= n
• sql-server ➜ top(n) (with ties)
• mysql ➜ limit

🥚 LEAD(칼럼명, 몇 번째 뒤의 행값?, 디폴트값)