SQLD_중요 개념 총정리(feat.노랭이(개정 후))

SQLD_중요 개념 총정리

SQLD

SQL 자격검정 실전문제(aka.노랭이)를 풀면서 중요하다고 생각하거나, 3회독에 틀린 문제를 오답노트 겸 다시한번 개념 정리를 하기위해 작성합니다. 실제로 SQLD 기출에서 이 유형의 문제들이 중요하다는 느낌을 받았습니다.

#[개념] - (문제번호) <= 이와 같은 형식으로 서술하였습니다.(개정 후 노랭이 기준)

제 1 과목

#스키마 - (5번, 6번)

• 외부 스키마
  - 여러 사용자 관점으로 구성하는 것
  - 사용자나 프로그래머가 개인의 입장에서 필요한 데이터베이스의 논리적 구조를 정의
• 개념 스키마
  - 조직 전체를 관장하는 입장(통합관점)에서 데이터베이스를 정의한 것
• 내부 스키마
  - 데이터베이스의 물리적 저장구조를 정의

#엔터티 유형 - (12번)

1. 유형과 무형
   • 유형 엔터티 - 물리적 형태 존재, 안정적, 지속적
   • 개념 엔터티 - 물리적 형태 x
   • 사건 엔터티 - 행위에 따라 발생
2. 발생 시점에 따라[기중행]
   • 기본 엔터티 - 독립적으로 생성, 고유한 주식별자 가짐
   • 중심 엔터티 - 기본 엔터티에서 파생, 행위 엔터티 생성
   • 행위 엔터티 - 2개 이상의 엔터티로부터 발생

 

#정규화 - (16번, 38번, 43번)

• 제 1 정규화 - 모든 속성은 반드시 하나의 값을 가짐(원자성)
• 제 2 정규화 - 완전 함수 종속, 기본키의 부분 집합이 결정자가 되면 안된다.
• 제 3 정규화 - 이행 종속을 없애도록 테이블 분해 (A->B & B->C => A->C)
• BCNF 정규화 - 모든 결정자가 후보키가 되도록 분해
• 제 4 정규화 - 다중값 종속성 제거
• 제 5 정규화 - 조인에 의한 종속성 발생시 분해
• 반정규화(역정규화) - 성능을 위해 중복을 허용하고, 조인을 줄임

 

#연관관계, 의존관계 - (20번)

• 연관관계 - 소스코드에서 멤버변수로 선언하여 사용 가능
• 의존관계 - 오퍼레이션에서 파라미터 등으로 이용 가능

 

#NULL - (46번, 49번)

• 아직 정해지지 않은 값
• 0이나 공백 과는 다른 개념
• NULL 포함 연산은 항상 NULL
• 집계함수는 NULL을 제외하고 연산
• 비교시 항상 unknown
• NULL = NULL 일 경우, FALSE 또는 unknown

 

제 2 과목

#SQL 명령어 - (1번, 2번, 3번, 5번, 6번)

DDL(Data Definition Language)	CREATE, DROP, ALTER, RENAME	데이터 구조를 정의(구조 생성, 변경, 삭제)
DML(Data Manipulation Language)	SELECT, INSERT, UPDATE, DELETE	데이터의 변형
DCL(Data Control Language)	GRANT, REVOKE	권한
TCL(Transaction Control Language)	COMMIT, ROLLBACK	트랜잭션 제어

 

#SELECT절 - (4번, 7번, 8번, 10번, ...)

• 순서
  • FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
• WHERE 절에는 집계함수 사용 불가
• SELECT, FROM 은 필수

 

#CASE 구문 - (17번, 18번)

-- 첫 번째 표현식
SELECT LOC
    CASE WHEN LOC = 'NEW YORK' THEN 'EAST' ELSE 'ETC' END as AREA
FROM DEPT;

-- 두 번째 표현식
SELECT LOC
    CASE LOC WHEN 'NEW YORK' THEN 'EAST' ELSE 'ETC' END as AREA
FROM DEPT;

LOC이 NEW YORK 이면 EAST, 아니면 ETC

#NULL표현식 - (20번, 31번)

• NULLIF(A, B) - A와 B가 같으면 NULL을 반환, 아니면 A를 반환
• IFNULL(A, B) - MYSQL/ A가 NULL이면 B 반환
• NVL(A, B) - Oracle/ A가 NULL이면 B 반환
• ISNULL(A, B) - SQL server/ A가 NULL이면 B 반환

 

#USING, ON - (42번)

-- USING 예시
SELECT * FROM Employees
JOIN Departments USING (DepartmentID);

-- ON 예시
SELECT * FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

USING은 같은 이름의 컬럼을 가져야함.
ON은 연결 조건을 명시해야함

#JOIN - (43번, 46번, 47번, 48번)

INNER JOIN

• 두 테이블의 공통된 값으로 행 결합

SELECT * FROM Table1
INNER JOIN Table2 ON Table1.common_column = Table2.common_column;

 

OUTER JOIN

• INNER JOIN에서 일치하지 않는 행도 결과에 포함
• LEFT OUTER JOIN : 왼쪽 전체 행과 오른쪽의 일치하는 행을 포함
• FULL OUTER JOIN : 양쪽 테이블의 모든 행을 포함
• RIGHT OUTER JOIN : 오른쪽 전체 행과 왼쪽의 일치하는 행을 포함

-- LEFT OUTER JOIN 예시
SELECT * FROM Table1
LEFT OUTER JOIN Table2 ON Table1.common_column = Table2.common_column;

 

NATURAL JOIN

• 두 테이블의 공통 컬럼을 찾아서 그 컬럼 기준으로 행 결합. 결과는 두 테이블에서 컬럼이름이 같은 컬럼만 포함.

SELECT * FROM Table1
NATURAL JOIN Table2;

 

CROSS JOIN

• 양쪽 테이블의 가능한 모든 조합을 포함.(n*m개의 행이 만들어짐, 카타시안 곱)

SELECT * FROM Table1
CROSS JOIN Table2;

 

SELF JOIN

• 자신과 결합. 테이블 내 부모-자식 관계를 표현하거나, 테이블 내에서 데이터를 비교할 때 사용.

SELECT * FROM Table1 t1
INNER JOIN Table1 t2 ON t1.parent_id = t2.id;

 

#소계함수 - (52번, ...)

GROUPING SETS 

• 그룹별 소계를 나타내는 함수
• GROUPING SETS((A,B), A, B, ()) 는 (A, B), A, B, 전체 에 대한 소계를 나타냄

SELECT Region, Product, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY GROUPING SETS ((Region, Product), (Region), (Product), ());

결과

Region	Product	TotalSales
North	Widget	1000
North	Gizmo	1500
South	Widget	2000
South	Gizmo	2500
North	NULL	2500
South	NULL	4500
NULL	Widget	3000
NULL	Gizmo	4000
NULL	NULL	7000

 

ROLLUP

• ROLLUP(A, B) 는 (A, B), A, 전체 에 대한 소계를 나타냄

SELECT Region, Product, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY ROLLUP (Region, Product);

결과

Region	Product	TotalSales
North	Widget	1000
North	Gizmo	1500
North	NULL	2500
South	Widget	2000
South	Gizmo	2500
South	NULL	4500
NULL	NULL	7000

 

CUBE

• CUBE(A, B) 는 (A, B), A, B, 전체 에 대한 소계를 나타냄
• 모든 조합의 결과를 나타냄

SELECT Region, Product, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY CUBE (Region, Product);

결과

Region	Product	TotalSales
North	Widget	1000
North	Gizmo	1500
North	NULL	2500
South	Widget	2000
South	Gizmo	2500
South	NULL	4500
NULL	Widget	3000
NULL	Gizmo	4000
NULL	NULL	7000

 

#계층형 질의문 - (63번)

• START WITH : 계층 구조의 루트(시작점)를 정의
• CONNECT BY PRIOR : 계층 구조의 부모-자식 관계 정의

SELECT employee_name, LEVEL
FROM employees
START WITH manager_id IS NULL --> manager_id가 NULL인 사람부터 시작, 루트 노드
CONNECT BY PRIOR employee_id = manager_id; --> 부모 : employee_id, 자식 : manager_id

• ORDER SIBLINGS BY : 같은 계층에 있는 형제 노드끼리의 정렬

SELECT employee_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

 

#윈도우 함수 - (66번, ...)

ROW_NUMBER()

• 고유의 일련번호를 할당할 때 사용하는 윈도우 함수

SELECT
    sale_id,
    salesperson,
    region,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num
FROM
    sales;

결과

sale_id	salesperson	region	amount	row_num
5	Bob	East	300	1
4	Alice	East	250	2
2	Bob	East	150	3
1	Alice	East	100	4
6	Charlie	West	350	1
3	Charlie	West	200	2

 

• 지역별 그룹화, amount값 기준 내림차순

 

LAG()

• 이전행의 값을 가져옴
• LAG(column, offset, default value) - column의 이전 offset번째 값을 가져옴. 만약 없을경우 default_value를 반환

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary
FROM
    employees;

결과

employee_id	employee_name	department	salary	prev_salary
1	Alice	HR	5000	0
3	Charlie	HR	5500	5000
2	Bob	IT	6000	5500
5	Eve	IT	6500	6000
4	David	IT	7000	6500

• ORDER BY salary, salary 기준으로 정렬

LEAD()

• LAG()와 똑같지만 차이점은 이후 행을 가져온다는 것

 

RANK()

• 순위를 매기는 함수, 동일 값이 있으면 다음 순위로 넘기지 않고 동일 순위를 부여함

DENSE_RANK()

• 순위를 매기는 함수, 동일 값이 있으면 다음 순위로 넘기면서 순차적 순위 부여

NTITLE(n)

• 결과를 n개의 동일한 크기의 구간으로 분할하고, 각 행에 해당하는 구간 번호 부여

CUME_DIST()

• 누적 분포를 나타냄

SELECT
    ID,
    Value,
    CUME_DIST() OVER (ORDER BY Value) AS cumulative_distribution
FROM
    your_table;


-------<결과>-----
| ID | Value | cumulative_distribution |
|----|-------|-------------------------|
| 1  | 10    | 0.20                    |
| 2  | 20    | 0.40                    |
| 3  | 30    | 0.60                    |
| 4  | 40    | 0.80                    |
| 5  | 50    | 1.00                    |

 

PERCENT_RANK()

• 각 값의 백분위 순위를 나타냄

SELECT
    ID,
    Value,
    PERCENT_RANK() OVER (ORDER BY Value) AS percent_rank
FROM
    your_table;



-------<결과>-------
| ID | Value | percent_rank |
|----|-------|--------------|
| 1  | 10    | 0.00         |
| 2  | 20    | 0.25         |
| 3  | 30    | 0.50         |
| 4  | 40    | 0.75         |
| 5  | 50    | 1.00         |

 

#서브쿼리 설명 - (68번)

• 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용할 수 있다.
• 서브쿼리는 SELECT절, FROM절, HAVING절, ORDER BY절 등에서 사용이 가능하다.
• 연관 서브쿼리는 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브쿼리이다.
• 서브쿼리의 결과가 복수 행 결과를 반환하는 경우에는 IN, ALL, ANY 등의 복수행 비교 연산자와 사용하여야 한다.
• 다중 컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 비교되는데, SQL server 지원 x

 

#TOP (n) WITH TIES - (125번)

• TOP (n)은 상위 n개의 레코드를 반환, WITH TIES는 동일값이 있다면 모두 반환

SELECT TOP (4) WITH TIES EmployeeID, Sales
FROM SalesTable
ORDER BY Sales DESC;