728x90
반응형
SQLD_중요 개념 총정리
SQL 자격검정 실전문제(aka.노랭이)를 풀면서 중요하다고 생각하거나, 3회독에 틀린 문제를 오답노트 겸 다시한번 개념 정리를 하기위해 작성합니다. 실제로 SQLD 기출에서 이 유형의 문제들이 중요하다는 느낌을 받았습니다.
#[개념] - (문제번호) <= 이와 같은 형식으로 서술하였습니다.(개정 후 노랭이 기준)
제 1 과목
#스키마 - (5번, 6번)
- 외부 스키마
- 여러 사용자 관점으로 구성하는 것
- 사용자나 프로그래머가 개인의 입장에서 필요한 데이터베이스의 논리적 구조를 정의 - 개념 스키마
- 조직 전체를 관장하는 입장(통합관점)에서 데이터베이스를 정의한 것 - 내부 스키마
- 데이터베이스의 물리적 저장구조를 정의
#엔터티 유형 - (12번)
- 유형과 무형
- 유형 엔터티 - 물리적 형태 존재, 안정적, 지속적
- 개념 엔터티 - 물리적 형태 x
- 사건 엔터티 - 행위에 따라 발생
- 발생 시점에 따라[기중행]
- 기본 엔터티 - 독립적으로 생성, 고유한 주식별자 가짐
- 중심 엔터티 - 기본 엔터티에서 파생, 행위 엔터티 생성
- 행위 엔터티 - 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;
728x90
반응형
'SQL > 추가 공부' 카테고리의 다른 글
소계 함수(ROLLUP, CUBE)_(feat. 노랭이 p.94 82번)[개정 후] (6) | 2024.05.18 |
---|