실습 프로젝트: 쇼핑몰 DB

-- 회원 테이블
CREATE TABLE users (
    user_id    INT PRIMARY KEY,
    username   VARCHAR(50) NOT NULL UNIQUE,
    email      VARCHAR(100) NOT NULL UNIQUE,
    password   VARCHAR(200) NOT NULL,
    grade      VARCHAR(10) DEFAULT 'BRONZE',
    created_at DATE DEFAULT CURRENT_DATE
);

-- 상품 테이블
CREATE TABLE products (
    product_id  INT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    price       INT NOT NULL CHECK (price >= 0),
    stock       INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category    VARCHAR(50),
    description VARCHAR(500),
    created_at  DATE DEFAULT CURRENT_DATE
);

-- 주문 테이블
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES users(user_id),
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    status      VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    total_amount INT DEFAULT 0
);

-- 주문 상세 테이블
CREATE TABLE order_items (
    item_id     INT PRIMARY KEY,
    order_id    INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id  INT NOT NULL REFERENCES products(product_id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  INT NOT NULL CHECK (unit_price >= 0)
);

INSERT INTO users (user_id, username, email, password, grade, created_at)
VALUES (1, 'kim_dev', 'kim@example.com', 'hashed_pw1', 'GOLD', DATE '2024-01-15');

INSERT INTO users (user_id, username, email, password, grade, created_at)
VALUES (2, 'lee_design', 'lee@example.com', 'hashed_pw2', 'BRONZE', DATE '2024-02-20');

INSERT INTO users (user_id, username, email, password, grade, created_at)
VALUES (3, 'park_pm', 'park@example.com', 'hashed_pw3', 'SILVER', DATE '2024-03-10');

INSERT INTO users (user_id, username, email, password, grade, created_at)
VALUES (4, 'choi_data', 'choi@example.com', 'hashed_pw4', 'BRONZE', DATE '2024-05-05');

INSERT INTO users (user_id, username, email, password, grade, created_at)
VALUES (5, 'jung_back', 'jung@example.com', 'hashed_pw5', 'GOLD', DATE '2024-06-20');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (1, '맥북 프로 16', 3690000, 10, '전자기기', '최신 M3 칩 탑재');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (2, '에어팟 프로', 359000, 50, '전자기기', '노이즈 캔슬링 지원');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (3, '클린 코드', 33000, 100, '도서', '로버트 C. 마틴 저');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (4, '기계식 키보드', 89000, 30, '전자기기', '체리 청축');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (5, '모니터 암', 45000, 0, '가구', '듀얼 모니터용, 현재 품절');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (6, '리팩터링', 44000, 80, '도서', '마틴 파울러 저');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (7, '무선 마우스', 35000, 200, '전자기기', '인체공학 설계');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (8, '스탠딩 데스크', 450000, 5, '가구', '전동 높이 조절');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (9, 'USB-C 허브', 55000, 150, '전자기기', '7-in-1 멀티포트');

INSERT INTO products (product_id, name, price, stock, category, description)
VALUES (10, '실용주의 프로그래머', 38000, 60, '도서', '앤드류 헌트 저');

INSERT INTO orders (order_id, user_id, order_date, status, total_amount)
VALUES (1, 1, DATE '2024-06-01', 'DELIVERED', 4408000);

INSERT INTO orders (order_id, user_id, order_date, status, total_amount)
VALUES (2, 1, DATE '2024-07-15', 'SHIPPED', 33000);

INSERT INTO orders (order_id, user_id, order_date, status, total_amount)
VALUES (3, 2, DATE '2024-08-01', 'PAID', 177000);

INSERT INTO orders (order_id, user_id, order_date, status, total_amount)
VALUES (4, 3, DATE '2024-08-15', 'DELIVERED', 3690000);

INSERT INTO orders (order_id, user_id, order_date, status, total_amount)
VALUES (5, 5, DATE '2024-09-01', 'PENDING', 89000);

INSERT INTO orders (order_id, user_id, order_date, status, total_amount)
VALUES (6, 4, DATE '2024-09-10', 'CANCELLED', 359000);

-- 주문1: kim이 맥북 1개 + 에어팟 2개 구매
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (1, 1, 1, 1, 3690000);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (2, 1, 2, 2, 359000);

-- 주문2: kim이 클린코드 1권 구매
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (3, 2, 3, 1, 33000);

-- 주문3: lee가 키보드 1개 + 리팩터링 2권 구매
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (4, 3, 4, 1, 89000);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (5, 3, 6, 2, 44000);

-- 주문4: park이 맥북 1개 구매
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (6, 4, 1, 1, 3690000);

-- 주문5: jung이 키보드 1개 구매
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (7, 5, 4, 1, 89000);

-- 주문6: choi가 에어팟 1개 구매 (취소됨)
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (8, 6, 2, 1, 359000);

COMMIT;

SELECT name, price, stock
FROM products
WHERE category = '전자기기'
ORDER BY price DESC;

name              | price    | stock
------------------+----------+------
맥북 프로 16      | 3690000  | 10
에어팟 프로        | 359000   | 50
기계식 키보드      | 89000    | 30
USB-C 허브        | 55000    | 150
무선 마우스        | 35000    | 200

SELECT name, stock, price
FROM products
WHERE stock > 0
ORDER BY stock DESC;

name                  | stock | price
----------------------+-------+--------
무선 마우스            | 200   | 35000
USB-C 허브            | 150   | 55000
클린 코드              | 100   | 33000
리팩터링               | 80    | 44000
실용주의 프로그래머     | 60    | 38000
에어팟 프로            | 50    | 359000
기계식 키보드          | 30    | 89000
맥북 프로 16          | 10    | 3690000
스탠딩 데스크          | 5     | 450000

SELECT name, price
FROM products
WHERE price BETWEEN 30000 AND 100000
ORDER BY price;

name                  | price
----------------------+--------
클린 코드              | 33000
무선 마우스            | 35000
실용주의 프로그래머     | 38000
리팩터링               | 44000
모니터 암              | 45000
USB-C 허브            | 55000
기계식 키보드          | 89000

SELECT name, price FROM products WHERE name LIKE '%프로%';

name          | price
--------------+--------
맥북 프로 16  | 3690000
에어팟 프로    | 359000
실용주의 프로그래머 | 38000

SELECT username, email, grade
FROM users
WHERE grade IN ('GOLD', 'SILVER')
ORDER BY grade, username;

username   | email              | grade
-----------+--------------------+-------
kim_dev    | kim@example.com    | GOLD
jung_back  | jung@example.com   | GOLD
park_pm    | park@example.com   | SILVER

SELECT category,
       COUNT(*) AS 상품수,
       AVG(price) AS 평균가격,
       MIN(price) AS 최저가격,
       MAX(price) AS 최고가격,
       SUM(stock) AS 총재고
FROM products
GROUP BY category
ORDER BY 상품수 DESC;

category | 상품수 | 평균가격  | 최저가격 | 최고가격  | 총재고
---------+--------+----------+---------+----------+------
전자기기  | 5      | 845600   | 35000   | 3690000  | 440
도서     | 3      | 38333    | 33000   | 44000    | 240
가구     | 2      | 247500   | 45000   | 450000   | 5

SELECT name, price, category
FROM products
WHERE price = (SELECT MAX(price) FROM products);

name          | price    | category
--------------+----------+----------
맥북 프로 16  | 3690000  | 전자기기

SELECT name, price, category
FROM products
WHERE price >= (SELECT AVG(price) FROM products)
ORDER BY price DESC;

name          | price    | category
--------------+----------+----------
맥북 프로 16  | 3690000  | 전자기기

SELECT u.username, o.order_id, o.order_date, o.status, o.total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
ORDER BY o.order_date DESC;

username    | order_id | order_date | status    | total_amount
------------+----------+------------+-----------+-------------
choi_data   | 6        | 2024-09-10 | CANCELLED | 359000
jung_back   | 5        | 2024-09-01 | PENDING   | 89000
park_pm     | 4        | 2024-08-15 | DELIVERED | 3690000
lee_design  | 3        | 2024-08-01 | PAID      | 177000
kim_dev     | 2        | 2024-07-15 | SHIPPED   | 33000
kim_dev     | 1        | 2024-06-01 | DELIVERED | 4408000

SELECT u.username,
       p.name AS product_name,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price AS subtotal
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN users u ON o.user_id = u.user_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_date, oi.item_id;

username   | product_name    | quantity | unit_price | subtotal
-----------+-----------------+----------+------------+---------
kim_dev    | 맥북 프로 16    | 1        | 3690000    | 3690000
kim_dev    | 에어팟 프로      | 2        | 359000     | 718000
kim_dev    | 클린 코드        | 1        | 33000      | 33000
lee_design | 기계식 키보드    | 1        | 89000      | 89000
lee_design | 리팩터링         | 2        | 44000      | 88000
park_pm    | 맥북 프로 16    | 1        | 3690000    | 3690000
jung_back  | 기계식 키보드    | 1        | 89000      | 89000
choi_data  | 에어팟 프로      | 1        | 359000     | 359000

SELECT u.username, u.email, u.created_at
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

SELECT p.name, p.stock, p.category
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

name                  | stock | category
----------------------+-------+---------
모니터 암              | 0     | 가구
스탠딩 데스크          | 5     | 가구
무선 마우스            | 200   | 전자기기
USB-C 허브            | 150   | 전자기기
실용주의 프로그래머     | 60    | 도서

SELECT p.name,
       SUM(oi.quantity) AS 총판매수량,
       SUM(oi.quantity * oi.unit_price) AS 총매출
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
GROUP BY p.product_id, p.name
ORDER BY 총매출 DESC
FETCH FIRST 3 ROWS ONLY;  -- Oracle/PostgreSQL (MySQL: LIMIT 3)

name          | 총판매수량 | 총매출
--------------+-----------+--------
맥북 프로 16  | 2         | 7380000
에어팟 프로    | 2         | 718000
기계식 키보드  | 1         | 89000

SELECT EXTRACT(MONTH FROM o.order_date) AS 월,
       COUNT(DISTINCT o.order_id) AS 주문수,
       SUM(o.total_amount) AS 월매출
FROM orders o
WHERE o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
GROUP BY EXTRACT(MONTH FROM o.order_date)
ORDER BY 월;

월 | 주문수 | 월매출
---+--------+---------
6  | 1      | 4408000
7  | 1      | 33000
8  | 2      | 3867000

SELECT u.username, u.grade,
       COUNT(o.order_id) AS 주문횟수,
       SUM(o.total_amount) AS 총구매액
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
  AND u.grade = 'GOLD'
GROUP BY u.user_id, u.username, u.grade
ORDER BY 총구매액 DESC;

SELECT p.name, p.stock, p.category,
       SUM(oi.quantity) AS 총판매수량
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE p.stock <= 10
  AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
GROUP BY p.product_id, p.name, p.stock, p.category
ORDER BY p.stock;

-- 변경 전 확인
SELECT name, price FROM products WHERE category = '전자기기';

-- 할인 적용
UPDATE products SET price = CAST(ROUND(price * 0.9) AS INT)
WHERE category = '전자기기';

-- 변경 후 확인
SELECT name, price FROM products WHERE category = '전자기기';

변경 전:                        변경 후:
name          | price           name          | price
맥북 프로 16  | 3690000         맥북 프로 16  | 3321000
에어팟 프로    | 359000          에어팟 프로    | 323100
기계식 키보드  | 89000           기계식 키보드  | 80100
무선 마우스    | 35000           무선 마우스    | 31500
USB-C 허브    | 55000           USB-C 허브    | 49500

-- 10% 할인을 되돌리려면 0.9로 나누기
UPDATE products SET price = CAST(ROUND(price / 0.9) AS INT)
WHERE category = '전자기기';

UPDATE orders
SET status = CASE
    WHEN status = 'PENDING' AND order_date < DATE '2024-09-05'  THEN 'EXPIRED'
    WHEN status = 'SHIPPED' AND order_date < DATE '2024-08-01'  THEN 'DELIVERED'
    ELSE status
END
WHERE status IN ('PENDING', 'SHIPPED');

UPDATE users u
SET grade = (
    SELECT CASE
        WHEN COALESCE(SUM(o.total_amount), 0) >= 3000000 THEN 'GOLD'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 1000000 THEN 'SILVER'
        ELSE 'BRONZE'
    END
    FROM orders o
    WHERE o.user_id = u.user_id
      AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
);

-- 결과 확인
SELECT username, grade FROM users ORDER BY username;

-- 삭제 전 확인
SELECT oi.item_id, o.order_id, o.status, p.name
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'CANCELLED';

-- 삭제 실행
DELETE FROM order_items
WHERE order_id IN (
    SELECT order_id FROM orders WHERE status = 'CANCELLED'
);

-- 취소 주문 자체도 삭제
DELETE FROM orders WHERE status = 'CANCELLED';

-- 확인
SELECT p.name, p.stock
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE p.stock = 0
  AND oi.item_id IS NULL;

-- 삭제
DELETE FROM products
WHERE stock = 0
  AND NOT EXISTS (
      SELECT 1
      FROM order_items oi
      WHERE oi.product_id = products.product_id
  );

-- MySQL
SELECT p.name,
       GROUP_CONCAT(DISTINCT u.username ORDER BY u.username) AS 구매회원
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
JOIN users u ON o.user_id = u.user_id
WHERE o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
GROUP BY p.product_id, p.name;

-- PostgreSQL
SELECT p.name,
       STRING_AGG(DISTINCT u.username, ', ' ORDER BY u.username) AS 구매회원
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
JOIN users u ON o.user_id = u.user_id
WHERE o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
GROUP BY p.product_id, p.name;

SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS 카테고리매출,
       ROUND(
           SUM(oi.quantity * oi.unit_price) * 100.0 /
           (SELECT SUM(oi2.quantity * oi2.unit_price)
            FROM order_items oi2
            JOIN orders o2 ON oi2.order_id = o2.order_id
            WHERE o2.status IN ('PAID', 'SHIPPED', 'DELIVERED')),
           1
       ) AS 비중_퍼센트
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
GROUP BY p.category
ORDER BY 카테고리매출 DESC;

SELECT u.username,
       COUNT(o.order_id) AS 주문횟수,
       MIN(o.order_date) AS 첫주문일,
       MAX(o.order_date) AS 최근주문일
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
GROUP BY u.user_id, u.username
HAVING COUNT(o.order_id) >= 2;

BEGIN;  -- START TRANSACTION (MySQL)

-- 1. 주문 생성
INSERT INTO orders (order_id, user_id, order_date, status, total_amount)
VALUES (7, 2, CURRENT_DATE, 'PAID', 89000);

-- 2. 주문 상세 생성
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (9, 7, 4, 1, 89000);

-- 3. 재고 차감 (재고가 있을 때만 성공)
UPDATE products SET stock = stock - 1
WHERE product_id = 4
  AND stock >= 1;

-- 4. 영향 행 수 확인
SELECT stock FROM products WHERE product_id = 4;

-- UPDATE된 행이 1개이면 아래 COMMIT 실행
COMMIT;

-- UPDATE된 행이 0개이거나 중간에 실패했다면 COMMIT 대신 ROLLBACK
-- ROLLBACK;

BEGIN;

-- 여러 회원의 등급을 하나의 정책 변경으로 처리
UPDATE users SET grade = 'SILVER' WHERE user_id = 1;  -- 등급 변경
UPDATE users SET grade = 'GOLD' WHERE user_id = 2;    -- 등급 변경

-- 변경 확인
SELECT user_id, username, grade FROM users WHERE user_id IN (1, 2);

-- 확인 후 확정
COMMIT;

-- 실습에서 변경한 데이터를 원복하려면 처음부터 다시 시작
-- 방법 1: ROLLBACK (COMMIT 전이라면)
ROLLBACK;

-- 방법 2: 테이블 재생성
DROP TABLE order_items;
DROP TABLE orders;
DROP TABLE products;
DROP TABLE users;
-- 위의 CREATE TABLE과 INSERT를 다시 실행

1. INSERT — 컬럼 리스트를 항상 명시하고, FK 순서를 지켜야 한다
   (부모 테이블 먼저 삽입)

2. SELECT — WHERE, ORDER BY, GROUP BY, HAVING의 조합으로
   다양한 비즈니스 요구를 처리할 수 있다

3. JOIN — 정규화로 분리된 데이터를 다시 결합하는 핵심 도구이다
   LEFT JOIN으로 "없는 것"도 찾을 수 있다

4. UPDATE — 서브쿼리, CASE를 활용하면 복잡한 일괄 수정이 가능하다
   항상 SELECT로 먼저 확인하고 실행한다

5. DELETE — 순서가 중요하다 (자식 먼저, 부모 나중에)
   삭제 전 영향 범위를 반드시 확인한다

6. 트랜잭션 — 관련 DML을 하나의 단위로 묶어 원자성을 보장한다
   문제가 있으면 ROLLBACK으로 전체를 취소한다