SQLD 핵심 정리 :: 시험 전에 보는 핵심노트📕

✅  SQL 명령어

DML 조작어	DDL 정의어	DCL 제어어	TCL 트랜젝션 제어어
SELECT INSERT UPDATE DELETE	CREATE ALTER DROP RENAME	GRANT REVOKE	COMMIT ROLLBACK

 

✅ 문자열 자료형의 구분

CHARACTER, CHAR : 고정길이 문자열

VARCHAR(10) : 가변길이 문자열

 

✅ 집합연산자

UNION(합집합), INTERSECTION/INTERSECT(교집합), DIFFERENCE/EXCEPT/MINUS(차집합), PRODUCT/CROSS JOIN(A테이블 * B테이블)

 

✅ 순수관계연산자

- (가로행 조회) : select - where

- (세로행 조회) : project - select

- (세로 교집합) : natural join - 다양한 조인

 

✅ 차수(세로)와 카디널리티(가로)

 

✅ 정규화

제1정규화 : 중복 속성 → 하나의 속성(컬럼묶기)

제2정규화 : 반드시 PK가 2개 이상의 컬럼으로 만들어져 있어야 함(#학번 #대출번호)

제3정규화 : PK가 모든 컬럼을 결정할 수 있는 결정자가 되어야 함(이행적 종속관계를 띈다)

BCNF(Boyce-Codd Normal Form) : 모든결정자가 후보키가 되도록 테이블 분해

제4정규화 : 여러칼럼들이 하나의컬럼을 종속하는 경우 분해. 다중값 종속성 제거

제5정규화 : 조인에의해 종속성이 발생되는경우 분해 

 

✅ select count(' ')

Oracle - 빈 문자열로 0으로 조회

SQL-Server - 하나의 문자열로 1로 조회

 

🖍️ 문자와 숫자 비교 TO_NUMBER

'where "번호" = 1' 일 때, 무조건 숫자에 맞춰서 비교!

where to_number('001') = 1 처럼 숫자에 to_number을 붙일 수 있는 상황이어야 비교가 가능하다.

 

✅ 연산자 우선순위

1. ()

2. 비교연산자(>=, <=, =, !=, <>, ^=) / SQL연산자(IN, BETWEEN, LIKE, IS NULL)

3. NOT

4. AND

5. OR

 

✅ LIKE 연산자의 문자열 비교

1. COL1 LIKE '_A%'

2번째 자리가 A로 시작하고, 그 뒤에 다른문자가 오는 문자열. {'BANANA', 'BA', 'BAKADI'}

2. COL1 LIKE 'J%ES' → {'JAES', 'J2ES'}

 

✅ null의 계산 방법

1. AVG(1, 2, null)

평균을 구할 때 null은 제외하고 계산한다. (1+2) / 2

2. COL1 > 0 

* COL1 = {30, NULL, 0}

null 연산은 is null에만 응답한다. {30}

 

✅ order by와 null값

1. Oracle

- nulls last : null값을 마지막순서로

order by foot_size DESC nulls last

- nulls first : null값을 가장먼저 출력

 

🖍️ 2. SQL-Server : null이 먼저 출력

 

✅ 그룹 함수

1. GROUPING SETS

grouping sets (A, B) = group by A union all group by B

 

2. ROLLUP(바지 롤업을 연상)

group by rollup(A, B) order by a, b = group by 결과 아래에 한줄씩 소계, 맨 마지막 줄에 합계

A     |     B      |    SUM

--------------------

A1   |     B1     |    100  

A1   |     B2     |    260  

A1   |     null   |    100  

A2   |     B1     |    420  

A2   |     B2     |    580  

A1   |     null   |    1000  

null |     null   |    1360

 

3. CUBE

group by cube(a, b) order by 1, 2 = ROLLUP 결과 + 합계 윗줄에 B의 소계 추가

A     |     B      |    SUM

--------------------

A1   |     B1     |    100  

A1   |     B2     |    260  

A1   |     null   |    100  

A2   |     B1     |    420  

A2   |     B2     |    580  

A1   |     null   |    1000  

null |     B1     |    520

null |     B2     |    840  

null |     null   |    1360

 

💡 ROLLUP / CUBE를 GROUPING SETS로 변환하기

rollup, cube 결과를 연상하기 어려울때, grouping sets (group by union all group by)로 풀어서 연상하면 같은 결과를 찾을 수 있음

1. ROLLUP : group by grouping sets (b, c), b, ()

select a, b, c, d, sum(num)
from sales
group by grouping sets (a, rollup(b, c), d)
order by 1, 2, 3, 4;

// equals

select a, b, c, d, sum(num)
from sales
group by grouping sets (a, (b, c), b, (), d)
order by 1, 2, 3, 4;

2. CUBE : group by grouping sets (b, c), b,  c, ()

select a, b, c, d, sum(num)
from sales
group by grouping sets (a, cube(b, c), d)
order by 1, 2, 3, 4;

// equals

select a, b, c, d, sum(num)
from sales
group by grouping sets (a, (b, c), b, c, (), d)
order by 1, 2, 3, 4;

 

✅  윈도우 함수 - 윈도우를 설정할 수 있음

* OVER()

- select sum(sal) over() - 집계함수(sum)을 사용하더라도 전체 행의 row를 모두 조회

- over(partition by job) = select sum(sal) group by job

 

* 윈도우 설정하기

1. ROWS - row 기준으로 윈도우 설정하기

(1) ROWS UNBOUNDED PRECEDING

본인로우 포함, 위쪽 전체의 로우가 본인의 윈도우. (3번 row의 윈도우는 1~3)

select sum(sal) OVER (ORDER BY sal ROWS UNBOUNDED PRECEDING) AS emp1

* emp1 : row별 윈도우의 합계를 보여줌. 2번째 행 부터 누적된 값이 조회됨

(2) ROWS between UNBOUNDED PRECEDING and 1 PRECEDING : 본인 포함하지 않고, 윗칸으로 모든 합계

(3) ROWS between UNBOUNDED PRECEDING and CURRENT ROW : 본인 포함해서, 윗칸으로 모든 합계

(4) ROWS between UNBOUNDED PRECEDING and 1 FOLLOWING : 본인 포함해서, 윗칸으로 모든 합계, 아래 한 칸 까지 합계

(5) ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING : 본인 포함해서, 전체 합계

(6) ROWS 1 PRECEDING : 본인로우 포함 위쪽으로 1칸 = 나와 윗칸 더하기

(7) ROWS CURRENT ROW : 본인로우만 윈도우에 포함 = 원래의 값이 조회됨

(8) OVER (ORDER BY sal) - order by 뒤로 윈도우 설정 함수가 없을 때

윈도우 인덱스 기준으로 합계를 더하는 것이 아니라, order by 기준인 sal의 기준으로 합계를 만든다.

예를들어 3~5번 row의 sal이 연달아서 '123', '123', '123'인 경우에 모두 합계해버린다.

SAL  |  ROWS  |  only ORDER BY

1100 |  1100     |  1100

123   |  1223    |  1469    

123   |  1346    |  1469

123   |  1469    |  1469

 

2. RANGE - 범위 기준으로 윈도우 설정하기

select ename, sal
       , count(*) over (order by sal RANGE between 200 preceding and 200 following) as col

sal 기준으로 범위 내의 값을 조회.

sal이 800인 행이라면, sal이 600~1000 사이인 row를 count합니다.

 

3. windowing 구문을 partition by와 함께 사용하기

partition by는 group by와 비슷한 기능을 하는데,

over(partition by job order by sal ~) 이라면, job기준으로 파티션을 나누고(=group by 를 하고)

sal값이 같은 것끼리 합계한 결과를 조회합니다.

 

✅  비율/통계 함수

1. 전체 합계의 비율 구하기

select ename, sal,
       sal/sum(sal) OVER()
from emp

2. RATIO_TO_REPORT : 1번의 결과를 한번에 수행해주는 비율 함수

select ratio_to_report(sal) over()

3. cume_dist : 누적 분포 값

4. percent_rank : 백분위 순위

5. NTILE : order_by_clause로 행을 정렬 후 ntile의 개수만큼 버킷을 생성 후 행에 해당하는 버킷 번호를 할당

 

아래의 글에 정리가 잘 되어있습니다.

https://chgpky.tistory.com/10

[SQL]분석함수(analytic function)_순위함수(NTILE, CUME_DIST, PERCENT_RANK, RATIO_TO_REPORT)

 

🖍️  HASH JOIN

- cpu 연산이 많이 발생, 선행 테이블의 크기가 작아야 함

- 인덱스 없어도됨

- 해시 함수를 이용해 조인하기때문에 '='로 수행하는 조인동등조건에서만 사용가능

- 해시테이블을 메모리에 생성, 메모리가 부족하면 임시영역(디스크)에 저장

- 선행테이블은 build input, 후행테이블은 prove input

 

🖍️  두 테이블 간에 동일한 칼럼 이름을 가진 것을 모두 출력하는 join은 ? NATURAL_JOIN

 

✅  식별자의 분류

- 대표성여부 : 주식별자 / 보조식별자

- 생성여부 : 내부식별자 / 외부식별자

- 속성수 : 단일식별자 / 복합식별자

- 대체여부 : 본질식별자(원조식별자) / 인조식별자

 

✅ 주식별자 특징

1. 유일성 : 모든 인스턴스를 유일하게 구분

2. 최소성 : 유일성을 만족하는 최소한의 속성으로 구성

3. 불면성 : 식별자의 값은 변하지않아야 함

4. 존재성 : 값이 존재

 

✅ 주식별자 도출기준 : 업무 자주이용됨, 명칭/내역 제외 명칭 대신 코드로, 조인으로 인한 성능저하 예방을 위해 최대한 적은 속성수 구성 

 

✅ 논리식별자로 정의된 물리Key 종류

1. Primary 기본키 : 대표키

2. Candidate 후보키 : 유일성/최소성 만족. 후보키들 중 하나가 기본키, 나머지가 대체키가 됨

3. Alternate 대체키 : 후보키들 중 기본키가 아닌 것

4. Super 슈퍼키 : 유일성 만족. 최소성 불만족.

5. Foreign 외래키 : 다른 테이블의 기본키를 참조

 

✅ where A.게시판ID = B.게시판ID(+)

(+) : outer join 표기

 

✅ left outer join을 시도할 때, 어떠한 행을 포함시키는 조건을 걸려면

꼭, on () 내부에 조건을 작성해야 한다. 외부 where 절에 조건이 작성되면 행에 걸리지 않음.

 

✅ 포함/제외 조건

포함 : in, exists

제외 : not in, not exists