2024 SQLD 자격증: 주요 개념 및 시험 이론

SQL 특징

 - 구조적(structured)

 - 집합적(set-based)

 - 선언적(declarative)

 - 비절차적

구분	종류
DML	SELECT, INSERT, UPDATE, DELETE, MERGE
DDL	CREATE, DROP, ALTER, RENAME, TRUNCATE
DCL	GRANT, REVOKE
TCL	COMMIT, ROLLBACK, SAVEPOINT

 

DROP, TRUNCATE는 auto commit

DELETE는 commit 필요

---

UML 클래스다이어그램

 

식별자 관계	비식별자 관계
연관 관계, 필수적 관계	의존 관계, 선택적 관계
실선	점선
멤버 변수	파라미터
강한 연결	약한 연결

 

 

---

데이터 독립성

 

외부 스키마 : 여러 사용자가 보는 개인적 DB 스키마
개념 스키마 : 모든 사용자 관점을 통합한 전체 DB
내부 스키마 : 물리적 장치에서 데이터가 실제적 저장

---

정규화	반정규화
입력, 수정, 삭제 성능 일반적으로 향상	입력, 수정, 삭제 성능 저하
조회 성능 저하 가능성 존재	중복성을 증가시켜 조회 속도 향상

---

내부 식별자

 - 해당 엔티티 내부에서 생성

 

외부 식별자

 - 다른 엔티티에서 옴

 

 

본질 식별자

 - 업무에 의해 만들어짐, 가공되지 않은 식별자

 

인조 식별자

 - 인위적으로 만들어짐

---

NULL 관련 함수

 

NVL(식1,식2) / ISNULL(식1,식2) : 식1의 값이 NULL 이면 식2 출력

SELECT NVL(salary, 0) AS salary
FROM employees;

SELECT ISNULL(salary, 0) AS salary
FROM sales;

 - salary가 NULL인 경우 0을 반환합니다.

 

 

NVL2(식1, 식2, 식3) : 식1이 NULL이 아니면 식2를 반환하고,

                                   식1이 NULL이면 식3을 반환

SELECT employee_id,
       NVL2(manager_id, '관리자 있음', '관리자 없음') AS manager_status
FROM employees;

 - manager_id가 NULL이 아닌 경우 '관리자 있음'을 반환합니다.
 - manager_id가 NULL인 경우 '관리자 없음'을 반환합니다.

 

NULLIF(식1,식2) : 식1이 식2와 같으면 NULL을, 아니면 식1을 출력

SELECT NULLIF(commission, 0) AS commission
FROM sales;

 - commission이 0인 경우 NULL을 반환합니다. 0이 아니면 commission을 반환합니다.

 

COALESCE(식1,식2) : 임의의 개수표현식에서 NULL이 아닌 최초의 표현식, 모두 NULL이면 NULL 반환

ex)COALESCE(NULL,NULL,‘abc’) -> ‘abc'

SELECT COALESCE(phone_home, phone_work, phone_mobile) AS contact_number
FROM customers;

 - 목록에서 발견된 'NULL'이 아닌 첫 번째 전화번호를 반환합니다.

 

COUNT(*) : NULL 포함 행의 수
COUNT(표현식) : NULL 제외 행의 수
SUM, AVG : NULL 제외 합계, 평균 연산

※MIN, MAX, SUM 함수는 NULL 값을 계산에서 포함하지 않으며, 모든 값이 NULL인 경우에만 결과가 NULL이 됩니다.

---

CEIL/CEILING(n) : 크거나 같은 최소 정수 반환
FLOOR(n) : 작거나 같은 최대 정수 리턴

SELECT CEIL(10.5)
	, FLOOR(10.5)
FROM DUAL;

CEIL	11
FOOLR	10

 

SELECT FLOOR(10.4) , FLOOR(-2.4) FROM DUAL;

-> 10, -3

---

연산자 우선순위 :

1. 괄호( )

2. 단항 연산자: +, -, ~(부정) // 음수, 양수
3. 지수: **
4. 곱셈과 나눗셈: *, /
5. 덧셈과 뺄셈: +, -
6. 연결: ||
7. 비교 연산자: =, !=, >, <, >=, <=, IS [NOT] NULL, LIKE, BETWEEN , IN
8. 논리 연산자: NOT, AND, OR

---

UNION ALL
 - alias는 첫 번째 SQL의 컬럼 기준

 - 정렬은 마지막 SQL기준

---

순수 관계 연산자
 - SELECT -> WHERE절로 구현
 - PROJECT -> SELECT절로 구현
 - (NATRUAL) JOIN -> 다양한 JOIN
 - DIVIDE -> 현재 사용x

---

JOIN 종류

 

FULL OUTER JOIN
조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어
JOIN하여 결과를 생성한다. 중복 데이터는 삭제한다.

---

GROUP BY 절에서는 ALIAS 사용 불가

SELECT 쿼리 실행 순서
 - FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
F W G H S O

---

WHERE절에서는

집계 함수(SUM, AVG, COUNT 등) 사용 불가

---

식별자 : 엔터티내에서 인스턴스를 구분하는 구분자
 - 식별자는 논리 데이터 모델링 단계에 사용
 - Key는 물리 데이터 모델링 단계에 사용

---

그룹별 집계

 

ROLLUP

SELECT department, manager, SUM(sales)
FROM sales_data
GROUP BY ROLLUP(department, manager);

 - [department, manager]로 그룹화된 합계.
 - department 별 합계 (manager가 NULL로 표시).
 - 전체 합계 (department와 manager 모두 NULL).

 

CUBE

SELECT department, manager, SUM(sales)
FROM sales_data
GROUP BY CUBE(department, manager);

 - [department, manager]로 그룹화된 합계.
 - department 별 합계 (manager가 NULL로 표시).
 - manager 별 합계 (department가 NULL로 표시).
 - 전체 합계 (department와 manager 모두 NULL).

 

GROUPING SETS

SELECT department, manager, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS (
    (department, manager),
    (department),
    (manager)
);

 - [department, manager]로 그룹화된 합계.
 - department 별 합계 (manager가 NULL로 표시).
 - manager 별 합계 (department가 NULL로 표시).
※전체 합계는 생성 X (명시하지 않음).

 

차이점
ROLLUP: 계층적인 그룹화 집계를 수행하며, 최종 집계까지의 중간 단계 그룹화를 모두 포함.
CUBE: 모든 가능한 조합에 대해 집계를 수행하여 다차원 분석에 적합.
GROUPING SETS: 특정한 그룹화 집합에 대해서만 집계를 수행하는 가장 유연한 방식.

---

윈도우 함수

 

ROW_NUMBER

SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

 - salary가 같아도 각 행에 고유한 번호가 부여됩니다.

 

RANK

SELECT employee_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

 - 동일한 salary를 가진 행에는 같은 순위가 부여되며, 다음 순위는 건너뜁니다.
 - 예를 들어, salary가 같은 행에 RANK 1이 부여되면, 그 다음 순위는 2가 아닌 3이 됩니다.

 

DENSE_RANK

SELECT employee_id, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

 - 동일한 salary를 가진 행에는 같은 순위가 부여되며, 다음 순위는 건너뛰지 않습니다.

 - 순위에 갭이 없습니다.

 

예시

employee_id	salary
1	5000
2	4000
3	4000
4	3000

 

이 데이터를 기준으로 세 함수를 적용한 결과는 다음과 같습니다:

employee_id	salary	ROW_NUMBER	RANK	DENSE_RANK
1	5000	1	1	1
2	4000	2	2	2
3	4000	3	2	2
4	3000	4	4	3

 

ROW_NUMBER() : 1, 2, 3, 4

RANK() : 1, 2, 2, 4

DENSE_RANK() : 1, 2, 2, 3

---

LAG, LEAD 함수

 

LAG

 - 현재 행을 기준으로 이전 행의 값을 조회한다

 - 주로 현재 행의 값과 이전 행의 값을 비교할 때 사용

-- 문법
LAG(expression, offset, default) OVER (ORDER BY column)

expression: 조회하려는 값이 있는 열 또는 표현식.
offset: 현재 행에서 몇 행 뒤로 가서 값을 조회할지 지정합니다. 기본값은 1
default: 조회할 행이 없는 경우 반환할 기본값.

 

SELECT 
    sale_id, 
    sale_date, 
    amount, 
    LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS previous_amount
FROM 
    sales;

SALE_ID	SALE_DATE	AMOUNT	PREVIOUS_AMOUNT
1	2024-08-01	1000	0
2	2024-08-02	1500	1000
3	2024-08-03	1200	1500

 >  PREVIOUS_AMOUNT 열은 이전 행의 금액을 보여줍니다.

 

 

LEAD

 - 현재 행을 기준으로 다음 행의 값을 조회

 - 주로 현재 행의 값과 다음 행의 값을 비교할 때 사용

-- 문법
LEAD(expression, offset, default) OVER (ORDER BY column)

expression: 조회하려는 값이 있는 열 또는 표현식.
offset: 현재 행에서 몇 행 앞으로 가서 값을 조회할지 지정합니다. 기본값은 1입니다.
default: 조회할 행이 없는 경우 반환할 기본값.

 

SELECT 
    sale_id, 
    sale_date, 
    amount, 
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_amount
FROM 
    sales;

SALE_ID	SALE_DATE	AMOUNT	NEXT_AMOUNT
1	2024-08-01	1000	1500
2	2024-08-02	1500	1200
3	2024-08-03	1200	0

 

---

정렬시 NULL값은 가장 큰값으로 인식

ASC(오름차순) : 마지막에 위치

DSC(내림차순) : 처음에 위치