SQLSQL

users 테이블:
┌────┬───────┬─────────────────┬─────┐
│ id │ name  │ email           │ age │
├────┼───────┼─────────────────┼─────┤
│ 1  │ Alice │ alice@email.com │ 30  │
│ 2  │ Bob   │ bob@email.com   │ 25  │
│ 3  │ Carol │ carol@email.com │ 35  │
└────┴───────┴─────────────────┴─────┘

-- 테이블 생성
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age   INTEGER CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 컬럼 추가
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 테이블 삭제
DROP TABLE users;

-- 삽입
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@email.com', 30);

-- 조회
SELECT id, name, age
FROM users
WHERE age > 25
ORDER BY age DESC
LIMIT 10;

-- 수정
UPDATE users
SET age = 31
WHERE name = 'Alice';

-- 삭제
DELETE FROM users
WHERE id = 3;

-- 집계 함수
SELECT
    COUNT(*) AS total,
    AVG(age)  AS avg_age,
    MAX(age)  AS max_age,
    MIN(age)  AS min_age
FROM users;

-- GROUP BY
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age
HAVING COUNT(*) > 1;

-- 서브쿼리
SELECT name FROM users
WHERE age = (SELECT MAX(age) FROM users);

-- INNER JOIN: 두 테이블 모두에 있는 데이터
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: users 기준, orders가 없어도 포함
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN: 양쪽 모두 포함, 매칭 없으면 NULL
SELECT u.name, o.product
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN: 카티션 곱 (모든 조합)
SELECT u.name, p.name
FROM users u
CROSS JOIN products p;

-- 각 직원의 급여와 부서 평균 급여를 동시에 조회
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

-- 순위 함수
SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,  -- 고유 순번
    RANK()       OVER (ORDER BY salary DESC) AS rank,      -- 동순위 시 간격 발생
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank -- 동순위 시 간격 없음
FROM employees;

-- 누적 합계
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM sales;

-- LAG / LEAD: 이전/다음 행 참조
SELECT
    date,
    revenue,
    LAG(revenue, 1)  OVER (ORDER BY date) AS prev_revenue,
    LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day_change
FROM daily_sales;

-- 기본 CTE
WITH high_earners AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 80000
),
dept_counts AS (
    SELECT department_id, COUNT(*) as emp_count
    FROM employees
    GROUP BY department_id
)
SELECT h.name, h.salary, d.emp_count
FROM high_earners h
JOIN dept_counts d ON h.department_id = d.department_id;

-- 재귀 CTE: 조직도/트리 탐색
WITH RECURSIVE org_chart AS (
    -- 기저 케이스: 최상위 직원
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀: 하위 직원 탐색
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, name FROM org_chart ORDER BY level, name;

-- 뷰 생성
CREATE VIEW active_user_orders AS
SELECT u.name, u.email, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.name, u.email;

-- 뷰 사용 (일반 테이블처럼)
SELECT * FROM active_user_orders WHERE total_spent > 10000;

-- 뷰 삭제
DROP VIEW active_user_orders;

-- 인덱스 없이: 전체 테이블 스캔 O(n)
SELECT * FROM users WHERE email = 'alice@email.com';

-- 단일 컬럼 인덱스 생성: B-Tree 검색 O(log n)
CREATE INDEX idx_users_email ON users(email);

-- 복합 인덱스: 자주 함께 조회되는 컬럼
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 부분 인덱스: 조건에 맞는 행만 인덱싱
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- 인덱스 삭제
DROP INDEX idx_users_email;

BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
  UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;  -- 둘 다 성공 시 확정

-- 오류 발생 시
ROLLBACK;  -- 원래 상태로 되돌림

-- SAVEPOINT: 부분 롤백
BEGIN;
  INSERT INTO logs VALUES ('start');
  SAVEPOINT sp1;
  UPDATE users SET status = 'active' WHERE id = 1;  -- 실패
  ROLLBACK TO sp1;     -- sp1 이전 상태로만 복원
  INSERT INTO logs VALUES ('partial rollback');
COMMIT;

-- 실행 계획 확인
EXPLAIN SELECT * FROM users WHERE email = 'alice@email.com';

-- 실제 실행 시간 포함
EXPLAIN ANALYZE SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- 주요 키워드 해석
-- Seq Scan: 전체 테이블 순차 스캔 (인덱스 없음, 느림)
-- Index Scan: 인덱스 사용 (빠름)
-- Hash Join: 해시 기반 조인
-- cost=X..Y: 예상 비용 (첫 행..전체 행)
-- rows=N: 예상 결과 행 수