1. 서브쿼리 (Subquery)

• 서브쿼리(Subquery): SELECT문 안에 포함된 또 다른 SELECT문. 메인쿼리를 실행하기 위해 먼저 실행되는 쿼리

1-1. 반환 결과에 따른 분류

-- 단일행 서브쿼리
SELECT 사원명 FROM 사원
WHERE 연봉 > (SELECT AVG(연봉) FROM 사원);
 
-- 다중행 서브쿼리 (IN)
SELECT 사원명 FROM 사원
WHERE 부서번호 IN (SELECT 부서번호 FROM 부서 WHERE 지역 = '서울');
 
-- 다중행 서브쿼리 (ANY, ALL)
SELECT 사원명 FROM 사원
WHERE 연봉 > ANY (SELECT 연봉 FROM 사원 WHERE 부서번호 = 20);
 
-- 다중칼럼 서브쿼리
SELECT * FROM 사원
WHERE (부서번호, 직급) = (SELECT 부서번호, 직급 FROM 사원 WHERE 사원번호 = 1001);

📌 ANY vs ALL 비교 (★ 필수 암기)

💡 함정 포인트: ANY와 ALL은 반대로 외우기 쉬우므로 주의! ANY는 조건을 완화하고, ALL은 조건을 강화합니다.

1-2. 연관 서브쿼리 (Correlated Subquery)

• 메인쿼리의 컬럼을 서브쿼리 내부에서 참조하는 서브쿼리로, 메인쿼리 행마다 서브쿼리가 반복 실행됨

-- 연관 서브쿼리: 부서별 평균보다 연봉이 높은 사원
SELECT A.사원명, A.연봉
FROM 사원 A
WHERE A.연봉 > (SELECT AVG(B.연봉) FROM 사원 B WHERE B.부서번호 = A.부서번호);

• EXISTS / NOT EXISTS: 서브쿼리 결과가 존재하는지(행이 1건이라도 있는지) 여부만 판단 → 주로 연관 서브쿼리와 함께 사용

SELECT 사원명 FROM 사원 A
WHERE EXISTS (SELECT 1 FROM 부서 B WHERE B.부서번호 = A.부서번호 AND B.지역 = '서울');

1-3. 뷰처럼 사용하는 서브쿼리

-- 인라인 뷰 (FROM절)
SELECT 부서번호, 평균연봉
FROM (SELECT 부서번호, AVG(연봉) AS 평균연봉 FROM 사원 GROUP BY 부서번호) V
WHERE 평균연봉 >= 4000;
 
-- 스칼라 서브쿼리 (SELECT절)
SELECT 사원명,
    (SELECT 부서명 FROM 부서 D WHERE D.부서번호 = E.부서번호) AS 부서명
FROM 사원 E;

💡 서브쿼리 위치 요약: SELECT절(스칼라) / FROM절(인라인뷰) / WHERE절(중첩)

2. 집합 연산자 & 그룹 함수

2-1. 집합 연산자 (Set Operator)

• 두 개 이상의 SELECT문 결과를 하나로 합치는 연산자로, 컬럼 개수와 데이터 타입이 일치해야 함

SELECT 사원명 FROM 사원 WHERE 부서번호 = 10
UNION
SELECT 사원명 FROM 사원 WHERE 부서번호 = 20;
 
SELECT 사원번호 FROM 사원
MINUS
SELECT 사원번호 FROM 퇴직자;

💡 UNION vs UNION ALL: UNION은 중복 제거를 위한 정렬(SORT) 작업이 발생해 UNION ALL보다 느립니다. 중복이 없다는 걸 안다면 UNION ALL 사용을 권장합니다.

2-2. 그룹 함수 (GROUP BY 확장)

📌 ROLLUP vs CUBE vs 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(부서번호, 직급);

💡 결과 행 개수 비교: GROUPING SETS ≤ ROLLUP ≤ CUBE (CUBE가 모든 조합을 다 만들어서 가장 많음)

3. 윈도우 함수 (Window Function)

• 윈도우 함수(Window Function): 행과 행 사이의 관계를 정의하기 위한 함수. GROUP BY처럼 여러 행을 하나로 합치지 않고, 개별 행을 유지하면서 집계/순위 등을 함께 보여줌

기본 문법

함수명(인자) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)

• PARTITION BY: 그룹을 나누는 기준 (생략 시 전체를 하나의 그룹으로 처리)
• ORDER BY: 그룹 내 정렬 기준 (순위/누적 함수에서 필요)

3-1. 순위 함수

SELECT 사원명, 연봉,
    RANK() OVER (ORDER BY 연봉 DESC) AS 순위,
    DENSE_RANK() OVER (ORDER BY 연봉 DESC) AS 밀집순위,
    ROW_NUMBER() OVER (ORDER BY 연봉 DESC) AS 순번
FROM 사원;

💡 시험 단골 체크: 공동 1위가 2명이면 RANK는 다음이 3위, DENSE_RANK는 다음이 2위입니다.

3-2. 행 이동 함수 (LAG/LEAD)

SELECT 사원명, 입사일,
    LAG(입사일, 1) OVER (ORDER BY 입사일) AS 이전입사자_입사일,
    LEAD(입사일, 1) OVER (ORDER BY 입사일) AS 다음입사자_입사일
FROM 사원;

3-3. 비율/집계 함수

SELECT 사원명, 연봉,
    SUM(연봉) OVER (PARTITION BY 부서번호) AS 부서별_연봉합계,
    NTILE(4) OVER (ORDER BY 연봉 DESC) AS 연봉_4분위등급
FROM 사원;

4. 계층형 질의 & VIEW

4-1. 계층형 질의 (Hierarchical Query)

• 자기 참조 관계(재귀 관계)를 가진 데이터를 트리(계층) 구조로 조회할 때 사용 (조직도, 부품 구성표 등, Oracle 기준)

SELECT LEVEL, LPAD(' ', (LEVEL-1)*2) || 사원명 AS 조직도
FROM 사원
START WITH 관리자번호 IS NULL          -- 최상위(루트) 조건
CONNECT BY PRIOR 사원번호 = 관리자번호  -- 부모-자식 연결 조건
ORDER SIBLINGS BY 사원명;

💡 PRIOR 방향성: PRIOR 사원번호 = 관리자번호 → 사원번호(자식)가 이전 행, 즉 위→아래(부모→자식) 방향으로 전개(순방향 조회)됩니다. 반대로 쓰면 자식→부모(상향) 조회가 됩니다.

4-2. VIEW (가상 테이블)

• VIEW: 실제 데이터를 저장하지 않고, SELECT문을 저장해두고 참조할 때마다 실행하는 가상의 테이블

특징

• 실제 데이터가 저장되지 않음 (기본 뷰 기준)
• 자주 쓰는 복잡한 쿼리를 단순화하여 재사용 가능
• 보안: 특정 컬럼/행만 노출시켜 접근 제한 가능
• 뷰를 통한 DML(INSERT/UPDATE/DELETE)은 일부 제약 존재 (여러 테이블 조인 뷰 등은 제한)

CREATE VIEW V_사원목록 AS
SELECT 사원번호, 사원명, 부서번호
FROM 사원
WHERE 재직여부 = 'Y';
 
CREATE OR REPLACE VIEW V_사원목록 AS ...  -- 뷰 재정의
DROP VIEW V_사원목록;                     -- 뷰 삭제

5. DCL & TCL

5-1. DCL (Data Control Language) — 권한 제어

GRANT SELECT, INSERT ON 사원 TO 사용자A;
REVOKE INSERT ON 사원 FROM 사용자A;

• WITH GRANT OPTION: 부여받은 권한을 다른 사용자에게도 부여할 수 있는 권한까지 함께 부여

5-2. TCL (Transaction Control Language) — 트랜잭션 제어

SAVEPOINT SP1;
UPDATE 사원 SET 연봉 = 5000 WHERE 사원번호 = 1001;
ROLLBACK TO SP1;   -- SAVEPOINT SP1 시점으로 되돌림
COMMIT;            -- 최종 반영

트랜잭션의 4가지 특성 (ACID)

💡 암기 팁: ACID는 앞글자로 원자성-일관성-고립성-지속성 순서로 외우면 쉽습니다.

📌 3단계 최종 암기 요약

1. 서브쿼리 종류: 단일행(=,>,<) / 다중행(IN,ANY,ALL,EXISTS) / 다중칼럼
2. ANY vs ALL: > ANY(최솟값보다 크면 OK, 조건완화) / > ALL(최댓값보다 커야, 조건강화)
3. 서브쿼리 위치: SELECT(스칼라) / FROM(인라인뷰) / WHERE(중첩, 연관)
4. 집합 연산자: UNION(중복제거, 정렬) / UNION ALL(중복유지, 빠름) / INTERSECT(교집합) / MINUS(차집합)
5. 그룹함수 결과 크기: GROUPING SETS ≤ ROLLUP ≤ CUBE
6. 윈도우함수 순위: RANK(건너뜀) / DENSE_RANK(안 건너뜀) / ROW_NUMBER(고유순번)
7. 행이동 함수: LAG(이전행) / LEAD(다음행)
8. 계층형 질의: START WITH(시작점) + CONNECT BY PRIOR(연결조건) + LEVEL(깊이)
9. VIEW: 데이터 저장 안 함, SELECT문 저장, 재사용/보안 목적
10. DCL: GRANT(권한부여) / REVOKE(권한회수)
11. TCL: COMMIT(영구반영) / ROLLBACK(취소) / SAVEPOINT(중간저장)
12. ACID: 원자성 / 일관성 / 고립성 / 지속성

✏️ 예상 문제 (3단계 종합, 15문항)

Q1. 다음 중 다중행 서브쿼리에서 사용할 수 없는 연산자는?
① IN
② ANY
③ ALL
④ =

Q2. 다음 서브쿼리의 실행 결과에 대한 설명으로 옳은 것은?

SELECT 사원명 FROM 사원
WHERE 연봉 > ALL (SELECT 연봉 FROM 사원 WHERE 부서번호 = 20);

① 부서번호 20의 최솟값보다 연봉이 높은 사원을 조회한다.
② 부서번호 20의 최댓값보다 연봉이 높은 사원을 조회한다.
③ 부서번호 20의 평균값보다 연봉이 높은 사원을 조회한다.
④ 부서번호 20에 속한 모든 사원을 조회한다.

Q3. 메인쿼리의 컬럼을 서브쿼리 내부에서 참조하며, 메인쿼리의 행 개수만큼 반복 실행되는 서브쿼리는 무엇인가?

Q4. FROM절에 위치하여 마치 임시 테이블처럼 사용되는 서브쿼리를 무엇이라 하는가?

Q5. UNION과 UNION ALL의 차이로 옳은 것은?
① UNION은 중복을 제거하지 않고, UNION ALL은 중복을 제거한다.
② UNION은 중복 제거를 위한 정렬이 발생하여 UNION ALL보다 느리다.
③ UNION과 UNION ALL은 결과가 항상 동일하다.
④ UNION ALL은 두 SELECT문의 컬럼 개수가 달라도 사용할 수 있다.

Q6. 부서번호별, 직급별 소계와 함께 두 컬럼의 가능한 모든 조합에 대한 소계, 그리고 전체 총계까지 모두 구하고자 할 때 사용해야 하는 그룹 함수는?
① ROLLUP
② CUBE
③ GROUPING SETS
④ HAVING

Q7. 연봉 기준으로 순위를 매길 때, 공동 1위가 2명 존재하는 경우 다음 순위가 “2위”로 이어지지 않고 “3위”로 건너뛰는 윈도우 함수는 무엇인가?

Q8. 사원 테이블에서 입사일 순으로 정렬했을 때, 각 사원의 “바로 다음에 입사한 사원의 입사일”을 함께 조회하고자 한다. 사용해야 할 윈도우 함수는 무엇인가?

Q9. 다음 계층형 질의에서 CONNECT BY PRIOR 구문의 방향성에 대한 설명으로 옳은 것은?

SELECT 사원명 FROM 사원
START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호;

① 자식 → 부모 방향으로 조회한다 (상향 조회).
② 부모 → 자식 방향으로 조회한다 (하향 조회).
③ START WITH 조건과 무관하게 전체 테이블을 조회한다.
④ 방향성과 관계없이 항상 같은 결과가 나온다.

Q10. VIEW(뷰)에 대한 설명으로 옳지 않은 것은?
① 뷰는 실제 데이터를 저장하지 않고 SELECT문을 저장한다.
② 뷰를 사용하면 복잡한 쿼리를 단순화하여 재사용할 수 있다.
③ 뷰를 통해 특정 컬럼만 노출시켜 보안을 강화할 수 있다.
④ 모든 뷰는 예외 없이 INSERT, UPDATE, DELETE가 자유롭게 가능하다.

Q11. DCL 명령어인 GRANT와 REVOKE에 대한 설명으로 옳은 것은?

Q12. 트랜잭션 도중 특정 지점을 저장해두고, 이후 문제가 발생하면 전체가 아닌 해당 지점까지만 되돌릴 수 있게 하는 TCL 명령어는 무엇인가?

Q13. 트랜잭션의 특성(ACID) 중, “트랜잭션에 포함된 작업은 전부 반영되거나 전부 취소되어야 한다”는 특성은 무엇인가?

Q14. 다음 중 EXISTS 서브쿼리에 대한 설명으로 옳은 것은?
① EXISTS는 서브쿼리의 특정 컬럼 값을 반환하여 비교에 사용한다.
② EXISTS는 서브쿼리 결과가 존재하는지(1건 이상 있는지) 여부만 판단한다.
③ EXISTS는 단일행 서브쿼리에서만 사용할 수 있다.
④ EXISTS는 항상 다중칼럼 서브쿼리와 함께 사용해야 한다.

Q15. SELECT절에 위치하며, 반드시 하나의 값(1행 1열)만 반환해야 하는 서브쿼리는 무엇인가?

✅ 정답 및 해설

• Q1. 정답: ④
  • 해설: =는 결과가 1건일 때만 사용 가능한 단일행 서브쿼리 연산자입니다. 다중행(여러 건) 서브쿼리에서는 IN, ANY, ALL, EXISTS를 사용해야 합니다.
• Q2. 정답: ②
  • 해설: > ALL은 서브쿼리 결과 “모두”보다 커야 하므로, 결국 서브쿼리 결과 중 최댓값보다 커야 조건을 만족합니다.
• Q3. 정답: 연관 서브쿼리(Correlated Subquery)
  • 해설: 메인쿼리의 컬럼(예: A.부서번호)을 서브쿼리 조건에서 참조하며, 메인쿼리의 각 행마다 서브쿼리가 반복 실행됩니다.
• Q4. 정답: 인라인 뷰(Inline View)
  • 해설: FROM절에 오는 서브쿼리로, 실행 결과를 마치 하나의 테이블처럼 활용할 수 있습니다.
• Q5. 정답: ②
  • 해설: UNION은 결과의 중복을 제거하기 위해 내부적으로 정렬(SORT) 작업을 수행하므로 UNION ALL보다 느립니다. UNION ALL은 중복 제거 없이 그대로 합칩니다.
• Q6. 정답: ②
  • 해설: CUBE는 지정한 컬럼들의 가능한 모든 조합에 대한 소계와 총계를 생성합니다. ROLLUP은 계층적(순차적) 소계만, GROUPING SETS는 지정한 조합만 생성합니다.
• Q7. 정답: RANK()
  • 해설: RANK()는 동순위가 있으면 다음 순위 번호를 건너뜁니다(1,2,2,4). 반면 DENSE_RANK()는 건너뛰지 않습니다(1,2,2,3).
• Q8. 정답: LEAD()
  • 해설: LEAD는 현재 행 기준으로 이후(다음) 행의 값을 가져옵니다. 이전 행의 값을 가져오려면 LAG를 사용합니다.
• Q9. 정답: ②
  • 해설: PRIOR 사원번호 = 관리자번호는 이전(부모) 행의 사원번호가 현재(자식) 행의 관리자번호와 같다는 의미로, 부모에서 자식 방향(하향)으로 전개하며 조회합니다.
• Q10. 정답: ④
  • 해설: 여러 테이블을 조인한 뷰나 집계 함수를 포함한 뷰 등은 DML(특히 INSERT/UPDATE)에 제약이 있어 자유롭게 되지 않는 경우가 많습니다.
• Q11. 정답:
  • GRANT는 특정 사용자에게 데이터베이스 객체에 대한 권한(SELECT, INSERT 등)을 부여하는 명령어이고, REVOKE는 부여된 권한을 다시 회수하는 명령어입니다.
• Q12. 정답: SAVEPOINT
  • 해설: SAVEPOINT로 저장점을 만들어두면, ROLLBACK TO 저장점명으로 해당 지점까지만 부분 롤백할 수 있습니다.
• Q13. 정답: 원자성(Atomicity)
  • 해설: 원자성은 트랜잭션의 모든 작업이 전부 반영되거나(COMMIT) 전부 취소(ROLLBACK)되어야 한다는 특성입니다(All or Nothing).
• Q14. 정답: ②
  • 해설: EXISTS는 특정 값을 반환하는 것이 아니라, 서브쿼리의 조건을 만족하는 행이 하나라도 존재하는지(TRUE/FALSE)만 판단합니다. 주로 연관 서브쿼리와 함께 사용됩니다.
• Q15. 정답: 스칼라 서브쿼리(Scalar Subquery)
  • 해설: SELECT절에 위치하는 서브쿼리로, 여러 값을 반환하면 오류가 발생하므로 반드시 하나의 값만 반환하도록 작성해야 합니다.