1과목
제1장. 데이터 모델링의 이해
1-1. 데이터 모델링 개요
데이터 모델링이란?
- 현실 세계의 데이터를 약속된 표기법으로 표현하는 과정
- 특징 3가지: 추상화(Abstraction), 단순화(Simplification), 명확화(Clarity)
데이터 모델링의 3가지 관점
- 데이터 관점: 무엇(What)을 저장할 것인가
- 프로세스 관점: 업무의 흐름(How)
- 상관 관점: 데이터와 프로세스의 관계
데이터 모델링의 유의사항
- 유연성: 변화에 대응 가능해야 함
- 유일성: 데이터가 유일하게 식별되어야 함
- 일관성: 데이터 간 상호 일관성 보장
데이터 모델링의 3단계
| 단계 | 설명 | 특징 |
|---|---|---|
| 개념적 모델링 | 업무 중심의 포괄적 수준 모델링 | 추상화 수준 가장 높음, ERD 생성, 엔터티·관계 위주 |
| 논리적 모델링 | 특정 데이터베이스 모델에 맞게 구체화 | 정규화 수행, Key·속성·관계 등 상세 정의, DBMS 종속적 |
| 물리적 모델링 | 실제 DB에 이식할 수 있도록 성능·저장 고려 | 테이블·인덱스·파티션 등 물리적 구조 설계 |
데이터 모델링의 3가지 요소
- Things (엔터티) → 업무가 관여하는 어떤 것
- Attributes (속성) → 어떤 것이 가지는 성격
- Relationships (관계) → 어떤 것 간의 관계
데이터 독립성
| 독립성 | 설명 |
|---|---|
| 논리적 독립성 | 개념 스키마 변경 → 외부 스키마 영향 없음 |
| 물리적 독립성 | 내부 스키마 변경 → 개념 스키마 영향 없음 |
3단계 스키마 구조 (ANSI/SPARC)
- 외부 스키마: 사용자 관점 (View) — 여러 개 존재 가능
- 개념 스키마: 통합 관점 (논리적 구조) — 1개
- 내부 스키마: 물리적 저장 구조 — 1개
사상(Mapping)
- 외부/개념 사상: 논리적 독립성 보장
- 개념/내부 사상: 물리적 독립성 보장
1-2. 엔터티(Entity)
엔터티의 특징 (6가지 — 모두 충족해야 엔터티)
- 업무에서 필요로 하고 관리하고자 하는 정보
- 유일한 식별자에 의해 식별 가능
- 2개 이상의 인스턴스의 집합
- 업무 프로세스에 의해 이용
- 반드시 속성이 있어야 함
- 다른 엔터티와 최소 1개 이상의 관계가 있어야 함
엔터티 분류
유형/무형에 따른 분류
- 유형 엔터티: 물리적 형태 (예: 사원, 물품, 강사)
- 개념 엔터티: 개념적으로만 존재 (예: 조직, 보험상품)
- 사건 엔터티: 업무 수행 시 발생 (예: 주문, 청구, 미납)
발생 시점에 따른 분류
- 기본(키) 엔터티: 독립적으로 존재, 다른 엔터티의 부모 역할, 자신의 고유 주식별자를 가짐 (예: 사원, 부서, 고객, 상품)
- 중심 엔터티: 기본 엔터티로부터 발생, 업무의 중심 (예: 계약, 사고, 주문, 매출)
- 행위 엔터티: 2개 이상의 부모 엔터티로부터 발생 (예: 주문목록, 사원변경이력)
엔터티 명명 규칙
- 협업에서 사용하는 용어, 약어 지양, 단수 명사, 유일한 이름, 명확한 이름
1-3. 속성(Attribute)
속성이란?
- 업무에서 필요로 하는 인스턴스로 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위
엔터티·인스턴스·속성·속성값의 관계
- 1개의 엔터티 → 2개 이상의 인스턴스
- 1개의 엔터티 → 2개 이상의 속성
- 1개의 속성 → 1개의 속성값 (정규화 기준)
- 속성은 주식별자에 함수적으로 종속
속성 분류
특성에 따른 분류
| 분류 | 설명 | 예시 |
|---|---|---|
| 기본 속성 | 업무로부터 추출한 본래 속성 | 이름, 나이, 주소 |
| 설계 속성 | 모델링 과정에서 새로 만든 속성 | 상품코드, 지역코드 |
| 파생 속성 | 다른 속성으로부터 계산/변환된 속성 | 합계, 평균 (가급적 적게 정의) |
구성방식에 따른 분류
- PK 속성, FK 속성, 일반 속성
형태에 따른 분류
- 단일 속성 / 복합 속성 (예: 주소 → 시+구+동)
- 단일값 속성 / 다중값 속성 (다중값 → 엔터티로 분리)
도메인(Domain)
- 각 속성이 가질 수 있는 값의 범위
- 예: 학점 속성 → {A, B, C, D, F}
1-4. 관계(Relationship)
관계의 종류
- 존재 관계: 엔터티 간 상태로 존재하는 관계 (예: 부서-사원 소속)
- 행위 관계: 엔터티 간 행위로 발생하는 관계 (예: 고객이 주문을 한다)
관계의 표기법
관계차수(Cardinality)
- 1:1 → 양쪽 모두 하나씩 대응
- 1:M → 한쪽은 하나, 다른 쪽은 여러 개
- M:N → 양쪽 모두 여러 개 (→ 반드시 교차 엔터티로 해소)
관계선택사양(Optionality)
- 필수(Mandatory): 반드시 관계가 존재 (
|표기) - 선택(Optional): 관계가 없을 수도 있음 (
O표기)
ERD 관계 vs UML 관계
- ERD: 존재/행위 관계 구분 없이 실선
- UML: 연관관계(실선, 멤버변수로 참조), 의존관계(점선, 파라미터 등 일시적)
1-5. 식별자(Identifier)
식별자의 특징 (4가지)
- 유일성: 각 인스턴스를 유일하게 식별
- 최소성: 최소한의 속성 수로 구성
- 불변성: 한번 지정되면 변하지 않아야 함
- 존재성: 반드시 데이터 값이 존재 (NULL 불가)
식별자 분류
| 분류 기준 | 종류 | 설명 |
|---|---|---|
| 대표성 | 주식별자 | 엔터티를 대표하는 유일한 식별자 |
| 보조식별자 | 유일성은 있으나 대표가 아닌 식별자 | |
| 스스로 생성 | 내부식별자 | 엔터티 내부에서 자체 생성 |
| 외부식별자 | 다른 엔터티에서 받아온 식별자 (FK) | |
| 단일/복합 | 단일식별자 | 하나의 속성으로 식별 |
| 복합식별자 | 2개 이상의 속성으로 식별 | |
| 대체 여부 | 본질식별자(원조) | 업무에 의해 자연 발생 |
| 인조식별자 | 인위적으로 만든 식별자 (예: 순번) |
식별자 관계 vs 비식별자 관계 (매우 자주 출제!)
| 구분 | 식별자 관계 | 비식별자 관계 |
|---|---|---|
| 표기법 | 실선 | 점선 |
| FK 위치 | 자식의 PK에 포함 | 자식의 일반 속성 |
| 관계 강도 | 강한 관계 | 약한 관계 |
| 자식 독립성 | 부모 없이 존재 불가 | 부모 없이도 존재 가능 |
| NULL 허용 | FK에 NULL 불가 | FK에 NULL 가능 |
| 예시 | 주문→주문상세 | 부서→사원 |
제2장. 데이터 모델과 SQL (개정 파트)
2-1. 정규화(Normalization)
정규화란?
- 데이터의 중복을 제거하고 이상 현상(Anomaly)을 방지하기 위해 릴레이션을 분리하는 과정
- 논리적 모델링 단계에서 수행
이상 현상(Anomaly)
- 삽입 이상: 불필요한 데이터를 함께 삽입해야 하는 문제
- 갱신 이상: 일부만 수정하여 데이터 불일치 발생
- 삭제 이상: 필요한 데이터까지 함께 삭제되는 문제
정규화 단계
| 단계 | 핵심 | 제거 대상 |
|---|---|---|
| 제1정규형(1NF) | 모든 속성은 원자값(Atomic Value) | 반복 그룹, 다중값 속성 제거 |
| 제2정규형(2NF) | 부분 함수 종속 제거 | 복합키의 일부에만 종속되는 속성 분리 |
| 제3정규형(3NF) | 이행 함수 종속 제거 | A→B→C에서 A→C 관계 분리 |
| BCNF | 모든 결정자가 후보키 | 후보키가 아닌 결정자 제거 |
암기 팁: 1NF(원자값) → 2NF(부분종속↓) → 3NF(이행종속↓) → BCNF(결정자=후보키)
함수 종속성
- 완전 함수 종속: 기본키 전체에 종속 (2NF 만족)
- 부분 함수 종속: 기본키의 일부에만 종속 (2NF 위반)
- 이행 함수 종속: A→B, B→C이면 A→C (3NF 위반)
2-2. 관계와 조인의 이해
관계와 조인
- 데이터 모델에서의 관계(Relationship)는 SQL에서 JOIN으로 구현
- 식별자를 상속받아 매핑 키(FK)로 데이터를 결합
식별 관계와 조인
- 부모의 PK가 자식의 PK에 포함 → 자식은 부모 없이 존재 불가
- 조인 시 자식의 PK를 통해 부모 데이터에 접근 가능
비식별 관계와 조인
- 부모의 PK가 자식의 일반 속성(FK)으로 상속
- 조인이 빈번하게 발생할 수 있음
- FK에 NULL이 허용될 수 있어 OUTER JOIN이 필요한 경우 있음
2-3. 트랜잭션의 이해
트랜잭션의 특성 (ACID)
| 특성 | 설명 |
|---|---|
| Atomicity (원자성) | 전부 성공 또는 전부 실패 (All or Nothing) |
| Consistency (일관성) | 트랜잭션 전후로 데이터 일관성 유지 |
| Isolation (격리성/고립성) | 다른 트랜잭션에 영향을 주지 않음 |
| Durability (영속성/지속성) | 완료된 트랜잭션은 영구 반영 |
IE 표기법과 바커(Barker) 표기법
- IE 표기법: 필수 관계(
|), 선택 관계(O), 실선으로 표현 - 바커 표기법: 필수 관계(실선), 선택 관계(점선)
2-4. NULL 속성의 이해 (매우 자주 출제!)
NULL이란?
- 아직 정의되지 않은 미지의 값 (0이나 공백이 아님!)
- NOT NULL / PK 제약조건이 아닌 한 NULL 허용
NULL 연산 규칙
NULL + 숫자 = NULL(산술 연산 결과는 항상 NULL)NULL = NULL → UNKNOWN (FALSE 처리)NULL <> NULL → UNKNOWN (FALSE 처리)- NULL과의 모든 비교 → UNKNOWN → WHERE절에서 FALSE로 처리
NULL 논리 연산
NULL AND TRUE = UNKNOWNNULL AND FALSE = FALSE(FALSE가 우선)NULL OR TRUE = TRUE(TRUE가 우선)NULL OR FALSE = UNKNOWNNOT NULL = UNKNOWN
NULL과 집계함수
- COUNT(*): NULL 포함 (전체 행 수)
- COUNT(칼럼): NULL 제외
- SUM, AVG, MAX, MIN: NULL 제외하고 계산
AVG 함정: (10 + 20 + NULL) → AVG = (10+20)/2 = 15 (NULL 행은 분모에서도 제외!)
NULL 처리 함수
| 함수 | 설명 | DBMS |
|---|---|---|
| NVL(a, b) | a가 NULL이면 b 반환 | Oracle |
| ISNULL(a, b) | a가 NULL이면 b 반환 | SQL Server |
| NVL2(a, b, c) | a가 NULL이 아니면 b, NULL이면 c | Oracle |
| NULLIF(a, b) | a=b이면 NULL, 아니면 a 반환 | 공통 |
| COALESCE(a, b, c, ...) | NULL이 아닌 첫 번째 값 반환 | 공통 |
2-5. 본질식별자 vs 인조식별자
본질식별자
- 업무에 의해 자연 발생한 식별자
- 예: 주민등록번호, 사번 등
인조식별자
- 본질식별자가 복잡하거나 여러 개일 때 인위적으로 만든 식별자
- 예: 순번(SEQ), 자동 증가 ID
- 단점: 중복 데이터 발생 가능, 불필요한 인덱스 추가 필요
- 원칙: 꼭 필요한 경우에만 사용
2과목
제1장. SQL 기본
3-1. 관계형 데이터베이스 개요
SQL 문장 분류
| 분류 | 명령어 | 특징 |
|---|---|---|
| DDL (정의어) | CREATE, ALTER, DROP, RENAME, TRUNCATE | AUTO COMMIT (롤백 불가) |
| DML (조작어) | SELECT, INSERT, UPDATE, DELETE | 롤백 가능, COMMIT 필요 |
| DCL (제어어) | GRANT, REVOKE | 권한 관련 |
| TCL (트랜잭션 제어) | COMMIT, ROLLBACK, SAVEPOINT | 트랜잭션 관리 |
관계형 데이터베이스 연산
집합 연산
- 합집합(UNION), 교집합(INTERSECTION), 차집합(DIFFERENCE), 곱집합(PRODUCT = CROSS JOIN)
관계 연산
- SELECT(행 선택), PROJECT(열 선택), JOIN(결합), DIVIDE(나눗셈)
3-2. DDL (Data Definition Language)
제약조건(Constraints)
| 제약조건 | 설명 |
|---|---|
| PRIMARY KEY | 유일성 + NOT NULL (테이블당 1개) |
| UNIQUE | 유일성 보장 (NULL 허용 — 1개) |
| NOT NULL | NULL 값 입력 금지 |
| CHECK | 지정 조건에 맞는 데이터만 허용 |
| FOREIGN KEY | 참조 무결성 보장 (NULL 가능) |
| DEFAULT | 기본값 설정 |
FK의 참조 동작 (DELETE/UPDATE 시)
| 옵션 | 설명 |
|---|---|
| CASCADE | 부모 삭제 시 자식도 함께 삭제 |
| SET NULL | 부모 삭제 시 자식의 FK를 NULL로 |
| SET DEFAULT | 부모 삭제 시 자식의 FK를 기본값으로 |
| RESTRICT | 자식이 참조 중이면 부모 삭제 불가 |
| NO ACTION | RESTRICT와 유사 (참조 무결성 위반 시 에러) |
TRUNCATE vs DELETE vs DROP
| 구분 | DROP | TRUNCATE | DELETE |
|---|---|---|---|
| 분류 | DDL | DDL | DML |
| ROLLBACK | 불가 | 불가 | 가능 |
| 저장 공간 | 반환 | 반환 | 미반환 |
| WHERE 조건 | 불가 | 불가 | 가능 |
| 테이블 정의 | 삭제 | 유지 | 유지 |
| 로그 | 기록 안함 | 기록 안함 | 기록 |
ALTER TABLE 주요 문법
-- 칼럼 추가
ALTER TABLE 테이블명 ADD 칼럼명 데이터타입;
-- 칼럼 수정 (Oracle)
ALTER TABLE 테이블명 MODIFY (칼럼명 데이터타입);
-- 칼럼 수정 (SQL Server)
ALTER TABLE 테이블명 ALTER COLUMN 칼럼명 데이터타입;
-- 칼럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 칼럼명;
-- 칼럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN 기존명 TO 새이름;
-- 제약조건 추가/삭제
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY (칼럼);
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
DB 키의 종류
| 키 | 설명 |
|---|---|
| 기본키(PK) | NULL 불가, 유일성, 테이블 대표 |
| 후보키 | 유일성 + 최소성 (PK가 될 수 있는 키) |
| 슈퍼키 | 유일성만 만족 (최소성 불필요) |
| 대체키 | 후보키 중 PK가 아닌 키 |
| 외래키(FK) | 참조 무결성, NULL 가능 |
| 고유키(UNIQUE) | 유일성, NULL 허용 |
3-3. DML (Data Manipulation Language)
-- INSERT
INSERT INTO 테이블명 (칼럼1, 칼럼2) VALUES (값1, 값2);
INSERT INTO 테이블명 VALUES (값1, 값2, 값3); -- 전체 칼럼 순서대로
-- UPDATE
UPDATE 테이블명 SET 칼럼1 = 값1, 칼럼2 = 값2 WHERE 조건;
-- DELETE
DELETE FROM 테이블명 WHERE 조건;
-- SELECT
SELECT ALL/DISTINCT 칼럼명 FROM 테이블명;
SELECT 실행 순서 (매우 자주 출제!)
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
암기: "프 웨 그 해 셀 오" (From Where Group Having Select Order)
SELECT절의 Alias는 ORDER BY에서만 사용 가능! (WHERE, GROUP BY, HAVING에서 사용 불가)
ALIAS
- 칼럼명 뒤에
AS또는 공백으로 지정 - 공백·특수문자·대소문자 구분 시 큰따옴표("") 사용 (Oracle)
합성 연산자(문자열 결합)
- Oracle:
|| - SQL Server:
+ - 공통:
CONCAT(a, b)함수
3-4. WHERE 절
연산자 우선순위
- 괄호
() - NOT
- 비교 연산자 (
=,>,<,>=,<=) - AND
- OR
AND가 OR보다 우선순위 높음! (매우 자주 출제)
NULL 비교
-- NULL 비교는 반드시 IS NULL / IS NOT NULL 사용
WHERE 칼럼 IS NULL;
WHERE 칼럼 IS NOT NULL;
-- = NULL, <> NULL 은 항상 UNKNOWN (결과 없음)
BETWEEN, IN, LIKE
WHERE 급여 BETWEEN 1000 AND 3000; -- 1000 이상 3000 이하 (양 끝 포함!)
WHERE 부서 IN ('영업', '인사', '개발');
WHERE 이름 LIKE '김%'; -- '김'으로 시작
WHERE 이름 LIKE '%수'; -- '수'로 끝남
WHERE 이름 LIKE '_민%'; -- 두 번째 글자가 '민'
WHERE 이름 LIKE '___'; -- 정확히 3글자
와일드카드:
%(0개 이상 문자),_(정확히 1개 문자)
NOT IN과 NULL 함정
WHERE 부서 NOT IN (10, 20, NULL)
-- → 부서 <> 10 AND 부서 <> 20 AND 부서 <> NULL
-- → 마지막 조건이 항상 UNKNOWN → 전체 결과 없음!
ROWNUM (Oracle) / TOP (SQL Server)
-- Oracle
SELECT * FROM 사원 WHERE ROWNUM <= 5;
-- SQL Server
SELECT TOP 5 * FROM 사원;
3-5. 함수 (Function)
문자열 함수
| 함수 | 설명 | 예시 |
|---|---|---|
| UPPER / LOWER | 대소문자 변환 | |
| LENGTH / LEN | 문자열 길이 | Oracle: LENGTH, SQL Server: LEN |
| SUBSTR / SUBSTRING | 부분 문자열 | SUBSTR('SQLD', 2, 2) → 'QL' |
| LTRIM / RTRIM / TRIM | 특정 문자/공백 제거 | |
| LPAD / RPAD | 특정 문자로 채우기 | LPAD('123', 5, '0') → '00123' |
| REPLACE | 문자열 치환 | REPLACE('ABBA', 'B', 'X') → 'AXXA' |
| CONCAT | 문자열 결합 |
숫자 함수
| 함수 | 설명 | 주의 |
|---|---|---|
| ROUND(n, m) | 반올림 | ROUND(123.456, 2) → 123.46 |
| TRUNC(n, m) | 버림(절삭) | TRUNC(123.456, 2) → 123.45 |
| CEIL / CEILING | 올림 (크거나 같은 최소 정수) | CEIL(-1.5) = -1 |
| FLOOR | 내림 (작거나 같은 최대 정수) | FLOOR(-1.5) = -2 |
| MOD(m, n) | 나머지 | MOD(7, 3) → 1 |
| ABS | 절대값 | |
| SIGN | 부호 (양수=1, 0=0, 음수=-1) |
날짜 함수
-- Oracle
SYSDATE -- 현재 날짜+시간
SYSDATE + 1 -- 1일 후 (날짜 ± 숫자 = 일 단위)
ADD_MONTHS(SYSDATE, 3) -- 3개월 후
MONTHS_BETWEEN(날짜1, 날짜2) -- 개월 수 차이
EXTRACT(YEAR FROM SYSDATE) -- 연도 추출
-- SQL Server
GETDATE() -- 현재 날짜+시간
DATEADD(DAY, 1, GETDATE()) -- 1일 후
DATEDIFF(DAY, 날짜1, 날짜2) -- 일수 차이
변환 함수
-- Oracle
TO_CHAR(날짜/숫자, 'FORMAT') -- → 문자
TO_NUMBER('123') -- → 숫자
TO_DATE('2024-01-01', 'YYYY-MM-DD') -- → 날짜
-- SQL Server
CAST(값 AS 데이터타입)
CONVERT(데이터타입, 값, 스타일)
명시적 변환 권장! (암시적 변환은 성능 저하 우려)
CASE 표현식
-- Simple CASE
CASE 칼럼
WHEN 값1 THEN 결과1
WHEN 값2 THEN 결과2
ELSE 기본결과
END
-- Searched CASE (조건식)
CASE
WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
ELSE 기본결과
END
ELSE 생략 시 조건 불만족이면 NULL 반환
DECODE (Oracle 전용)
DECODE(칼럼, 값1, 결과1, 값2, 결과2, 기본결과)
-- CASE의 Simple 형태와 동일 기능
3-6. GROUP BY와 집계 함수
집계 함수
| 함수 | NULL 처리 |
|---|---|
| COUNT(*) | NULL 포함 |
| COUNT(칼럼) | NULL 제외 |
| SUM / AVG / MAX / MIN | NULL 제외 |
전체 평균을 원하면:
AVG(NVL(칼럼, 0))또는SUM(칼럼)/COUNT(*)
GROUP BY 규칙
- SELECT에 집계함수가 아닌 칼럼 → 반드시 GROUP BY에 포함
- GROUP BY절에는 Alias 사용 불가
- WHERE절에서는 집계함수 사용 불가 → HAVING 사용
HAVING vs WHERE
- WHERE: 개별 행에 대한 조건 (GROUP BY 이전)
- HAVING: 그룹에 대한 조건 (GROUP BY 이후)
SELECT 부서, AVG(급여) AS 평균급여
FROM 사원
WHERE 입사일 >= '2020-01-01' -- 개별 행 필터링 (먼저)
GROUP BY 부서
HAVING AVG(급여) >= 3000; -- 그룹 필터링 (나중에)
3-7. ORDER BY
ORDER BY 칼럼 ASC; -- 오름차순 (기본값)
ORDER BY 칼럼 DESC; -- 내림차순
ORDER BY 1, 2 DESC; -- 칼럼 순번으로도 가능
NULL 정렬 순서
- Oracle: NULL이 가장 큰 값 (ASC → 마지막, DESC → 처음)
- SQL Server: NULL이 가장 작은 값 (ASC → 처음, DESC → 마지막)
3-8. JOIN (가장 중요!)
INNER JOIN
-- ANSI 표준
SELECT A.이름, B.부서명
FROM 사원 A INNER JOIN 부서 B ON A.부서코드 = B.부서코드;
-- Oracle 전통
SELECT A.이름, B.부서명
FROM 사원 A, 부서 B WHERE A.부서코드 = B.부서코드;
NATURAL JOIN
- 같은 이름의 모든 칼럼 자동 조인
- 별칭(테이블명.칼럼) 사용 불가
- USING, ON절 사용 불가
USING 절
- 같은 이름의 특정 칼럼만 지정
- 별칭(테이블명.칼럼) 사용 불가
SELECT 부서코드, 이름 FROM 사원 JOIN 부서 USING (부서코드);
OUTER JOIN
-- LEFT OUTER JOIN: 왼쪽 테이블 전체 보존
SELECT A.이름, B.부서명
FROM 사원 A LEFT OUTER JOIN 부서 B ON A.부서코드 = B.부서코드;
-- RIGHT OUTER JOIN: 오른쪽 테이블 전체 보존
-- FULL OUTER JOIN: 양쪽 모두 보존
Oracle (+) 표기법
-- (+)는 데이터가 없는(채워질) 쪽에 붙임
A.col = B.col(+) -- A 기준 LEFT JOIN (B가 없으면 NULL로 채움)
A.col(+) = B.col -- B 기준 RIGHT JOIN
-- FULL OUTER JOIN은 (+)로 불가!
OUTER JOIN에서 ON vs WHERE 함정
-- ON절 조건: 조인 전에 적용 (OUTER 효과 유지)
-- WHERE절 조건: 조인 후에 적용 (OUTER 효과 사라질 수 있음!)
-- 예시: OUTER JOIN인데 WHERE로 필터링하면 INNER JOIN과 같은 결과
SELECT * FROM A LEFT JOIN B ON A.id = B.id
WHERE B.name = '홍길동'; -- B.name이 NULL인 행 제거 → LEFT JOIN 무효화!
CROSS JOIN (카테시안 곱)
SELECT * FROM 사원 CROSS JOIN 부서;
-- 결과 행수 = 사원 행수 × 부서 행수
SELF JOIN
SELECT A.사원명, B.사원명 AS 매니저명
FROM 사원 A JOIN 사원 B ON A.매니저ID = B.사원ID;
제2장. SQL 활용
4-1. 서브쿼리 (Subquery)
서브쿼리 위치별 분류
| 위치 | 이름 | 특징 |
|---|---|---|
| SELECT절 | 스칼라 서브쿼리 | 단일 행, 단일 칼럼만 반환 (없으면 NULL) |
| FROM절 | 인라인 뷰 | 동적 임시 테이블 |
| WHERE절 | (일반) 서브쿼리 | 조건에 사용 |
| HAVING절 | 서브쿼리 | 그룹 조건에 사용 |
서브쿼리 유형
| 유형 | 반환 | 연산자 |
|---|---|---|
| 단일행 | 1건의 결과 | =, <, >, <=, >=, <> |
| 다중행 | 여러 건의 결과 | IN, ALL, ANY(=SOME), EXISTS |
| 다중칼럼 | 여러 칼럼 반환 |
다중행 연산자
| 연산자 | 설명 |
|---|---|
| IN | 서브쿼리 결과 중 하나라도 일치 |
| ALL | 서브쿼리 결과 모두와 비교 (> ALL = 최대값보다 큰) |
| ANY / SOME | 서브쿼리 결과 하나라도 만족 (> ANY = 최소값보다 큰) |
| EXISTS | 결과가 1건이라도 존재하면 TRUE |
연관 서브쿼리 vs 비연관 서브쿼리
- 비연관: 서브쿼리가 독립적으로 실행 (한 번만 실행)
- 연관: 메인쿼리의 값을 참조 (행마다 실행)
-- 연관 서브쿼리: 부서별 평균 급여보다 높은 사원
SELECT 사원명, 급여
FROM 사원 A
WHERE 급여 > (SELECT AVG(급여) FROM 사원 B WHERE B.부서코드 = A.부서코드);
4-2. 집합 연산자 (Set Operator)
| 연산자 | 설명 | 중복 | 정렬 |
|---|---|---|---|
| UNION | 합집합 | 중복 제거 | O |
| UNION ALL | 합집합 | 중복 포함 (빠름) | X |
| INTERSECT | 교집합 | 중복 제거 | O |
| MINUS / EXCEPT | 차집합 | Oracle: MINUS, SQL Server: EXCEPT | O |
규칙: 칼럼 수 같아야 함, 데이터 타입 호환, ORDER BY는 마지막 쿼리에만 사용
4-3. 그룹 함수 (고급)
ROLLUP
GROUP BY ROLLUP(A, B)
-- 결과 그룹: (A,B), (A), () ← 계층적 소계 + 총합
-- 인자 순서에 따라 결과 다름! (n개 인자 → n+1 레벨)
CUBE
GROUP BY CUBE(A, B)
-- 결과 그룹: (A,B), (A), (B), () ← 모든 가능한 조합
-- n개 인자 → 2^n 레벨
GROUPING SETS
GROUP BY GROUPING SETS(A, B)
-- 결과 그룹: (A), (B) ← 지정한 것만
-- 총합 포함하려면: GROUPING SETS(A, B, ())
GROUPING 함수
- 해당 칼럼이 소계/합계로 집계된 행이면 1, 아니면 0
SELECT 부서, 직급, SUM(급여),
GROUPING(부서) AS G1, -- 소계행이면 1
GROUPING(직급) AS G2
FROM 사원
GROUP BY ROLLUP(부서, 직급);
4-4. 윈도우 함수 (Window Function)
기본 구문
함수명(인자) OVER (
[PARTITION BY 칼럼] -- 그룹 분할
[ORDER BY 칼럼] -- 정렬
[ROWS/RANGE BETWEEN ...] -- 윈도우 범위
)
윈도우 함수는 결과 행 수에 영향을 주지 않음 (GROUP BY와 차이!)
순위 함수 (매우 자주 출제!)
| 함수 | 설명 | 예시 (점수: 100, 100, 90) |
|---|---|---|
| RANK() | 동일 순위 허용, 건너뜀 | 1, 1, 3 |
| DENSE_RANK() | 동일 순위 허용, 건너뛰지 않음 | 1, 1, 2 |
| ROW_NUMBER() | 동일값이어도 고유 순번 부여 | 1, 2, 3 |
-- PARTITION BY 적용: 부서별 순위
RANK() OVER (PARTITION BY 부서코드 ORDER BY 급여 DESC)
집계 윈도우 함수
SUM(급여) OVER (ORDER BY 사원번호) -- 누적합
AVG(급여) OVER () -- 전체 평균 (파티션 없이)
급여 / SUM(급여) OVER () AS 비율 -- 전체 대비 비율
행 순서 함수
| 함수 | 설명 |
|---|---|
| LAG(칼럼, n, 기본값) | 현재 행보다 n행 앞(이전) 값 |
| LEAD(칼럼, n, 기본값) | 현재 행보다 n행 뒤(다음) 값 |
| FIRST_VALUE(칼럼) | 파티션 내 첫 번째 값 |
| LAST_VALUE(칼럼) | 파티션 내 마지막 값 |
LAST_VALUE 함정 : 기본 RANGE = UNBOUNDED PRECEDING ~ CURRENT ROW
진짜 마지막 값 원하면ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING명시!
WINDOWING 절
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 처음~현재 (기본값)
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- 현재~끝
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- 이전1행~다음1행
- ROWS: 물리적 행 수 기준
- RANGE: 논리적 값 범위 기준
비율 함수
| 함수 | 설명 |
|---|---|
| PERCENT_RANK() | (순위-1) / (전체 행수-1) → 0~1 |
| CUME_DIST() | 누적 분포 → 0 초과 1 이하 |
| NTILE(n) | n개 그룹으로 균등 분할 |
| RATIO_TO_REPORT(칼럼) | 파티션 내 합계 대비 비율 (Oracle) |
4-5. Top N 쿼리
-- Oracle: ROWNUM (WHERE절)
SELECT * FROM (SELECT * FROM 사원 ORDER BY 급여 DESC)
WHERE ROWNUM <= 5;
-- SQL Server: TOP
SELECT TOP 5 * FROM 사원 ORDER BY 급여 DESC;
-- ANSI 표준: FETCH (Oracle 12c+, SQL Server 2012+)
SELECT * FROM 사원 ORDER BY 급여 DESC
FETCH FIRST 5 ROWS ONLY;
-- OFFSET ~ FETCH
SELECT * FROM 사원 ORDER BY 급여 DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
4-6. 계층형 질의
Oracle: CONNECT BY
SELECT LEVEL, 사원ID, 매니저ID,
LPAD(' ', (LEVEL-1)*2) || 사원명 AS 사원명
FROM 사원
START WITH 매니저ID IS NULL -- 루트 노드 조건
CONNECT BY PRIOR 사원ID = 매니저ID -- 부모→자식 (순방향)
ORDER SIBLINGS BY 사원명; -- 같은 레벨에서 정렬
핵심 키워드
LEVEL: 현재 깊이 (루트=1)CONNECT_BY_ROOT 칼럼: 루트 노드의 칼럼 값CONNECT_BY_ISLEAF: 말단 노드이면 1, 아니면 0SYS_CONNECT_BY_PATH(칼럼, 구분자): 루트~현재 경로PRIOR: 이미 조회된(부모) 쪽에 붙임
방향 판단
CONNECT BY PRIOR 자식칼럼 = 부모칼럼→ 순방향 (Top-Down)CONNECT BY PRIOR 부모칼럼 = 자식칼럼→ 역방향 (Bottom-Up)
ANSI 표준: 재귀 CTE
WITH RECURSIVE CTE AS (
-- 앵커 멤버 (루트)
SELECT 사원ID, 매니저ID, 사원명, 1 AS LEVEL
FROM 사원 WHERE 매니저ID IS NULL
UNION ALL
-- 재귀 멤버
SELECT A.사원ID, A.매니저ID, A.사원명, B.LEVEL + 1
FROM 사원 A INNER JOIN CTE B ON A.매니저ID = B.사원ID
)
SELECT * FROM CTE;
4-7. PIVOT과 UNPIVOT (신규 추가!)
PIVOT
- 행 → 열 변환 (세로 데이터를 가로로 펼침)
-- Oracle PIVOT 구문
SELECT *
FROM (SELECT 부서, 직급, 급여 FROM 사원)
PIVOT (
SUM(급여) -- 집계함수(집계할 칼럼)
FOR 직급 -- 열로 변환할 칼럼
IN ('과장' AS 과장, '대리' AS 대리, '사원' AS 사원) -- 열 값 지정
);
PIVOT 핵심 정리
- FROM절의 서브쿼리에 포함된 칼럼 중 PIVOT에서 사용하지 않은 칼럼이 GROUP BY 기준이 됨
- 집계함수는 SUM, COUNT, AVG, MAX, MIN 등 사용 가능
- IN 절에 명시한 값만 열로 생성됨
-- CASE 문으로 PIVOT 대체 (PIVOT 지원하지 않는 DBMS)
SELECT 부서,
SUM(CASE WHEN 직급 = '과장' THEN 급여 ELSE 0 END) AS 과장,
SUM(CASE WHEN 직급 = '대리' THEN 급여 ELSE 0 END) AS 대리,
SUM(CASE WHEN 직급 = '사원' THEN 급여 ELSE 0 END) AS 사원
FROM 사원
GROUP BY 부서;
UNPIVOT
- 열 → 행 변환 (가로 데이터를 세로로 변환)
-- Oracle UNPIVOT 구문
SELECT *
FROM 월별매출
UNPIVOT (
매출액 -- 값이 들어갈 칼럼명
FOR 월 -- 기존 열 이름이 들어갈 칼럼명
IN (M01, M02, M03, M04) -- 행으로 변환할 기존 칼럼들
);
UNPIVOT 핵심 정리
- 여러 칼럼의 데이터를 행으로 변환
- NULL 값이 있는 행은 기본적으로 제외됨
- NULL도 포함하려면:
UNPIVOT INCLUDE NULLS
4-8. 정규 표현식 (Regular Expression) (신규 추가!)
주요 함수
| 함수 | 설명 |
|---|---|
| REGEXP_LIKE(칼럼, 패턴) | 패턴과 일치하는 행 반환 (WHERE절) |
| REGEXP_REPLACE(문자열, 패턴, 대체문자) | 패턴과 일치하는 부분을 대체 |
| REGEXP_SUBSTR(문자열, 패턴) | 패턴과 일치하는 부분 문자열 추출 |
| REGEXP_INSTR(문자열, 패턴) | 패턴과 일치하는 위치(인덱스) 반환 |
| REGEXP_COUNT(문자열, 패턴) | 패턴과 일치하는 횟수 반환 |
주요 정규 표현식 메타문자
| 메타문자 | 설명 | 예시 |
|---|---|---|
. |
임의의 한 문자 | a.c → abc, aXc |
* |
0회 이상 반복 | ab*c → ac, abc, abbc |
+ |
1회 이상 반복 | ab+c → abc, abbc (ac는 X) |
? |
0회 또는 1회 | ab?c → ac, abc |
^ |
문자열 시작 | ^abc → abc로 시작 |
$ |
문자열 끝 | abc$ → abc로 끝남 |
[] |
문자 클래스 (집합) | [abc] → a, b, c 중 하나 |
[^] |
부정 문자 클래스 | [^abc] → a, b, c 외 문자 |
{n} |
정확히 n회 반복 | a{3} → aaa |
{n,m} |
n회 이상 m회 이하 | a{2,4} → aa, aaa, aaaa |
| ` | ` | OR (또는) |
() |
그룹화 | (ab)+ → ab, abab |
\d |
숫자 [0-9] | |
\w |
문자+숫자+_ | |
\s |
공백 문자 |
POSIX 문자 클래스
| 표현 | 의미 |
|---|---|
[:alpha:] |
알파벳 문자 |
[:digit:] |
숫자 |
[:alnum:] |
알파벳 + 숫자 |
[:upper:] |
대문자 |
[:lower:] |
소문자 |
[:space:] |
공백 문자 |
정규 표현식 활용 예시
-- 이메일 형식 검증
WHERE REGEXP_LIKE(이메일, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')
-- 숫자만 포함된 문자열
WHERE REGEXP_LIKE(칼럼, '^[0-9]+$')
-- 전화번호 형식 (010-XXXX-XXXX)
WHERE REGEXP_LIKE(전화번호, '^010-[0-9]{4}-[0-9]{4}$')
-- 특수문자 제거
SELECT REGEXP_REPLACE('Hello! World@#', '[^a-zA-Z ]', '') FROM DUAL;
-- 결과: 'Hello World'
-- 첫 번째 숫자 추출
SELECT REGEXP_SUBSTR('ABC123DEF', '[0-9]+') FROM DUAL;
-- 결과: '123'
-- 공백 문자 여러 개를 하나로 치환
SELECT REGEXP_REPLACE('A B C', '\s+', ' ') FROM DUAL;
-- 결과: 'A B C'
4-9. 셀프 조인 & 뷰
뷰(View)
CREATE VIEW 뷰이름 AS
SELECT 칼럼1, 칼럼2 FROM 테이블 WHERE 조건;
뷰의 장점
- 독립성: 테이블 구조 변경 시 뷰만 수정
- 편리성: 복잡한 쿼리를 단순화
- 보안성: 특정 칼럼만 노출 가능
- 실제 데이터를 저장하지 않음 (논리적 테이블)
제3장. 관리 구문
5-1. DML (상세)
(제1장에서 다룬 내용 참조 — INSERT, UPDATE, DELETE, SELECT)
5-2. TCL (Transaction Control Language)
COMMIT; -- 변경 사항 확정
ROLLBACK; -- 변경 사항 취소
SAVEPOINT sp1; -- 저장점 설정
ROLLBACK TO sp1; -- 저장점까지만 취소
Oracle: DDL 수행 시 AUTO COMMIT 발생 (이전 DML도 함께 커밋!)
SQL Server: DDL도 ROLLBACK 가능! (Oracle과 차이)
5-3. DCL (Data Control Language)
GRANT / REVOKE
GRANT SELECT, INSERT ON 테이블명 TO 사용자;
GRANT ALL ON 테이블명 TO 사용자 WITH GRANT OPTION;
REVOKE SELECT ON 테이블명 FROM 사용자;
WITH GRANT OPTION vs WITH ADMIN OPTION
| 구분 | WITH GRANT OPTION | WITH ADMIN OPTION |
|---|---|---|
| 대상 | 객체 권한 | 시스템 권한, ROLE |
| 회수 시 | 연쇄 회수 (전파된 권한 모두 회수) | 연쇄 회수 안 됨 |
ROLE
- 권한의 묶음 (여러 권한을 하나의 ROLE로 관리)
CREATE ROLE 역할명;
GRANT SELECT, INSERT ON 테이블 TO 역할명;
GRANT 역할명 TO 사용자;
빈출 함정 포인트 총정리
NULL 관련 함정
1. NULL과의 모든 비교 = UNKNOWN (FALSE 처리)
2. NULL + 숫자 = NULL
3. COUNT(*) vs COUNT(칼럼) → NULL 포함 여부 차이
4. AVG는 NULL 행을 분모에서 제외 → 전체 평균 원하면 NVL 처리
5. NOT IN에 NULL 포함 시 결과 없음!
→ WHERE 부서 NOT IN (10, 20, NULL) → 결과 0건!
6. Oracle: 빈 문자열('') = NULL로 처리
→ LENGTH('') = NULL (Oracle), LEN('') = 0 (SQL Server)
OUTER JOIN 함정
1. Oracle (+): 데이터 없는(채워질) 쪽에 붙임
2. ON절 조건: 조인 전 적용 (OUTER 유지)
WHERE절 조건: 조인 후 적용 (OUTER 효과 사라질 수 있음!)
GROUP BY 함정
1. SELECT에 집계함수 아닌 칼럼 → 반드시 GROUP BY에 포함
2. GROUP BY에 Alias 사용 불가
3. WHERE절에 집계함수 불가 → HAVING 사용
NATURAL JOIN / USING 함정
1. NATURAL JOIN: 테이블명.칼럼명 (별칭) 사용 불가
2. USING: 해당 칼럼에 테이블명.칼럼명 (별칭) 사용 불가
PIVOT 함정
1. FROM 서브쿼리에 불필요한 칼럼을 넣으면 GROUP BY 기준이 달라짐
2. IN절에 명시하지 않은 값은 결과에 포함 안 됨
Oracle vs SQL Server 차이점 정리
| 항목 | Oracle | SQL Server |
|---|---|---|
| 빈 문자열 | NULL로 처리 | 빈 문자열('') 그대로 |
| 문자열 결합 | || |
+ |
| 문자열 길이 | LENGTH() | LEN() |
| 부분 문자열 | SUBSTR() | SUBSTRING() |
| NULL 치환 | NVL() | ISNULL() |
| 현재 날짜 | SYSDATE | GETDATE() |
| 상위 N건 | WHERE ROWNUM <= N | SELECT TOP N |
| 칼럼 수정 | MODIFY | ALTER COLUMN |
| 테이블명 변경 | RENAME TO | sp_rename |
| DDL ROLLBACK | 불가 (AUTO COMMIT) | 가능 |
| NULL 정렬 | 가장 큰 값 | 가장 작은 값 |
| FROM절 생략 | FROM DUAL 필요 | 생략 가능 |
| 빈 문자열 길이 | NULL | 0 |