[SQLD 요약노트] 2과목: SQL 활용+관리 구문

자격증 시험준비

[SQLD 요약노트] 2과목: SQL 활용+관리 구문 – 핵심 내용 총정리

노랑별이 2025. 5. 15. 19:41

안녕하세요!

오늘은 SQLD 2과목 중 SQL 활용과 관리구문 부분을 공부한 내용을 정리해보려고 합니다. 시험 준비와 향후 복습에 도움이 되길 바랍니다.

1. SQL 활용

1) 서브쿼리

서브쿼리는 하나의 쿼리문(메인 쿼리) 안에 포함된 또 다른 쿼리문(서브 쿼리) 입니다. 서브쿼리를 활용하면 복잡한 조건이나 계산을 효율적으로 처리할 수 있습니다.

유형	설명
단일행 서브쿼리	한 개의 값 반환 (예: =, > 등과 함께 사용)
다중행 서브쿼리	여러 행 반환 (IN: 목록에 포함, ANY: 하나라도 만족, ALL: 모든조건 만족, EXISTS: 결과가 존재)
다중열 서브쿼리	여러 컬럼 반환
스칼라 서브쿼리	SELECT절 안에 위치해 1개 컬럼처럼 사용
인라인 뷰	FROM절에서 임시 테이블처럼 사용 (장점 - 보안성, 독립성, 편의성)
상호연관 서브쿼리	외부 쿼리의 각 행마다 서브쿼리가 실행됨

# 단일행 서브쿼리
SELECT 이름 FROM 직원 WHERE 급여 > (SELECT AVG(급여) FROM 직원);

# 다중열 서브쿼리
SELECT * FROM 직원 WHERE (부서, 직급) IN (SELECT 부서, 직급 FROM 승진대상);

# 다중행 서브쿼리
SELECT * FROM 직원 WHERE 부서 IN (SELECT 부서코드 FROM 부서 WHERE 지역 = '서울');

# 스칼라 서브쿼리
SELECT 이름, (SELECT 부서명 FROM 부서 WHERE 부서.부서코드 = 직원.부서코드) AS 부서 FROM 직원;

# 인라인 뷰
SELECT a.사원명, b.평균급여
FROM 직원 a, (SELECT 부서코드, AVG(급여) AS 평균급여 FROM 직원 GROUP BY 부서코드) b
WHERE a.부서코드 = b.부서코드;

# 상호연관 서브쿼리
SELECT * FROM 직원 a 
WHERE 급여 > (SELECT AVG(급여) FROM 직원 b WHERE b.부서코드 = a.부서코드);

2) 집합 연산자

집합 연산자는 두 개 이상의 SELECT 쿼리 결과에 대한 집합 연산(합집합, 교집합, 차집합 등)을 수행합니다. 두 집합의 스키마(컬럼 수, 컬럼 순서, 데이터 타입)이 일치해야 동작합니다.

연산자	설명
UNION	합집합 (중복 제거)
UNION ALL	합집합 (중복 허용)
INTERSECT	교집합
MINUS / EXCEPT	차집합 (Oracle은 MINUS, PostgreSQL은 EXCEPT)

# 합집합
# UNION
SELECT 컬럼1, 컬럼2 FROM 테이블1
UNION
SELECT 컬럼1, 컬럼2 FROM 테이블2;

# UNION ALL
SELECT 컬럼1, 컬럼2 FROM 테이블1
UNION ALL
SELECT 컬럼1, 컬럼2 FROM 테이블2;

# 교집합
SELECT 컬럼1, 컬럼2 FROM 테이블1
INTERSECT
SELECT 컬럼1, 컬럼2 FROM 테이블2;

# 차집합
SELECT 컬럼1, 컬럼2 FROM 테이블1
MINUS  -- Oracle
-- EXCEPT  (SQL Server, PostgreSQL)
SELECT 컬럼1, 컬럼2 FROM 테이블2;

3) 그룹 함수

그룹 함수는 GROUP BY절에서 여러 행을 하나의 결과값으로 요약하는 함수입니다. 집계함수(COUNT, SUM, AVG, MIN, MAX) 외에도 ROLLUP, CUBE, GROUPING SETS 등의 함수가 존재합니다.

ROLLUP: 소계, 총계 → 컬럼 순서 중요

SELECT 부서, 직급, SUM(급여)
FROM 직원
GROUP BY ROLLUP(부서, 직급);

# 이 경우 그룹화 순서: (부서, 직급) → 부서 → 전체 행

CUBE: 가능한 모든 조합 그룹화

SELECT 부서, 직급, SUM(급여)
FROM 직원
GROUP BY CUBE(부서, 직급);

GROUPING SETS: 원하는 조합만 그룹화

SELECT 부서, 직급, SUM(급여)
FROM 직원
GROUP BY GROUPING SETS(부서, 직급, ());

# NULL 혹은 ()는 모든 행에 대한 전체 그룹화를 수행

4) 윈도우 함수

윈도우 함수는 GROUP BY와 달리, 그룹화하지 않고 각 행에 대하여 계산된 값을 반환하는 함수입니다. OVER 키워드와 함께 사용됩니다.

각 행마다 계산된 값을 반환 (GROUP BY 없이 사용 가능)
OVER() 키워드 필수

유형	설명
순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계 함수	SUM, AVG, MIN, MAX 등
행 간 참조 함수	LAG, LEAD, FIRST_VALUE, LAST_VALUE
비율 함수	RATIO_TO_REPORT, PERCENT_RANK 등

# 순위 함수
# 순위 매기기 (동점이면 건너뜀: 1, 2, 2, 4)
SELECT 이름, 점수, RANK() OVER (ORDER BY 점수 DESC) AS 순위 FROM 학생;

# 순위 매기기 (동점이어도 순차적: 1, 2, 2, 3)
SELECT 이름, 점수, DENSE_RANK() OVER (ORDER BY 점수 DESC) AS 순위 FROM 학생;

# 단순 행 번호 (1, 2, 3, 4)
SELECT 이름, 점수, ROW_NUMBER() OVER (ORDER BY 점수 DESC) AS 번호 FROM 학생;

# PARTITION BY로 그룹별 순위
SELECT 부서, 이름, 급여, 
       RANK() OVER (PARTITION BY 부서 ORDER BY 급여 DESC) AS 부서내순위 
FROM 직원;

# 집계함수
SELECT 이름, 부서, 급여,
       SUM(급여) OVER (PARTITION BY 부서) AS 부서별총급여,
       AVG(급여) OVER (PARTITION BY 부서) AS 부서별평균급여
FROM 직원;

# 행 간 참조 함수
# 이전 행 값
SELECT 이름, 급여, LAG(급여) OVER (ORDER BY 입사일) AS 이전사원급여 FROM 직원;

# 다음 행 값
SELECT 이름, 급여, LEAD(급여) OVER (ORDER BY 입사일) AS 다음사원급여 FROM 직원;

# 첫 번째 행 값
SELECT 이름, 급여, 
       FIRST_VALUE(급여) OVER (PARTITION BY 부서 ORDER BY 입사일) AS 첫입사자급여
FROM 직원;

# 마지막 행 값 (범위 지정 필요)
SELECT 이름, 급여,
       LAST_VALUE(급여) OVER (
         PARTITION BY 부서 
         ORDER BY 입사일
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS 마지막입사자급여
FROM 직원;

윈도우 함수는 현재 행까지만 제한하기 때문에, LAST_VALUE는 범위 지정 키워드 사용이 필요

범위 지정 키워드

ROWS BETWEEN [시작 범위] AND [끝 범위]
범위 지정 생략 시, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 동작

가능한 범위 옵션

- CURRENT ROW: 현재 행
- n PRECEDING: 현재 행에서 n행 이전
- n FOLLOWING: 현재 행에서 n행 이후
- UNBOUNDED PRECEDING: 파티션의 첫 번째 행
- UNBOUNDED FOLLOWING: 파티션의 마지막 행

# 비율 함수
# 백분율 순위 (0~1 사이 값)
SELECT 이름, 점수, 
       PERCENT_RANK() OVER (ORDER BY 점수) AS 백분율순위
FROM 학생;

# 누적 분포 값
SELECT 이름, 점수,
       CUME_DIST() OVER (ORDER BY 점수) AS 누적분포
FROM 학생;

# 특정 그룹 내 비율
SELECT 이름, 부서, 급여,
       RATIO_TO_REPORT(급여) OVER (PARTITION BY 부서) AS 부서내급여비율
FROM 직원;

# 그룹을 n개로 나눈 값
SELECT 이름, 부서, 급여,
       NTILE(4) OVER (PARTITION BY 부서 ORDER BY 급여) AS 분위수
FROM 직원;

5) Top N 쿼리

Top N 쿼리는 상위 N개의 데이터를 추출하는 쿼리입니다.

방법	사용 DB
TOP N	SQL Server
LIMIT N	MySQL, PostgreSQL
FETCH FIRST N ROWS ONLY	Oracle, DB2
ROWNUM	Oracle

# SQL Server
SELECT TOP 5 이름, 급여 FROM 직원 ORDER BY 급여 DESC;

# MySQL, PostgreSQL
SELECT 이름, 급여 FROM 직원 ORDER BY 급여 DESC LIMIT 5;

# Oracle 12c 이상, DB2
SELECT 이름, 급여 FROM 직원 ORDER BY 급여 DESC FETCH FIRST 5 ROWS ONLY;

# Oracle (옛 방식)
SELECT * FROM (
  SELECT 이름, 급여, ROWNUM AS rnum
  FROM (SELECT 이름, 급여 FROM 직원 ORDER BY 급여 DESC)
) WHERE rnum <= 5;

유의사항

ORDER BY 사용은 필수 (정렬 없이 사용 시 랜덤 결과 출력)
TOP 사용 시 동일한 순위도 함께 출력하려면 WITH TIES 사용
ROWNUM 사용 시 = 또는 > 연산자는 사용 불가 (<=, < 만 사용 가능)

6) 계층형 질의와 셀프 조인

계층형 질의: 상하 구조 탐색 (예: 사원-상사 관계)

# Oracle의 계층형 질의
SELECT LEVEL, 사원번호, 이름, 관리자사원번호
FROM 직원
START WITH 관리자사원번호 IS NULL -- 최상위 노드 지정
CONNECT BY PRIOR 사원번호 = 관리자사원번호; -- 계층 관계 지정

셀프 조인: 같은 테이블 내에서 서로 연결

# 직원과 관리자 정보 조회
SELECT e.이름 AS 직원, m.이름 AS 관리자
FROM 직원 e, 직원 m
WHERE e.관리자사원번호 = m.사원번호;

7) PIVOT 절과 UNPIVOT 절

유형	설명
PIVOT	행 → 열 (Long → Wide)
UNPIVOT	열 → 행 (Wide → Long)

# PIVOT
# 부서별, 직급별 급여 합계를 가로로 표시
SELECT *
FROM (SELECT 부서, 직급, 급여 FROM 직원)
PIVOT (
  SUM(급여) FOR 직급 IN ('사원', '대리', '과장', '부장')
);

# UNPIVOT
# 분기별 판매량을 월별 판매량으로 변환
SELECT 상품, 월, 판매량
FROM 분기별판매
UNPIVOT (
  판매량 FOR 월 IN (Q1 AS '1월', Q2 AS '2월', Q3 AS '3월', Q4 AS '4월')
);

8) 정규 표현식(Oracle)

정규 표현식은 문자와 기호를 조합하여 특정 패턴을 검색하거나 일치 여부를 확인할 때 사용합니다.

함수	설명
REGEXP_LIKE	패턴 일치 여부 확인
REGEXP_REPLACE	패턴 찾아서 대체
REGEXP_INSTR	일치 시작 위치 반환
REGEXP_SUBSTR	일치 문자열 추출
REGEXP_COUNT	일치한 횟수 반환

주요 정규 표현식 기호

^: 문자열 시작
$: 문자열 끝
.: 임의의 한 문자
*: 0번 이상 반복
+: 1번 이상 반복
?: 0번 또는 1번
[]: 문자 클래스
|: 대체(OR)
(): 그룹화
\d: 숫자
\w: 영문자, 숫자, 밑줄
\s: 공백 문자

# REGEXP_LIKE
SELECT * FROM 고객 WHERE REGEXP_LIKE(전화번호, '^01[0-9]-[0-9]{4}-[0-9]{4}$');
# 예시: REGEXP_LIKE('hello123', '^[a-z]+[0-9]+$') → TRUE

# REGEXP_REPLACE
SELECT REGEXP_REPLACE('010/1234/5678', '/', '-') FROM DUAL;
# 결과: 010-1234-5678

# REGEXP_INSTR
SELECT REGEXP_INSTR('abc123def', '[0-9]+') FROM DUAL;
# 결과: 4 (숫자가 처음 등장하는 위치)

# REGEXP_SUBSTR
SELECT REGEXP_SUBSTR('abc123def', '[0-9]+') FROM DUAL;
# 결과: 123 (연속된 숫자 추출)

# REGEXP_COUNT
SELECT REGEXP_COUNT('a1b2c3', '[0-9]') FROM DUAL;
# 결과: 3 (숫자의 개수)

2. 관리 구문

1) DML (Data Manipulation Language)

DML은 데이터 조작 언어로, INSERT, UPDATE, DELETE, MERGE문을 포함합니다.

구문	설명
INSERT	데이터 삽입 (전체 컬럼 / 일부 지정 가능)
UPDATE	데이터 수정 (WHERE절 없으면 전부 수정됨!)
DELETE	데이터 삭제
MERGE	조건에 따라 INSERT 또는 UPDATE 수행 (Upsert 개념)

# INSERT
# 전체 컬럼 값 추가
INSERT INTO 직원 VALUES (1001, '홍길동', '개발', 3000000);
# 명시된 컬럼 값 추가
INSERT INTO 직원 (사원번호, 이름, 부서) VALUES (1002, '김철수', '영업');

# UPDATE
# 특정 직원의 급여 수정
UPDATE 직원 SET 급여 = 3500000 WHERE 사원번호 = 1001;
# 모든 직원의 급여 10% 인상
UPDATE 직원 SET 급여 = 급여 * 1.1;

# DELETE
# 특정 직원 삭제
DELETE FROM 직원 WHERE 사원번호 = 1001;
# 모든 직원 삭제
DELETE FROM 직원;

# MERGE
MERGE INTO 타겟테이블 a
USING 소스테이블 b
ON (a.키컬럼 = b.키컬럼)
WHEN MATCHED THEN
  UPDATE SET a.컬럼1 = b.컬럼1, a.컬럼2 = b.컬럼2
WHEN NOT MATCHED THEN
  INSERT (a.키컬럼, a.컬럼1, a.컬럼2)
  VALUES (b.키컬럼, b.컬럼1, b.컬럼2);

2) TCL (Transaction Control Language)

TCL은 트랜잭션 제어문으로, COMMIT, ROLLBACK, SAVEPOINT 등을 포함합니다.

COMMIT: 작업 확정
ROLLBACK: 작업 취소
SAVEPOINT: 중간 저장점 생성

트랜잭션 특징

원자성(Atomicity)
일관성(Consistency)
고립성(Isolation)
연속성(Durability)

자동커밋(AUTOCOMMIT)

Oracle

DDL: 무조건 자동커밋이 수행
DML: 자동커밋(AUTOCOMMIT) 모드 ON/OFF에 따라 롤백(ROLLBACK)이 가능

SQL Server

자동커밋(AUTOCOMMIT) 모드 ON/OFF에 따라서 DDL과 DML이 동작

Oracle은 기본적으로 자동커밋 모드 OFF / SQL Server는 기본적으로 자동커밋 모드 ON

# 자동커밋 설정
SET AUTOCOMMIT ON; -- 또는 OFF

# 트랜잭션 시작
BEGIN;  -- 또는 START TRANSACTION;

# 트랜잭션 내 작업
INSERT INTO 직원 VALUES(1003, '이영희', '인사', 2800000);
UPDATE 부서 SET 인원수 = 인원수 + 1 WHERE 부서명 = '인사';

# 변경사항 저장
COMMIT;

# 또는 변경사항 취소
ROLLBACK;

# 저장점 생성
SAVEPOINT sp1;

# 특정 저장점까지 롤백
ROLLBACK TO sp1;

3) DDL (Data Definition Language)

DDL은 데이터의 구조(스키마)를 정의하는 언어입니다.

구문	설명
CREATE	테이블/뷰 등 생성
ALTER	테이블 구조 변경
DROP	테이블 삭제
TRUNCATE	데이터만 삭제 (구조는 유지) → ROLLBACK 불가!

CREATE

CREATE TABLE 직원 (
  사원번호 NUMBER(5) PRIMARY KEY,
  이름 VARCHAR2(20) NOT NULL,
  부서코드 NUMBER(3) REFERENCES 부서(부서코드),
  급여 NUMBER(10) DEFAULT 0,
  입사일 DATE
);

PK는 개체무결성을 보장, FK는 참조무결성을 보장합니다.

주요 데이터 유형

1. 문자형

CHAR(n): 고정 길이 문자열 (최대 2000바이트)
VARCHAR2(n): 가변 길이 문자열 (최대 4000바이트)
NVARCHAR2(n): 유니코드 가변 길이 문자열
CLOB: 대용량 텍스트 데이터 (최대 4GB)

2. 숫자형

NUMBER(p,s): 정밀도(p)와 소수점(s)을 가진 숫자
INTEGER: 정수

3. 날짜/시간형

DATE: 날짜와 시간 정보 저장
TIMESTAMP: 보다 정밀한 시간 정보 저장

테이블명과 컬럼명 정의 시 기본 규칙

숫자로 시작할 수 없으며, A-Z, a-z, 0-9, _, $, # 문자만 허용
성격에 맞는 의미 있는 이름 사용

ALTER

# 컬럼 추가
ALTER TABLE 직원 ADD (이메일 VARCHAR2(100));

# 컬럼 수정
ALTER TABLE 직원 MODIFY (이름 VARCHAR2(30));

# 컬럼 삭제
ALTER TABLE 직원 DROP COLUMN 이메일;

# 컬럼명 변경
ALTER TABLE 직원 RENAME COLUMN 급여 TO 월급;

# 제약조건 추가
ALTER TABLE 직원 ADD CONSTRAINT pk_직원 PRIMARY KEY (사원번호);

DROP

DROP TABLE 직원 [CASCADE CONSTRAINTS];

TRUNCATE

TRUNCATE TABLE 직원;

4) DCL (권한 제어어)

구문	설명
GRANT	권한 부여 (SELECT, INSERT, UPDATE, ALL PRIVILEGES)
REVOKE	권한 회수
WITH GRANT OPTION	다른 사용자에게도 부여 가능
WITH ADMIN OPTION	시스템 권한 부여 가능 (CREATE, DROP 등)

GRANT

# 객체 권한 부여
GRANT SELECT, INSERT ON 직원 TO 사용자1;

# 모든 권한 부여
GRANT ALL PRIVILEGES ON 직원 TO 사용자1;

# 권한 부여 옵션
GRANT SELECT ON 직원 TO 사용자1 WITH GRANT OPTION;

# 시스템 권한 부여
GRANT CREATE TABLE TO 사용자1 WITH ADMIN OPTION;

주요 권한

SELECT(조회)
INSERT(삽입)
UPDATE(수정)
DELETE(삭제)
ALL PRIVILEGES(모든 권한)

REVOKE

# 객체 권한 회수
REVOKE SELECT, INSERT ON 직원 FROM 사용자1;

# 시스템 권한 회수
REVOKE CREATE TABLE FROM 사용자1;

권한 부여 옵션

1. WITH GRANT OPTION

객체 권한 부여 (SELECT, INSERT, UPDATE, DELETE 등)
권한을 받은 사용자가 다른 사용자에게 동일한 권한을 부여할 수 있음

2. WITH ADMIN OPTION

시스템 권한 부여 (CREATE TABLE, DROP TABLE 등)
권한을 받은 사용자가 다른 사용자에게 동일한 권한을 부여할 수 있음

✅ 오늘의 학습 핵심 요약

서브쿼리: 단일/다중행 구분 및 위치 이해
집합 연산자: 컬럼 구조 일치해야 사용 가능
ROLLUP, CUBE, 윈도우 함수는 실무에서도 자주 활용됨
정규표현식 함수는 Oracle에서 유용
관리 구문은 실수 시 데이터 손실 주의! (특히 TRUNCATE)

마치며

오늘은 SQLD 2과목 중 SQL 활용과 관리 구문 파트를 정리했습니다.

서브쿼리부터 시작해서 윈도우 함수, PIVOT/UNPIVOT, 그리고 DML, TCL, DDL, DCL까지 다양한 SQL 문법과 개념을 살펴보았습니다. 이제 SQL 문법이 단순한 문법이 아니라, 데이터 분석과 실무 흐름 속에서 어떻게 활용되는지를 조금씩 감 잡을 수 있게 되었어요.

다음은 SQLD 시험 전 기출문제 중심 복습 & 실전 전략을 정리해볼 예정입니다.

함께 끝까지 달려봐요! 💪