헝그리개발자

시험을 준비하면서 핵심적인 부분만 노트를 하여 공개한다.

SQLD / SQLP 공통 : 제2과목은 SQL 기본 및 활용 이다.

2과목. SQL 기본 및 활용

- SQL 기본
- SQL 활용
- 관리 구문

DDL
- 데이터 정의어 (Data Definition Language)
- CREATE, ALTER, DROP, RENAME, TRUNCATE

DML
- 데이터 조작어 (Data Manipulation Language)
- SELECT, INSERT, UPDATE, DELETE, MERGE

DCL
- 데이터 제어어 (Data Control Language)
- GRANT, REVOKE

TCL
- 트랜잭션 제어어 (Transaction Control Language)
- COMMIT, ROLLBACK, SAVEPOINT

제약조건

PK (Primary Key)
- 데이터를 고유하게 식별하기 위한 기본키, NULL 값은 불가

Unique Key
- 데이터를 고유하게 식별하기 위한 고유키로, NULL 값은 제약 대상에서 제외

NOT NULL
- NULL 값을 금지

CHECK
- True / False 의 논리식으로 값의 범위를 지정

FK (FOREIGN KEY)
- 테이블간의 관계에서 기본키를 다른 테이블의 외래키로 지정하여 사용




함수

ABS
- 절대값 : 음수 -> 양수

FLOOR
- 내림 함수
- 3.14 => 3, -3.14 => -4

TRUNC
- 숫자 : 지정한 자리수(없으면 소수점) 을 날려버리는 함수
- 날짜 : 날짜의 시간을 날려 버림, YY : 년도 빼고 나머지 절사, MM : 년/월 빼고 나머지 절사
- 3.14 => 3, -3.14 => -3

ROUND
- 반올림

TO_NUMBER, TO_DATE, TO_CHAR
- 숫자, 날짜, 문자 변환

LOWER, UPPER
- 소문자, 대문자 변환

SUBSTR, LENGTH
- 문자열 자르기, 문자열 길이

LTRIM, RTRIM, TRIM
- 문자열 왼쪽 공백 제거, 오른쪽 공백 제거, 양쪽 공백 제거

COALESCE
- COALESCE(v1, v2, v3) : 앞에서 부터 NULL 이 아닌 최초값

NVL
- NVL(expr, v1) : expr 이 NULL 이면 v1, NULL 이 아니면 expr

NVL2
- NVL2(expr, v1, v2) : expr 이 NULL 이면 v2, NULL 이 아니면 v1

ISNULL
- MSSQL 함수, NVL 과 동일

NULLIF
- NULLIF(expr, v1) : expr 과 v1 이 같으면 NULL, 같지 않으면 expr

DECODE
- DECODE(expr, c1, v1, c2, v2, v3) : expr 이 c1 이면 v1, c2 일때 v2, 그외에는 v3

LPAD, RPAD
- 왼쪽에 문자 채우기, 오른쪽에 문자 채우기
- LPAD('ABCD', 10, ' ') =>  '      ABCD' => 왼쪽에 스페이스를 채워서 10자리 문자로 만든다.

집계함수
- COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN
- 갯수, 합계, 평균, 가장큰값, 가장작은값, 표준편차, 분산
- NULL 은 제외된다.


CASE 문
- case expr when 1 then '참' else '거짓' end : expr 이 1이면 참 아니면 거짓
- case when expr <= 10 then '참' else '거짓' end : expr 이 10 이하면 참, 아니면 거짓


GROUP BY / HAVING
- 집계함수 Count 에서 데이터가 없으면 0 이 나오지만, having 절에 의해 데이터가 없으면, no records 이다.


ORDER BY
- 기본정렬은 오름차순 ASC 이고, 내림차순은 DESC 이다.
- 오라클은 NULL 이 가장 큰 값으로 간주하여, 오름차순에서 가장 아래에 위치한다.
- MSSQL 은 NULL 이 가장 작은 값으로 간주하여, 오름차순에서 가장 위에 위치한다.



SELECT 실행순서

TOP N 쿼리

MSSQL
select top 10 * from order by col1

Oracle
select *
from (
     select *
      from
      order by col1
)
where rownum <= 10




INNER JOIN
- 조건에 일치하는 행만 반환한다.
from A inner join B on A.col1 = B.col1
from A join B on A.col1 = B.col1
from A, B where A.col1 = B.col1

- using 사용
from A join B using(col1)


OUTER JOIN
- Join 조건에 일치하는 행과, 일치하지 않는 행도 반환한다.

left outer join

from A left outer join B on A.col1 = B.col1
from A left join B on A.col1 = B.col1
from A, B where A.col1 = B.col1(+)

- 조건에 일치하는 행과 왼쪽 테이블 기준으로 오른쪽에는 일치하지 않는 행도 반환한다.
- A, B 테이블에 일치하는 데이터와, A에만 있고 B에는 없는 행도 반환


right outer join

from A right outer join B on A.col1 = B.col1
from A right join B on A.col1 = B.col1
from A, B where A.col1(+) = B.col1

- 조건에 일치하는 행과 오른쪽 테이블 기준으로 왼쪽에는 일치하지 않는 행도 반환한다.
- A, B 테이블에 일치하는 데이터와, B에만 있고 A에는 없는 행도 반환


full outer join

from A full outer join B on A.col1 = B.col1

- 조건에 일치하는 행과 양쪽 테이블의 조건에 일치하지 않는 행도 반환한다.
- A, B 테이블에 일치하는 데이터와, A에만 있고 B에는 없는 행, B에만 있고 A에는 없는 행도 반환


NATURAL JOIN
- A, B 테이블에 같은 이름의 모든 컬럼이 자동으로 연결된다.
- SELECT 절에 EMP.col 테이블명을 지정해서 쓰면 오류가 발생한다.

from A natural join B


CROSS JOIN
- join 조건이 없는 경우, 테이블간에 모든 데이터 조합으로 행을 반환한다.
- M * N 의 행 조합

from A cross join B



집합 연산자

UNION
- 합집합, 중복데이터는 제외

UNION ALL
- 합집합, 중복데이터 유지

INTERSECT
- 교집합, 중복데이터는 제외

EXCEPT (오라클 MINUS)
- 차집합
- A 에서 B 의 데이터를 제외한 결과



계층형질의

START WITH
CONNECT BY

조직도와 같은 Tree 구조의 데이터를 조회할 때 사용한다.

가상컬럼
level : 해당 행이 ROOT 데이터이면 1, 하위 데이터이면 2, 그 하위이면 +1 씩 증가
connect_by_isleaf : 해당 행이 LEAF 데이터 이면 1, 아니면 0
connect_by_iscycle : 중복 참조이면 1, 아니면 0 : 현 데이터의 하위 데이터가, 하위 데이터의 현데이터로 지정한 경우, 무한 루프에 빠지게 된다.

select level, ( lpad(' ', 4 * (level-1)) || empno ) as empno
, connect_by_isleaf as isleaf
from emp
start with empno is null
connect by prior mgr = empno;

start with 조건에는 root 노드를 지정하고
connect by 에는 하위노드를 찾는 방법을 지정한다.

prior 에 지정된 컬럼에서 상대편 컬럼으로 찾아간다.

CONNECT BY PRIOR 하위 컬럼 = 상위 컬럼 : 상위 -> 하위로 가는 순방향 전개
CONNECT BY PRIOR 상위 컬럼 = 하위 컬럼 : 하위 -> 상위로 가는 역방향 전개

ORDER SIBLINGS BY
- order by 와 같이 정렬을 수행하나, 같은 level 간의 정렬 순위를 지정한다.

NOCYCLE
- connect by NOCYCLE prior mgr = empno
- 데이터 입력실수로, 이전에 전개된 데이터가 다시 나타나는 현상이 발생하면 무한루프에 빠지게 된다.
- 이전에 전개된 데이터가 다시 나오면 전개를 멈추는 옵션.



서브쿼리
- 괄호로 묶인 SELECT 절

단일행 서브쿼리
- 결과행이 1개만 나오는 서브쿼리
- select 쿼리 안에 단일 값 조회시 사용되거나, wehere 절의 비교 연산자 등에 온다.

다중행 서브쿼리
- 결과행이 n 개 나오는 서브쿼리
- from 절의 쿼리에 오거나, where 절의 in , all, any, some, exists 등에 온다.



뷰(VIEW)

독립성 - 테이블구조가 변경되어도, 뷰를 사용하는 응용프로그램은 변경할 필요가 없다.
편리성 - 복잡한 질의나 자주 사용하는 질의를 뷰로 만들면, 쿼리가 간단해 진다.
보안성 - 숨기고 싶은 정보는 제외하고, 정보(컬럼) 를 제공할 수 있다.



그룹 함수

ROLLUP
- 지정된 컬럼의 소계와 합계를 구하는 함수
- 인수의 순서에 따라 결과가 달라진다.

group by rollup(dname, job)
- dname 별 소계, 전체 합계를 구한다.

CUBE
- 가능한 모든 조합의 소계와 합계를 구한다.
- 인수의 순서는 상관이 없다.

group by cube(dname, job)
- 모든 dname 별 소계, job 별 소계, 전체 합계를 구한다.


GROUPING SETS
- 소계 / 합계를 직접 선택하여 지정할 수 있는 함수
- () 또는 null 을 쓰면 전체 합계를 구한다.

group by grouping sets( (dname, job), (dname), () )
- 이렇게 쓰면 group by rollup(dname, job) 과 동일하다.

group by grouping sets( (dname, job), (dname), (job), () )
- 이렇게 쓰면 group by cube(dname, job) 과 동일하다.

GROUPING 함수
- select 절에서 해당 행이 소계에 사용된 행인지 여부를 판단할 때 사용한다.

select (case when grouping(job) = 1 then 'JOB합계' else job end )
- 부서별 모든 소계행인경우, JOB합계로 표시하고, 아닌경우 job 명칭을 표시한다.




Window 함수

partition by

sum(sal) over(partition by deptno)

sum(sal) over(partition by deptno group by emp range between unbounded preceding and current ro)

rows
- 물리적인 결과 행의 수를 지정한다.

range
- 논리적인 값에 의한 범위를 지정한다.

n preceding
- n 개 앞의 행

unbounded preceding
- 첫번째 행

n following
- n 개 뒤의 행

unbounded following
- 마지막 행

current row
- 현재 행



RANGE UNBOUNDED PRECEDING
SUM(sal) OVER(partition by mgr order by sal range unbounded preceding)
= RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 와 동일하다.
- 파티션 내에서 첫번째 행 부터 현재 행까지의 합계 즉 누적합을 표시

ROWS BETWEEN 1 PRECEDING AND 1  FOLLOWING
SUM(sal) OVER(partition by mgr order by sal rows between 1 preceding and 1 following)
- 현재행+앞에1행+뒤에1행 합계

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
SUM(sal) OVER(partition by mgr order by sal range between current row and unbounded following)
- 현재행부터 마지막 행까지 합계



CTE(Common Table Expression)
- 일시적인 결과 세트를 정의하고 이를 나중에 쿼리에서 참조할 수 있게 하는 방법

CTE와 서브쿼리의 차이점
- 가독성: CTE는 복잡한 쿼리를 여러 부분으로 나누어 가독성 높여준다.
- 재사용성: CTE는 동일한 쿼리 내에서 여러 번 재사용 가능
- 성능: CTE와 서브쿼리의 성능 차이는 데이터베이스 시스템과 쿼리의 복잡도 따라 다름
- CTE는 재귀적 쿼리에 유리

WITH kor_restaurants AS (
     SELECT *
      FROM restaurants 
      WHERE rest_type = 'kor'
)

재귀적 CTE

WITH cte (n) AS 
(
      SELECT 1
      UNION ALL
      SELECT n+1 FROM cte WHERE n < 5
)



NTILE
- 전체건수를 지정한 건수로 N등분

LAG
- N개 앞의 행 값 조회

LEAD
- N개 뒤의 행 값 조회



where in (list) 에서 list 에 null 이 있으면, 실제 null 인 데이터는 제외 된다.
- where ID in (1, 2, null) : ID 가 null 인 데이터는 제외되고, 1이나 2인 데이터만 조회된다.

순위

PIVOT
- 행을 열로 변환한다.

SELECT *
FROM 테이블
PIVOT ( 그룹합수(집계컬럼) FOR 피벗컬럼 IN (피벗컬럼값 AS 별칭 ... )

SELECT job, d1, d2, d3 
FROM emp PIVOT ( 
      SUM(sal) FOR deptno IN ('10' AS d1, '20' AS d2, '30' AS d3) 
)


UNPIVOT
- 열을 행으로 변환한다.


SELECT *
FROM 테이블
UNPIVOT ( 컬럼별칭(값) FOR 컬럼별칭(열) IN (피벗열명 AS '별칭', ... )

SELECT col_nm, col_val
FROM emp UNPIVOT (
      col_val FOR col_nm IN (col1, col2, col3)
)

시험을 준비하면서 핵심적인 부분만 노트를 하여 공개한다.

SQLD / SQLP 공통 : 제1과목은 데이터 모델링의 이해 이다.

1과목. 데이터 모델링의 이해

- 데이터 모델링의 이해
- 데이터 모델과 SQL

엔터티
- 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적, 영속적으로 존재하는 단위
- 인스턴스의 집합

속성
- 엔터티에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위
- 엔터티에 속하는 개체들의 특성
- 엔터티를 설명하고 인스턴스의 구성요소가 된다.
- 하나의 속성은 하나의 속성값을 가진다.

관계
- 엔터티와 엔터티의 관계
- 두개의 엔터티로부터 인스턴스를 연관짓는 행위
- 인스턴스 간에 논리적인 연관관계로 존재 또는 행위로서 연관성이 부여된 상태



도메인
- 속성이 가질 수 있는 값의 범위
- 엔터티 내에서 속성에 대한 데이터 타입, 크기, 제약사항을 지정하는 것.



엔터티 분류

유형무형에 따른 분류

발생시점에 의한 분류

스키마
외부 스키마 : 데이터베이스의 사용자 관점
개념 스키마 : 통합 관점, 데이터베이스 관리자 관점
내부 스키마 : 물리적 관점, 시스템 설계자 관점


관계(Relationship) 표기법
- 관계명
- 관계차수 : 1:1, 1:N, N:N
- 관계선택사양 : 필수, 옵션

식별자
- 엔터티를 대표하는 속성으로, 인스턴스를 구분할 수 있다.

주식별자/보조식별자
- 주식별자 : 유일성, 최소성, 존재성, 불변성을 만적하는 대표 식별자
- 보조식별자 : 유일성은 만족하지만 대표성을 만족하지 못하는 식별자

내부식별자/외부식별자
- 내부식별자: 엔터티 내부에서 스스로 생성되는 식별자
- 외부식별자: 다른 엔터티와 관계로 생성되는 식별자

단일식별자/복합식별자
- 단일식별자 : 하나의 속성
- 복합식별자 : 2개 이상의 복합 속성

본질식별자/인조식별자
- 본질식별자 : 비지니스 프로세스에서 만들어지는 식별자
- 인조식별자 : 인위적으로 만들어지는 식별자




데이터베이스 모델링의 특징
- 추상화, 단순화, 명확화

모델링의 3단계
- 개념적 모델링 : 추상적, 업무 중심적, 포괄적인 수준의 모델링
- 논리적 모델링 : 세부속성,식별자,관계를 표현, 정규화
- 물리적 모델링 : 데이터베이스 성능을 고려한 설계


정규화
- 데이터의 일관성, 유연성, 중복을 제거하고 데이터를 분해를 하는 과정
- 이상현상을 제거하는 설계 기법이다.

제1정규화
- 속성의 원자성 확보 : 속성은 하나의 속성값을 가져야 한다.
- 기본키(PK) 설정

제2정규화
- 기본키 중에 특정컬럼에만 종속된 경우, 부분 함수 종속성 제거

제3정규화
- 이행함수 종속 제거


이상현상

반정규화
- 데이터베이스의 성능 향상을 위해 데이터의 중복을 허용하거나, 조인을 줄이는 목적으로 하는 성능 향상 방법


 
분산데이터베이스
- 병렬처리로 빠른 응답이 가능, 용량확장이 쉬움
- 보안이나 무결성이 안좋다. 여러 네트워크에 분산되어 관리와 통제가 어렵다.




트랜잭션의 4가지 특징 (ACID)

NULL 값
- 아직 정해지지 않은 미지의 값

NULL 비교는 IS NULL / IS NOT NULL 을 사용한다.

where in (list) 에서 list 에 NULL 이 있으면, 실제 NULL 인 데이터는 제외 된다.
- where ID in (1, 2, NULL) : ID 가 NULL 인 데이터는 제외되고, 1이나 2인 데이터만 조회된다.


NULL 과의 연산은 NULL 이 된다.
NULL + 10 => NULL

집계함수에서 NUL 값이 있다면 제외된다.
- SUM, COUNT 등