정보처리기사 실기 — 2. 데이터베이스 구축

정처기 실기 6과목 중 데이터베이스 구축 정리.
이번 글은 하위 영역 중 SQL 응용(SQL 작성·절차형 SQL)에 집중.
논리/물리 설계, 데이터 전환, 무결성·보안은 추후 추가 예정.

---

0. 과목 구조 한눈에 (시험용 지도)

---

1. SQL 개념

• SQL(Structured Query Language): 관계형 DBMS에서 데이터를 정의·조작·제어하는 국제 표준 언어.
• 비절차적(선언적): "어떻게"가 아니라 "무엇을" 원하는지 기술 — DBMS가 실행 계획을 결정.
• 실기에서는 문법 분류, SELECT·DML 작성, 절차형 SQL(프로시저·트리거) 작성이 핵심.

1.1 SQL 4분류 ★

• 시험 트릭: TRUNCATE는 교재에 따라 DDL로 분류 — 지문의 분류 기준을 따른다.
• 트리거는 DCL을 사용할 수 없음 — DCL 포함 프로시저/함수 호출 시 오류.

---

2. DDL — 데이터 정의어

• DB 구조·형식·접근 방식을 정의·변경·삭제.

2.1 CREATE

테이블 생성 예시

CREATE TABLE EMP (
  EMP_NO   INT          PRIMARY KEY,
  EMP_NM   VARCHAR(20)  NOT NULL,
  DEPT_CD  CHAR(3)      REFERENCES DEPT(DEPT_CD),
  SALARY   NUMBER(10,2) CHECK (SALARY >= 0)
);

제약 조건(실기에서 자주 등장)

2.2 ALTER / DROP

-- 컬럼 추가
ALTER TABLE EMP ADD EMAIL VARCHAR(50);

-- 컬럼 타입 변경
ALTER TABLE EMP MODIFY SALARY NUMBER(12,2);

-- 테이블 삭제
DROP TABLE EMP;

-- 인덱스 삭제
DROP INDEX IDX_EMP_NM;

2.3 VIEW(뷰)

• 하나 이상의 기본 테이블로부터 유도되는 가상 테이블.
• 용도: 복잡 질의 단순화, 접근 통제(민감 컬럼 숨김).
• 갱신 가능 뷰는 제약이 많음 — 단순 뷰만 INSERT/UPDATE 가능한 경우가 많다.

CREATE VIEW V_EMP_DEPT AS
SELECT E.EMP_NO, E.EMP_NM, D.DEPT_NM
FROM   EMP E
JOIN   DEPT D ON E.DEPT_CD = D.DEPT_CD;

2.4 INDEX(인덱스)

• 검색 시간 단축을 위한 보조 데이터 구조.
• 장점: SELECT 속도 향상.
• 단점: INSERT/UPDATE/DELETE 비용↑, 저장 공간↑ — 트레이드오프.

CREATE INDEX IDX_EMP_DEPT ON EMP(DEPT_CD);

---

3. DML — 데이터 조작어 (SELECT 작성)

3.1 SELECT 기본 구조 ★

SELECT   [DISTINCT] 컬럼1, 컬럼2, ...
FROM     테이블 [별칭]
[WHERE   조건]
[GROUP BY 그룹컬럼]
[HAVING  그룹조건]
[ORDER BY 정렬컬럼 [ASC|DESC]]

실행 순서(암기용): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

3.2 WHERE 절 — 조건 작성

-- 비교·논리
SELECT * FROM EMP WHERE SALARY >= 3000 AND DEPT_CD = 'D01';

-- IN / NOT IN
SELECT * FROM EMP WHERE DEPT_CD IN ('D01', 'D02', 'D03');

-- LIKE (와일드카드: % = 0개 이상, _ = 1문자)
SELECT * FROM EMP WHERE EMP_NM LIKE '김%';

-- BETWEEN
SELECT * FROM EMP WHERE SALARY BETWEEN 2000 AND 5000;

-- NULL
SELECT * FROM EMP WHERE EMAIL IS NULL;
SELECT * FROM EMP WHERE EMAIL IS NOT NULL;

3.3 ORDER BY · DISTINCT

-- 정렬 (ASC 기본, DESC 내림차순)
SELECT EMP_NM, SALARY FROM EMP ORDER BY SALARY DESC, EMP_NM ASC;

-- 중복 제거
SELECT DISTINCT DEPT_CD FROM EMP;

3.4 집계 함수 ★

SELECT DEPT_CD,
       COUNT(*)     AS CNT,
       AVG(SALARY)  AS AVG_SAL,
       MAX(SALARY)  AS MAX_SAL
FROM   EMP
GROUP BY DEPT_CD
HAVING AVG(SALARY) > 3000;

• WHERE: 그룹화 전 행 필터.
• HAVING: 그룹화 후 그룹 조건 — 집계 함수 조건에 사용.

3.5 조인(Join) ★

-- INNER JOIN
SELECT E.EMP_NM, D.DEPT_NM
FROM   EMP E
INNER JOIN DEPT D ON E.DEPT_CD = D.DEPT_CD;

-- LEFT JOIN (부서 없는 사원도 포함)
SELECT E.EMP_NM, D.DEPT_NM
FROM   EMP E
LEFT JOIN DEPT D ON E.DEPT_CD = D.DEPT_CD;

-- SELF JOIN (같은 부서 동료)
SELECT A.EMP_NM, B.EMP_NM AS COLLEAGUE
FROM   EMP A
JOIN   EMP B ON A.DEPT_CD = B.DEPT_CD AND A.EMP_NO <> B.EMP_NO;

• 외부 조인 핵심: 매칭 실패 시 NULL로 채움.

3.6 서브쿼리 ★

스칼라 서브쿼리 — 결과 1행 1열

SELECT EMP_NM, SALARY
FROM   EMP
WHERE  SALARY > (SELECT AVG(SALARY) FROM EMP);

IN / NOT IN

SELECT * FROM EMP
WHERE DEPT_CD IN (SELECT DEPT_CD FROM DEPT WHERE LOC = '서울');

EXISTS — 존재 여부(상관 서브쿼리와 자주 출제)

SELECT * FROM DEPT D
WHERE EXISTS (
  SELECT 1 FROM EMP E WHERE E.DEPT_CD = D.DEPT_CD
);

서브쿼리 vs JOIN: 같은 결과도 JOIN으로, EXISTS로 풀 수 있음 — 문제에서 요구하는 형태 확인.

3.7 집합 연산

SELECT EMP_NM FROM EMP WHERE DEPT_CD = 'D01'
UNION
SELECT EMP_NM FROM EMP WHERE SALARY > 5000;

---

4. DML — INSERT · UPDATE · DELETE

4.1 INSERT

-- 단일 행
INSERT INTO EMP (EMP_NO, EMP_NM, DEPT_CD, SALARY)
VALUES (1001, '홍길동', 'D01', 3500);

-- 서브쿼리로 다중 삽입
INSERT INTO EMP_BACKUP
SELECT * FROM EMP WHERE DEPT_CD = 'D01';

4.2 UPDATE

UPDATE EMP
SET    SALARY = SALARY * 1.1,
       DEPT_CD = 'D02'
WHERE  DEPT_CD = 'D01';

• WHERE 생략 시 전체 행 갱신 — 실기·실무 모두 주의.

4.3 DELETE

DELETE FROM EMP WHERE DEPT_CD = 'D03';

-- TRUNCATE (DDL로 분류되는 경우 많음) — 전체 삭제, 롤백 불가(제품·설정에 따라)
TRUNCATE TABLE EMP_BACKUP;

---

5. DCL · TCL

5.1 DCL — 권한

-- 권한 부여
GRANT SELECT, INSERT ON EMP TO user1;
GRANT ALL PRIVILEGES ON EMP TO user1 WITH GRANT OPTION;

-- 권한 회수
REVOKE INSERT ON EMP FROM user1;
REVOKE SELECT ON EMP FROM user1 CASCADE;

• WITH GRANT OPTION: 받은 권한을 다른 사용자에게 재부여 가능.
• CASCADE: 해당 권한으로 부여된 연쇄 권한도 함께 회수.

5.2 TCL — 트랜잭션

BEGIN;          -- 또는 START TRANSACTION
UPDATE EMP SET SALARY = SALARY * 1.05 WHERE DEPT_CD = 'D01';
SAVEPOINT sp1;
DELETE FROM EMP WHERE EMP_NO = 9999;
ROLLBACK TO sp1;  -- sp1 이후만 취소
COMMIT;           -- 확정
-- ROLLBACK;      -- 전체 취소

ACID

---

6. 고급 SQL — 그룹·윈도 함수

6.1 ROLLUP · CUBE · GROUPING SETS

SELECT DEPT_CD, JOB, SUM(SALARY)
FROM   EMP
GROUP BY ROLLUP(DEPT_CD, JOB);

6.2 윈도우 함수(Window Function) ★

• OLAP 용도 — 행을 유지하면서 집계·순위 계산.

SELECT EMP_NM, DEPT_CD, SALARY,
       RANK()       OVER (PARTITION BY DEPT_CD ORDER BY SALARY DESC) AS SAL_RANK,
       ROW_NUMBER() OVER (PARTITION BY DEPT_CD ORDER BY SALARY DESC) AS ROW_NUM,
       SUM(SALARY)  OVER (PARTITION BY DEPT_CD)                      AS DEPT_TOTAL
FROM   EMP;

윈도 함수 분류

• RANK(): 동점 시 같은 순위, 다음 순위 건너뜀(1,1,3).
• DENSE_RANK(): 동점 시 같은 순위, 다음 순위 연속(1,1,2).
• ROW_NUMBER(): 동점이어도 고유 번호.

---

7. 절차형 SQL ★

• 실행 순서를 정해 반복·분기·예외 처리를 하는 SQL.

7.1 저장 프로시저(Stored Procedure)

• DBMS에 저장·컴파일된 절차 — EXECUTE/CALL로 호출.
• 트랜잭션 단위로 특정 기능 수행 — 일마감, 일괄 작업 등.
• Stored Procedure = 스토어드 프로시저.

CREATE OR REPLACE PROCEDURE SP_RAISE_SAL(
  p_dept_cd IN VARCHAR,
  p_rate    IN NUMBER
)
BEGIN
  UPDATE EMP
  SET    SALARY = SALARY * (1 + p_rate)
  WHERE  DEPT_CD = p_dept_cd;
  COMMIT;
END;
/

EXECUTE SP_RAISE_SAL('D01', 0.1);
-- CALL SP_RAISE_SAL('D01', 0.1);

7.2 트리거(Trigger)

• INSERT/UPDATE/DELETE 이벤트 발생 시 자동 실행.
• DCL 사용 불가 — 무결성 유지, 감사 로그 등.

CREATE OR REPLACE TRIGGER TRG_EMP_AUDIT
BEFORE INSERT OR UPDATE ON EMP
FOR EACH ROW
BEGIN
  :NEW.MOD_DT := SYSDATE;
END;
/

7.3 사용자 정의 함수(UDF)

• SQL문에서 호출 → 단일 값 반환.
• SELECT, INSERT, UPDATE, DELETE 등 DML 호출로 실행.

CREATE OR REPLACE FUNCTION FN_TAX(p_sal NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN p_sal * 0.033;
END;
/

SELECT EMP_NM, SALARY, FN_TAX(SALARY) AS TAX FROM EMP;

7.4 제어문·커서(개념)

• IF / CASE / LOOP / WHILE — 절차형 흐름 제어.
• 커서(Cursor): SELECT 결과를 한 행씩 순회 처리.

---

8. DBMS 접속·동적 SQL·ORM (개념)

• MyBatis 특징: JDBC 단순화, SQL과 Java 코드 분리, 동적 SQL 지원.
• SQL 인젝션 방지: 문자열 결합 대신 Prepared Statement(바인딩).

---

9. 실기 SQL 작성 체크리스트

문제에서 테이블·조건이 주어졌을 때 순서:

1. 무엇을 조회/변경하는지(DML 종류) 확인.
2. FROM 테이블·별칭 — 조인 필요 시 JOIN + ON 조건.
3. WHERE — 문제 조건을 그대로 옮김(NULL은 IS NULL).
4. GROUP BY — 집계 함수 쓰면 그룹 컬럼 포함.
5. HAVING — 그룹 조건(집계 함수 조건).
6. ORDER BY — 정렬 요구 확인.
7. INSERT/UPDATE/DELETE — 대상 컬럼·WHERE 누락 여부 재확인.

자주 틀리는 포인트

• WHERE vs HAVING — 행 필터 vs 그룹 필터.
• 외부 조인 방향 — 기준 테이블이 LEFT인지 RIGHT인지.
• COUNT(*) vs COUNT(컬럼) — NULL 처리 차이.
• UNION vs UNION ALL — 중복 제거 여부.
• 서브쿼리 단일 행 가정 — 다중 행이면 IN/EXISTS 사용.

---

10. 시험 포인트(암기 체크)

• SQL DDL·DML·DCL·TCL 4분류 — 명령어 매칭 문제가 매우 흔함.
• WHERE(그룹 전) vs HAVING(그룹 후) — 집계 조건 위치.
• INNER vs LEFT JOIN — 매칭 실패 시 NULL·행 누락 차이.
• 프로시저(호출 실행) vs 트리거(이벤트 자동) vs UDF(단일 값 반환).
• 트리거는 DCL 불가 — 지문 오답으로 자주 등장.
• RANK / DENSE_RANK / ROW_NUMBER — 동점 처리 차이.
• 인덱스 — SELECT↑, INSERT/UPDATE/DELETE↓ 트레이드오프.
• GRANT … WITH GRANT OPTION, REVOKE … CASCADE — 권한 연쇄.
• Prepared Statement — SQL 인젝션 대응 정답.

---

11. 스스로 검토할 때 쓰는 오답 노트(빈칸)

1. 테이블 구조를 정의하는 SQL 분류는 (　　　) 이다.
2. GROUP BY 이후 그룹 조건은 (　　　) 절을 사용한다.
3. 양쪽 테이블에 매칭되는 행만 반환하는 조인은 (　　　) JOIN 이다.
4. INSERT/UPDATE/DELETE 이벤트 시 자동 실행되는 절차형 SQL은 (　　　) 이다.
5. 동점 시 순위를 건너뛰지 않는 순위 함수는 (　　　) 이다.

---