← 정보처리기사 실기 개념정리 목차

SQL 완전 정리 ⑦ DDL·제약 조건·뷰·인덱스

앞 편들이 "이미 있는 테이블에서 꺼내는" DML 이었다면, 이 편은 테이블이라는 그릇 자체를 만들고 고치고 부수는 DDL(Data Definition Language)이다. 핵심은 세 가지다 — ① 열마다 타입으로 담을 수 있는 값의 모양을 정하고, ② 제약 조건으로 "들어와서는 안 되는 값"을 DBMS 가 대신 막게 하고, ③ 뷰·인덱스로 같은 데이터를 다르게 보여 주거나 빨리 찾게 한다. 제약 조건은 "검사 규칙", 뷰는 "저장된 SELECT", 인덱스는 "정렬된 찾아보기 표"라고 기억하면 각 문법의 동작이 거기서 그대로 따라 나온다. 본문의 쿼리·결과·오류 메시지는 PostgreSQL 14 와 SQLite 3.54 에서 실제로 실행해 맞췄다 (Oracle·MySQL 전용 문법은 대응만 소개).


0. 이 편에서 쓰는 테이블

부서

dnodnameloc
10개발서울
20영업부산
30인사서울
40기획NULL

사원

enoenamednojobsalmgrhiredatebonus
1김하늘10팀장500NULL2019-03-01NULL
2이도윤10대리35012021-07-1530
3박서준10사원28022023-01-02NULL
4최지우20팀장450NULL2018-11-2050
5정민재20사원30042022-05-0920
6한유나20사원30042024-02-01NULL
7오세린NULL사원250NULL2025-01-06NULL

이 편은 "테이블을 만드는" 편이라, 위 두 테이블을 제약 조건까지 붙여 직접 CREATE 하는 것부터 시작한다(§2). FK 참조 동작(§4)·뷰(§8)·인덱스(§9) 실험은 이 두 테이블의 복사본 위에서 한다.


1. 데이터 타입

열을 만들 때 반드시 타입을 정한다. 타입은 "이 열에 들어올 수 있는 값의 집합" — 곧 §5 에서 볼 도메인 무결성의 첫 관문이다. 실기에서 나오는 것은 문자·숫자·날짜 세 계열이면 충분하다.

1.1 문자 — CHAR vs VARCHAR

타입길이저장 방식쓰임
CHAR(n)고정 n 자n 보다 짧으면 뒤를 공백으로 채워(padding) 저장주민번호 앞자리, 코드값처럼 길이가 늘 같은 값
VARCHAR(n)가변, 최대 n 자들어온 길이만큼만 저장이름·주소처럼 길이가 들쭉날쭉한 값
CREATE TABLE t_char (c CHAR(5), v VARCHAR(5));
INSERT INTO t_char VALUES ('ab', 'ab');
SELECT LENGTH(c), OCTET_LENGTH(c), LENGTH(v) FROM t_char;   -- PostgreSQL
LENGTH(c)OCTET_LENGTH(c)LENGTH(v)
252

c 는 실제로 'ab '(5바이트)로 저장돼 있지만, PostgreSQL 은 CHAR 의 뒤 공백을 "의미 없는 채움"으로 보고 LENGTH·문자열 연결에서 떼어 낸다. 비교에서도 차이가 난다.

SELECT 'ab'::char(5)    = 'ab   '::char(5)    AS c_eq,
       'ab'::varchar(5) = 'ab   '::varchar(5) AS v_eq;
c_eqv_eq
tf

CHAR 끼리 비교할 때는 뒤 공백을 무시하지만(둘 다 같은 길이로 채워지므로), VARCHAR 의 공백은 진짜 문자다.

길이를 넘으면 오류가 난다(표준 동작, PostgreSQL·Oracle·MySQL strict 모드).

INSERT INTO t_char VALUES ('abcdef', 'x');
-- ERROR:  value too long for type character(5)

SQLite 는 예외 — SQLite 는 CHAR(5)·VARCHAR(5) 를 그냥 TEXT 로 취급해 길이 제한도 패딩도 하지 않는다. VARCHAR(5) 열에 'abcdefgh' 를 넣어도 들어가고 LENGTH 는 8 이다. 타입 검사 실험은 PostgreSQL 기준으로 보자.

1.2 숫자 — INTEGER / NUMERIC(p, s) / DECIMAL

타입의미예
SMALLINT·INTEGER(INT)·BIGINT정수. 범위만 다르다(2·4·8바이트)사번, 수량
NUMERIC(p, s) = DECIMAL(p, s)고정 소수점. 전체 자릿수 p(정밀도), 그중 소수 이하 s(스케일)금액, 이율
REAL·DOUBLE PRECISION·FLOAT부동 소수점(근삿값)과학 측정치

NUMERIC(5,2) 는 "전체 5자리, 소수 2자리" → 정수부는 최대 3자리, 범위 −999.99 ~ 999.99.

SELECT CAST(123.456 AS NUMERIC(5,2));   -- 123.46  (소수 3째 자리에서 반올림)
SELECT CAST(1234.5  AS NUMERIC(5,2));
-- ERROR:  numeric field overflow
-- DETAIL:  A field with precision 5, scale 2 must round to an absolute value less than 10^3.

함정 — 소수부가 넘치면 반올림해서 넣어 주지만, 정수부가 넘치면 오류다. "p 는 소수점 이하 포함 전체 자릿수"를 잊고 NUMERIC(5,2) 에 1234.5 가 들어간다고 답하는 실수가 흔하다.

정수 타입끼리의 나눗셈은 정수로 잘린다(1편에서 본 7/2 = 3). 금액 계산에 NUMERIC 을 쓰는 이유는 FLOAT 의 이진 근삿값 오차(0.1 + 0.2 ≠ 0.3)가 없기 때문이다.

1.3 날짜·시간 — DATE / TIME / TIMESTAMP

타입담는 것리터럴
DATE연-월-일 (PostgreSQL·MySQL). Oracle DATE 는 시·분·초까지 포함DATE '2024-02-28'
TIME시:분:초TIME '09:30:00'
TIMESTAMP날짜 + 시각 (소수 초까지)TIMESTAMP '2024-02-28 23:30:00'
SELECT DATE '2024-02-28' + 1                               AS d1,
       TIMESTAMP '2024-02-28 23:30:00' + INTERVAL '1 hour' AS ts;   -- PostgreSQL
d1ts
2024-02-292024-02-29 00:30:00

윤년(2024-02-29)과 자정 넘김을 DBMS 가 알아서 계산한다 — 날짜를 문자열로 저장하지 말아야 하는 이유다.

1.4 DBMS 별 대응

표준 / PostgreSQLOracleMySQL비고
VARCHAR(n)VARCHAR2(n)VARCHAR(n)Oracle 은 VARCHAR2 사용 권장. Oracle 은 빈 문자열 '' 을 NULL 로 취급
CHAR(n)CHAR(n)CHAR(n)
INTEGER, NUMERIC(p,s)NUMBER(p), NUMBER(p,s)INT, DECIMAL(p,s)Oracle 은 정수·실수 모두 NUMBER 하나로
TEXTCLOBTEXT긴 문자열
DATE / TIMESTAMPDATE(초까지) / TIMESTAMPDATE / DATETIME·TIMESTAMP
BOOLEAN(없음 — 23ai 이전) NUMBER(1)·CHAR(1)BOOLEAN(=TINYINT(1))

2. CREATE TABLE — 전체 골격

2.1 문법 골격

CREATE TABLE 테이블명 (
    열이름 데이터타입 [DEFAULT 기본값] [열 수준 제약 …],
    열이름 데이터타입 …,
    …
    [CONSTRAINT 제약이름] 테이블 수준 제약 (열목록),
    …
);

한 테이블 정의 안에는 열 정의와 테이블 수준 제약이 쉼표로 나열된다. 제약은 두 자리에 쓸 수 있다.

위치모양쓸 수 있는 것
열 수준열 정의 바로 뒤에 NOT NULL, UNIQUE, PRIMARY KEY, CHECK (…), REFERENCES 부모(열)그 열 하나에만 관한 제약
테이블 수준열 정의가 끝난 뒤 따로 PRIMARY KEY (a, b), FOREIGN KEY (a) REFERENCES …, CHECK (a <= b)여러 열에 걸친 제약(복합 키, 열끼리 비교하는 CHECK)

NOT NULL 은 열 수준에만 쓴다(표준). 복합 기본키 PRIMARY KEY (eno, pno) 나 CHECK (bonus <= sal) 처럼 두 열 이상이 얽힌 제약은 테이블 수준에만 쓸 수 있다.

2.2 공통 스키마를 제약과 함께 만들기

CREATE TABLE 부서 (
    dno   INTEGER     PRIMARY KEY,               -- 열 수준
    dname VARCHAR(20) NOT NULL UNIQUE,
    loc   VARCHAR(20)
);

CREATE TABLE 사원 (
    eno      INTEGER      NOT NULL,
    ename    VARCHAR(20)  NOT NULL,
    dno      INTEGER,
    job      VARCHAR(10)  DEFAULT '사원',
    sal      NUMERIC(7,0) CHECK (sal > 0),       -- 이름 없는 열 수준 CHECK
    mgr      INTEGER,
    hiredate DATE         DEFAULT CURRENT_DATE,
    bonus    NUMERIC(5,0),
    CONSTRAINT pk_사원      PRIMARY KEY (eno),                          -- 테이블 수준
    CONSTRAINT fk_사원_부서 FOREIGN KEY (dno) REFERENCES 부서 (dno),
    CONSTRAINT fk_사원_mgr  FOREIGN KEY (mgr) REFERENCES 사원 (eno),    -- 자기 참조
    CONSTRAINT ck_bonus     CHECK (bonus <= sal)                        -- 두 열 비교
);

부서 4행, 사원 7행을 §0 의 값 그대로 INSERT 하면 모든 제약을 통과한다. fk_사원_mgr 는 자기 자신을 참조하는 FK — 관리자도 사원이므로 mgr 은 사원.eno 에 있는 값(또는 NULL)이어야 한다.

2.3 CONSTRAINT 이름 붙이기

CONSTRAINT 이름 을 생략하면 DBMS 가 이름을 지어 준다.

제약직접 지은 이름PostgreSQL 이 자동으로 지은 이름
사원 PKpk_사원(생략 시) 사원_pkey
sal CHECK—사원_sal_check
부서.dname UNIQUE—부서_dname_key

Oracle 은 SYS_C0012345 같은 일련번호를 붙인다. 이름이 중요한 이유는 두 가지다 — ① 위반 오류 메시지에 이름이 그대로 찍혀 어떤 규칙이 깨졌는지 바로 알 수 있고, ② 나중에 ALTER TABLE … DROP CONSTRAINT 이름 으로 지울 때 이름이 필요하다(§6).


3. 제약 조건 6종

제약막는 것NULL테이블당자동 인덱스
NOT NULL값 비우기금지열마다—
UNIQUE중복 값여러 개 허용(대부분 DBMS)여러 개생성
PRIMARY KEY중복 + NULL금지하나생성
FOREIGN KEY부모에 없는 값허용여러 개(PG·Oracle 은 자식 쪽 자동 생성 안 함)
CHECK조건이 FALSE 인 값UNKNOWN 이면 통과여러 개—
DEFAULT(막지 않음) 값 생략 시 채움—열마다—

DEFAULT 는 엄밀히 "제약"이라기보다 열 속성이지만, 교재·시험에서는 제약 조건 목록에 함께 넣는다.

3.1 NOT NULL·DEFAULT

INSERT INTO 사원 (eno, ename, sal) VALUES (8, '신입', 200);
SELECT eno, ename, job, dno FROM 사원 WHERE eno = 8;
enoenamejobdno
8신입사원NULL

열 목록에서 빠진 열만 DEFAULT 를 받는다 — job 은 '사원', hiredate 는 오늘 날짜, DEFAULT 가 없는 dno 는 NULL. 반대로 NULL 을 명시하면 DEFAULT 가 끼어들지 않는다.

INSERT INTO 사원 (eno, ename, job, sal) VALUES (9, '명시', NULL, 200);
SELECT eno, job FROM 사원 WHERE eno = 9;    -- job = NULL (DEFAULT '사원' 아님)

함정 — "DEFAULT 가 있으면 NULL 이 안 들어간다"는 틀렸다. DEFAULT 는 생략했을 때만 쓰인다. NULL 을 막으려면 NOT NULL 이 따로 필요하다. 명시적으로 기본값을 쓰고 싶으면 VALUES (9, '명시', DEFAULT, 200) 처럼 DEFAULT 키워드를 넣는다.

3.2 위반하면 — 오류 예시 (PostgreSQL)

시도오류 메시지
INSERT … (eno, ename, sal) VALUES (1, '중복', 100)duplicate key value violates unique constraint "pk_사원" — Key (eno)=(1) already exists.
… VALUES (NULL, '널키', 100)null value in column "eno" of relation "사원" violates not-null constraint
… VALUES (10, NULL, 100)null value in column "ename" … violates not-null constraint
… VALUES (10, '음수', -5)new row for relation "사원" violates check constraint "사원_sal_check"
… (eno, ename, dno, sal) VALUES (10, '유령', 99, 100)violates foreign key constraint "fk_사원_부서" — Key (dno)=(99) is not present in table "부서".
INSERT INTO 부서 VALUES (50, '개발', '대전')duplicate key value violates unique constraint "부서_dname_key"
… (eno, ename, sal, bonus) VALUES (10, '보너스', 100, 200)violates check constraint "ck_bonus"
DELETE FROM 부서 WHERE dno = 10update or delete on table "부서" violates foreign key constraint "fk_사원_부서" on table "사원"

위반한 문장은 통째로 실패하고 테이블은 바뀌지 않는다. 마지막 줄은 부모 쪽 위반이다 — 개발부에 사원이 있으므로 부서 10 을 지우면 자식 행이 고아가 된다. 이때 무슨 일이 일어날지 정하는 것이 §4 의 참조 동작이다.

3.3 PRIMARY KEY = UNIQUE + NOT NULL (+ 하나뿐)

기본키는 행을 유일하게 식별하는 열(들)이다. 그래서 중복도 NULL 도 안 된다. 기본키 후보였지만 뽑히지 못한 키 (대체키)는 보통 UNIQUE NOT NULL 로 건다 — 위 부서.dname 이 그 예다.

복합 기본키는 테이블 수준으로 쓴다.

CREATE TABLE 참여 (
    eno   INTEGER REFERENCES 사원 (eno),
    pno   INTEGER,
    hours INTEGER,
    PRIMARY KEY (eno, pno)          -- (2,100)·(2,200) 은 OK, (2,100) 두 번은 위반
);

3.4 UNIQUE 는 NULL 을 여러 개 허용한다

CREATE TABLE u (k INTEGER UNIQUE);
INSERT INTO u VALUES (NULL), (NULL), (1);    -- 성공
SELECT COUNT(*) AS rows, COUNT(k) AS nonnull FROM u;
rowsnonnull
31

INSERT INTO u VALUES (1) 을 한 번 더 하면 그때는 duplicate key … "u_k_key" 로 실패한다. PostgreSQL·SQLite 모두 같은 결과다.

왜 그런가? UNIQUE 는 "두 행의 값이 같으면 안 된다"는 규칙인데, NULL = NULL 은 TRUE 가 아니라 UNKNOWN 이다 (1편의 3값 논리). "같다"가 성립하지 않으니 위반도 아니다.

DBMSUNIQUE 열에 NULL 여러 개
PostgreSQL·Oracle·MySQL·SQLite허용
SQL Server하나만 허용 (두 번째 NULL 은 중복 오류)
PostgreSQL 15+UNIQUE NULLS NOT DISTINCT 로 하나만 허용하게 바꿀 수 있음

3.5 CHECK 는 UNKNOWN 이면 통과한다

INSERT INTO 사원 (eno, ename, sal, bonus) VALUES (11, '널급여', NULL, 999);   -- 성공!
enoenamesalbonus
11널급여NULL999

CHECK (sal > 0) 은 NULL > 0 → UNKNOWN, CHECK (bonus <= sal) 도 999 <= NULL → UNKNOWN. CHECK 는 결과가 FALSE 일 때만 거부하므로 둘 다 통과한다. WHERE 가 "TRUE 인 행만 남기는" 것과 정반대 방향이라 헷갈리기 쉽다.

조건 결과WHERECHECK
TRUE남김통과
FALSE버림거부
UNKNOWN버림통과

NULL 까지 막으려면 sal NUMERIC(7,0) NOT NULL CHECK (sal > 0) 처럼 NOT NULL 을 함께 건다.

3.6 FOREIGN KEY

[CONSTRAINT 이름] FOREIGN KEY (자식열 [, …])
    REFERENCES 부모테이블 (부모열 [, …])
    [ON DELETE 동작] [ON UPDATE 동작]
  • 참조되는 부모 열은 PRIMARY KEY 또는 UNIQUE 여야 한다(그래야 "어느 행"인지 하나로 정해진다).
  • 자식 열 값은 부모에 있는 값이거나 NULL. NULL 은 "아직 소속 없음"을 뜻하며 검사하지 않는다 — 그래서 오세린(dno NULL)이 들어간다.
  • 열 수준으로는 dno INTEGER REFERENCES 부서(dno) 처럼 FOREIGN KEY 키워드 없이 쓴다.

SQLite 주의 — SQLite 는 FK 를 문법으로는 받지만 기본값이 꺼져 있다. PRAGMA foreign_keys = ON; 을 실행해야 비로소 FOREIGN KEY constraint failed 가 난다. 꺼진 상태에서는 부모에 없는 99 도 그냥 들어간다.


4. FK 참조 동작 — ON DELETE / ON UPDATE

부모 행을 지우거나 키를 바꿀 때 그 값을 참조하던 자식 행을 어떻게 할지 정하는 옵션이다.

FOREIGN KEY (dno) REFERENCES 부서 (dno)
    ON DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }
    ON UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }
동작부모 삭제 시 자식 행은한 줄 의미
CASCADE같이 삭제 (UPDATE 면 같이 변경)연쇄
SET NULLFK 열을 NULL 로소속 해제
SET DEFAULTFK 열을 DEFAULT 값으로기본 소속으로 이동
RESTRICT자식이 있으면 부모 삭제 즉시 거부금지
NO ACTION자식이 있으면 거부 — 단 문장(또는 트랜잭션) 끝에 검사금지(기본값)

4.1 같은 데이터로 다섯 가지 비교

CREATE TABLE d2 (dno INTEGER PRIMARY KEY, dname VARCHAR(10));
INSERT INTO d2 VALUES (10,'개발'), (20,'영업'), (30,'인사'), (99,'미배정');

CREATE TABLE e2 (
    eno   INTEGER PRIMARY KEY,
    ename VARCHAR(10),
    dno   INTEGER DEFAULT 99 REFERENCES d2 (dno) ON DELETE ⟨동작⟩
);
INSERT INTO e2 VALUES (1,'김하늘',10), (2,'이도윤',10), (4,'최지우',20);

DELETE FROM d2 WHERE dno = 10;     -- 자식 2명을 가진 부모 삭제
ON DELETEDELETE 결과e2 (eno:dno)d2 의 dno
CASCADE성공4:20 (1·2번 사원 삭제됨)20, 30, 99
SET NULL성공1:NULL, 2:NULL, 4:2020, 30, 99
SET DEFAULT성공1:99, 2:99, 4:2020, 30, 99
RESTRICT오류1:10, 2:10, 4:20 (그대로)10, 20, 30, 99
NO ACTION오류1:10, 2:10, 4:20 (그대로)10, 20, 30, 99

PostgreSQL·SQLite(PRAGMA foreign_keys=ON) 모두 같은 결과다. 자식이 없는 DELETE FROM d2 WHERE dno = 30 은 다섯 경우 모두 그냥 성공한다.

SET DEFAULT 함정 — 기본값 99 가 부모에 실제로 있어야 한다. 99(미배정) 행이 없으면 바뀐 값이 다시 FK 를 위반해 DELETE 가 실패한다. SET NULL 도 FK 열이 NOT NULL 이면 쓸 수 없다.

ON UPDATE 도 같은 방식이다. ON UPDATE CASCADE 로 걸고 UPDATE d2 SET dno = 11 WHERE dno = 10 을 하면 자식 두 행의 dno 도 11 로 따라 바뀐다(PG 확인).

DBMSON DELETE 지원ON UPDATE 지원
PostgreSQL·MySQL(InnoDB)·SQLite5종 모두 (MySQL InnoDB 는 SET DEFAULT 거부)5종
OracleCASCADE, SET NULL 만 (생략 = NO ACTION)없음

4.2 RESTRICT vs NO ACTION — 검사 시점

결과만 보면 둘 다 "거부"인데 무엇이 다른가? 언제 검사하느냐다.

  • RESTRICT — 부모 행을 지우는 그 순간 자식을 확인하고 거부. 미룰 수 없다.
  • NO ACTION — 문장이 끝날 때 확인한다. 제약을 DEFERRABLE INITIALLY DEFERRED 로 만들면 COMMIT 시점까지 미룰 수 있어, 그 사이에 부모를 되살리면 통과한다.
CREATE TABLE e2 (eno INTEGER PRIMARY KEY,
                 dno INTEGER REFERENCES d2 (dno) ON DELETE NO ACTION
                     DEFERRABLE INITIALLY DEFERRED);
INSERT INTO e2 VALUES (1, 10);

BEGIN;
DELETE FROM d2 WHERE dno = 10;          -- 아직 오류 아님 (검사 보류)
INSERT INTO d2 VALUES (10, '개발2');    -- 부모를 다시 만들어 둔다
COMMIT;                                 -- 커밋 시 검사 → 자식의 10 이 부모에 있으므로 통과

같은 코드를 ON DELETE RESTRICT 로 바꾸면 DEFERRABLE 을 붙였어도 DELETE 줄에서 바로 오류가 나고 트랜잭션 전체가 실패한다(PG 확인). DEFERRABLE 이 없는 보통의 경우에는 두 동작의 차이가 겉으로 드러나지 않는다.


5. 무결성 3종

제약 조건은 결국 무결성(integrity) — "데이터가 현실과 어긋나지 않는 상태" — 을 DBMS 가 지키게 하는 장치다.

무결성규칙지키는 제약깨진 예
개체 무결성기본키는 NULL 일 수 없고 중복될 수 없다PRIMARY KEYeno 가 NULL 인 사원 → 누구인지 식별 불가
참조 무결성외래키 값은 부모의 기본키 값이거나 NULL 이다FOREIGN KEY (+ 참조 동작)dno = 99 인 사원 → 존재하지 않는 부서 소속
도메인 무결성속성 값은 정의된 도메인(타입·범위·형식) 안에 있어야 한다데이터 타입, CHECK, NOT NULL, DEFAULTsal = −5, 성별 = 'X'

교재에 따라 키 무결성(후보키는 유일해야 한다 → UNIQUE), 사용자 정의 무결성(업무 규칙 → CHECK·트리거)을 더하기도 한다. 실기에서 "기본키에 NULL 불가"는 개체, "외래키는 참조 테이블의 기본키 값 또는 NULL"은 참조 무결성이다.


6. ALTER TABLE — 이미 있는 테이블 고치기

데이터를 지우지 않고 구조만 바꾼다. 실험용 테이블 하나로 차례대로 해 보자(PostgreSQL).

CREATE TABLE t (id INTEGER PRIMARY KEY, name VARCHAR(10), sal INTEGER);
INSERT INTO t VALUES (1,'a',100), (2,'b',200);

ALTER TABLE t ADD COLUMN email VARCHAR(30) DEFAULT 'none';   -- ① 열 추가
idnamesalemail
1a100none
2b200none

새 열은 맨 뒤에 붙고, 기존 행에는 DEFAULT 값(없으면 NULL)이 채워진다. 그래서 기존 행이 있는 테이블에 ADD COLUMN x INTEGER NOT NULL 을 DEFAULT 없이 하면 기존 행이 NULL 이 되어 실패한다.

ALTER TABLE t ALTER COLUMN name TYPE VARCHAR(30);          -- ② 타입 변경
ALTER TABLE t ALTER COLUMN name SET NOT NULL;              -- ③ NOT NULL 추가
ALTER TABLE t RENAME COLUMN name TO ename;                 -- ④ 열 이름 변경
ALTER TABLE t ADD CONSTRAINT ck_t_sal CHECK (sal >= 150);  -- ⑤ 제약 추가 → 실패!
-- ERROR:  check constraint "ck_t_sal" of relation "t" is violated by some row
ALTER TABLE t ADD CONSTRAINT ck_t_sal2 CHECK (sal >= 50);  -- 성공
ALTER TABLE t DROP CONSTRAINT ck_t_sal2;                   -- ⑥ 제약 삭제 (이름 필요!)
ALTER TABLE t DROP COLUMN email;                           -- ⑦ 열 삭제
ALTER TABLE t RENAME TO t_new;                             -- ⑧ 테이블 이름 변경

⑤ 처럼 이미 들어 있는 데이터가 새 제약을 어기면 제약 추가 자체가 실패한다(id 1 의 sal 100). 타입을 줄일 때도 같다 — ALTER COLUMN sal TYPE VARCHAR(2) 는 '100' 이 2자를 넘어 value too long 으로 실패한다.

6.1 DBMS 별 문법 대응

작업표준 / PostgreSQLOracleMySQL
열 추가ADD [COLUMN] c 타입ADD (c 타입)ADD [COLUMN] c 타입
열 삭제DROP [COLUMN] cDROP COLUMN cDROP [COLUMN] c
타입 변경ALTER COLUMN c TYPE 타입MODIFY (c 타입)MODIFY [COLUMN] c 타입
열 이름 변경RENAME COLUMN a TO bRENAME COLUMN a TO bRENAME COLUMN a TO b (8.0+) / CHANGE a b 타입
NOT NULLALTER COLUMN c SET NOT NULLMODIFY (c NOT NULL)MODIFY c 타입 NOT NULL
기본값ALTER COLUMN c SET DEFAULT vMODIFY (c DEFAULT v)ALTER [COLUMN] c SET DEFAULT v
제약 추가ADD CONSTRAINT 이름 …같음같음
제약 삭제DROP CONSTRAINT 이름같음DROP CONSTRAINT 이름(8.0.19+), DROP FOREIGN KEY 이름, DROP PRIMARY KEY
테이블 이름ALTER TABLE a RENAME TO b같음 / RENAME a TO bRENAME TABLE a TO b

실기 빈칸 문제의 단골은 Oracle·MySQL 의 MODIFY 와 표준의 ALTER COLUMN 구분이다. ALTER TABLE 사원 MODIFY ename VARCHAR(40); 은 MySQL, ALTER TABLE 사원 ALTER COLUMN ename TYPE VARCHAR(40); 은 PostgreSQL 이다. SQLite 의 ALTER TABLE 은 ADD COLUMN·DROP COLUMN·RENAME 만 되고 타입 변경·제약 추가는 안 된다.


7. DROP·TRUNCATE·DELETE, 그리고 CREATE TABLE AS

7.1 세 가지 "지우기" 비교

DELETE FROM t [WHERE …]TRUNCATE TABLE tDROP TABLE t
분류DMLDDLDDL
지우는 것조건에 맞는 행모든 행행 + 테이블 구조 자체(제약·인덱스·권한 포함)
WHERE가능불가불가
구조 유지유지유지사라짐
행 단위 DELETE 트리거발생발생 안 함발생 안 함
로그·속도행마다 기록 → 느림저장 공간을 통째로 비움 → 빠름빠름
롤백 (Oracle·MySQL)가능불가(자동 커밋)불가(자동 커밋)
롤백 (PostgreSQL)가능가능(트랜잭션 안이면)가능(트랜잭션 안이면)

PostgreSQL 은 DDL 도 트랜잭션의 일부라 되돌릴 수 있다. 실제로 확인한 순서:

-- s 에 3행, AFTER DELETE FOR EACH ROW 트리거가 log 테이블에 'del id' 를 남긴다
BEGIN; TRUNCATE s; SELECT COUNT(*) FROM s;  ROLLBACK;   -- 0  → 롤백 후 3
BEGIN; DROP TABLE s;                        ROLLBACK;   -- 롤백 후 테이블·데이터 그대로
DELETE FROM s WHERE id = 1;     -- 트리거 발생 → log: 'del 1'
TRUNCATE s;                     -- 남은 2행 삭제, 트리거 없음 → log 는 여전히 'del 1' 하나

시험 기준 — 정보처리기사는 Oracle 관점으로 "TRUNCATE·DROP 은 DDL 이라 롤백할 수 없다, DELETE 는 롤백 가능"을 정답으로 본다. Oracle·MySQL 은 DDL 을 실행하기 전후로 암묵적 COMMIT 을 하기 때문이다. PostgreSQL 은 예외로 기억해 두자. SQLite 에는 TRUNCATE 문이 없다(DELETE FROM t; 를 내부적으로 빠르게 처리한다).

7.2 DROP … CASCADE / RESTRICT

CREATE VIEW v_s AS SELECT id FROM s;
DROP TABLE s;            -- 또는 DROP TABLE s RESTRICT;
-- ERROR:  cannot drop table s because other objects depend on it
-- DETAIL:  view v_s depends on table s
-- HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE s CASCADE;
-- NOTICE:  drop cascades to view v_s       ← 뷰도 함께 삭제됨
옵션의미기본값
RESTRICT이 객체에 의존하는 객체(뷰·FK 등)가 있으면 삭제 거부PostgreSQL·표준의 기본
CASCADE의존 객체까지 함께 삭제(FK 는 제약만 삭제, 자식 테이블은 남음)

Oracle 은 DROP TABLE 부서 CASCADE CONSTRAINTS; — 참조하는 FK 제약을 같이 지운다. Oracle 에서 의존 뷰는 지워지지 않고 INVALID 상태로 남는다.

같은 단어 CASCADE 가 두 곳에 나온다. ON DELETE CASCADE 는 행(데이터)을 연쇄 삭제하고, DROP … CASCADE 는 객체(뷰·제약)를 연쇄 삭제한다.

7.3 CREATE TABLE … AS SELECT (CTAS)

CREATE TABLE s_copy  AS SELECT * FROM s WHERE v > 10;   -- 구조 + 데이터 복사
CREATE TABLE s_empty AS SELECT * FROM s WHERE 1 = 0;    -- 구조만 복사 (0행)

원본 s 는 id INTEGER PRIMARY KEY, v INTEGER NOT NULL DEFAULT 1 CHECK (v > 0) 였다. 복사본의 열 정보를 데이터 사전(§11)에서 보면:

tablecolumndata_typeis_nullablecolumn_default
sidintegerNO
svintegerNO1
s_copyidintegerYES
s_copyvintegerYES

열 이름과 타입만 복사되고 PK·NOT NULL·CHECK·DEFAULT·FK·인덱스는 모두 빠진다. 그래서 INSERT INTO s_copy VALUES (2, -5), (2, NULL) 이 성공한다 (id 중복, 음수, NULL 모두 통과). SQLite 도 CREATE TABLE c(id INT, v INT) 로 제약 없이 만든다.

Oracle 은 예외적으로 NOT NULL 만 복사한다. 시험에서 "CTAS 로 복사하면 제약 조건은 복사되지 않는다(Oracle 은 NOT NULL 제외)"로 나온다. WHERE 1 = 0 은 "항상 거짓" 조건으로 빈 껍데기 테이블을 만드는 관용구다.


8. VIEW — 이름 붙인 SELECT

뷰는 데이터를 저장하지 않는 가상 테이블이다. DBMS 는 뷰의 정의(SELECT 문)만 데이터 사전에 저장해 두고, 누가 뷰를 조회하면 그 SELECT 를 그 자리에서 펼쳐(치환해) 실행한다. 그래서 뷰의 동작은 모두 "뷰 이름 = 괄호 친 서브쿼리"로 이해하면 된다.

8.1 생성·변경·삭제

CREATE [OR REPLACE] VIEW 뷰이름 [(열별칭, …)] AS
    SELECT …
    [WITH CHECK OPTION];
DROP VIEW 뷰이름 [CASCADE | RESTRICT];
CREATE VIEW v_dev AS
    SELECT eno, ename, sal FROM 사원 WHERE dno = 10;
SELECT * FROM v_dev ORDER BY eno;
enoenamesal
1김하늘500
2이도윤350
3박서준280
  • CREATE OR REPLACE VIEW — 있으면 정의를 바꾸고 없으면 만든다. 뷰를 지웠다 다시 만들면 권한·의존 객체가 날아가므로 이 방식을 쓴다. PostgreSQL 은 열을 뒤에 추가하는 변경만 허용한다(eno, ename, sal, job 은 OK, eno, ename 으로 줄이면 cannot drop columns from view).
  • 표준 SQL 에는 ALTER VIEW 로 정의를 바꾸는 문법이 없다 — 정의 변경은 OR REPLACE 또는 DROP 후 CREATE. (ALTER VIEW … RENAME 같은 부가 기능은 벤더별로 있다.)

8.2 기반 테이블 변경이 즉시 보인다

UPDATE 사원 SET sal = 600 WHERE eno = 1;
SELECT * FROM v_dev WHERE eno = 1;       -- 김하늘 | 600

뷰가 결과를 저장하지 않으니 조회할 때마다 최신 데이터가 나온다. (결과를 실제로 저장해 두는 것은 구체화 뷰(materialized view)로, REFRESH 해야 갱신된다 — 일반 뷰와 구분.)

8.3 뷰를 통한 갱신

단순한 뷰는 뷰에 UPDATE·INSERT 하면 기반 테이블이 바뀐다.

UPDATE v_dev SET sal = 290 WHERE eno = 3;
SELECT eno, sal FROM 사원 WHERE eno = 3;     -- 3 | 290

갱신 가능한 뷰의 조건 (표준·PostgreSQL 자동 갱신 기준) — 뷰의 한 행이 기반 테이블의 정확히 한 행에 대응해야 한다.

갱신 불가 요인이유
GROUP BY·HAVING·집계 함수한 행 = 여러 원본 행의 요약 → 어느 행을 고칠지 모름
DISTINCT중복 제거로 원본 행과 1:1 이 깨짐
조인(FROM 에 테이블 둘 이상)어느 테이블을 고칠지 모호 (DBMS 별로 일부 허용)
UNION 등 집합 연산행의 출처가 여럿
계산식·상수 열 (sal * 12)그 열은 값을 거꾸로 되돌릴 수 없음 (다른 열은 갱신 가능)
INSERT 시 뷰에 없는 열이 NOT NULL(DEFAULT 없음)그 열을 채울 방법이 없음
CREATE VIEW v_dept_sum AS
    SELECT dno, COUNT(*) AS cnt, SUM(sal) AS total FROM 사원 GROUP BY dno;
UPDATE v_dept_sum SET total = 0 WHERE dno = 10;
-- ERROR:  cannot update view "v_dept_sum"
-- DETAIL:  Views containing GROUP BY are not automatically updatable.

SQLite 의 뷰는 전부 읽기 전용이다(cannot modify v because it is a view). INSTEAD OF 트리거를 달아야 갱신된다.

8.4 WITH CHECK OPTION

뷰의 WHERE 를 벗어나는 행을 뷰를 통해 넣으면 어떻게 될까?

INSERT INTO v_dev VALUES (20, '외부', 100);          -- 성공 (dno 는 뷰에 없으니 NULL)
SELECT eno, ename, dno FROM 사원 WHERE eno = 20;     -- 20 | 외부 | NULL
SELECT * FROM v_dev WHERE eno = 20;                  -- 0행!

뷰로 넣은 행이 뷰에서 바로 사라진다(dno 가 10 이 아니므로). 이런 "보이지 않는 곳으로의 쓰기"를 막는 것이 WITH CHECK OPTION 이다 — 뷰를 통한 INSERT·UPDATE 결과가 뷰의 WHERE 조건을 만족해야만 허용한다.

CREATE VIEW v_dev2 AS
    SELECT eno, ename, sal, dno FROM 사원 WHERE dno = 10
    WITH CHECK OPTION;

INSERT INTO v_dev2 VALUES (21, '체크', 100, 20);    -- dno 20 → 거부
-- ERROR:  new row violates check option for view "v_dev2"
INSERT INTO v_dev2 VALUES (21, '체크', 100, 10);    -- dno 10 → 성공
UPDATE v_dev2 SET dno = 20 WHERE eno = 21;          -- 뷰 밖으로 내보내기 → 거부
-- ERROR:  new row violates check option for view "v_dev2"
뷰를 통한 작업CHECK OPTION 없음WITH CHECK OPTION
조건 밖 값 INSERT성공 (뷰에선 안 보임)거부
조건 밖으로 UPDATE성공 (행이 뷰에서 사라짐)거부
조건 안 값 INSERT·UPDATE성공성공
기반 테이블에 직접 INSERT성공성공 (CHECK OPTION 은 뷰를 거칠 때만 검사)

8.5 뷰 위의 뷰와 의존성

CREATE VIEW v_big AS SELECT * FROM v_dept_sum WHERE total >= 1100;
SELECT * FROM v_big;
dnocnttotal
1031130

(원본 데이터 기준: v_dept_sum 은 10 → 3명·1130, 20 → 3명·1050, NULL → 1명·250.) 김하늘의 급여를 100 내리면 v_dept_sum 의 10 번 합계가 1030 이 되고 v_big 은 0행이 된다 — 두 겹으로 펼쳐져도 여전히 실시간이다.

DROP VIEW v_dept_sum;
-- ERROR:  cannot drop view v_dept_sum because other objects depend on it
-- DETAIL:  view v_big depends on view v_dept_sum
DROP VIEW v_dept_sum CASCADE;     -- v_big 까지 함께 삭제

§7.2 의 테이블 DROP 과 같은 규칙이다. 기반 테이블을 CASCADE 로 지우면 그 위의 뷰들이 줄줄이 지워진다.

8.6 장단점

장점설명
논리적 독립성테이블 구조가 바뀌어도 뷰 정의만 고치면 뷰를 쓰는 프로그램은 그대로
보안급여 열을 뺀 뷰만 권한을 주면(GRANT SELECT ON v_dev …) 민감 열을 숨길 수 있다
편의성복잡한 조인·조건을 한 번 정의해 두고 테이블처럼 재사용
여러 사용자의 다른 관점같은 데이터를 부서별·용도별로 다르게 보여 줌
단점설명
독자적 인덱스 불가뷰는 저장된 데이터가 없으니 CREATE INDEX … ON v_dev 불가 ("v_dev" is not a table or materialized view)
정의 변경 불가ALTER VIEW 로 정의를 고칠 수 없다 → DROP 후 재생성 / OR REPLACE
갱신 제약§8.3 의 조건을 벗어나면 INSERT·UPDATE·DELETE 불가
성능 착시뷰 조회도 매번 원본 SELECT 를 실행한다(빨라지지 않는다)

9. INDEX — 정렬된 찾아보기 표

9.1 문법

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블 (열 [ASC|DESC] [, 열 …]);
DROP INDEX 인덱스명;                  -- 표준·PG·Oracle
DROP INDEX 인덱스명 ON 테이블;        -- MySQL·SQL Server

UNIQUE INDEX 는 중복 값을 막는다 — PK·UNIQUE 제약을 걸면 DBMS 가 내부적으로 이것을 자동 생성한다(§3 표). 인덱스는 테이블과 별개의 객체라 테이블에는 영향이 없고, 지워도 데이터는 그대로다. 대신 INSERT·UPDATE·DELETE 때마다 인덱스도 고쳐야 해서 쓰기가 느려진다.

9.2 B-트리 개념

책 뒤의 "찾아보기"처럼 키 값을 정렬해 두고, 각 키 옆에 그 행의 위치(PG: TID, Oracle: ROWID) 를 적어 둔 구조다.

단계담긴 것 (name 인덱스 예)
루트 노드[ … | 'user5' | … ] → 범위별 자식 포인터
브랜치 노드[ 'user3' | 'user4' | … ] → 다음 단계 포인터
리프 노드('user777', 행 위치) ('user7770', 행 위치) … 정렬된 키 + 행 위치, 옆 리프와 연결
  • 모든 리프의 깊이가 같은 균형 트리라 20만 행이어도 3~4 단계면 찾는다 — O(log n). 인덱스가 없으면 전체를 읽는 Full/Seq Scan, O(n).
  • 리프가 정렬·연결돼 있어 = 뿐 아니라 범위(BETWEEN, <), 접두 일치(LIKE 'abc%'), ORDER BY 에도 쓰인다.

9.3 인덱스가 안 쓰이는 경우 — EXPLAIN 으로 확인

20만 행 테이블을 만들고 실행 계획을 보자(행이 적으면 옵티마이저가 인덱스 대신 Seq Scan 을 고르므로 충분히 넣는다).

CREATE TABLE big (id INTEGER PRIMARY KEY, name VARCHAR(20), code VARCHAR(10), dno INTEGER);
INSERT INTO big SELECT g, 'user' || g, (g % 1000)::text, g % 50
FROM generate_series(1, 200000) g;
CREATE INDEX ix_big_name ON big (name);
CREATE INDEX ix_big_code ON big (code);
ANALYZE big;
EXPLAIN (COSTS OFF) SELECT * FROM big WHERE name = 'user777';
WHERE 조건실행 계획 (PostgreSQL 14)이유
name = 'user777'Index Scan using ix_big_name정렬된 키로 바로 찾음
UPPER(name) = 'USER777'Seq Scan · Filter: upper(name)열을 가공하면 인덱스의 정렬 값과 다른 값이 됨
name LIKE '%777'Seq Scan앞이 % 면 어디서 시작할지 모름 (전화번호부에서 "끝이 777" 찾기)
name <> 'user777'Seq Scan부정 조건 — 거의 전부가 해당돼 인덱스를 타는 이득이 없음
CAST(code AS INTEGER) = 7Seq Scan · Filter: (code)::integer = 7형 변환이 열 쪽에 걸림 (가공과 같음)
code = '7'Bitmap Index Scan on ix_big_code타입이 맞으면 사용

해결책도 원리와 같다 — 열 쪽을 건드리지 말고 값 쪽을 바꾼다(sal * 12 > 6000 → sal > 500). 정말 가공이 필요하면 함수 기반 인덱스를 만든다. CREATE INDEX ix_big_upper ON big (UPPER(name)); 후에는 UPPER(name) = 'USER777' 도 Bitmap Index Scan 이 된다.

엔진 차이 — MySQL·Oracle 은 code = 7(문자 열 vs 숫자)을 열 쪽을 숫자로 암묵 변환해 실행하므로 인덱스를 못 탄다(시험의 "형 변환" 사례). PostgreSQL 은 아예 operator does not exist: character varying = integer 오류를 낸다. 또 PG 는 기본 정렬 규칙(로캘)이 C 가 아니면 LIKE 'user777%' 에도 보통 인덱스를 못 쓰고, CREATE INDEX … (name varchar_pattern_ops) 로 만들어야 Index Scan 이 된다(확인함). 부정 조건도 "무조건 못 쓴다"가 아니라 "대부분의 경우 이득이 없어 안 쓴다"가 정확하다.

9.4 복합 인덱스와 선두 열

CREATE INDEX ix_big_dno_name ON big (dno, name);   -- (dno 로 먼저 정렬, 같은 dno 안에서 name 정렬)
WHERE 조건 (ix_big_name 을 지운 뒤)실행 계획
dno = 7 AND name = 'user7'Index Scan using ix_big_dno_name
name = 'user7' (선두 열 없음)Seq Scan

전화번호부가 (성, 이름) 순으로 정렬돼 있으면 "이름이 민준인 사람"은 처음부터 끝까지 봐야 한다. 복합 인덱스는 선두 열 조건이 있어야 제대로 쓰인다 — 그래서 열 순서는 "항상 조건에 들어가는 열, 그리고 = 로 비교되는 열"을 앞에 둔다.

9.5 클러스터드 vs 넌클러스터드

클러스터드(Clustered) 인덱스넌클러스터드(Non-clustered) 인덱스
데이터 저장테이블 행 자체가 인덱스 키 순서대로 물리 정렬행은 따로, 인덱스는 키 + 행 위치만
테이블당하나 (물리 순서는 하나뿐)여러 개
비유사전 — 본문이 가나다순책 뒤 찾아보기 — 쪽 번호로 다시 찾아감
범위 검색매우 빠름(연속 블록)키마다 행을 찾아가야 함
대표MySQL InnoDB 의 PK, SQL Server 의 PK(기본)그 외 보조 인덱스

PostgreSQL 은 테이블이 힙(입력 순)이고 모든 인덱스가 넌클러스터드다. CLUSTER 명령으로 한 번 정렬할 수는 있지만 이후 입력은 다시 흐트러진다.


10. 시퀀스 / AUTO_INCREMENT / IDENTITY / SERIAL

번호를 자동으로 매기는 장치다. 표준은 시퀀스 객체와 IDENTITY 열 두 가지.

CREATE SEQUENCE seq_eno START WITH 100 INCREMENT BY 10;
SELECT nextval('seq_eno') AS a, nextval('seq_eno') AS b, currval('seq_eno') AS c;   -- PostgreSQL
abc
100110110

nextval 은 다음 값을 꺼내며 증가시키고, currval 은 이 세션에서 마지막으로 꺼낸 값을 다시 보여 준다. 꺼낸 번호는 롤백해도 돌아오지 않는다 — 시퀀스 번호에 빈 번호(gap)가 생기는 것은 정상이다.

DBMS문법다음 값
표준 / PG 10+ / Oracle 12c+id INTEGER GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY자동
PostgreSQL (옛 방식)id SERIAL (= INTEGER + 시퀀스 + DEFAULT nextval)자동
OracleCREATE SEQUENCE s …; → INSERT … VALUES (s.NEXTVAL, …)s.NEXTVAL, s.CURRVAL
PostgreSQL위와 같음nextval('s'), currval('s')
MySQLid INT AUTO_INCREMENT PRIMARY KEY자동, LAST_INSERT_ID()
SQLiteid INTEGER PRIMARY KEY [AUTOINCREMENT]자동 (rowid)
SQL Serverid INT IDENTITY(1,1)자동

GENERATED ALWAYS 열에 값을 직접 넣으면 거부된다 — cannot insert a non-DEFAULT value into column "id"(PG). BY DEFAULT 는 직접 넣는 값을 허용한다.


11. 시스템 카탈로그(데이터 사전)

테이블·열·제약·뷰·인덱스·사용자·권한 같은 메타데이터(데이터에 관한 데이터) 를 DBMS 가 스스로 저장하는 테이블 모음이다.

  • DDL 을 실행하면 DBMS 가 카탈로그를 자동으로 갱신한다. 사용자는 SELECT 로 조회만 하고 직접 INSERT·UPDATE 하지 않는다.
  • 카탈로그도 테이블이므로 SQL 로 조회한다. 표준은 INFORMATION_SCHEMA 스키마의 뷰들.
SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = '부서'
ORDER BY ordinal_position;
table_namecolumn_namedata_typeis_nullable
부서dnointegerNO
부서dnamecharacter varyingNO
부서loccharacter varyingYES

information_schema.table_constraints 에서는 §2.2 에서 붙인 이름 pk_사원(PRIMARY KEY), fk_사원_부서·fk_사원_mgr(FOREIGN KEY), ck_bonus(CHECK) 가 그대로 보인다.

DBMS카탈로그 조회
표준 / PG / MySQLINFORMATION_SCHEMA.TABLES, .COLUMNS, .TABLE_CONSTRAINTS, .VIEWS
PostgreSQL 고유pg_catalog.pg_class, pg_attribute, pg_indexes / psql \d 테이블
OracleUSER_TABLES, USER_TAB_COLUMNS, USER_CONSTRAINTS, USER_VIEWS (ALL_·DBA_ 접두는 범위만 넓힘)
MySQLSHOW TABLES, DESC 테이블, SHOW INDEX FROM 테이블
SQLitesqlite_master(정의 SQL 저장), PRAGMA table_info('t')

확인 문제

  1. CREATE TABLE u (k INTEGER UNIQUE); 에 INSERT INTO u VALUES (NULL); (NULL) (1) (1) 을 한 문장씩 실행했다. 남는 행 수는?
  2. (O/X) sal INTEGER CHECK (sal > 0) 열에 NULL 을 INSERT 하면 CHECK 위반 오류가 난다.
  3. §4.1 의 d2·e2 에서 ON DELETE SET NULL 로 FK 를 건 뒤 DELETE FROM d2 WHERE dno = 10; 을 실행했다. e2 의 (eno, dno) 를 모두 쓰라.
  4. MySQL 에서 사원.ename 을 VARCHAR(40) 으로 바꾸는 문장의 빈칸: ALTER TABLE 사원 ( ① ) ename VARCHAR(40);
  5. 다음 설명에 해당하는 명령은? — "테이블 구조는 남기고 모든 행을 지운다. WHERE 를 쓸 수 없고, Oracle 에서는 롤백할 수 없다."
  6. §8.4 의 v_dev2(WHERE dno = 10 WITH CHECK OPTION)에 INSERT INTO v_dev2 VALUES (30, '테스트', 100, 20); 을 실행하면?
  7. name 열에 B-트리 인덱스가 있다. 인덱스를 쓰기 어려운 조건을 모두 고르라. ㉠ name = '김하늘' ㉡ SUBSTR(name, 1, 1) = '김' ㉢ name LIKE '%늘' ㉣ name BETWEEN '가' AND '나'
  8. (O/X) CREATE TABLE 사원2 AS SELECT * FROM 사원; 으로 만든 사원2 에는 사원의 PRIMARY KEY 가 그대로 복사된다.

정답과 풀이

  1. 3행 — NULL 끼리는 같다고 판정되지 않으므로(NULL = NULL → UNKNOWN) 두 NULL 모두 들어간다. 두 번째 1 만 중복 오류. (SQL Server 라면 2행)
  2. X — NULL > 0 은 UNKNOWN 이고, CHECK 는 FALSE 일 때만 거부한다. NULL 을 막으려면 NOT NULL 을 함께 건다.
  3. (1, NULL), (2, NULL), (4, 20) — 부서 10 을 참조하던 두 행의 FK 만 NULL 이 되고 행은 남는다. CASCADE 였다면 (4, 20) 한 행만 남는다.
  4. ① MODIFY — 표준·PG 는 ALTER COLUMN ename TYPE VARCHAR(40), Oracle 은 MODIFY (ename VARCHAR2(40)).
  5. TRUNCATE — DELETE 는 WHERE 가능·롤백 가능, DROP 은 구조까지 삭제. (PostgreSQL 은 트랜잭션 안의 TRUNCATE 도 롤백된다.)
  6. 오류(new row violates check option) — 넣는 행의 dno 20 이 뷰 조건 dno = 10 을 만족하지 않는다. CHECK OPTION 이 없었다면 들어가되 뷰에서는 안 보인다.
  7. ㉡, ㉢ — ㉡ 열을 함수로 가공, ㉢ 앞이 %. ㉣ 범위 조건은 정렬된 리프를 따라가므로 인덱스에 잘 맞는다.
  8. X — CTAS 는 열 이름·타입과 데이터만 복사한다. PK·UNIQUE·CHECK·FK·DEFAULT·인덱스는 빠진다(Oracle 은 NOT NULL 만 복사). 필요하면 ALTER TABLE 사원2 ADD PRIMARY KEY (eno);.

한 줄 요약

주제기억할 한 줄
CHAR vs VARCHARCHAR 는 고정 길이로 공백 패딩, 비교 시 뒤 공백 무시. VARCHAR 는 가변(Oracle VARCHAR2)
NUMERIC(p, s)p = 전체 자릿수, s = 소수 자릿수. 소수부 초과는 반올림, 정수부 초과는 오류
열/테이블 수준복합 키·열끼리 비교 CHECK 는 테이블 수준, NOT NULL 은 열 수준만
UNIQUE / PKUNIQUE 는 NULL 여러 개 허용(SQL Server 제외), PK = UNIQUE + NOT NULL, 테이블당 하나
CHECKFALSE 일 때만 거부 — UNKNOWN(NULL) 은 통과
DEFAULT열을 생략했을 때만 적용, NULL 을 명시하면 NULL
FK 참조 동작CASCADE 같이 삭제 · SET NULL · SET DEFAULT · RESTRICT 즉시 거부 · NO ACTION 문장 끝(지연 가능) 거부
무결성개체 = PK 는 NULL·중복 불가 · 참조 = FK 는 부모 값 또는 NULL · 도메인 = 타입·CHECK
ALTER표준·PG ALTER COLUMN … TYPE, Oracle·MySQL MODIFY. 기존 데이터가 어기면 제약 추가 실패
DELETE / TRUNCATE / DROPDML·WHERE·트리거 / DDL·전체 행·구조 유지 / DDL·구조까지. Oracle 은 DDL 롤백 불가, PG 는 가능
DROP CASCADE의존 객체(뷰 등)까지 삭제. RESTRICT(기본)는 의존 객체 있으면 거부
CTAS데이터·타입만 복사, 제약·인덱스는 복사 안 됨. WHERE 1 = 0 이면 빈 구조
VIEW저장된 SELECT, 항상 최신. 독립성·보안·편의 / 인덱스 불가·ALTER 불가·갱신 제약
WITH CHECK OPTION뷰를 통한 INSERT·UPDATE 결과가 뷰 조건을 벗어나면 거부
INDEXB-트리 = 정렬 키 + 행 위치. 열 가공·앞 %·부정·형 변환이면 못 씀, 복합 인덱스는 선두 열부터
클러스터드행 자체가 키 순서로 저장, 테이블당 하나 (InnoDB PK)
자동 번호표준 IDENTITY / PG SERIAL·nextval / Oracle 시퀀스.NEXTVAL / MySQL AUTO_INCREMENT
데이터 사전DBMS 가 자동 관리하는 메타데이터, 사용자는 조회만 (INFORMATION_SCHEMA, Oracle USER_*)
← SQL ⑥ 윈도 함수SQL ⑧ DML·트랜잭션·권한 →