← 강의 목록

SQLD

6편 - SQLD 2과목 2장 : SQL 활용

제2장. SQL 활용

2.1 JOIN

2.1.1 JOIN의 개념

JOIN은 두 개 이상의 테이블에서 데이터를 조합하여 결과를 반환하는 기법이다.

2.1.2 EQUI JOIN (등가 조인)

-- Oracle 전통 방식
SELECT e.사원명, d.부서명
FROM   사원 e, 부서 d
WHERE  e.부서코드 = d.부서코드;

-- ANSI 표준 방식 (INNER JOIN)
SELECT e.사원명, d.부서명
FROM   사원 e
INNER JOIN 부서 d ON e.부서코드 = d.부서코드;

-- USING 절 (칼럼명이 동일할 때)
SELECT e.사원명, d.부서명
FROM   사원 e
JOIN   부서 d USING (부서코드);
-- USING 절에서는 테이블 별칭 사용 불가! (부서코드 앞에 e. 또는 d. 못 씀)

-- NATURAL JOIN (동일 칼럼명 자동 매핑)
SELECT 사원명, 부서명
FROM   사원
NATURAL JOIN 부서;
-- 주의: 동일 칼럼명이 여러 개면 모두 조인 조건에 포함됨

2.1.3 NON-EQUI JOIN (비등가 조인)

-- 등호(=)가 아닌 비교 연산자 사용
SELECT e.사원명, e.급여, g.등급
FROM   사원 e, 급여등급 g
WHERE  e.급여 BETWEEN g.최저급여 AND g.최고급여;

2.1.4 OUTER JOIN

-- LEFT OUTER JOIN: 왼쪽 테이블의 모든 행 보존
SELECT e.사원명, d.부서명
FROM   사원 e
LEFT OUTER JOIN 부서 d ON e.부서코드 = d.부서코드;
-- 부서가 없는 사원도 결과에 포함 (부서명은 NULL)

-- Oracle 전통 방식 (+)
SELECT e.사원명, d.부서명
FROM   사원 e, 부서 d
WHERE  e.부서코드 = d.부서코드(+);
-- (+)는 데이터가 없는 쪽에 붙인다!

-- RIGHT OUTER JOIN: 오른쪽 테이블의 모든 행 보존
SELECT e.사원명, d.부서명
FROM   사원 e
RIGHT OUTER JOIN 부서 d ON e.부서코드 = d.부서코드;
-- 사원이 없는 부서도 결과에 포함

-- FULL OUTER JOIN: 양쪽 모두 보존
SELECT e.사원명, d.부서명
FROM   사원 e
FULL OUTER JOIN 부서 d ON e.부서코드 = d.부서코드;
-- Oracle 전통 방식으로는 FULL OUTER JOIN 불가!

2.1.5 CROSS JOIN (교차 조인)

-- 카테시안 곱: M × N 건 생성
SELECT e.사원명, d.부서명
FROM   사원 e
CROSS JOIN 부서 d;

-- Oracle 전통 방식: WHERE 절 없이 조인
SELECT e.사원명, d.부서명
FROM   사원 e, 부서 d;

2.1.6 SELF JOIN (자기 조인)

-- 같은 테이블을 두 번 사용
SELECT e.사원명 AS 사원, m.사원명 AS 관리자
FROM   사원 e
LEFT JOIN 사원 m ON e.관리자번호 = m.사원번호;

2.2 서브쿼리 (Subquery)

2.2.1 서브쿼리의 종류

위치명칭설명
WHERE 절서브쿼리 (중첩 서브쿼리)조건 비교에 사용
FROM 절인라인 뷰 (Inline View)가상 테이블로 사용
SELECT 절스칼라 서브쿼리하나의 값만 반환

2.2.2 단일행 서브쿼리 vs 다중행 서브쿼리

-- 단일행 서브쿼리: 결과가 1건 → =, >, <, >=, <= 사용
SELECT * FROM 사원
WHERE 급여 > (SELECT AVG(급여) FROM 사원);

-- 다중행 서브쿼리: 결과가 여러 건 → IN, ANY, ALL, EXISTS 사용
SELECT * FROM 사원
WHERE 부서코드 IN (SELECT 부서코드 FROM 부서 WHERE 지역 = '서울');

다중행 비교 연산자

연산자설명예시
IN목록 중 하나와 일치WHERE 급여 IN (3000, 4000, 5000)
ANY/SOME목록 중 하나라도 만족WHERE 급여 > ANY (3000, 4000, 5000) → 급여 > 3000
ALL목록 모두 만족WHERE 급여 > ALL (3000, 4000, 5000) → 급여 > 5000
EXISTS서브쿼리 결과 존재 여부WHERE EXISTS (SELECT 1 FROM 주문 WHERE ...)

시험 핵심:

  • > ANY = 최솟값보다 크면 됨
  • > ALL = 최댓값보다 커야 함
  • < ANY = 최댓값보다 작으면 됨
  • < ALL = 최솟값보다 작아야 함

2.2.3 연관 서브쿼리 (Correlated Subquery)

-- 메인 쿼리의 칼럼을 서브쿼리에서 참조
SELECT e.사원명, e.급여, e.부서코드
FROM   사원 e
WHERE  e.급여 > (SELECT AVG(e2.급여) FROM 사원 e2 WHERE e2.부서코드 = e.부서코드);
-- 자기 부서의 평균 급여보다 높은 사원

2.2.4 인라인 뷰 (Inline View)

-- FROM 절에 서브쿼리
SELECT a.부서코드, a.평균급여
FROM   (SELECT 부서코드, AVG(급여) AS 평균급여
        FROM   사원
        GROUP BY 부서코드) a
WHERE  a.평균급여 >= 5000;

2.2.5 스칼라 서브쿼리 (Scalar Subquery)

-- SELECT 절에 서브쿼리 (반드시 1건, 1칼럼 반환)
SELECT 사원명,
       급여,
       (SELECT 부서명 FROM 부서 WHERE 부서코드 = e.부서코드) AS 부서명
FROM   사원 e;

2.3 집합 연산자

2.3.1 집합 연산자 종류

연산자설명중복 제거
UNION합집합O (중복 제거)
UNION ALL합집합X (중복 포함, 성능 좋음)
INTERSECT교집합O
MINUS (Oracle)차집합O
-- 두 쿼리의 칼럼 수와 데이터 타입이 일치해야 함
SELECT 사원번호, 사원명 FROM 정규사원
UNION
SELECT 사원번호, 사원명 FROM 계약사원;

-- UNION ALL은 정렬하지 않아 성능이 좋음
SELECT 사원번호, 사원명 FROM 정규사원
UNION ALL
SELECT 사원번호, 사원명 FROM 계약사원;

-- MINUS: 첫 번째 결과에서 두 번째 결과를 뺌
SELECT 부서코드 FROM 부서
MINUS
SELECT DISTINCT 부서코드 FROM 사원;
-- 사원이 없는 부서

2.4 그룹 함수

2.4.1 집계 함수 (Aggregate Function)

함수설명NULL 처리
COUNT(*)전체 행 수NULL 포함
COUNT(칼럼)해당 칼럼의 NULL이 아닌 행 수NULL 제외
SUM(칼럼)합계NULL 제외
AVG(칼럼)평균NULL 제외
MAX(칼럼)최대값NULL 제외
MIN(칼럼)최소값NULL 제외

시험 핵심: AVG는 NULL을 제외하고 계산한다! 예: 값이 100, 200, NULL인 경우 → AVG = (100+200)/2 = 150 (3으로 나누지 않음)

SELECT 부서코드,
       COUNT(*) AS 인원수,
       SUM(급여) AS 급여합계,
       ROUND(AVG(급여), 0) AS 평균급여,
       MAX(급여) AS 최대급여,
       MIN(급여) AS 최소급여
FROM   사원
GROUP BY 부서코드;

2.4.2 GROUP BY

-- GROUP BY에 나열되지 않은 칼럼은 SELECT에서 집계 함수 없이 사용 불가!
-- 에러:
SELECT 부서코드, 사원명, SUM(급여) FROM 사원 GROUP BY 부서코드;
-- 사원명이 GROUP BY에 없으므로 에러

-- 올바른 사용:
SELECT 부서코드, COUNT(*), SUM(급여)
FROM   사원
GROUP BY 부서코드;

2.4.3 HAVING

-- GROUP BY 결과에 대한 조건 (집계 함수 조건)
SELECT 부서코드, AVG(급여) AS 평균급여
FROM   사원
GROUP BY 부서코드
HAVING AVG(급여) >= 5000;

-- WHERE vs HAVING
-- WHERE: 그룹화 전에 행 필터링 (집계 함수 사용 불가)
-- HAVING: 그룹화 후에 그룹 필터링 (집계 함수 사용 가능)

2.4.4 고급 그룹 함수

(1) ROLLUP

-- 소계와 총계를 자동 생성
SELECT 부서코드, 직급, SUM(급여)
FROM   사원
GROUP BY ROLLUP(부서코드, 직급);
-- 결과:
-- D01, 과장, 5000
-- D01, 대리, 3000
-- D01, NULL, 8000  ← 부서코드별 소계
-- D02, 과장, 6000
-- D02, NULL, 6000  ← 부서코드별 소계
-- NULL, NULL, 14000 ← 전체 총계

-- ROLLUP은 인자의 순서에 따라 결과가 달라진다!
-- ROLLUP(A, B) → A별 소계 + 전체 총계
-- ROLLUP(B, A) → B별 소계 + 전체 총계

(2) CUBE

-- 모든 조합의 소계를 생성
SELECT 부서코드, 직급, SUM(급여)
FROM   사원
GROUP BY CUBE(부서코드, 직급);
-- ROLLUP 결과 + 직급별 소계까지 모두 포함

(3) GROUPING SETS

-- 특정 그룹만 선택적으로 생성
SELECT 부서코드, 직급, SUM(급여)
FROM   사원
GROUP BY GROUPING SETS(부서코드, 직급);
-- 부서코드별 합계와 직급별 합계만 각각 생성 (교차 소계 없음)

(4) GROUPING 함수

-- 소계/총계 행인지 구분 (0: 실제 그룹, 1: 소계/총계)
SELECT CASE GROUPING(부서코드) WHEN 1 THEN '전체' ELSE 부서코드 END AS 부서,
       CASE GROUPING(직급) WHEN 1 THEN '소계' ELSE 직급 END AS 직급,
       SUM(급여)
FROM   사원
GROUP BY ROLLUP(부서코드, 직급);

2.5 윈도우 함수 (Window Function / 분석 함수)

2.5.1 윈도우 함수 기본 구조

함수명(인자) OVER (
    [PARTITION BY 칼럼]          -- 그룹 분할
    [ORDER BY 칼럼 [ASC|DESC]]  -- 정렬
    [WINDOWING 절]               -- 범위 지정
)

2.5.2 순위 함수

SELECT 사원명, 부서코드, 급여,
       RANK()       OVER (ORDER BY 급여 DESC) AS "RANK",
       DENSE_RANK() OVER (ORDER BY 급여 DESC) AS "DENSE_RANK",
       ROW_NUMBER() OVER (ORDER BY 급여 DESC) AS "ROW_NUMBER"
FROM   사원;
급여RANKDENSE_RANKROW_NUMBER
5000111
5000112
4000323
3000434

차이점:

  • RANK: 동일 순위 → 다음 순위 건너뜀 (1,1,3,4)
  • DENSE_RANK: 동일 순위 → 다음 순위 연속 (1,1,2,3)
  • ROW_NUMBER: 동일 값이어도 고유 번호 (1,2,3,4)

2.5.3 PARTITION BY

-- 부서별 급여 순위
SELECT 사원명, 부서코드, 급여,
       RANK() OVER (PARTITION BY 부서코드 ORDER BY 급여 DESC) AS 부서내순위
FROM   사원;

2.5.4 집계 윈도우 함수

-- 누적 합계
SELECT 사원명, 급여,
       SUM(급여) OVER (ORDER BY 급여) AS 누적합계
FROM   사원;

-- 부서별 누적 합계
SELECT 사원명, 부서코드, 급여,
       SUM(급여) OVER (PARTITION BY 부서코드 ORDER BY 급여) AS 부서별누적합계
FROM   사원;

-- 전체 합계 대비 비율
SELECT 사원명, 급여,
       ROUND(급여 / SUM(급여) OVER () * 100, 2) AS 비율
FROM   사원;

2.5.5 행 순서 함수

함수설명
LAG(칼럼, n, default)현재 행에서 n행 이전 값
LEAD(칼럼, n, default)현재 행에서 n행 이후 값
FIRST_VALUE(칼럼)파티션 내 첫 번째 값
LAST_VALUE(칼럼)파티션 내 마지막 값
SELECT 사원명, 입사일, 급여,
       LAG(급여, 1, 0) OVER (ORDER BY 입사일) AS 이전사원급여,
       LEAD(급여, 1, 0) OVER (ORDER BY 입사일) AS 다음사원급여
FROM   사원;

2.5.6 비율 함수

함수설명
RATIO_TO_REPORT(칼럼)전체 합 대비 비율
PERCENT_RANK()백분위 순위 (0~1)
CUME_DIST()누적 분포
NTILE(n)n개 그룹으로 균등 분할
-- 4개 그룹으로 분할 (4분위)
SELECT 사원명, 급여,
       NTILE(4) OVER (ORDER BY 급여 DESC) AS 분위
FROM   사원;

2.6 계층형 쿼리 (Oracle)

2.6.1 기본 구조

SELECT LEVEL, LPAD(' ', (LEVEL-1)*2) || 사원명 AS 조직도,
       사원번호, 관리자번호
FROM   사원
START WITH 관리자번호 IS NULL           -- 루트 노드 조건
CONNECT BY PRIOR 사원번호 = 관리자번호;  -- 부모-자식 관계 정의

2.6.2 핵심 키워드

키워드설명
LEVEL현재 노드의 깊이 (루트 = 1)
START WITH루트 노드를 지정하는 조건
CONNECT BY PRIOR부모-자식 관계를 정의
PRIOR이전 행(부모)을 참조
ORDER SIBLINGS BY같은 레벨 내에서 정렬
CONNECT_BY_ROOT 칼럼루트 노드의 해당 칼럼 값
SYS_CONNECT_BY_PATH(칼럼, 구분자)루트에서 현재까지의 경로
CONNECT_BY_ISLEAF리프 노드 여부 (0/1)
-- 순방향 전개 (부모 → 자식)
CONNECT BY PRIOR 사원번호 = 관리자번호

-- 역방향 전개 (자식 → 부모)
CONNECT BY PRIOR 관리자번호 = 사원번호

-- 경로 표시
SELECT SYS_CONNECT_BY_PATH(사원명, ' > ') AS 경로
FROM   사원
START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호;
-- 결과: ' > 대표이사 > 영업이사 > 영업팀장'

2.7 DCL (Data Control Language)

2.7.1 GRANT (권한 부여)

-- 시스템 권한 부여
GRANT CREATE SESSION TO user1;
GRANT CREATE TABLE TO user1;

-- 객체 권한 부여
GRANT SELECT ON 사원 TO user1;
GRANT INSERT, UPDATE ON 사원 TO user1;

-- WITH GRANT OPTION: 받은 권한을 다른 사용자에게 부여 가능
GRANT SELECT ON 사원 TO user1 WITH GRANT OPTION;

2.7.2 REVOKE (권한 회수)

REVOKE SELECT ON 사원 FROM user1;

-- WITH GRANT OPTION으로 부여한 경우:
-- user1의 권한 회수 시 user1이 다른 사용자에게 부여한 권한도 함께 회수됨 (CASCADE)

2.7.3 ROLE

-- 롤 생성 및 권한 부여
CREATE ROLE 영업팀_롤;
GRANT SELECT ON 고객 TO 영업팀_롤;
GRANT SELECT ON 주문 TO 영업팀_롤;

-- 사용자에게 롤 부여
GRANT 영업팀_롤 TO user1;

2.8 TCL (Transaction Control Language)

2.8.1 트랜잭션의 특성 (ACID)

특성설명
원자성 (Atomicity)트랜잭션은 전부 실행되거나 전부 취소되어야 함 (All or Nothing)
일관성 (Consistency)트랜잭션 실행 전후로 데이터베이스 상태가 일관되어야 함
고립성 (Isolation)동시에 실행되는 트랜잭션들이 서로 영향을 주지 않아야 함
지속성 (Durability)성공적으로 완료된 트랜잭션의 결과는 영구적으로 반영되어야 함

2.8.2 COMMIT, ROLLBACK, SAVEPOINT

-- COMMIT: 변경사항을 확정
INSERT INTO 사원 VALUES (1001, '홍길동', ...);
UPDATE 사원 SET 급여 = 5000 WHERE 사원번호 = 1002;
COMMIT;

-- ROLLBACK: 변경사항을 취소
DELETE FROM 사원 WHERE 부서코드 = 'D01';
ROLLBACK;  -- DELETE가 취소됨

-- SAVEPOINT: 중간 저장점
INSERT INTO 사원 VALUES (1003, '이순신', ...);
SAVEPOINT SP1;
UPDATE 사원 SET 급여 = 3000 WHERE 사원번호 = 1003;
SAVEPOINT SP2;
DELETE FROM 사원 WHERE 사원번호 = 1001;
ROLLBACK TO SP2;  -- DELETE만 취소
ROLLBACK TO SP1;  -- UPDATE까지 취소
COMMIT;            -- INSERT만 확정

시험 핵심:

  • DDL 문(CREATE, ALTER, DROP, TRUNCATE, RENAME) 실행 시 AUTO COMMIT 발생
  • 정상적으로 세션 종료 시 AUTO COMMIT
  • 비정상 종료 시 AUTO ROLLBACK
  • SAVEPOINT 이후의 ROLLBACK TO는 해당 지점까지만 롤백

2.9 뷰 (VIEW)

-- 뷰 생성
CREATE OR REPLACE VIEW V_사원_급여 AS
SELECT 사원번호, 사원명, 급여, 부서코드
FROM   사원
WHERE  급여 >= 3000;

-- 뷰 사용
SELECT * FROM V_사원_급여;

-- 뷰 삭제
DROP VIEW V_사원_급여;

뷰의 장점: 독립성(테이블 구조 변경 시 뷰만 수정), 편의성(복잡한 쿼리를 간단하게), 보안성(특정 칼럼만 노출)