30일간의 SQL 기초 학습

__NUMBEREDHEADINGS__

1일차 - SQL 기본

데이터 베이스 기본 용어

영문 용어	한글 용어	설명
SQL	질의어	Structured Query Language,SQL은 데이터베이스에서 데이터를 관리하거나 조작하기 위해 설계된 표준 프로그래밍 언어
QUERY	쿼리	질의어,SQL 문장에서 데이터를 조회하는 명령어를 Query 라 함. 질의 > 학생들(STUDENTS) 에서 나이가 18보다 큰 학생의 이름과 ,나이를 조회 SQL > SELECT name, age FROM STUDENTS WHERE age > 18;
TABLE	테이블	데이터를 정리하고 표시하기 위한 행(row)과 열(column)로 구성된 구조, 엑셀에서 쉬트를 테이블과 유사하다고 생각하면 됨. =================== \| Name \| Age \| City \| =================== \| Alice \| 24 \| New York \| --------------------------------- \| Bob \| 30 \| Chicago \| --------------------------------- 테이블 생성 예시 CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(100), age INT, city VARCHAR(100) );
COLUMN	컬럼(열)	컬럼은 세로 줄, 열을 의미, 엑셀에서 세로줄과 유사 데이터값을 조회 하기위한 타이틀 =================== \| Name \| Age \| City \| ===================
ROW	로우(행)	로우는 줄,행을 의미, 엑셀에서 가로줄과 유사 , 주로 1개의 줄을 레코드라고 한다. 데이터의 값이 저장되어 있다. \| Alice \| 24 \| New York \| --------------------------------- \| Bob \| 30 \| Chicago \|
INDEX	인덱스	데이터를 빠르게 검색하기 위해 사용하는 특별한 데이터 구조, 책에 있는 목차(INDEX) 와 유사
VIEW	뷰	데이터베이스에서는 가상 테이블을 의미, 테이블에 있는 일부 속성(컬럼)을 사용하고 할때 사용

현업에서 사용하는 업무 용어

모델링

데이터베이스에서 사용하는 테이블과 컬럼을 설계 하는 업무
주로 논리 모델은 모델링 단계를 의미하고 물리모델은 데이터베이스에 적용하는 단계를 의미함

Entity(엔터티)

주로 논리모델링에서 사용하는 용어로 속성을 포함하고 있는 객체를 의미함.
물리모델에서는 엔터티를 테이블이라 함.

ERD

Entity Relationship Diagram

엔터티의 관계를 다이어그램으로 나타낸 도표

DW(데이터웨어하우스)

현업 업무는 실시간 업무를 처리 하는 기간계와 통계성 업무를 처리하는 정보계로 나누어짐
OLTP 업무는 기간계 , OLAP 업무는 정보계라 칭함

ASIS 시스템

현재 개발중인 시스템에서 과거 시스템을 부를때 칭함.
주로 레거시 난 AS-IS 라 칭함

TOBE 시스템

현재 개발중인 시스템을 칭함
주로 차세대라 칭함.

DDL DML DCL TCL

DDL (Data Definition Language; 데이터 정의 언어)

데이터 정의어
:데이터베이스의 구조를 만들고 변경,수정 하는 명령어
1. - CREATE TABLE : 테이블 생성
2. - ALTER TABLE : 테이블 수정
3. - DROP TABLE : 테이블 삭제
4. - CREATE INDEX : 인덱스 생성
5. - ALTER INDEX : 인덱스 수정
6. - DROP INDEX : 인덱스 삭제

DML (Data Manipulation Language; 데이터 조작 언어)

데이터 조작어
:테이블 안의 데이터를 넣고, 바꾸고, 지우고, 조회하는 데 사용해요.
1. - SELECT
2. - SELECT INTO
3. - INSERT
4. - DELETE
5. - UPDATE
6. - CREATE SEQUENCE
7. - ALTER SEQUENCE
8. - DROP SEQUENCE

DCL (Data Control Language : 데이터 제어 언어)

데이터 제어어
사용자 권한을 관리해요. 누가 무엇을 할 수 있는지 정해요.
1. - CREATE USER
2. - ALTER USER
3. - GRANT
TCL (Transaction Control Language; 트랜잭션 제어 언어)
1. - COMMIT
2. - ROLLBACK

DDL (Data Definition Language; 데이터 정의 언어)

CREATE / ALTER / DROP [테이블 or 인덱스]

CREATE TABLE (테이블 생성)

CREATE TABLE 테이블명 (
    컬럼명 컬럼 타입 ,
             ... ,
    <column-name-n> <type>
)
[tablespace 테이블스페이스명] -- 생략시 default tablespace에 생성.
;

ALTER TABLE (테이블 변경)

컬럼 추가

ALTER TABLE 테이블명
ADD ( 컬럼명 데이터타입, ...<column-name-n> <type> );

컬럼 수정

ALTER TABLE 테이블명
MODIFY ( 컬럼명 새로운타입 );

컬럼 삭제

 
ALTER TABLE 테이블명
 DROP COLUMN 컬럼명;

DROP TABLE (테이블 삭제)

DROP TABLE 테이블명 ;

CREATE INDEX (인덱스 생성)

CREATE INDEX 인덱스명 
    ON 테이블명 (
              컬럼1,컬럼2, ...
              );

ALTER INDEX (인덱스 수정)

인덱스 재생성 (통계정보 수집은 동시에 )

-- COLLECTING STATISTICS ON INDEX
ALTER INDEX 인덱스 
    REBUILD COMPUTE STATISTICS; -- 인덱스를 통계정보화 함께 리빌드 한다.

인덱스명 변경

-- RENAME INDEX
ALTER INDEX 인덱스명
     RENAME TO 새 인덱스명;

DROP INDEX (인덱스 삭제)

DROP INDEX 인덱스명;

DML (Data Manipulation Language; 데이터 조작 언어)

SELECT (조회)

SELECT 컬럼명1, 컬럼명2, ..., <column-name-n>
 FROM 테이블명1,테이블명2, ..., <table-name-n>
WHERE <조건 표현 구문 >
GROUP BY <그룹핑 컬럼>, ..., <grouping-column-name-n>
HAVING <그룹핑 표현구문>
 ORDER BY <정렬 컬럼1>, ..., <order-column-name-n>
;

INSERT (입력)

전체 데이터 입력시

-- INSERT ALL VALUES
INSERT INTO 테이블명
VALUES ( 값1, ..., <value-n> );

부분 데이터 입력시

 
-- INSERT SOME VALUES
INSERT INTO 테이블명 ( 컬럼명1, ..., <column-name-n> )
VALUES ( 값1, ..., <value-n> );

DELETE (삭제)

DELETE FROM 테이블명
      WHERE 조건표현구문;

UPDATE (갱신)

UPDATE 테이블명
   SET 컬럼명 = <value>
 WHERE 조건표현구문

CREATE SEQUENCE

CREATE SEQUENCE 시퀀스명
       MINVALUE <min-value>
       MAXVALUE <max-value>
     START WITH <start-value>
   INCREMENT BY <step-value>
          CACHE <cache-value>

ALTER SEQUENCE

-- ALTER MINVALUE
ALTER SEQUENCE 시퀀스명 MINVALUE <new-min-value>;

-- ALTER MAXVALUE
ALTER SEQUENCE 시퀀스명 MAXVALUE <new-max-value>;

-- ALTER INCREMENT
ALTER SEQUENCE 시퀀스명 INCREMENT BY <new-step-value>;

-- SET CYCLE OR NOCYCLE
ALTER SEQUENCE 시퀀스명 <CYCLE | NOCYCLE>;

-- ALTER CACHE
ALTER SEQUENCE 시퀀스명 CACHE <new-cache-value>;

DROP SEQUENCE

DROP SEQUENCE 시퀀스명;

DCL (Data Control Language; 데이터 제어 언어)

CREATE USER

CREATE USER 유저명 IDENTIFIED BY <user-password>;

ALTER USER

ALTER USER 유저명 IDENTIFIED BY <new-user-password>;

GRANT

GRANT 권한 TO 유저명;

TCL (Transaction Control Language; 트랜잭션 제어 언어)

COMMIT

ROLLBACK

2일차 - SELECT 구문 사용법

SELECT 기본 구문

명령어	설명
SELECT	테이블에 있는 속성을 지정하여 데이터를 조회하는 명령어 (주로 속성(컬럼)을 기술)
FROM	데이터가 있는 테이블이나 뷰를 지정하는 명령어 (주로 테이블, 뷰, 인라인뷰 등을 기술)
WHERE	데이터를 조회하는 조건을 지정하는 명령어 (주로 조건을 기술)
GROUP BY	특정 속성을 기준으로 그룹화하여 검색할 때 사용하는 명령어 (주로 SELECT 절에 사용된 속성을 그룹핑할 때 기술)
HAVING	그룹화된 속성에 조건을 지정하는 명령어 (주로 ~보다 크거나 작은 경우, 같은 경우를 지정)
ORDER BY	정렬 명령어 (주로 속성값에 오름차순, 내림차순 속성을 지정)

SELECT 기본 예제

테스트용 테이블 생성 및 관련 데이터 입력 SQL
- employees : 사원 테이블
- sales : 판매 테이블
- customers : 고객 테이블
- orders : 주문 테이블
- students : 학생 테이블

`employees` 사원 테이블 생성

CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    department_id NUMBER,
    salary        NUMBER,
    hire_date     DATE
);

-- 샘플 데이터 입력 
INSERT INTO employees VALUES (1, 10, 4800, TO_DATE('2006-03-15', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (2, 10, 5200, TO_DATE('2007-06-01', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (3, 20, 6000, TO_DATE('2005-08-20', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (4, 20, 5500, TO_DATE('2004-11-10', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (5, 30, 7000, TO_DATE('2006-12-25', 'YYYY-MM-DD'));
commit;

`sales` 판매 테이블 생성

CREATE TABLE sales (
    sale_id          NUMBER PRIMARY KEY,
    sale_date        DATE,
    product_category VARCHAR2(50),
    sale_amount      NUMBER
);

INSERT INTO sales VALUES (1, TO_DATE('2023-01-15', 'YYYY-MM-DD'), '전자제품', 300000);
INSERT INTO sales VALUES (2, TO_DATE('2023-02-10', 'YYYY-MM-DD'), '전자제품', 400000);
INSERT INTO sales VALUES (3, TO_DATE('2023-04-05', 'YYYY-MM-DD'), '가전',     500000);
INSERT INTO sales VALUES (4, TO_DATE('2023-07-20', 'YYYY-MM-DD'), '가전',     600000);
INSERT INTO sales VALUES (5, TO_DATE('2023-10-03', 'YYYY-MM-DD'), '도서',     200000);
INSERT INTO sales VALUES (6, TO_DATE('2023-10-15', 'YYYY-MM-DD'), '도서',     850000);
commit;

`students` 학생 테이블 생성

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    department VARCHAR2(100),
    score      NUMBER
);

::<source lang=sql>
INSERT INTO students VALUES (1, '컴퓨터공학과', 90);
INSERT INTO students VALUES (2, '컴퓨터공학과', 85);
INSERT INTO students VALUES (3, '전자공학과',   88);
INSERT INTO students VALUES (4, '전자공학과',   82);
INSERT INTO students VALUES (5, '심리학과',     78); -- 제외됨 (80 미만)
INSERT INTO students VALUES (6, '경영학과',     95);
INSERT INTO students VALUES (7, '경영학과',     90);
commit;

`customers` 고객, `orders` 주문 테이블 생성

CREATE TABLE customers (
    customer_id   NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100)
);

CREATE TABLE orders (
    order_id     NUMBER PRIMARY KEY,
    customer_id  NUMBER,
    order_amount NUMBER,
    order_date   DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 고객
INSERT INTO customers VALUES (1, '홍길동');
INSERT INTO customers VALUES (2, '김영희');
INSERT INTO customers VALUES (3, '이철수');

-- 주문
INSERT INTO orders VALUES (101, 1, 2000000, TO_DATE('2022-02-15', 'YYYY-MM-DD'));
INSERT INTO orders VALUES (102, 1, 3500000, TO_DATE('2022-06-10', 'YYYY-MM-DD'));
INSERT INTO orders VALUES (103, 2, 1500000, TO_DATE('2022-07-20', 'YYYY-MM-DD'));
INSERT INTO orders VALUES (104, 2, 2000000, TO_DATE('2022-09-12', 'YYYY-MM-DD'));
INSERT INTO orders VALUES (105, 3, 500000,  TO_DATE('2022-03-01', 'YYYY-MM-DD'));
commit;

예제 1. 부서/사원 조회

부서별 평균 급여가 5000 이상인 부서 중
2005년 이후 입사한 사원만 조회하여
평균 급여가 높은 순으로 정렬

SELECT department_id AS 부서번호
     , COUNT(*) AS 사원수
     , ROUND(AVG(salary), 0) AS 평균급여
     , MAX(hire_date) AS 최근입사일
  FROM employees
-- 2005년 이후 입사한 사원만 조회
 WHERE hire_date > TO_DATE('2005-01-01', 'YYYY-MM-DD')
-- 부서별 평균 급여가 5000 이상인 부서
 GROUP BY department_id
HAVING AVG(salary) >= 5000
-- 평균 급여가 높은 순으로 정렬
ORDER BY 평균급여 DESC;

예제 2. 제품 판매 분석

2023년 분기별, 카테고리별 판매액 집계
총 판매액이 100만원 이상인 그룹만 표시
분기와 카테고리 순으로 정렬

SELECT TO_CHAR(sale_date, 'Q') AS 분기
     , product_category AS 카테고리
     , COUNT(*) AS 판매건수
     , SUM(sale_amount) AS 총판매액
     , AVG(sale_amount) AS 평균판매액
  FROM sales
-- 2023년 분기별,  
 WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
-- 판매액 별,카테고리별 그룹
 GROUP BY TO_CHAR(sale_date, 'Q'), product_category
-- 총 판매액이 100만원 이상
HAVING SUM(sale_amount) >= 1000000
 ORDER BY 분기 ASC, 총판매액 DESC;

예제 3. 학생 성적 조회

각 학과별로 성적이 80점 이상인 학생들만 집계
학과 평균이 85점 이상인 그룹만 선택
평균 점수 순으로 정렬

SELECT department AS 학과
     , COUNT(*) AS 학생수
     , ROUND(AVG(score), 1) AS 평균점수
     , MAX(score) AS 최고점수
     , MIN(score) AS 최저점수
  FROM students
-- 성적이 80점 이상인 
 WHERE score >= 80
-- 학과  평균이 85점 이상인 그룹만
 GROUP BY department
HAVING AVG(score) >= 85
-- 평균 점수 순으로 정렬
 ORDER BY 평균점수 DESC;

예제 4. 고객 구매 패턴 조회(ANSI SQL)

연간 구매액 500만원 이상
2022년에 구매한 고객별 구매 통계
총 구매액 순으로 정렬

SELECT c.customer_id AS 고객ID
     , c.customer_name AS 고객명
     , COUNT(o.order_id) AS 주문횟수
     , SUM(o.order_amount) AS 총구매액
     , MAX(o.order_date) AS 최근구매일
  FROM customers c
  JOIN orders o 
   ON c.customer_id = o.customer_id
-- 2022년에 연간 
 WHERE o.order_date BETWEEN TO_DATE('2022-01-01', 'YYYY-MM-DD') AND TO_DATE('2022-12-31', 'YYYY-MM-DD')
-- 구매액이 500만원이상 구매한 고객아이디,고객명 그룹별
 GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.order_amount) >= 5000000
-- 총 구매액 순으로 정렬 
 ORDER BY 총구매액 DESC;

3일차 - SELECT 구문 - WITH절

WITH 절

WITH 절이란?

(영문)The WITH clause lets you define a temporary result set (also called a CTE: Common Table Expression) that you can refer to within your main query.
(한글)WITH 절은 *임시 테이블(=서브쿼리 결과)을 먼저 정의하고, 그걸 메인 쿼리에서 재사용할 수 있게 해주는 문법이에요.또한, CTE 라고 불려요

왜 사용할까?

복잡한 서브쿼리를 미리 이름 붙여서 정리할 수 있음
가독성 좋아짐
쿼리 재사용 가능 (같은 서브쿼리를 여러 번 쓰지 않아도 됨)
중첩된 서브쿼리를 피할 수 있음

기본 문법

WITH 임시이름 AS (
    서브쿼리
)
SELECT ...
FROM 임시이름;

사용 예제

예제 테이블: sales

id	product	amount	year
1	Laptop	2000	2023
2	Phone	1500	2023
3	Laptop	2200	2024
4	Phone	1800	2024
5	Laptop	2500	2024

⸻

예제 1: 2024년 판매 데이터만 따로 떼어서 분석

WITH sales_2024 AS (
    SELECT *
    FROM sales
    WHERE year = 2024
)
SELECT product, SUM(amount) AS total
FROM sales_2024
GROUP BY product;

결과

product	total
Laptop	4700
Phone	1800

설명: WITH sales_2024라는 임시 테이블을 만들어 2024년 데이터만 분리하고, 그걸 기준으로 제품별 총 판매액을 계산했어요.

⸻

예제 2: WITH 절을 두 개 이상 사용하는 경우

WITH 
sales_2023 AS (
    SELECT * FROM sales WHERE year = 2023
),
sales_2024 AS (
    SELECT * FROM sales WHERE year = 2024
)
SELECT '2023' AS year, product, SUM(amount) AS total
FROM sales_2023
GROUP BY product
UNION ALL
SELECT '2024' AS year, product, SUM(amount) AS total
FROM sales_2024
GROUP BY product;

결과

year	product	total
2023	Laptop	2000
2023	Phone	1500
2024	Laptop	4700
2024	Phone	1800

설명: 연도별로 따로 나눈 데이터를 UNION ALL로 합쳐서 보여줍니다.

⸻

WITH 절 vs 서브쿼리

항목	WITH 절	서브쿼리
가독성	매우 높음	복잡하면 읽기 어려움
재사용	가능 (한 번 정의해서 여러 번 사용)	동일 쿼리를 반복 작성해야 함
성능	Oracle이 내부적으로 최적화 가능	중복 계산 발생 시 성능 저하 가능

주의사항

Oracle 11g 이상부터 지원되며, Oracle 19c에서도 완벽히 사용 가능
WITH 절은 SELECT, INSERT, UPDATE, DELETE에도 사용할 수 있음
WITH 절 내 쿼리에는 ORDER BY를 직접 넣을 수 없음 (단, ROW_NUMBER()와 함께 정렬 가능)

⸻

요약 정리

개념	설명
WITH 절	서브쿼리를 임시 테이블처럼 이름 붙여 사용
장점	쿼리 재사용, 가독성 향상, 유지보수 용이
사용 예	복잡한 쿼리 정리, 여러 조건 조합 분석 등

WITH 절 고급

WITH 재귀 쿼리란?

재귀 CTE(Common Table Expression)는 WITH 절 안에서 자기 자신을 호출해서 계층적으로 데이터를 조회할 수 있는 기능입니다.
주로 트리 구조, 부서-직원, 폴더-파일, 부모-자식 관계 등 계층형 데이터를 처리할 때 사용합니다.

기본 구조

WITH cte_name (컬럼들) AS (
    -- anchor (기준이 되는 첫 번째 쿼리)
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    -- recursive (재귀적으로 자기 자신 호출)
    SELECT ...
    FROM 원본테이블 t
    JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;

⸻

실전 예제: 조직도 계층 구조 조회

예제 테이블: employees

emp_id	name	manager_id
1	CEO	`NULL`
2	Alice	1
3	Bob	1
4	Carol	2
5	David	2
6	Eve	3

⸻

목표

CEO → 부하 직원 → 그 밑 직원 순으로 전체 조직 계층도를 출력하고 싶어요.

⸻

쿼리

WITH org_chart (emp_id, name, manager_id, level) AS (
    -- Anchor: 최상위 관리자 (CEO)
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: 상위 직원의 부하직원들
    SELECT e.emp_id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT LPAD(' ', (level - 1) * 4) || name AS indented_name, level
FROM org_chart
ORDER BY level, emp_id;

⸻

결과

indented_name	level
CEO	1
Alice	2
Bob	2
Carol	3
David	3
Eve	3

설명: LPAD를 이용해서 직원 이름 앞에 공백을 넣어 들여쓰기 효과를 주었고, level을 이용해 계층을 구분했어요.

⸻

실전 활용:

메뉴 구조, 카테고리 분류, 댓글 트리 등

게시판 댓글 (대댓글)
온라인 쇼핑몰 카테고리 (대분류 > 중분류 > 소분류)
파일 시스템 (폴더 > 하위 폴더 > 파일)
사내 조직도

⸻

주의사항 (Oracle 19c 기준)

항목	설명
RECURSIVE 키워드	Oracle에서는 쓰지 않습니다. PostgreSQL 같은 DBMS에서만 필요합니다.
UNION ALL 필수	재귀 CTE는 반드시 UNION ALL로 이어야 합니다.
LEVEL, CONNECT BY	기존의 Oracle 계층 쿼리 방식 (CONNECT BY)와도 유사한 역할을 합니다.

⸻

CONNECT BY vs WITH 재귀

특징	CONNECT BY	WITH 재귀(CTE)
문법	Oracle 전용 문법	표준 SQL 방식 (Oracle에서도 지원됨)
읽기 쉬움	간단하지만 복잡해지면 어려움	가독성 높음, 유연함
순서 제어	ORDER SIBLINGS BY 필요	그냥 ORDER BY 사용 가능
컬럼 추가 유연성	낮음	WITH에 원하는 컬럼 자유롭게 추가 가능

⸻ 실전 시나리오별 WITH 절 (특히 재귀 CTE 포함)을 예제

시나리오 1: 조직도 조회 (부서장 → 직원 트리 구조)

상황

인사팀에서 조직도를 트리 형태로 보고 싶어합니다.
employees 테이블에는 emp_id, name, manager_id가 있고, 누가 누구의 상사인지 정보가 들어 있습니다.

쿼리 예제

WITH org AS (
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.emp_id
)
SELECT LPAD(' ', (level - 1) * 4) || name AS name, level
FROM org
ORDER BY level, emp_id;

활용 목적: 사내 인트라넷 조직도, 상하 관계 파악, 평가 구조 설계

시나리오 2: 게시판 댓글 트리 조회

상황

게시판에서 댓글-대댓글 구조를 조회해야 합니다.
comments 테이블: comment_id, parent_id, content

쿼리 예제

WITH reply_tree AS (
    SELECT comment_id, parent_id, content, 1 AS depth
    FROM comments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.comment_id, c.parent_id, c.content, r.depth + 1
    FROM comments c
    JOIN reply_tree r ON c.parent_id = r.comment_id
)
SELECT LPAD(' ', (depth - 1) * 4) || content AS content_tree
FROM reply_tree;

활용 목적: 커뮤니티 사이트, 포럼, 블로그 댓글 관리

시나리오 3: 제품 카테고리 분류 트리

상황

온라인 쇼핑몰에서 상품이 속한 카테고리를 트리로 관리하고 싶어요.
categories 테이블: category_id, parent_id, name

쿼리 예제

WITH category_tree AS (
    SELECT category_id, parent_id, name, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.category_id, c.parent_id, c.name, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT LPAD(' ', (level - 1) * 2) || name AS category_path
FROM category_tree
ORDER BY level, name;

활용 목적: 카테고리 트리 메뉴 생성, 상품 등록 시 분류 관리

시나리오 4: 연속 날짜 생성 (캘린더 만들기)

상황

날짜 기준 보고서 작성 시 날짜 테이블이 없으면 어려움.
WITH 절로 임시 날짜 테이블을 생성해서 활용 가능.

쿼리 예제: 2024년 1월 1일~10일까지 날짜 생성

WITH dates (dt) AS (
    SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD') FROM dual
    UNION ALL
    SELECT dt + 1 FROM dates
    WHERE dt + 1 <= TO_DATE('2024-01-10', 'YYYY-MM-DD')
)
SELECT dt FROM dates;

활용 목적: 일별 매출 집계, 출근/결근 현황 분석, 캘린더 생성

시나리오 5: 누적 매출 구하기 (누적합)

상황

시간 순으로 누적 매출을 보여줘야 하는 보고서.
sales 테이블: sale_date, amount

WITH daily_sales AS (
    SELECT sale_date, amount
    FROM sales
    WHERE sale_date BETWEEN DATE '2024-01-01' AND DATE '2024-01-10'
),
running_total AS (
    SELECT ds1.sale_date, ds1.amount,
        (SELECT SUM(ds2.amount)
         FROM daily_sales ds2
         WHERE ds2.sale_date <= ds1.sale_date) AS cumulative_total
    FROM daily_sales ds1
)
SELECT * FROM running_total
ORDER BY sale_date;

활용 목적: 기간 누적 매출, 사용자 활동 누적, 진도율 계산 등

실전 활용 요약

시나리오	핵심 목적	활용 분야
조직도	계층 구조 조회	인사 시스템
댓글	댓글-대댓글 구조 트리화	커뮤니티, 블로그
카테고리 분류	대/중/소 분류 트리화	쇼핑몰, 콘텐츠 플랫폼
날짜 생성	연속 날짜 생성	보고서, 캘린더, 출석부
누적 매출	시간 순 누적 값 계산	통계 보고서, 재무 데이터

4일차 - SELECT 구문 - GROUP BY절

GROUP BY 절

GROUP BY 절이란?

(영문): GROUP BY is used to group rows that have the same values in specified columns into summary rows.
(한글): GROUP BY는 특정 컬럼 값을 기준으로 같은 값들끼리 묶어서 결과를 보여주는 SQL 절입니다.

특징

집계 함수(예: COUNT(), SUM(), AVG(), MAX(), MIN())와 함께 자주 사용됩니다.

데이터를 그룹별 요약하거나 분류해서 볼 수 있습니다.

사용 방법

SELECT 컬럼명, 집계함수
FROM 테이블명
GROUP BY 컬럼명;

예제

employees 테이블 예시

emp_id	name	department	salary
1	John	Sales	3000
2	Alice	Sales	3200
3	Bob	IT	4000
4	Carol	IT	4200
5	David	HR	2800

예제 1: 부서별 평균 급여 구하기

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

조회 결과

department	avg_salary
Sales	3100
IT	4100
HR	2800

설명: department(부서)별로 묶어서, 각 부서의 salary(급여) 평균을 구한 것입니다.

예제 2: 부서별 직원 수 구하기

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;

조회 결과

department	num_employees
Sales	2
IT	2
HR	1

설명: GROUP BY department를 통해 부서별로 직원 수를 센 것입니다.

주의사항

GROUP BY에 사용하지 않은 컬럼은 SELECT절에 직접 쓸 수 없습니다. (단, 집계 함수(max(),count(),sum()..) 안에 들어가는 경우는 예외)

GROUP BY는 정렬 기능이 아닙니다. 정렬은 ORDER BY를 사용해야 합니다.

GROUP BY 절 고급

HAVING 절

HAVING은 GROUP BY로 그룹화한 결과에 조건을 거는 절입니다.

WHERE는 그룹화 전에 조건, HAVING은 그룹화 후 조건입니다.

예제 테이블: sales

id	product	region	amount
1	Laptop	Seoul	2000
2	Laptop	Busan	1500
3	Phone	Seoul	1000
4	Phone	Busan	1200
5	Laptop	Seoul	2200
6	Phone	Seoul	1300

예제 1: 지역별로 제품 판매 총합이 3000 이상인 경우만 보기

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) >= 3000;

결과

region	total_sales
Seoul	5500

설명: GROUP BY region으로 지역별로 묶고, HAVING을 통해 총합이 3000 이상인 지역만 필터링합니다.

다중 컬럼 GROUP BY

개념 • 두 개 이상의 컬럼을 기준으로 복합적으로 그룹화할 수 있습니다.

예제 2: 지역 + 제품별 판매 총액 구하기

SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY region, product;

결과

지역별 제품 매출
지역	제품	매출액(USD)
Seoul	Laptop	4,200
Seoul	Phone	2,300
Busan	Laptop	1,500
Busan	Phone	1,200

설명: region과 product를 동시에 그룹화하여, 각 지역-제품 조합별로 총 판매량을 계산했습니다.

GROUP BY + JOIN 활용

복잡한 데이터 분석에는 JOIN과 GROUP BY를 함께 사용합니다.

예제 테이블

employees

직원 정보
사원 ID	이름	부서 ID
1	John	10
2	Alice	10
3	Bob	20

departments

부서 정보
부서 ID	부서 이름
10	Sales
20	IT

salaries

직원 급여 정보
사원 ID	급여 (USD)
1	3,000
2	3,500
3	4,000

부서별 평균 급여 구하기 (JOIN 사용)

SELECT d.dept_name, AVG(s.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN salaries s ON e.emp_id = s.emp_id
GROUP BY d.dept_name;

결과

부서별 평균 급여
부서명	평균 급여(USD)
Sales	3,250
IT	4,000
전체 평균	3,625

설명: 여러 테이블을 JOIN해서 dept_name 기준으로 묶고 평균 급여를 계산했습니다.

ROLLUP (고급)

ROLLUP은 그룹별 합계 외에 **전체 합계(총계)**까지 같이 보여줍니다.

예제 4: 제품별 판매 총액과 전체 총액까지 보여주기

SELECT product, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(product);

결과

제품별 매출 현황 (집계 결과)
제품	매출액(USD)
Laptop	3,700
Phone	3,500
전체 합계	7,200

설명: NULL은 전체 총합을 의미합니다. 제품별 합계 + 전체 합계를 한 번에 볼 수 있습니다.

요점정리

SQL 그룹화(Grouping) 주요 개념
개념	설명	사용 예시
GROUP BY	특정 컬럼 값으로 행을 그룹화	SELECT dept, COUNT(*) FROM emp GROUP BY dept
HAVING	그룹화된 결과에 필터링 조건 적용 (WHERE 절과 유사하지만 그룹 후 적용)	SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVING AVG(salary) > 5000
다중 그룹화	두 개 이상 컬럼으로 계층적 그룹화	SELECT dept, gender, COUNT(*) FROM emp GROUP BY dept, gender
JOIN + GROUP BY	여러 테이블 연결 후 그룹화 수행	SELECT d.dept_name, COUNT(e.emp_id) FROM emp e JOIN dept d ON e.dept_id = d.dept_id GROUP BY d.dept_name
ROLLUP	그룹별 소계 + 총계를 한 번에 출력 (다차원 분석용)	SELECT dept, gender, SUM(salary) FROM emp GROUP BY ROLLUP(dept, gender)

5일차 - SELECT 구문 - JOIN

Oracle 테이블 조인 (Table Join)

조인이란?

> 여러 테이블에서 관련된 데이터를 **하나의 결과로 결합**해서 보여주는 SQL 기능입니다.

예: 직원 정보는 `EMPLOYEES`, 부서 정보는 `DEPARTMENTS`에 있을 때

👉 직원 이름 + 부서 이름을 함께 보여주고 싶을 때 조인을 사용합니다.

예제 테이블

-- EMPLOYEES 테이블
| EMP_ID | NAME    | DEPT_ID |
|--------|---------|---------|
| 101    | Alice   | 10      |
| 102    | Bob     | 20      |
| 103    | Charlie | 10      |

-- DEPARTMENTS 테이블
| DEPT_ID | DEPT_NAME  |
|---------|------------|
| 10      | HR         |
| 20      | IT         |
| 30      | Marketing  |

조인 종류

조인 종류	설명
INNER JOIN	두 테이블에서 일치하는 값이 있는 행만 보여줌
LEFT OUTER JOIN	왼쪽 테이블은 무조건 모두 출력, 오른쪽에 일치하는 값이 없으면 NULL
RIGHT OUTER JOIN	오른쪽 테이블은 무조건 모두 출력, 왼쪽에 일치하는 값이 없으면 NULL
FULL OUTER JOIN	양쪽 테이블을 모두 포함, 일치하지 않으면 NULL
CROSS JOIN (카테시안 조인)	두 테이블의 모든 행 조합을 출력 (곱집합)
ANTI JOIN	조건에 맞지 않는 행만 보여줌 (EXISTS가 아닌 NOT EXISTS 조건)

INNER JOIN

SELECT E.EMP_ID, E.NAME, D.DEPT_NAME
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
ON E.DEPT_ID = D.DEPT_ID;

EMP_ID	NAME	DEPT_NAME
101	Alice	HR
102	Bob	IT
103	Charlie	HR

LEFT OUTER JOIN

SELECT E.EMP_ID, E.NAME, D.DEPT_NAME
FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D
ON E.DEPT_ID = D.DEPT_ID;

`EMPLOYEES`의 모든 직원은 다 나오고,
일치하지 않는 부서가 있으면 `DEPT_NAME`은 NULL

RIGHT OUTER JOIN

SELECT E.EMP_ID, E.NAME, D.DEPT_NAME
FROM EMPLOYEES E
RIGHT OUTER JOIN DEPARTMENTS D
ON E.DEPT_ID = D.DEPT_ID;

`DEPARTMENTS`의 모든 부서는 다 나오고,
직원이 없는 부서는 `EMP_ID`, `NAME`이 NULL

FULL OUTER JOIN

SELECT E.EMP_ID, E.NAME, D.DEPT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D
ON E.DEPT_ID = D.DEPT_ID;

`EMPLOYEES`, `DEPARTMENTS` 둘 다 포함
한쪽에만 데이터가 있으면 다른 쪽은 NULL

CARTESIAN JOIN (CROSS JOIN)

SELECT E.EMP_ID, E.NAME, D.DEPT_NAME
FROM EMPLOYEES E
CROSS JOIN DEPARTMENTS D;

조인 조건 없이 **모든 행을 조합**
EMPLOYEES가 3행, DEPARTMENTS가 3행이면 → 결과는 9행

EMP_ID	NAME	DEPT_NAME
101	Alice	HR
101	Alice	IT
101	Alice	Marketing
102	Bob	HR
102	Bob	IT
...	...	...

> ❗ 주의: **WHERE 조건 없이 CROSS JOIN을 쓰면 큰 결과가 나올 수 있어 조심해야 합니다.** 카테시안 조인

ANTI JOIN (조건에 부합하지 않는 데이터만)

SELECT *
FROM DEPARTMENTS D
WHERE NOT EXISTS (
  SELECT 1 FROM EMPLOYEES E WHERE E.DEPT_ID = D.DEPT_ID
);

**직원이 없는 부서만 조회**하는 예제입니다.

DEPT_ID	DEPT_NAME
30	Marketing

> ✅ 실무에서 **특정 값이 존재하지 않는 경우**를 찾을 때 많이 사용합니다.

요점정리

조인 종류	대표 SQL 구문	출력 대상
INNER JOIN	A INNER JOIN B ON 조건	A와 B 모두 조건에 맞는 데이터만
LEFT OUTER JOIN	A LEFT OUTER JOIN B ON 조건	A는 모두, B는 맞는 것만
RIGHT OUTER JOIN	A RIGHT OUTER JOIN B ON 조건	B는 모두, A는 맞는 것만
FULL OUTER JOIN	A FULL OUTER JOIN B ON 조건	A와 B 모두 포함
CROSS JOIN	A CROSS JOIN B	모든 조합 (곱집합)
ANTI JOIN	WHERE NOT EXISTS (서브쿼리)	조건에 부합하지 않는 데이터

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Joins.html#GUID-39081984-8D38-4D64-A847-AA43F515D460

6일차 - INSERT 구문

INSERT 구문

기본 개념 설명

INSERT 구문은 테이블에 새로운 데이터를 추가할 때 사용됩니다.

기본 문법

INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...);

INSERT INTO: 데이터를 추가할 테이블명을 지정합니다.
(컬럼1, 컬럼2, ...): 어떤 컬럼에 값을 넣을지를 지정합니다.
VALUES (...): 해당 컬럼들에 대응하는 값을 입력합니다.

예제 1

1.간단한 데이터 추가

고객 정보를 담는 CUSTOMER 테이블이 아래와 같다고 가정하겠습니다.

ID (NUMBER) NAME (VARCHAR2) EMAIL (VARCHAR2)

SQL 예제:

INSERT INTO CUSTOMER (ID, NAME, EMAIL)
VALUES (1, '홍길동', 'hong@example.com');

이 구문은 ID가 1이고 이름은 ‘홍길동’, 이메일은 ‘hong@example.com’인 고객 정보를 추가하는 것입니다.

⸻

예제 2

컬럼 이름 생략 (모든 컬럼에 값을 넣는 경우)

테이블의 컬럼 순서를 정확히 알고 계신 경우, 컬럼명을 생략하고 아래와 같이 작성하실 수도 있습니다.

INSERT INTO CUSTOMER
VALUES (2, '김철수', 'kim@example.com');

※ 이 방법은 컬럼 순서가 바뀌지 않았을 때만 안전합니다. 컬럼 순서가 바뀌면 오류가 발생할 수 있습니다.

⸻

예제 3

다른 테이블에서 데이터 복사

다른 테이블의 데이터를 이용하여 INSERT하는 방법도 있습니다.

INSERT INTO CUSTOMER (ID, NAME, EMAIL)
SELECT EMP_ID, EMP_NAME, EMP_EMAIL
FROM EMPLOYEE
WHERE DEPARTMENT = 'SALES';

위 구문은 EMPLOYEE 테이블에서 부서가 ‘SALES’인 직원을 CUSTOMER 테이블에 추가하는 예제입니다.

⸻

추가 팁

데이터를 추가한 후, COMMIT; 명령어를 실행하셔야 실제로 데이터가 저장됩니다.
만약 잘못 입력하셨다면, ROLLBACK;을 통해 마지막 COMMIT 이전 상태로 되돌릴 수 있습니다.
컬럼 수와 값의 수는 반드시 일치해야 합니다.

고급 기능

다음 기능들은 대량 데이터 처리, 조건부 삽입, 트랜잭션 제어 등에서 매우 유용합니다.

⸻

INSERT ALL — 여러 행을 한 번에 삽입 (다중 INSERT)

INSERT ALL
  INTO CUSTOMER (ID, NAME, EMAIL) VALUES (3, '이순신', 'lee@example.com')
  INTO CUSTOMER (ID, NAME, EMAIL) VALUES (4, '유관순', 'yoo@example.com')
  INTO CUSTOMER (ID, NAME, EMAIL) VALUES (5, '안중근', 'ahn@example.com')
SELECT * FROM DUAL;

INSERT ALL은 여러 레코드를 한 번에 삽입할 때 사용합니다.
SELECT * FROM DUAL은 형식상 필요합니다. DUAL은 Oracle의 가상 테이블입니다.

INSERT FIRST — 조건에 맞는 첫 번째 문장만 실행

INSERT FIRST
  WHEN SALARY > 5000 THEN
    INTO HIGH_SAL_EMP (EMP_ID, NAME, SALARY) VALUES (EMP_ID, NAME, SALARY)
  WHEN SALARY BETWEEN 3000 AND 5000 THEN
    INTO MID_SAL_EMP (EMP_ID, NAME, SALARY) VALUES (EMP_ID, NAME, SALARY)
  ELSE
    INTO LOW_SAL_EMP (EMP_ID, NAME, SALARY) VALUES (EMP_ID, NAME, SALARY)
SELECT EMP_ID, NAME, SALARY
FROM EMPLOYEE;

이 구문은 조건에 따라 다른 테이블로 분기해서 삽입합니다.
INSERT FIRST는 첫 번째 조건만 실행되며, 나머지는 건너뜁니다.

RETURNING INTO — INSERT한 후 값을 바로 변수에 저장 (PL/SQL에서 사용)

DECLARE
  v_id NUMBER;
BEGIN
  INSERT INTO CUSTOMER (ID, NAME, EMAIL)
  VALUES (CUSTOMER_SEQ.NEXTVAL, '장보고', 'jang@example.com')
  RETURNING ID INTO v_id;

  DBMS_OUTPUT.PUT_LINE('삽입된 고객 ID: ' || v_id);
END;

RETURNING INTO는 삽입 직후에 생성된 값을 변수에 즉시 저장합니다.
보통 시퀀스와 함께 자주 사용됩니다.

병렬 INSERT (대용량 INSERT 성능 향상)

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(CUSTOMER, 4) */
INTO CUSTOMER (ID, NAME, EMAIL)
SELECT EMP_ID, EMP_NAME, EMP_EMAIL
FROM EMPLOYEE;

or

-- 12C 이상부터 힌트로 가능 
INSERT /*+ ENABLE_PARALLEL_DML PARALLEL(CUSTOMER, 4) */
INTO CUSTOMER (ID, NAME, EMAIL)
SELECT EMP_ID, EMP_NAME, EMP_EMAIL
FROM EMPLOYEE;

PARALLEL 힌트를 사용하면 여러 프로세스를 사용해 INSERT 속도 향상 가능
단, ENABLE PARALLEL DML 세션 설정이 필요합니다.

MERGE 문 (조건에 따라 INSERT 또는 UPDATE)

MERGE INTO CUSTOMER c
USING TEMP_CUSTOMER t
ON (c.ID = t.ID)
WHEN MATCHED THEN
  UPDATE SET c.NAME = t.NAME, c.EMAIL = t.EMAIL
WHEN NOT MATCHED THEN
  INSERT (ID, NAME, EMAIL)
  VALUES (t.ID, t.NAME, t.EMAIL);

MERGE 문은 **“있으면 UPDATE, 없으면 INSERT”**의 로직을 한 번에 처리합니다.
대량의 동기화 작업에 매우 유용합니다.

7일차 - UPDATE 구문

기본 UPDATE 문법

UPDATE는 기존 데이터를 수정할 때 사용하는 명령어입니다.

▶ 기본 문법:

UPDATE 테이블명
SET 컬럼1 = 값1, 컬럼2 = 값2, ...
WHERE 조건;

SET: 변경하고 싶은 컬럼과 값을 지정합니다.
WHERE: 어떤 행(row)을 수정할지 조건을 줍니다.
WHERE 절이 없으면 테이블의 모든 행이 수정되므로 주의해야 합니다❗

▶ 예제 1: 고객 이메일 변경

UPDATE CUSTOMER
SET EMAIL = 'new_email@example.com'
WHERE ID = 1;

ID가 1번인 고객의 이메일을 변경합니다.

▶ 예제 2: 가격 일괄 인상

UPDATE PRODUCT
SET PRICE = PRICE * 1.1;

전체 상품 가격을 10% 인상합니다 (WHERE 없이 전체 행 업데이트❗).

▶ COMMIT과 ROLLBACK

• COMMIT; 실행 시 변경 내용이 영구 저장됩니다. • ROLLBACK; 실행 시 마지막 COMMIT 이전 상태로 복구됩니다.

고급 UPDATE 기술

고급 기능은 실무에서 대량 처리, 조건 분기, 복잡한 참조 등에 매우 유용합니다.

▶ 예제 3: 서브쿼리를 이용한 UPDATE

UPDATE CUSTOMER c
SET EMAIL = (
  SELECT E.EMP_EMAIL
  FROM EMPLOYEE E
  WHERE E.EMP_ID = c.ID
)
WHERE EXISTS (
  SELECT 1 FROM EMPLOYEE E WHERE E.EMP_ID = c.ID
);

EMPLOYEE 테이블에서 같은 ID를 가진 사람의 이메일로 CUSTOMER 테이블을 업데이트합니다.

▶ 예제 4: MERGE를 이용한 조건부 UPDATE or INSERT

MERGE INTO CUSTOMER c
USING TEMP_CUSTOMER t
ON (c.ID = t.ID)
WHEN MATCHED THEN
  UPDATE SET c.NAME = t.NAME, c.EMAIL = t.EMAIL
WHEN NOT MATCHED THEN
  INSERT (ID, NAME, EMAIL)
  VALUES (t.ID, t.NAME, t.EMAIL);

MERGE는 업데이트 또는 없으면 삽입까지 자동 처리됩니다.
데이터 동기화에 자주 사용됩니다.

▶ 예제 5: RETURNING INTO — 변경된 값을 PL/SQL 변수에 받기

DECLARE
  v_email VARCHAR2(100);
BEGIN
  UPDATE CUSTOMER
  SET EMAIL = 'update@example.com'
  WHERE ID = 1
  RETURNING EMAIL INTO v_email;

  DBMS_OUTPUT.PUT_LINE('변경된 이메일: ' || v_email);
END;

UPDATE한 결과를 변수에 즉시 받을 수 있어 후속 로직 처리에 유용합니다.

▶ 예제 6: 병렬 UPDATE (성능 최적화용)

ALTER SESSION ENABLE PARALLEL DML;

UPDATE /*+ PARALLEL(CUSTOMER, 4) */
CUSTOMER
SET STATUS = 'ACTIVE'
WHERE JOIN_DATE < SYSDATE - 365;

병렬로 처리하여 대량 데이터 수정 속도 향상 단, PARALLEL DML 활성화가 필요합니다.

요점 정리

구분	기능 설명	예시 구문 유형
초급	기본적인 데이터 수정	UPDATE ... SET ... WHERE
고급 1	서브쿼리를 활용한 값 수정	UPDATE ... SET = (SELECT)
고급 2	조건부 INSERT/UPDATE 통합 처리	MERGE INTO ...
고급 3	변경 결과 즉시 변수에 받기	RETURNING INTO
고급 4	대용량 성능 최적화 (병렬 처리)	UPDATE /+ PARALLEL /

8일차 - UPDATE 구문

MERGE 구문

MERGE는 특히 ETL 작업, 데이터 동기화, UPSERT 작업에서 많이 사용돼요.

MERGE란?

(영문): The MERGE statement allows you to conditionally insert or update data in a target table based on the results of a join with a source table.
(한글): MERGE는 하나의 SQL 문으로, 대상 테이블에 대해 조건에 따라 INSERT(삽입) 또는 **UPDATE(갱신)**를 할 수 있는 문법입니다. 흔히 “UPSERT” (update + insert)라고 부르기도 해요.

언제 쓰나요?

A 테이블에 B 테이블 데이터를 넣고 싶은데, 이미 있는 데이터는 UPDATE 하고 없는 데이터는 INSERT 해야 할 때
데이터 동기화, 이력 테이블 관리, 배치 업데이트 등에서 자주 사용

기본 문법

MERGE INTO 대상테이블 t
USING 원본테이블 s
ON (t.기준컬럼 = s.기준컬럼)
WHEN MATCHED THEN
    UPDATE SET t.컬럼1 = s.컬럼1, ...
WHEN NOT MATCHED THEN
    INSERT (컬럼1, 컬럼2, ...)
    VALUES (s.컬럼1, s.컬럼2, ...);

예제

예제 테이블

대상 테이블: target_employees

ID	Employee	Monthly Salary
1	Alice	$3,000
2	Bob	$3,200

원본 테이블: source_employees

Employee ID	Name	Salary (USD)
2	Bob	3,500
3	Carol	2,800

**target_employees** 테이블
emp_id	name	salary
1	Alice	3000
2	Bob	3200

**source_employees** 테이블
emp_id	name	salary
2	Bob	3500
3	Carol	2800

MERGE 쿼리

MERGE INTO target_employees t
USING source_employees s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
    INSERT (emp_id, name, salary)
    VALUES (s.emp_id, s.name, s.salary);

실행 결과

emp_id	name	salary
1	Alice	$3,000
2	Bob	$3,500
3	Carol	$2,800

조건 추가 (WHERE 사용)

WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary
    WHERE s.salary > t.salary -- 조건 만족할 때만 갱신

WHEN NOT MATCHED THEN
    INSERT (emp_id, name, salary)
    VALUES (s.emp_id, s.name, s.salary)
    WHERE s.salary >= 2500; -- 조건 만족할 때만 삽입

장점 요약

장점

설명

한 문장으로 처리

MERGE INTO target t<br>
  USING source s<br>
  ON (t.id = s.id)<br>
  WHEN MATCHED THEN UPDATE SET...<br>
  WHEN NOT MATCHED THEN INSERT...

 → 단일 쿼리로 INSERT/UPDATE 동시 처리 가능

조건부 UPSERT

WHEN MATCHED AND s.status='active' THEN UPDATE...<br>
  WHEN MATCHED AND s.status='expired' THEN DELETE...

 → 조건에 따라 UPDATE/DELETE/INSERT 액션 분기 처리

성능 최적화 가능

 - 테이블 풀 스캔 방지

 - 인덱스 활용한 효율적 비교

 - 네트워크 왕복 횟수 감소 (1회 실행)

주의사항

MERGE는 DELETE도 처리 가능하지만, 사용 시 주의 필요
트리거나 제약조건이 있는 테이블에서는 충돌 가능성 있음
대상 테이블과 원본 테이블은 충분히 식별 가능한 기준 컬럼으로 JOIN 해야 함

9일차 - SQL 활용

Oracle SQL과 ANSI SQL의 차이

Oracle SQL vs ANSI SQL

{{{요점 |내용= 개념정리

항목	설명
Oracle SQL	Oracle Database에 최적화된 전용 SQL 문법 스타일입니다.
ANSI SQL	국제 표준화된 SQL 문법으로, 다양한 DBMS에서 공통적으로 사용하는 방식입니다.
차이점 발생	주로 JOIN 문법, 시퀀스 사용법, 함수명 등에서 차이가 납니다.

JOIN 문법 차이점

Oracle 스타일

SELECT e.EMP_NAME, d.DEPT_NAME
  FROM EMPLOYEE e
     , DEPARTMENT d
 WHERE e.DEPT_ID = d.DEPT_ID;

ANSI SQL 스타일 (표준 Join)

SELECT e.EMP_NAME, d.DEPT_NAME
  FROM EMPLOYEE e
  JOIN DEPARTMENT d
    ON e.DEPT_ID = d.DEPT_ID;

Oracle 스타일은 WHERE절에서 조인을 처리
ANSI 스타일은 JOIN절을 명시적으로 사용 (가독성 좋고 유지보수 용이)

OUTER JOIN

Oracle 스타일

SELECT e.EMP_NAME, d.DEPT_NAME
  FROM EMPLOYEE e, DEPARTMENT d
 WHERE e.DEPT_ID = d.DEPT_ID(+);

(+) 기호는 Oracle 전용 문법입니다 (우측 테이블 기준 OUTER JOIN)

ANSI SQL 스타일

SELECT e.EMP_NAME, d.DEPT_NAME
FROM EMPLOYEE e
LEFT JOIN DEPARTMENT d
  ON e.DEPT_ID = d.DEPT_ID;

LEFT JOIN, RIGHT JOIN 명시적으로 사용 (ANSI)

시퀀스를 사용한 INSERT

Oracle 스타일

INSERT INTO CUSTOMER (ID, NAME)
VALUES (CUSTOMER_SEQ.NEXTVAL, '홍길동');

Oracle은 시퀀스명.NEXTVAL 방식으로 고유값을 생성합니다.

ANSI SQL 스타일

ANSI SQL에는 직접적인 NEXTVAL 문법이 없고, 일반적으로 IDENTITY 또는 AUTO_INCREMENT 사용을 다른 DBMS에서 지원합니다.

(Oracle에서 표준 방식처럼 하려면 IDENTITY 컬럼 사용)

-- Oracle 12c 이상에서 ANSI 스타일 유사하게 사용하는 경우
CREATE TABLE CUSTOMER (
  ID NUMBER GENERATED ALWAYS AS IDENTITY,
  NAME VARCHAR2(100)
);

Oracle SQL vs ANSI SQL 요점 정리

Oracle SQL과 ANSI SQL 비교
항목	Oracle SQL 스타일	ANSI SQL 스타일
INNER JOIN	SELECT * FROM emp e, dept d WHERE e.dept_id = d.dept_id;	SELECT * FROM emp e JOIN dept d ON e.dept_id = d.dept_id;
OUTER JOIN	SELECT * FROM emp e, dept d WHERE e.dept_id = d.dept_id(+);	SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id;
시퀀스 사용	INSERT INTO customer (id) VALUES (customer_seq.NEXTVAL);	-- Oracle 12c 이상: GENERATED AS IDENTITY 사용
조인 표현 방식	WHERE절 중심 조인	JOIN절 중심 명시적 조인
가독성 및 유지보수	복잡한 조인일수록 해석 어려움	구조적이고 읽기 쉬움

내용: 결론 요약
Oracle SQL: Oracle에 특화된 문법. 속도, 유연성 좋지만 이식성이 낮음.

ANSI SQL: 표준 SQL. 다양한 DBMS와 호환되며 유지보수가 용이함.

신규 개발이나 협업 환경에서는 ANSI SQL 권장.

10일차 - SQL 활용

Oracle 에서 SQL이 실행될 때 내부적으로 어떤 단계들을 거치는지 단계별로 하나씩 설명드릴게요.

Oracle SQL 실행 단계

단계별 용어 설명

용어	설명
SQL	Structured Query Language. 오라클 DB에 명령을 내리는 언어. 예: SELECT, INSERT, UPDATE, DELETE
파싱 (Parsing)	SQL 문을 분석하여 문법 검사 및 필요한 객체(테이블, 컬럼 등) 확인. 실행 계획 재사용 여부도 판단
하드 파싱 (Hard Parse)	이전에 실행된 적 없는 새로운 SQL일 때, 처음부터 실행 계획을 다시 만드는 작업. 성능 부담이 큼
소프트 파싱 (Soft Parse)	같은 SQL 문이 이미 실행된 적이 있을 때, 기존 실행 계획을 재사용함. 속도가 빠름
바인드 변수 (Bind Variable)	SQL 안에서 값 대신 사용하는 변수. 예: `:id`. 성능과 보안에 좋음
옵티마이저 (Optimizer)	SQL을 가장 빠르고 효율적으로 실행하기 위해 최적의 실행계획을 만드는 엔진
실행 계획 (Execution Plan)	옵티마이저가 만든 SQL 실행 방법의 설계도. 어떤 인덱스를 쓸지, 어떤 순서로 처리할지 결정
커서 (Cursor)	SQL 실행 결과를 저장하고, 그 결과를 한 행씩 처리하기 위한 메모리 영역
페칭 (Fetching)	SELECT 결과를 한 행씩 가져오는 작업
DML	Data Manipulation Language. 데이터를 다루는 명령어: INSERT, UPDATE, DELETE, SELECT 등
DDL	Data Definition Language. 데이터 구조를 정의하는 명령어: CREATE, ALTER, DROP 등

SQL 실행시 오라클 처리 절차

사용자가 SQL 문을 보냄
• 사용자가 SELECT * FROM employees; 같은 SQL 문을 작성해서 Oracle DB에 보냅니다.
파싱(Parsing)
• 오라클이 이 SQL 문장을 문법적으로 맞는지 확인하고,

• 어떤 테이블, 컬럼, 권한을 쓰는지 체크합니다.

• 만약 같은 SQL 문이 이전에 실행된 적이 있다면, 기존 결과(실행계획)를 재사용할 수 있습니다. (이를 Soft Parse라고 함)

• 처음 실행이거나 다른 SQL이면 새로운 계획을 짭니다. (이를 Hard Parse라고 함)
바인딩(Bind)
• SQL 문 안에 변수가 있다면 (WHERE id = :1 같은), 여기에 실제 값을 넣습니다.

• 주로 애플리케이션에서 사용하는 기능입니다.
최적화(Optimization)
• SQL이 효율적으로 실행되도록 가장 빠른 실행 방법(실행계획, Execution Plan)을 계산합니다.

• 예: 인덱스를 쓸까? 테이블 전체를 읽을까?
실행(Execution)
• 실제로 데이터를 읽거나 쓰는 작업이 시작됩니다.

• 예: 테이블에서 필요한 행을 찾아서 읽습니다.
결과 반환(Fetching)
• 실행된 결과를 한 줄씩 사용자에게 돌려줍니다.

• 예: SELECT면 결과 행을 돌려주고,

• INSERT, UPDATE, DELETE면 몇 개 행이 영향을 받았는지 알려줍니다.

실행 흐름 요약

- 단계 설명

단계	설명
1. SQL 전송	사용자가 SQL 문을 Oracle DB에 보냄
2. 파싱 (Parsing)	SQL 문법 검사, 필요한 테이블/컬럼/권한 확인. 이전 실행 계획이 있으면 재사용 (Soft Parse), 없으면 새로 생성 (Hard Parse)
3. 바인딩 (Binding)	변수(SQL 바인드 변수)에 실제 값을 연결. 주로 애플리케이션에서 사용
4. 최적화 (Optimization)	가장 효율적인 실행 방법(실행계획)을 계산. 인덱스 사용 여부 등 결정
5. 실행 (Execution)	실제 데이터 조회 또는 변경 작업 수행
6. 결과 반환 (Fetching)	결과를 사용자에게 반환. SELECT는 결과 행 반환, DML(INSERT, UPDATE, DELETE)은 처리된 행 수 반환

EXPLAIN PLAN이란?

SQL 문이 어떻게 실행될 예정인지 보여주는 계획표입니다.
인덱스를 쓸지, 테이블을 어떻게 읽을지 등을 확인할 수 있어 성능 튜닝에 매우 중요합니다.
실행 결과를 알려주는 건 아니고, 실행 “전”에 Oracle이 세운 실행 전략을 보여줍니다.

사용 방법

1단계: PLAN_TABLE 준비 (보통 기본 제공됨)

@?/rdbms/admin/utlxplan.sql

한 번만 실행하면 됩니다. PLAN_TABLE이라는 테이블이 생성됩니다.

2단계: 실행 계획 수집

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

3단계: 실행 계획 보기

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

3. 예제

EXPLAIN PLAN FOR
SELECT first_name FROM employees WHERE employee_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

4.요약

단계	설명	예제
1. PLAN_TABLE 생성	실행 계획을 저장할 테이블 생성 (한 번만 실행하면 됨)	@?/rdbms/admin/utlxplan.sql
2. EXPLAIN PLAN 실행	특정 SQL 문에 대한 실행 계획을 저장	EXPLAIN PLAN FOR SELECT * FROM employees;
3. 실행 계획 조회	저장된 실행 계획을 확인	SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQL 실행 계획을 해석하는 법

실행 계획(Execution Plan) 해석 - 주요 용어와 의미

실행 방식	설명	성능 특징
TABLE ACCESS FULL	테이블 전체를 처음부터 끝까지 읽음	느릴 수 있음 (행이 많을수록 비효율적)
TABLE ACCESS BY INDEX ROWID	인덱스로 찾은 후, 해당 행을 테이블에서 읽음	보통 빠름 (인덱스를 쓰기 때문에)
INDEX UNIQUE SCAN	인덱스에서 정확히 하나의 값을 빠르게 찾음 (PK 또는 Unique Index)	매우 빠름
INDEX RANGE SCAN	인덱스에서 범위 조건(>, <, BETWEEN 등)으로 여러 값을 검색	빠름
INDEX FULL SCAN	전체 인덱스를 처음부터 끝까지 읽음 (WHERE 절 없이 인덱스 컬럼만 SELECT할 때 등)	테이블보다 빠를 수 있음
NESTED LOOPS	작은 테이블 기준으로 반복해서 다른 테이블을 조회	소량의 데이터에 적합
HASH JOIN	두 테이블을 해시 테이블로 결합하여 조인	대량의 데이터 조인에 적합
MERGE JOIN	정렬된 두 테이블을 병합하면서 조인	정렬된 데이터 조인 시 효율적

해석 예시

EXPLAIN PLAN FOR
SELECT first_name FROM employees WHERE employee_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과 예:

| Id | Operation                   | Name       |
|----|-----------------------------|------------|
|  0 | SELECT STATEMENT            |            |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |
|  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |

해석:

INDEX UNIQUE SCAN: PK 인덱스를 사용해 employee_id = 100을 빠르게 찾음
TABLE ACCESS BY INDEX ROWID: 인덱스로 찾은 위치를 바탕으로 테이블에서 실제 데이터를 읽음

이런 식으로 계획을 보면 인덱스를 잘 사용하고 있는지, 전체 테이블을 읽는 불필요한 작업은 없는지 판단할 수 있어요.

11일차 - 최적의 SQL 작성법

12일차 - 서브쿼리

스칼라 인라인뷰

13일차 - 원도우 함수

윈도우 함수란?

일반적인 집계 함수(SUM, AVG, COUNT, 등)는 여러 행을 하나로 집계합니다.
윈도우 함수는 집계를 하되, 각 행마다 결과를 계산해서 반환합니다.
OVER() 절을 사용하며, PARTITION BY, ORDER BY, ROWS BETWEEN 등을 조합하여 정교한 분석이 가능합니다.

윈도우 함수 와 분석 함수는 같은것인가 ?

결론: 모든 분석 함수는 윈도우 함수의 한 종류입니다. Oracle에서는 둘을 거의 같은 의미로 사용합니다.
- 분석 함수 (Analytic Function) : Oracle에서 제공하는 특수한 SQL 함수로, OVER() 절을 통해 행의 집합(Window)에 대해 계산함
- 윈도우 함수 (Window Function) : SQL 표준에서 정의한 개념으로, OVER() 절을 사용하는 모든 함수를 가리킴

* Oracle에서 분석 함수(=윈도우 함수)

순위 함수: RANK(), DENSE_RANK(), ROW_NUMBER()
누적 함수: SUM(), AVG(), COUNT(), MAX(), MIN() with OVER()
시차 함수: LAG(), LEAD()
분포 함수: NTILE(), PERCENT_RANK(), CUME_DIST()
통계 함수: FIRST_VALUE(), LAST_VALUE()

모두 OVER() 절을 동반해야 "분석 함수"로 동작합니다.

구문 기본 형태

<함수명>(컬럼) OVER (
  PARTITION BY ...   -- 그룹핑 기준
  ORDER BY ...       -- 정렬 기준
  ROWS BETWEEN ...   -- 분석 범위
)

대표적인 윈도우 함수 목록

Oracle 윈도우 함수 종류와 예제
함수명	설명	예제 SQL	설명 결과
ROW_NUMBER()	파티션 내 행 번호를 순서대로 부여	SELECT EMP_NAME , ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RN FROM EMPLOYEE;	각 부서별 급여 내림차순 순위 (중복 순위 없음)
RANK()	동점자에게 같은 순위를 부여하고 다음 순위는 건너뜀	SELECT EMP_NAME , RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RANK FROM EMPLOYEE;	부서 내 급여 순위 (동점자 동일 순위)
DENSE_RANK()	RANK와 비슷하지만 순위를 건너뛰지 않음	SELECT EMP_NAME , DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS DRANK FROM EMPLOYEE;	순위에 공백 없이 채워짐
LAG()	현재 행 기준 이전 행의 값을 가져옴	SELECT EMP_NAME, SALARY , LAG(SALARY) OVER (ORDER BY EMP_ID) AS PREV_SAL FROM EMPLOYEE;	이전 직원의 급여 정보 확인
LEAD()	현재 행 기준 다음 행의 값을 가져옴	SELECT EMP_NAME, SALARY , LEAD(SALARY) OVER (ORDER BY EMP_ID) AS NEXT_SAL FROM EMPLOYEE;	다음 직원의 급여 정보 확인
SUM(), AVG(), COUNT(), MAX(), MIN()	집계 함수도 윈도우와 함께 사용 가능	SELECT EMP_NAME, SALARY , AVG(SALARY) OVER (PARTITION BY DEPT_ID) AS AVG_SAL FROM EMPLOYEE;\|\|부서별 평균 급여
FIRST_VALUE(), LAST_VALUE()	파티션 내 정렬 기준 첫/마지막 값 반환	SELECT EMP_NAME, SALARY , FIRST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS TOP_SAL FROM EMPLOYEE;	부서별 최고 급여

⸻

예제: 누적합(Cumulative Sum)

SELECT EMP_NAME, SALARY,

 SUM(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY) AS CUM_SAL

FROM EMPLOYEE;

부서별 급여 누적합을 계산합니다.

⸻

윈도우 프레임 옵션

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

의미	설명
UNBOUNDED PRECEDING	현재 파티션의 맨 처음부터 시작하는 윈도우 범위 지정 (예: 파티션 시작 ~ 현재 행까지 누적 계산 시 사용)
CURRENT ROW	현재 행까지만 포함하는 윈도우 범위 지정 (예: 이동 평균 계산 시 행 단위 제한)
1 PRECEDING, 1 FOLLOWING	현재 행을 기준으로 앞뒤 1행씩 포함한 범위 지정 (예: 3행 이동 평균 계산 시 사용)

예:

SUM(SALARY) OVER (
  PARTITION BY DEPT_ID 
  ORDER BY SALARY 
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

부서별로 급여 순으로 정렬하고, 현재 행까지 누적된 급여 합계를 구합니다.

실무 예제 1: 부서별 상위 3위 급여 직원 조회

SELECT *
FROM (
  SELECT EMP_ID, EMP_NAME, DEPT_ID, SALARY,
         RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS SAL_RANK
    FROM EMPLOYEE
)
WHERE SAL_RANK <= 3;

각 부서별로 급여 순위를 매긴 뒤, 상위 3명의 직원만 필터링합니다.

실무 활용: 팀별 Top Performer 조회 등

실무 예제 2: 직전과의 급여 변화량 분석

SELECT EMP_ID, EMP_NAME, SALARY,
       LAG(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY HIRE_DATE) AS PREV_SAL,
       SALARY - LAG(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY HIRE_DATE) AS DIFF_SAL
  FROM EMPLOYEE;

부서 내 입사일 기준으로 정렬하여 직전 직원과 급여 차이를 분석합니다.

실무 활용: 급여 갭 파악, 트렌드 분석 등

실무 예제 3: 부서별 누적 인건비 계산

SELECT EMP_ID, EMP_NAME, DEPT_ID, SALARY,
       SUM(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY HIRE_DATE
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_SALARY
  FROM EMPLOYEE;

입사일 기준으로 누적 급여를 계산합니다.

실무 활용: 연속적인 비용 추적, 월별 누적 등

실무 예제 4: 최초 입사자 정보 함께 보여주기 (FIRST_VALUE)

SELECT EMP_ID, EMP_NAME, DEPT_ID, HIRE_DATE,
       FIRST_VALUE(EMP_NAME) OVER (PARTITION BY DEPT_ID ORDER BY HIRE_DATE) AS FIRST_JOINED
  FROM EMPLOYEE;

각 부서에서 가장 먼저 입사한 직원의 이름을 모든 직원에게 보여줍니다.

실무 활용: 리더, 시니어 직원 분석 등

실무 예제 5: 부서 내 직원 수 대비 자신의 비율 (비율 분석)

SELECT EMP_ID, EMP_NAME, DEPT_ID,
       COUNT(*) OVER (PARTITION BY DEPT_ID) AS DEPT_TOTAL,
       1.0 / COUNT(*) OVER (PARTITION BY DEPT_ID) AS RATIO
  FROM EMPLOYEE;

각 직원이 부서 인원 중 차지하는 비율을 계산합니다.

실무 활용: KPI 가중치, 구성비 분석 등

이 외에도, 윈도우 함수는 보고서, 통계 분석, SLA 계산, 재고 흐름 추적 등 다양한 실무 시나리오에 활용됩니다.

요점 정리

• 윈도우 함수는 분석용 SQL에서 매우 중요합니다. • OVER() 절로 집계 범위를 제한하며, 집계 결과를 각 행에 붙여서 반환합니다. • RANK, ROW_NUMBER, LAG/LEAD, FIRST_VALUE 등 다양하게 활용 가능합니다.

14일차 - 그룹 함수

기본 개념 설명

Oracle SQL에서 그룹 함수(Group Functions)는 여러 행(Row)의 값을 그룹으로 묶어 하나의 결과 값으로 요약할 때 사용하는 함수입니다.
주로 GROUP BY절과 함께 사용되며, 집계 함수(Aggregate Functions)라고도 부릅니다.

Oracle의 주요 그룹 함수 종류

Oracle Group Function 종류 및 설명
함수명	설명	예제 SQL	결과 예시
COUNT()	행 수를 셉니다 (NULL 제외)	SELECT COUNT(*) FROM EMPLOYEE;	전체 직원 수
SUM()	숫자 컬럼의 합을 구합니다	SELECT SUM(SALARY) FROM EMPLOYEE;	전체 급여 합계
AVG()	평균 값을 계산합니다	SELECT AVG(SALARY) FROM EMPLOYEE;	평균 급여
MAX()	최댓값을 반환합니다	SELECT MAX(SALARY) FROM EMPLOYEE;	최고 급여
MIN()	최솟값을 반환합니다	SELECT MIN(SALARY) FROM EMPLOYEE;	최저 급여
STDDEV()	표준 편차를 구합니다	SELECT STDDEV(SALARY) FROM EMPLOYEE;	급여 분산도
VARIANCE()	분산을 구합니다	SELECT VARIANCE(SALARY) FROM EMPLOYEE;	급여 분산 값

주의할 점

NULL은 무시되는 함수가 많습니다 (COUNT(col), SUM(col), AVG(col) 등)
GROUP BY에서 사용되지 않은 컬럼은 SELECT에 단독으로 쓸 수 없습니다.
단일 결과를 원할 경우 GROUP BY 없이 사용할 수 있습니다.

예제

기본 예제

SELECT COUNT(*) AS TOTAL_EMPLOYEES,
       SUM(SALARY) AS TOTAL_SALARY,
       AVG(SALARY) AS AVG_SALARY,
       MAX(SALARY) AS MAX_SALARY,
       MIN(SALARY) AS MIN_SALARY
FROM EMPLOYEE;

EMPLOYEE 테이블의 직원 수, 급여 합계, 평균, 최고/최저 급여를 한 번에 출력합니다.

GROUP BY와 함께 사용하는 예제

SELECT DEPT_ID,
       COUNT(*) AS EMP_COUNT,
       AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEE
GROUP BY DEPT_ID;

부서별로 직원 수와 평균 급여를 계산합니다.

HAVING 절로 그룹 조건 추가

WHERE는 행 필터링, HAVING은 그룹 필터링에 사용합니다.

SELECT DEPT_ID,
       COUNT(*) AS EMP_COUNT
FROM EMPLOYEE
GROUP BY DEPT_ID
HAVING COUNT(*) >= 5;

직원 수가 5명 이상인 부서만 조회합니다.

고급 기능 및 실무 예제

▶ 예제 1: 평균 이상 급여 직원 조회

SELECT *
FROM EMPLOYEE
WHERE SALARY > (
  SELECT AVG(SALARY) FROM EMPLOYEE
);

서브쿼리를 통해 전체 평균보다 높은 급여를 받는 직원을 조회합니다.

▶ 예제 2: 월별 매출 요약

SELECT 
  TO_CHAR(SALE_DATE, 'YYYY-MM') AS 월,
  SUM(SALE_AMOUNT) AS 월매출,
  COUNT(*) AS 거래건수
FROM SALES
GROUP BY TO_CHAR(SALE_DATE, 'YYYY-MM')
ORDER BY 월;

TO_CHAR()로 날짜를 월 단위로 변환해 그룹핑합니다.

▶ 예제 3: 부서별 최고 급여자 정보

SELECT *
FROM EMPLOYEE E
WHERE (E.DEPT_ID, E.SALARY) IN (
  SELECT DEPT_ID, MAX(SALARY)
  FROM EMPLOYEE
  GROUP BY DEPT_ID
);

서브쿼리로 부서별 최고 급여를 구하고, 그 조건에 맞는 직원 정보 추출합니다.

▶ 예제 4: 비율(%) 분석

SELECT 
  DEPT_ID,
  COUNT(*) AS 직원수,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS 비율
FROM EMPLOYEE
GROUP BY DEPT_ID;

전체에서 해당 그룹이 차지하는 **구성비(%)**를 계산합니다. SUM(...) OVER ()는 전체 합계를 구할 때 사용하는 윈도우 함수입니다.

▶ 예제 5: 다중 집계 GROUPING SETS, ROLLUP, CUBE

-- ROLLUP: 부서별 + 전체 합계
SELECT DEPT_ID, JOB_ID, SUM(SALARY) AS 합계
FROM EMPLOYEE
GROUP BY ROLLUP(DEPT_ID, JOB_ID);

-- CUBE: 모든 조합(부서, 직무별 합계, 전체 합계 포함)
SELECT DEPT_ID, JOB_ID, SUM(SALARY) AS 합계
FROM EMPLOYEE
GROUP BY CUBE(DEPT_ID, JOB_ID);

-- GROUPING SETS: 원하는 조합만 집계
SELECT DEPT_ID, JOB_ID, SUM(SALARY) AS 합계
FROM EMPLOYEE
GROUP BY GROUPING SETS (
  (DEPT_ID), 
  (JOB_ID), 
  ()
);

복잡한 다차원 집계가 필요한 리포트에 활용됩니다.

요점 정리

Oracle Group Function 고급 기능 요약
기능명	설명	예제 구문
GROUP BY	컬럼 기준으로 행을 그룹화	SELECT DEPT_ID, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPT_ID;
HAVING	그룹 조건 필터링	... HAVING COUNT(*) > 5;
ROLLUP	소계, 총계 계산	GROUP BY ROLLUP(DEPT_ID, JOB_ID)
CUBE	다차원 집계 (모든 조합)	GROUP BY CUBE(DEPT_ID, JOB_ID)
GROUPING SETS	특정 조합만 집계	GROUP BY GROUPING SETS ((DEPT_ID), (JOB_ID), ())
비율 계산	전체 대비 그룹 비율	COUNT() 100 / SUM(COUNT(*)) OVER ()

15일차 - 인덱스

인덱스(Index)란?

책에 비유하면?
- 책에서 특정 단어를 찾을 때 “찾아보기(색인)” 를 이용하죠?
- 오라클의 인덱스도 같은 역할을 해요.
- 데이터를 빠르게 찾기 위해 정리된 목록(색인표) 을 만들어 놓는 거예요.

왜 인덱스가 필요할까?

상황	인덱스 없음	인덱스 있음
SELECT * FROM employees WHERE name = 'Kim';	전체 테이블을 처음부터 끝까지 검사함 ▸ Full Table Scan 발생	인덱스를 따라 바로 'Kim'의 위치로 감 ▸ Index Range Scan 동작

결과: 인덱스가 있으면 훨씬 빠릅니다!

인덱스의 기본 종류

인덱스 종류	설명	특징
B-tree 인덱스	가장 일반적인 인덱스	빠르고 대부분의 검색에 적합
Unique 인덱스	중복을 허용하지 않는 인덱스 (PK 같은 경우)	중복 데이터 방지
Composite 인덱스	여러 컬럼을 묶어서 만든 인덱스	WHERE 절에 여러 컬럼이 있을 때 효과적
Bitmap 인덱스	값의 종류가 적은 컬럼에 적합 (예: 성별)	주로 DW(데이터 웨어하우스)에서 사용
Function-Based 인덱스	계산 결과에 대해 인덱스를 만듦 (예: `UPPER(name)`)	함수가 들어간 WHERE 절에도 인덱스 사용 가능

인덱스는 자동으로 생길까?

Primary Key 또는 Unique 제약 조건을 만들면 Oracle이 자동으로 인덱스를 생성해줘요.
그 외에는 사용자가 직접 CREATE INDEX 문으로 만들어야 해요.

인덱스 만들기 예시

CREATE INDEX idx_emp_name 
   ON employees(name);

인덱스가 항상 좋은 건 아닐까?

읽기 성능은 좋아지지만,데이터를 INSERT, UPDATE, DELETE 할 때는 인덱스도 같이 수정돼야 하므로 부하가 생깁니다.
그래서 필요한 컬럼에만 신중하게 인덱스를 만들어야 해요.

인덱스 고급 기능

1. Function-Based Index (함수 기반 인덱스)

설명

SQL에서 WHERE UPPER(name) = 'KIM' 같이 함수가 쓰이면 일반 인덱스는 사용 못 해요.
이럴 때, 함수의 결과에 인덱스를 미리 만들어두면 성능 향상 가능!

예시

CREATE INDEX idx_upper_name 
   ON employees(UPPER(name));

2. Invisible Index (보이지 않는 인덱스)

설명

인덱스를 일시적으로 비활성화해서, 쿼리가 인덱스를 쓰지 않도록 설정.
성능 테스트할 때 아주 유용해요.

예시

ALTER INDEX idx_emp_name INVISIBLE;   -- 인덱스 감추기
ALTER INDEX idx_emp_name VISIBLE;     -- 다시 보이게 하기

3. Partial Index (부분 인덱스) - Oracle 12c부터는 Zone Maps로 유사 기능

설명

조건에 따라 일부 행에만 인덱스 생성 (일반적으로는 파티션 인덱스로 구현)
예: 2020년 이후 데이터만 인덱스 적용

4. Descending Index (내림차순 인덱스)

설명

정렬이 많은 컬럼에 대해 ORDER BY column DESC 쿼리 성능 향상

예시

CREATE INDEX idx_emp_salary_desc 
   ON employees(salary DESC);

5. Compressed Index (압축 인덱스)

설명

중복되는 인덱스 키가 많은 경우, 공간 절약과 읽기 성능 향상 효과
특히 Composite Index(다중 컬럼 인덱스)에 유리

예시

CREATE INDEX idx_comp 
   ON emp(dept_id, job_id) COMPRESS;

6. Index on Virtual Columns (가상 컬럼 인덱스)

설명

테이블에는 없지만 계산 결과로 만들어지는 가상 컬럼에도 인덱스를 생성할 수 있어요.

예시

ALTER TABLE emp 
       ADD (full_name AS (first_name || ' ' || last_name));

CREATE INDEX idx_fullname 
   ON emp(full_name);

7. Global vs Local Partitioned Index

설명

파티션 테이블에 인덱스를 만들 때,
- Global Index: 전체 테이블 기준 하나의 인덱스
- Local Index: 각 파티션마다 따로 인덱스 생성 (DML 변경 시 유지가 쉬움)

8. Online Index Operations

설명

Oracle 19c에서는 DML 중에도 인덱스를 생성하거나 재구성 가능
서비스 중단 없이 작업 가능 (Enterprise Edition 기준)

예시

CREATE INDEX idx_emp_job 
   ON employees(job_id) 
ONLINE;

요점 정리

고급 기능	설명	예시/비고
Function-Based Index	함수가 포함된 WHERE 절에서도 인덱스를 사용 가능	CREATE INDEX idx_upper_name ON emp(UPPER(name));
Invisible Index	인덱스를 일시적으로 숨겨 쿼리 테스트	ALTER INDEX idx_name INVISIBLE;
Descending Index	내림차순 정렬에 맞는 인덱스	CREATE INDEX idx_desc ON emp(salary DESC);
Compressed Index	중복 키를 압축하여 공간 절약 및 성능 향상	CREATE INDEX idx_comp ON emp(dept_id, job_id) COMPRESS;
Virtual Column Index	계산된 가상 컬럼에도 인덱스 가능	CREATE INDEX idx_virtual ON emp(full_name);
Global / Local Partitioned Index	파티션 테이블 인덱스 최적화 방식	Local은 유지관리 쉬움
Online Index Create/Rebuild	서비스 중단 없이 인덱스 작업 가능	CREATE INDEX ... ONLINE

16일차 - CONNECT BY 절

Oracle CONNECT BY 계층형 쿼리

오라클(Oracle) 데이터베이스에서 계층형 데이터를 조회할 때 CONNECT BY 절을 사용합니다. 트리 구조, 조직도, BOM 구조 등의 데이터를 SQL 하나로 탐색하고 출력할 수 있는 강력한 기능입니다.

이 글에서는 CONNECT BY, START WITH, LEVEL, CONNECT_BY_ROOT, CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, SYS_CONNECT_BY_PATH 등 계층형 SQL에서 사용하는 핵심 요소들을 예제와 함께 정리합니다.

CONNECT BY 절 구조

절	설명
START WITH	계층 구조의 시작점(루트 노드)을 지정
CONNECT BY	부모-자식 간 연결 조건 정의
PRIOR	연결 방향 지정 (부모 → 자식 또는 자식 → 부모)
ORDER SIBLINGS BY	같은 부모를 가진 노드 간 정렬

예:

SELECT employee_id, manager_id, LEVEL
  FROM employees
 START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_id;

PRIOR 키워드 설명

방식 설명

CONNECT BY PRIOR 자식 = 부모	부모 → 자식 (Top-down)

CONNECT BY 부모 = PRIOR 자식	자식 → 부모 (Bottom-up)

예제:

-- 순방향 전개

CONNECT BY PRIOR empno = mgr

-- 역방향 전개

CONNECT BY empno = PRIOR mgr

LEVEL 가상 컬럼

LEVEL은 계층의 깊이를 나타내는 가상 컬럼입니다. 루트 노드는 1부터 시작하며, 자식으로 갈수록 2, 3, ...으로 증가합니다.

CONNECT_BY_ROOT 함수

현재 행의 루트(시작점) 값을 반환합니다.

SELECT LEVEL, ename, CONNECT_BY_ROOT ename AS root_ename
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

CONNECT_BY_ISCYCLE 가상 컬럼

사이클(순환 참조)을 감지할 수 있는 가상 컬럼입니다. NOCYCLE 옵션과 함께 사용해야 하며, 순환이 감지되면 1을, 아니면 0을 반환합니다.

SELECT empno, CONNECT_BY_ISCYCLE, LEVEL
  FROM emp
 START WITH mgr IS NULL
CONNECT BY NOCYCLE PRIOR empno = mgr;

CONNECT_BY_ISLEAF 가상 컬럼

해당 노드가 리프(leaf, 자식이 없는 노드)인지를 판별합니다.

SELECT empno, CONNECT_BY_ISLEAF
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

SYS_CONNECT_BY_PATH 함수

루트부터 현재 행까지의 경로를 구분자와 함께 문자열로 반환합니다.

SELECT empno,
       SYS_CONNECT_BY_PATH(ename, ' > ') AS path
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

성능 팁

   START WITH 조건 컬럼에는 인덱스가 존재해야 성능 저하를 방지할 수 있습니다.

   LEVEL이 깊어질수록 비용이 증가합니다. 필요하다면 LEVEL <= n 조건을 함께 사용하세요.

   DUAL CONNECT BY 형태는 조인 없이 다량의 반복 데이터를 생성할 수 있으나, 성능 이슈 발생 시 MERGE JOIN을 고려하세요.

예:

SELECT /*+ LEADING(A) USE_MERGE(B) */
       COUNT (*)
  FROM t1 a
     , (SELECT LEVEL lv 
          FROM DUAL 
       CONNECT BY LEVEL <= 100) b
 WHERE b.lv <= a.c1;

실전 예제: 테스트 테이블 생성 및 계층 쿼리

CREATE TABLE t1 (
  parent_c VARCHAR2(1),
  child_c VARCHAR2(1)
);

INSERT INTO t1 VALUES ('a','b');
INSERT INTO t1 VALUES ('b','c');
INSERT INTO t1 VALUES ('a','c');
INSERT INTO t1 VALUES ('c','d');
INSERT INTO t1 VALUES ('c','e');
INSERT INTO t1 VALUES ('e','f');
COMMIT;

순방향 계층 전개 (부모 → 자식):

SELECT parent_c, child_c, LEVEL
  FROM t1
 START WITH parent_c = 'a'
CONNECT BY PRIOR child_c = parent_c;

역방향 계층 전개 (자식 → 부모):

SELECT parent_c, child_c, LEVEL
  FROM t1
 START WITH child_c = 'f'
CONNECT BY child_c = PRIOR parent_c;

날짜 생성 활용 예제

1) 일자 리스트 구하기:

SELECT TO_CHAR(TO_DATE('20240101','YYYYMMDD') + LEVEL - 1, 'YYYY-MM-DD') AS day
  FROM dual
CONNECT BY LEVEL <= TO_DATE('20240110','YYYYMMDD') - TO_DATE('20240101','YYYYMMDD') + 1;

2) 월별 리스트 구하기:

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('202201','YYYYMM'), LEVEL-1), 'YYYY-MM') AS month
  FROM dual
CONNECT BY LEVEL <= 12;

결론

CONNECT BY는 단순한 계층 쿼리 기능을 넘어, 조직도, 트리구조, BOM, 기간 생성 등 다양한 용도로 사용되는 필수 SQL 기능입니다.
위에서 다룬 가상 컬럼과 성능 팁을 익히면 보다 효율적으로 데이터 구조를 탐색할 수 있습니다.

17일차 - LATERAL

LATERAL 이란?

LATERAL이란?

(영문)The LATERAL clause allows a subquery in the FROM clause to reference columns from preceding tables in the same FROM clause.

(한글) LATERAL은 FROM 절에 있는 서브쿼리에서 같은 레벨에 있는 테이블의 컬럼을 사용할 수 있게 해주는 기능입니다.(oracle12c 이전에는 FROM절에 있는 걑은 레벨의 테이블을 같은 레벨에 있는 서브쿼리에서 사용할수 없었음)

왜 필요한가요?

기존에는 FROM 절의 **서브쿼리(subquery)**가 외부 테이블의 값을 참조할 수 없었어요.
LATERAL을 쓰면 이 제한을 풀 수 있어, 행 단위로 동적으로 계산하거나 조인할 수 있어요.

기본 문법

SELECT ...
FROM 테이블1 t,
     LATERAL (
         SELECT ... FROM ... WHERE ... t.컬럼 ...
     ) A

Oracle 12c 이상에서는 LATERAL을 쓰지 않고 CROSS APPLY 또는 **OUTER APPLY**를 쓰기도 합니다.
Oracle에서 CROSS APPLY = LATERAL.

예제

예제 테이블

orders

order_id	customer_id
1	100
2	200

customers

customer_id	name
100	Alice
200	Bob
300	Charlie

목표

orders 테이블에서 각 주문에 해당하는 고객 정보를 바로 옆에서 가져오기 (JOIN도 가능하지만, LATERAL은 더 유연한 활용이 가능)

⸻

쿼리 예시

SELECT o.order_id, c.name
FROM orders o,
     LATERAL (
         SELECT name
         FROM customers
         WHERE customers.customer_id = o.customer_id
     ) c;

결과

order_id	name
1	Alice
2	Bob

⸻

5. 동적으로 N개만 가져오기 (Top-N per group)

예제: 각 고객당 최근 주문 1개만 가져오기

SELECT c.customer_id, c.name, o.order_id
FROM customers c,
     LATERAL (
         SELECT order_id
         FROM orders
         WHERE orders.customer_id = c.customer_id
         ORDER BY order_id DESC
         FETCH FIRST 1 ROW ONLY
     ) o;

⸻

6. LATERAL vs APPLY

Oracle 구문	설명
LATERAL	Oracle 12c 이상에서 사용 가능
CROSS APPLY	LATERAL과 동일 (INNER JOIN)
OUTER APPLY	LATERAL + OUTER JOIN 느낌 (NULL 허용)

⸻

7. 요약

항목 내용 목적 FROM 서브쿼리에서 앞의 테이블 값을 참조하고 싶을 때 사용 장점 각 행별로 동적인 서브쿼리 실행 가능 주 사용처 Top-N per group, JSON 파싱, 동적 컬럼 처리 등 필요 조건 Oracle 12c 이상

⸻

보너스: JSON 컬럼 분해에도 유용!

SELECT t.id, j.*
FROM my_table t,
     JSON_TABLE(t.json_col, '$[*]'
       COLUMNS (name VARCHAR2(100) PATH '$.name',
                age  NUMBER       PATH '$.age')) j;

위 예제처럼 JSON_TABLE도 LATERAL처럼 동작합니다.

⸻

실전 시나리오(예: JSON 배열 쪼개기, 시간대별 계산, 조건별 행 생성 등)

18일차 - 정규표현식(regular expression)

Oracle 19c에서 사용하는 정규표현식 패턴

정규표현식 기초 설명 (Oracle 기준)

패턴	의미	쉬운 설명	예시
.	임의의 한 글자	어떤 글자든 한 글자만	`a.c` → "abc", "axc"
*	0회 이상 반복	없어도 되고, 많아도 됨	`a*` → "", "a", "aa", "aaa"
+	1회 이상 반복	적어도 한 번은 나와야 함	`a+` → "a", "aa", "aaa"
?	0회 또는 1회	있을 수도, 없을 수도 있음	`ab?` → "a", "ab"
^	문자열의 시작	맨 앞에서 시작해야 함	`^a` → "apple"에는 일치, "banana"에는 불일치
$	문자열의 끝	맨 끝에서 끝나야 함	`ing$` → "playing", "going"
[abc]	괄호 안 문자 중 하나	a, b, c 중 하나와 일치	`[abc]` → "a", "b", "c"
[^abc]	괄호 안 문자 제외	a, b, c를 제외한 문자와 일치	`[^abc]` → "d", "x"
[a-z]	알파벳 범위	a부터 z 중 하나	`[A-Z]` → 대문자, `[0-9]` → 숫자
{n}	정확히 n번 반복	정확히 n번 반복됨	`a{3}` → "aaa"
{n,}	n번 이상 반복	n번 이상 반복됨	`a{2,}` → "aa", "aaa", ...
{n,m}	n~m회 반복	n 이상 m 이하 반복	`a{2,4}` → "aa", "aaa", "aaaa"
	또는(OR)	둘 중 하나	dog → "cat", "dog"
\	이스케이프 문자	특수기호를 문자로 인식	`\.` → 점(.) 자체

예시로 이해하기

'^[A-Z][a-z]+$'

→ 의미: 첫 글자는 대문자, 그 뒤는 소문자 한 글자 이상, 전체가 한 단어

일치: "John", "David"
불일치: "john", "DAVID", "John123"

^[0-9]{3}-[0-9]{4}-[0-9]{4}$

→ 의미: 전화번호 형식 (숫자3-숫자4-숫자4)

일치: "010-1234-5678"
불일치: "10-1234-5678", "01012345678"

'^\d{6}-\d{7}$'

→ 의미: 주민등록번호 형식 (6자리-7자리 숫자) → \d는 숫자 [0-9]와 같음

일치: "990101-1234567"
불일치: "9901011234567", "99-0101-1234567"

정규 표현식(Regex) 활용 예제
사용 목적	정규식 예시	설명
이메일 검증	`^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$`	영문 소문자/숫자/특수문자(. _ % + -) 조합 @ 뒤에 도메인명과 최소 2자리 이상의 도메인 확장자
전화번호 확인	`^\d{3}-\d{3,4}-\d{4}$`	000-0000-0000 또는 000-000-0000 형식 \d는 숫자([0-9]와 동일)
숫자만 추출	`\d+`	문자열에서 연속된 숫자만 매칭 "+"는 1회 이상 반복 의미
특정 문자 제거	`REGEXP_REPLACE(col, '[^0-9]', )`	[^0-9]: 숫자가 아닌 모든 문자 숫자를 제외한 모든 문자를 공백으로 치환

실전예시

2. Oracle SQL에서 사용하는 정규표현식 함수 예제

1. REGEXP_LIKE – 패턴 일치 여부 확인

SELECT first_name
FROM employees
WHERE REGEXP_LIKE(first_name, '^A');  -- 이름이 A로 시작하는 직원

2. REGEXP_SUBSTR – 패턴에 맞는 부분 문자열 추출

SELECT REGEXP_SUBSTR('john.doe@example.com', '[a-z0-9._%+-]+') AS local_part
FROM dual;
-- 결과: 'john.doe'

3. REGEXP_INSTR – 패턴 위치 찾기

SELECT REGEXP_INSTR('employee123data', '[0-9]+') AS number_pos
FROM dual;
-- 결과: 9 (숫자가 처음 나오는 위치)

4. REGEXP_REPLACE – 패턴에 맞는 문자열 치환

SELECT REGEXP_REPLACE('abc123xyz', '[0-9]', '') AS removed_digits''
FROM dual;
-- 결과: 'abcxyz' (숫자 제거)

5. 전화번호 형식 확인

SELECT phone_number
FROM customers
WHERE REGEXP_LIKE(phone_number, '^\d{3}-\d{3,4}-\d{4}$');
-- 예: '010-1234-5678' 형식만 조회

실무에 유용한 정규식 활용법

1. 이메일 주소 유효성 검사

SELECT email
FROM users
WHERE REGEXP_LIKE(email, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$', 'i');

설명: @ 앞뒤 형식을 체크하고, 대소문자 구분 없이 검사 ('i' 플래그)
일치: user123@gmail.com, test.name@domain.co.kr

2. 주민등록번호 유효성 패턴 확인

SELECT ssn
FROM people
WHERE REGEXP_LIKE(ssn, '^\d{6}-\d{7}$');

설명: 주민번호 형식 ######-####### 검사
일치: 990101-1234567

3. 주민등록번호 마스킹 처리 (정규식 + 치환)

SELECT REGEXP_REPLACE(ssn, '(\d{6})-(\d{7})', '\1-*******') AS masked_ssn
FROM people;

결과: 990101-*******

4. 문자열에서 숫자만 추출

SELECT REGEXP_SUBSTR('abc123def456', '\d+') AS first_number
FROM dual;

결과: 123 (첫 번째 숫자 그룹 추출)

5. 특정 패턴이 포함된 문자열만 추출 (예: http 또는 https로 시작하는 URL)

SELECT REGEXP_SUBSTR('Visit https://example.com now', 'https?://[^\s]+') AS url
FROM dual;

결과: https://example.com

6. 정규표현식으로 공백/특수문자 제거

SELECT REGEXP_REPLACE('hello! @world#2024', '[^a-zA-Z0-9]', '') AS clean_string''
FROM dual;

결과: helloworld2024

7. 특정 문자로 시작하거나 끝나는 문자열 필터링

-- 이름이 'K'로 시작하는 경우
SELECT name FROM employees WHERE REGEXP_LIKE(name, '^K');

-- 이름이 'y'로 끝나는 경우
SELECT name FROM employees WHERE REGEXP_LIKE(name, 'y$');

19일차 - PL/SQL 기초

20일차 - PL/SQL 함수]

Oracle PL/SQL 함수 설명

개념 요약

함수(Function)는 하나의 값을 반환하는 PL/SQL 서브프로그램입니다.
프로시저(Procedure)와 달리, 함수는 항상 RETURN을 통해 결과를 반환해야 하며, SQL문 안에서도 호출이 가능합니다.

주요 특징

반드시 하나의 값 반환
SELECT문, WHERE절, PL/SQL 블록 등 다양한 위치에서 호출 가능
입력 매개변수(Parameter)를 사용할 수 있음

함수 문법

CREATE OR REPLACE FUNCTION 함수이름 (
   매개변수명 데이터타입
) RETURN 반환타입
IS
   -- 변수 선언부
BEGIN
   -- 로직 처리
   RETURN 반환값;
END;

함수 설명

항목	설명
함수(Function)	값을 반환하는 PL/SQL 서브프로그램
사용 목적	로직을 재사용하고 SQL 내에서 값 계산
반환값 필수	RETURN 문을 통해 한 개의 값 반환
호출 위치	SELECT, WHERE, PL/SQL 블록 등
예시	SELECT 함수명(매개변수) FROM DUAL;

예시

CREATE OR REPLACE FUNCTION get_tax (
   p_amount NUMBER
) RETURN NUMBER
IS
BEGIN
   RETURN p_amount * 0.1;
END;

사용 예시:

SELECT get_tax(1000) FROM DUAL;  -- 결과: 100

oracle 내장함수

1. 날짜 및 시간 함수

함수명	설명
SYSDATE	현재 날짜와 시간을 반환
SYSTIMESTAMP	현재 날짜와 시간(타임존 포함)을 반환
ADD_MONTHS(date, n)	날짜에 n개월을 더함
MONTHS_BETWEEN(d1, d2)	두 날짜 사이의 개월 수 계산
LAST_DAY(date)	해당 월의 마지막 날짜 반환
TRUNC(date)	날짜의 시/분/초를 제거 (일 단위로 자름)
ROUND(date, 'fmt')	날짜를 지정한 단위로 반올림

2. 문자 함수

함수명	설명
UPPER(str)	문자열을 대문자로 변환
LOWER(str)	문자열을 소문자로 변환
INITCAP(str)	각 단어의 첫 글자를 대문자로 변환
LENGTH(str)	문자열 길이 반환
SUBSTR(str, start, len)	문자열의 일부 추출
INSTR(str, substr)	부분 문자열의 시작 위치 반환
REPLACE(str, old, new)	문자열 치환
TRIM(str)	문자열 앞뒤의 공백 제거

3. 숫자 함수

함수명	설명
ROUND(n, m)	소수 m자리까지 반올림
TRUNC(n, m)	소수 m자리까지 자름
MOD(n1, n2)	나머지 반환
FLOOR(n)	이하 정수 반환
CEIL(n)	이상 정수 반환
ABS(n)	절대값 반환

4. 변환 함수

함수명	설명
TO_CHAR(date/number, fmt)	날짜나 숫자를 문자열로 변환
TO_DATE(str, fmt)	문자열을 날짜로 변환
TO_NUMBER(str)	문자열을 숫자로 변환

21일차 - PL/SQL 프로시져]

Oracle PL/SQL 프로시저 설명

1. 개념

**프로시저(Procedure)**는 특정 작업을 수행하는 PL/SQL 서브프로그램입니다.
함수와 달리 프로시저는 값을 반환하지 않으며, 주로 반복되는 작업이나 다양한 기능을 묶어두는 데 사용됩니다.
입력 매개변수를 받아서 로직을 처리한 후, 결과는 출력 매개변수를 통해 반환할 수 있습니다.

2. 주요 특징

반환 값 없음: 프로시저는 값을 반환하지 않음.
매개변수 사용 가능: 입력, 출력, 입력/출력 매개변수를 사용할 수 있음.
다양한 용도: 데이터 수정, 트랜잭션 처리, 복잡한 로직 등을 처리할 때 사용.

3. 프로시저 문법

CREATE OR REPLACE PROCEDURE 프로시저이름 (
   매개변수명 데이터타입 [IN | OUT | IN OUT]
) IS
   -- 변수 선언부
BEGIN
   -- 로직 처리
   NULL;  -- 여기에 실제 처리 로직이 들어감
END;

IN: 입력 매개변수 (값을 전달받음)
OUT: 출력 매개변수 (값을 반환)
IN OUT: 입력과 출력 모두 가능한 매개변수

4. 프로시저 설명

항목	설명
프로시저(Procedure)	값을 반환하지 않는 PL/SQL 서브프로그램
사용 목적	복잡한 작업을 서브프로그램으로 캡슐화하여 재사용 가능
매개변수	IN, OUT, IN OUT 매개변수 사용 가능
예시	EXECUTE 프로시저명(매개변수);

5. 프로시저 예시

CREATE OR REPLACE PROCEDURE add_employee (
   p_emp_id IN NUMBER,
   p_name IN VARCHAR2,
   p_salary IN NUMBER,
   p_department IN VARCHAR2
) IS
BEGIN
   INSERT INTO employees (emp_id, name, salary, department)
   VALUES (p_emp_id, p_name, p_salary, p_department);
   COMMIT;
END;

프로시저 호출 예시:

EXECUTE add_employee(101, 'John Doe', 5000, 'Sales');

6. 주요 활용 사례

데이터 삽입/수정: INSERT, UPDATE, DELETE 등 반복적인 데이터 조작
비즈니스 로직 캡슐화: 여러 SQL 문을 하나의 프로시저에 묶어서 복잡한 로직을 구현
에러 처리: 예외 처리 구문을 프로시저 내에서 정의하여 안정성 증가

예외 처리와 매개변수 예시

1. 예외 처리(Exception Handling)

Oracle PL/SQL 프로시저는 EXCEPTION 블록을 사용해 오류 발생 시 적절한 조치를 취할 수 있습니다.

예외 처리 포함한 프로시저 예시

CREATE OR REPLACE PROCEDURE delete_employee (
   p_emp_id IN NUMBER
) IS
BEGIN
   DELETE FROM employees WHERE emp_id = p_emp_id;
   
   IF SQL%ROWCOUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, '해당 사번의 직원이 존재하지 않습니다.');
   END IF;

   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
END;

특징 설명 (미디어위키 표 형식):

구문	설명
EXCEPTION	오류 발생 시 실행할 블록 시작
WHEN OTHERS THEN	모든 예외를 포괄적으로 처리
SQLERRM	오류 메시지 반환
RAISE_APPLICATION_ERROR	사용자 정의 오류 메시지 출력 (에러 코드 범위: -20001 ~ -20999)

2. IN, OUT, IN OUT 매개변수 예시

예제: 직원 급여 인상 후 결과 반환

CREATE OR REPLACE PROCEDURE raise_salary (
   p_emp_id IN NUMBER,
   p_raise_percent IN NUMBER,
   p_new_salary OUT NUMBER
) IS
BEGIN
   UPDATE employees
   SET salary = salary + salary * p_raise_percent / 100
   WHERE emp_id = p_emp_id;

   SELECT salary INTO p_new_salary
   FROM employees
   WHERE emp_id = p_emp_id;

   COMMIT;
END;

호출 예시 (익명 블록 사용):

DECLARE
   v_new_salary NUMBER;
BEGIN
   raise_salary(101, 10, v_new_salary);
   DBMS_OUTPUT.PUT_LINE('인상된 급여: ' || v_new_salary);
END;

3. 매개변수 유형 요약

매개변수 유형	설명
IN	호출 시 외부에서 값을 전달 (기본값)
OUT	프로시저가 값을 반환하기 위해 사용
IN OUT	전달도 받고 반환도 가능 (읽기/쓰기)

22일차 - PL/SQL 패키지

Oracle 19c에서의 **패키지(Package)**는 관련된 **PL/SQL 객체들(프로시저, 함수, 변수 등)**을 하나로 묶어 모듈화된 구조로 제공하는 기능입니다. 이를 통해 코드의 재사용성, 유지보수성, 성능을 크게 향상시킬 수 있습니다.

Oracle 19c 패키지 설명

(영문) Package = 꾸러미, 묶음 → 관련된 기능을 한 덩어리로 묶은 논리적 단위
(한글) 패키지 = 관련 기능들을 묶어 놓은 PL/SQL 코드 집합

특징

항목	설명
구성	`Specification(사양)`과 `Body(본문)` 두 부분으로 구성됨
모듈화	관련 로직을 하나로 묶어 코드 관리 용이
성능 향상	한번 메모리에 로딩되면 여러 번 호출 가능
정보 은닉	외부에 노출할 부분과 숨길 부분을 명확히 구분 가능
컴파일 독립성	명세(Spec)만 변경되지 않으면 Body 수정은 다른 코드에 영향 없음

⸻

사용 방법

(1) 패키지 사양(Specification)

CREATE OR REPLACE PACKAGE math_pkg IS
  FUNCTION add(x NUMBER, y NUMBER) RETURN NUMBER;
  FUNCTION subtract(x NUMBER, y NUMBER) RETURN NUMBER;
END math_pkg;

(2) 패키지 본문(Body)

CREATE OR REPLACE PACKAGE BODY math_pkg IS
  FUNCTION add(x NUMBER, y NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN x + y;
  END;

  FUNCTION subtract(x NUMBER, y NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN x - y;
  END;
END math_pkg;

⸻

자주 사용되는 예시

-- 함수 호출

SELECT math_pkg.add(10, 5) FROM dual;

-- 익명 블록에서 사용

BEGIN
  DBMS_OUTPUT.PUT_LINE(math_pkg.subtract(20, 7));
END;
/

⸻

주의사항

사양 변경 시 전체 패키지 재컴파일 필요
패키지 바디 없이도 사양만으로 컴파일 가능 (예: 상수 정의용)
너무 많은 기능을 넣으면 복잡도 증가 → 기능별로 나누는 것이 좋음

⸻

DBMS_OUTPUT
DBMS_SQL
UTL_FILE
DBMS_SCHEDULER 등

고급 기능

(1) Oracle 19c 기본 제공 패키지 정리

Oracle은 다양한 내장 패키지를 기본으로 제공하여 파일 처리, 출력, 이메일 발송, 스케줄링, 네트워크 통신 등 다양한 작업을 수행할 수 있도록 지원합니다.

아래는 자주 사용하는 기본 제공 패키지 목록과 설명입니다.

패키지 이름	설명
DBMS_OUTPUT	`PUT_LINE`을 통해 PL/SQL에서 텍스트 출력 가능. 디버깅용으로 자주 사용.
UTL_FILE	서버 디스크의 파일 읽기/쓰기 가능. 로그 저장 등에 활용됨.
DBMS_SQL	동적 SQL 실행에 사용. 컴파일 시점에 SQL 문이 정해지지 않을 때 유용.
DBMS_SCHEDULER	작업(Job), 프로그램(Program), 스케줄(Schedule) 등을 생성해 정기적인 작업 수행 가능.
DBMS_JOB	Oracle의 구버전 스케줄러. 현재는 DBMS_SCHEDULER 권장.
UTL_MAIL	이메일 전송 가능. SMTP 서버 설정이 필요.
UTL_HTTP	HTTP 프로토콜을 이용한 외부 웹 요청 가능. REST API 호출 등에 사용.
DBMS_METADATA	테이블, 뷰, 인덱스 등의 DDL 스크립트를 추출할 수 있음.
DBMS_STATS	통계 정보를 수집하여 옵티마이저 성능을 최적화.
DBMS_ALERT / DBMS_PIPE	세션 간 통신 기능 제공. 이벤트 기반 처리 가능.
DBMS_CRYPTO	암호화, 해시, 서명 기능 제공. 보안 관련 기능 수행 가능.

기본 제공 패키지를 사용하려면 DBA 권한 또는 특정 권한이 필요할 수 있으며, 일부 패키지는 오라클 서버 설정에 따라 기본적으로 비활성화되어 있을 수 있습니다.

⸻

(2) 사용자 정의 패키지 성능 최적화 방법

패키지를 효율적으로 사용하려면 다음의 최적화 전략을 따르는 것이 좋습니다.

1. 자주 쓰는 로직은 패키지화

반복적으로 사용하는 로직(예: 날짜 계산, 코드 변환 등)은 패키지에 넣어 재사용.

2. 필요한 항목만 Specification에 공개

외부에 보여줄 함수/프로시저만 명세에 넣고, 내부 로직은 Body에 숨기기.
예: 유틸 함수는 내부 전용으로 숨김 처리.

3. 전역 변수 사용 시 주의

패키지 전역 변수는 세션 단위로 관리되며, 모든 호출에 공유되지 않음.
상태 관리가 필요한 경우 전역 변수 사용보다 테이블 저장 권장.

4. 컴파일 효율성 고려

패키지 사양(Spec)을 변경하면 전체 Body와 관련 의존 객체가 무효화되므로 변경 최소화.

5. 바디 없는 패키지 사용

상수나 공용 상수 목록만 필요할 경우 Body 없이 Spec만 작성해 사용 가능.

CREATE OR REPLACE PACKAGE const_pkg IS
  c_tax_rate CONSTANT NUMBER := 0.1;
END const_pkg;

6. 예외 처리 일관화

공용 예외 처리 프로시저를 패키지에 넣어, 다른 PL/SQL 코드에서 통일되게 사용 가능.

7. BULK COLLECT, FORALL 등 고급 기법 사용

대량 데이터를 다룰 때는 성능을 위해 BULK COLLECT와 FORALL 사용.

FORALL i IN 1..v_data.COUNT
  INSERT INTO my_table VALUES v_data(i);

추가 팁: 패키지 성능 모니터링

Oracle의 DBA_OBJECTS, DBA_DEPENDENCIES 뷰 등을 통해 패키지 상태 및 의존성을 점검할 수 있습니다.
DBMS_PROFILER 또는 DBMS_HPROF를 사용하면 PL/SQL 성능 분석도 가능합니다.

23일차 - PL/SQL 트리거]

트리거

Oracle 트리거(Trigger)는 특정 이벤트가 발생했을 때 자동으로 실행되는 PL/SQL 블록입니다.
예를 들어, INSERT/UPDATE/DELETE가 발생할 때 자동으로 특정 로직을 실행할 수 있도록 설정합니다.

트리거 기본 개념

Oracle 트리거 기본개념
항목	설명
정의	특정 이벤트에 자동으로 반응하는 데이터베이스 객체
작동 시점	BEFORE / AFTER / INSTEAD OF
대상 이벤트	INSERT, UPDATE, DELETE
적용 대상	행(Row-Level) / 문장(Statement-Level)

기본 문법

CREATE [OR REPLACE] TRIGGER 트리거명
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
ON 테이블명
[FOR EACH ROW] -- 행 단위 트리거일 경우
[WHEN 조건]    -- (선택적 조건)
BEGIN
  -- 실행할 PL/SQL 블록
END;
/

예제 1

INSERT 발생 시 로그 기록 트리거

CREATE OR REPLACE TRIGGER trg_customer_insert_log
AFTER INSERT ON CUSTOMER
FOR EACH ROW
BEGIN
  INSERT INTO CUSTOMER_LOG (CUST_ID, ACTION_DATE, ACTION)
  VALUES (:NEW.ID, SYSDATE, 'INSERT');
END;
/

CUSTOMER 테이블에 데이터가 추가될 때, CUSTOMER_LOG 테이블에 로그를 남깁니다.

예제 2

급여 변경 감지 트리거 (UPDATE)

CREATE OR REPLACE TRIGGER trg_salary_change
BEFORE UPDATE OF SALARY ON EMPLOYEE
FOR EACH ROW
WHEN (OLD.SALARY != NEW.SALARY)
BEGIN
  INSERT INTO SALARY_AUDIT (EMP_ID, OLD_SALARY, NEW_SALARY, CHANGE_DATE)
  VALUES (:OLD.EMP_ID, :OLD.SALARY, :NEW.SALARY, SYSDATE);
END;
/

급여가 변경되면 SALARY_AUDIT 테이블에 변경 전/후 정보를 기록합니다.

예제 3

DELETE 시 데이터 백업 트리거

CREATE OR REPLACE TRIGGER trg_emp_delete_backup
BEFORE DELETE ON EMPLOYEE
FOR EACH ROW
BEGIN
  INSERT INTO EMPLOYEE_ARCHIVE (EMP_ID, NAME, DEPT_ID, SALARY, DELETED_AT)
  VALUES (:OLD.EMP_ID, :OLD.NAME, :OLD.DEPT_ID, :OLD.SALARY, SYSDATE);
END;
/

삭제 전 데이터를 백업 테이블로 복사합니다.
실무에서 데이터 삭제 로그를 남기기 위해 자주 사용합니다.

트리거 종류 요약

Oracle 19c 트리거 종류 요약
종류	설명	사용 예
BEFORE INSERT/UPDATE/DELETE	이벤트 발생 전에 실행	BEFORE UPDATE ON EMPLOYEE
AFTER INSERT/UPDATE/DELETE	이벤트 발생 후에 실행	AFTER INSERT ON CUSTOMER
INSTEAD OF	뷰(View)에 직접 수정이 불가할 때 대체 실행	INSTEAD OF INSERT ON vw_customer
STATEMENT 트리거	전체 문장 1회 실행 (행마다 아님)	CREATE TRIGGER trg_all AFTER INSERT ON table
ROW 트리거	각 행마다 실행됨	FOR EACH ROW 사용

시스템 트리거 (고급)

Oracle은 테이블 외에도 시스템 이벤트 트리거를 지원합니다:

CREATE OR REPLACE TRIGGER trg_logon_example
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO LOGIN_AUDIT(USER_NAME, LOGIN_TIME)
  VALUES (USER, SYSDATE);
END;
/

사용자가 데이터베이스에 접속할 때마다 로그인 기록을 남깁니다.

트리거 유의사항

주의사항	설 명
트리거는 자동 실행되므로, 무한 루프 주의	예: 트리거에서 다시 트리거를 부르는 경우
트리거는 성능에 영향을 줄 수 있음	많은 데이터에 작동하면 병목 발생 가능
트리거는 디버깅이 어렵기 때문에 주석, 로그 중요	로그 테이블 또는 DBMS_OUTPUT 활용 권장

24일차 - SQL 고급