#Step 03 — 예제 데이터베이스 구축과 탐색

-- 주문 600건을 만드는 핵심 부분
SELECT
    t.n                                   AS order_id,
    c.customer_id,                        -- customer_id = 1 + (n * 17) % 30
    DATE_ADD('2024-01-01', INTERVAL (t.n * 37) % 730 DAY)  AS order_date,
    ELT(1 + (t.n * 7) % 10,               -- 상태를 10개 슬롯에 분배
        'DELIVERED','DELIVERED','PAID','SHIPPED','DELIVERED',
        'CANCELLED','PAID','DELIVERED','SHIPPED','PENDING') AS status
FROM tally t
JOIN customers c ON c.customer_id = 1 + (t.n * 17) % 30
WHERE t.n <= 600;

USE shop;

SELECT * FROM orders ORDER BY order_id LIMIT 5;

SELECT * FROM order_items ORDER BY order_item_id LIMIT 5;

SELECT table_name,
       table_rows AS approx_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
       table_comment
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_type = 'BASE TABLE'
ORDER BY (data_length + index_length) DESC;

SELECT
  table_name           AS `자식 테이블`,
  column_name          AS `자식 컬럼`,
  referenced_table_name  AS `부모 테이블`,
  referenced_column_name AS `부모 컬럼`
FROM information_schema.key_column_usage
WHERE table_schema = 'shop'
  AND referenced_table_name IS NOT NULL
ORDER BY table_name;

-- 주문 상태 분포
SELECT status, COUNT(*) AS cnt,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders GROUP BY status ORDER BY cnt DESC;

-- 데이터가 언제부터 언제까지 있나
SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders;

-- 1) orders.total_amount 가 order_items 합계와 일치하는가?
SELECT COUNT(*) AS mismatched_orders
FROM orders o
WHERE o.total_amount <> (
    SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
    FROM order_items oi WHERE oi.order_id = o.order_id
);

-- 2) 결제가 없는 주문은 정말 PENDING 뿐인가?
SELECT o.status, COUNT(*) AS orders_without_payment
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL
GROUP BY o.status;

-- 각 스텝의 실습 테이블은 s02_, s11_ 처럼 접두사가 붙습니다. 찾아서 지우기:
SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';') AS cleanup_sql
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_name REGEXP '^s[0-9]{2}_';

-- =====================================================================
-- Step 03 practice.sql — 예제 데이터베이스 탐색
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < practice.sql
--
-- 이 파일은 전부 SELECT 입니다. 데이터를 바꾸지 않습니다.
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- [3-3] 데이터 둘러보기
-- ---------------------------------------------------------------------
SELECT * FROM orders      ORDER BY order_id      LIMIT 5;
SELECT * FROM order_items ORDER BY order_item_id LIMIT 5;   -- 주문 1건이 여러 줄로 갈라짐
SELECT * FROM products    ORDER BY product_id    LIMIT 5;
SELECT * FROM customers   ORDER BY customer_id   LIMIT 5;

-- 전체 행 수 한눈에
SELECT
  (SELECT COUNT(*) FROM categories)  AS categories,
  (SELECT COUNT(*) FROM customers)   AS customers,
  (SELECT COUNT(*) FROM products)    AS products,
  (SELECT COUNT(*) FROM orders)      AS orders,
  (SELECT COUNT(*) FROM order_items) AS order_items,
  (SELECT COUNT(*) FROM payments)    AS payments,
  (SELECT COUNT(*) FROM reviews)     AS reviews,
  (SELECT COUNT(*) FROM employees)   AS employees;


-- ---------------------------------------------------------------------
-- [3-4] 처음 보는 DB 구조 파악하기
-- ---------------------------------------------------------------------

-- ① 테이블 목록과 크기 — 데이터가 몰린 곳이 서비스의 핵심
SELECT table_name,
       table_rows AS approx_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
       table_comment
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_type = 'BASE TABLE'
ORDER BY (data_length + index_length) DESC;

-- ② 외래키 관계 = SQL 로 뽑는 ER 다이어그램  ★ 새 DB 만나면 이거부터
SELECT
  table_name             AS child_table,
  column_name            AS child_column,
  referenced_table_name  AS parent_table,
  referenced_column_name AS parent_column
FROM information_schema.key_column_usage
WHERE table_schema = 'shop'
  AND referenced_table_name IS NOT NULL
ORDER BY table_name;

-- ③ 값의 분포 확인
SELECT status, COUNT(*) AS cnt,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct   -- 윈도우 함수 (Step 17)
FROM orders
GROUP BY status
ORDER BY cnt DESC;

SELECT grade, COUNT(*) AS cnt FROM customers GROUP BY grade;
SELECT city,  COUNT(*) AS cnt FROM customers GROUP BY city ORDER BY cnt DESC;

-- ④ 데이터의 시간 범위 ★ 리포트 만들기 전에 반드시 확인
SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS last_order,
       DATEDIFF(MAX(order_date), MIN(order_date)) AS span_days
FROM orders;


-- ---------------------------------------------------------------------
-- [3-5] 데이터 정합성 검증
-- ---------------------------------------------------------------------

-- 1) orders.total_amount 가 order_items 합계와 일치하는가? → 0 이어야 정상
SELECT COUNT(*) AS mismatched_orders
FROM orders o
WHERE o.total_amount <> (
    SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
    FROM order_items oi
    WHERE oi.order_id = o.order_id
);

-- 2) 결제가 없는 주문은 정말 PENDING 뿐인가?  (안티 조인 패턴 → Step 07)
SELECT o.status, COUNT(*) AS orders_without_payment
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL
GROUP BY o.status;

-- 3) 후기가 하나도 없는 상품은 몇 개인가?
SELECT COUNT(*) AS products_without_review
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = p.product_id);

-- 4) 일부러 심어둔 NULL 들
SELECT customer_id, name, phone FROM customers WHERE phone IS NULL;
SELECT product_id, name        FROM products  WHERE attrs IS NULL;
SELECT category_id, name       FROM categories WHERE parent_id IS NULL;   -- 최상위 카테고리
SELECT employee_id, name       FROM employees WHERE manager_id IS NULL;   -- CEO


-- ---------------------------------------------------------------------
-- [3-6] 실습 흔적 청소용 SQL 을 SQL 로 생성하기
-- ---------------------------------------------------------------------
SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';') AS cleanup_sql
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_name REGEXP '^s[0-9]{2}_';

SELECT 'Step 03 practice 완료' AS msg;

-- =====================================================================
-- Step 03 exercise.sql — 연습문제
-- 전부 SELECT 로 풀 수 있습니다. 정답은 solution.sql
-- =====================================================================
USE shop;

-- 문제 1. shop 의 모든 테이블과 행 수를 "정확하게" 한 번에 출력하시오.
--   (information_schema.table_rows 는 추정값이라 정확하지 않습니다.
--    UNION ALL 로 각 테이블의 COUNT(*) 를 붙이세요.)


-- 문제 2. 보조 인덱스(PK 제외)가 하나도 없는 테이블을 찾으시오.
--   힌트: information_schema.statistics 에서 index_name <> 'PRIMARY' 인 것을 세어 0 인 테이블


-- 문제 3. 카테고리 계층을 "대분류 > 소분류" 형태로 출력하시오. (SELF JOIN)
--   예: 패션 > 남성의류


-- 문제 4. 주문이 한 건도 없는 고객이 있는가? 있다면 누구인가?
--   결과가 비어 있다면 그것도 답입니다. "쿼리가 틀렸다"와 "정말 없다"를 구분하세요.


-- 문제 5. 후기(reviews)가 하나도 없는 상품은 몇 개인가? 그 중 5개만 이름과 함께 보이시오.


-- 문제 6. products.attrs 가 NULL 인 상품을 찾으시오. 왜 NULL 인지 생각해 보시오.


-- 문제 7. 각 테이블의 PK 컬럼명을 information_schema 로 조회하시오.
--   (복합 PK 라면 컬럼들을 콤마로 이어 붙이시오. 힌트: GROUP_CONCAT)


-- 문제 8. (심화) 주문 금액이 가장 큰 주문 1건의 상세 내역을 출력하시오.
--   주문번호, 고객명, 주문일, 상품명, 수량, 단가, 라인금액(수량*단가)

-- =====================================================================
-- Step 03 solution.sql — 연습문제 정답
-- =====================================================================
USE shop;

-- ── 문제 1. 모든 테이블의 정확한 행 수
SELECT 'categories'  AS table_name, COUNT(*) AS exact_rows FROM categories
UNION ALL SELECT 'customers',   COUNT(*) FROM customers
UNION ALL SELECT 'products',    COUNT(*) FROM products
UNION ALL SELECT 'orders',      COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'payments',    COUNT(*) FROM payments
UNION ALL SELECT 'reviews',     COUNT(*) FROM reviews
UNION ALL SELECT 'employees',   COUNT(*) FROM employees
UNION ALL SELECT 'tally',       COUNT(*) FROM tally
ORDER BY exact_rows DESC;
-- 결과: tally 10000, order_items 1200, orders 600, payments 540,
--       reviews 80, products 40, customers 30, employees 18, categories 17
-- 해설: information_schema.table_rows 는 InnoDB 의 "통계 샘플 추정값"입니다.
--       실제로 100만 행인 access_logs 가 996,151 로 나오는 걸 Step 02 에서 봤습니다.
--       정확한 수가 필요하면 반드시 COUNT(*) 를 쓰세요.
--       (그래서 대용량 테이블의 COUNT(*) 는 비싼 연산입니다 → Step 15)


-- ── 문제 2. 보조 인덱스가 없는 테이블
SELECT t.table_name,
       COUNT(DISTINCT CASE WHEN s.index_name <> 'PRIMARY' THEN s.index_name END) AS secondary_indexes
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s
       ON s.table_schema = t.table_schema AND s.table_name = t.table_name
WHERE t.table_schema = 'shop'
  AND t.table_type = 'BASE TABLE'
  AND t.table_name NOT REGEXP '^s[0-9]{2}_'      -- 다른 스텝의 실습 테이블 제외
GROUP BY t.table_name
HAVING secondary_indexes = 0
ORDER BY t.table_name;
-- 결과: access_logs, tally
-- 해설: access_logs 에 인덱스가 없는 것은 의도한 것입니다.
--       Step 15 에서 여러분이 직접 인덱스를 설계해 붙이고, 100만 행 조회가
--       몇 초 → 몇 밀리초로 줄어드는 것을 실측합니다.
--       tally 는 PK(n) 만으로 충분합니다 (조회 용도가 n 뿐이므로).


-- ── 문제 3. 카테고리 계층 (SELF JOIN)
SELECT CONCAT(p.name, ' > ', c.name) AS category_path
FROM categories c
JOIN categories p ON p.category_id = c.parent_id     -- 같은 테이블을 두 번! 별칭이 필수
ORDER BY p.sort_order, c.sort_order;
-- 결과:
--   패션 > 남성의류 / 패션 > 여성의류 / 패션 > 신발
--   디지털 > 노트북 / 디지털 > 스마트폰 / 디지털 > 주변기기
--   식품 > 신선식품 / 식품 > 가공식품
--   리빙 > 주방용품 / 리빙 > 가구
--   도서 > IT/컴퓨터 / 도서 > 소설
-- 해설: SELF JOIN 은 같은 테이블에 서로 다른 별칭(c, p)을 붙여 조인하는 것입니다.
--       "자식 c 의 parent_id 와 같은 category_id 를 가진 부모 p" 를 붙였습니다.
--       계층이 2단계라서 JOIN 한 번으로 됐지만, 깊이가 정해지지 않은 계층은
--       JOIN 을 몇 번 해야 할지 알 수 없습니다. 그때 재귀 CTE 를 씁니다 → Step 09


-- ── 문제 4. 주문이 없는 고객
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
-- 결과: (0 rows) — 빈 결과
-- 해설: ★ 이 빈 결과가 정답입니다.
--       시드에서 customer_id = 1 + (n * 17) % 30 이 30명을 고르게 순환하므로
--       모든 고객이 최소 1건 이상 주문했습니다.
--       실무에서 안티 조인이 0건을 반환하면 "쿼리 버그"와 "정말 없음"을 구분해야 합니다.
--       구분법: LEFT JOIN 을 빼고 실행해서 행이 나오는지 확인하세요.
SELECT COUNT(DISTINCT c.customer_id) AS total_customers,
       COUNT(DISTINCT o.customer_id) AS customers_with_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;
-- → 30, 30  ... 전원이 주문 이력이 있음을 교차 검증
--
-- ★ 여기서 COUNT(DISTINCT c.customer_id) 대신 COUNT(*) 를 쓰면 어떻게 될까요?
--   답: 600 이 나옵니다. 고객 수가 아니라 "조인 결과의 행 수"(= 주문 수)를 세기 때문입니다.
--   조인 뒤에 COUNT(*) 를 쓰는 것은 초보자가 가장 자주 저지르는 집계 실수입니다.
--   조인은 행을 "불립니다". 무엇을 세고 있는지 항상 의식하세요 → Step 06, Step 07


-- ── 문제 5. 후기가 없는 상품
SELECT COUNT(*) AS products_without_review
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = p.product_id);
-- 결과: 24

SELECT p.product_id, p.name, p.price
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = p.product_id)
ORDER BY p.product_id
LIMIT 5;
-- 해설: NOT EXISTS 는 "일치하는 행이 하나도 없는가"를 묻습니다.
--       같은 뜻의 LEFT JOIN 버전:
--         SELECT p.* FROM products p
--         LEFT JOIN reviews r ON r.product_id = p.product_id
--         WHERE r.review_id IS NULL;
--       NOT IN 버전은 위험합니다. reviews.product_id 에 NULL 이 하나라도 있으면
--       결과가 통째로 비어버립니다 → Step 08 의 핵심 함정


-- ── 문제 6. attrs 가 NULL 인 상품
SELECT product_id, name, category_id
FROM products
WHERE attrs IS NULL;
-- 결과: 22번 'USB-C 허브 8in1', 29번 '프리미엄 라면 5입'
-- 해설: JSON 컬럼도 결국 컬럼이라 NULL 이 될 수 있습니다.
--       주의: JSON 의 NULL(문자열 'null')과 SQL 의 NULL 은 다릅니다!
--         - attrs IS NULL          → 컬럼 자체가 비어 있음
--         - JSON_TYPE(attrs)='NULL' → JSON 문서 안에 null 이 들어 있음
--       이 둘을 헷갈리면 JSON 쿼리가 조용히 틀립니다 → Step 18


-- ── 문제 7. 각 테이블의 PK 컬럼
SELECT t.table_name,
       GROUP_CONCAT(k.column_name ORDER BY k.ordinal_position) AS pk_columns
FROM information_schema.tables t
JOIN information_schema.key_column_usage k
  ON  k.table_schema   = t.table_schema
  AND k.table_name     = t.table_name
  AND k.constraint_name = 'PRIMARY'          -- PK 의 제약 이름은 항상 'PRIMARY'
WHERE t.table_schema = 'shop'
  AND t.table_type = 'BASE TABLE'
  AND t.table_name NOT REGEXP '^s[0-9]{2}_'
GROUP BY t.table_name
ORDER BY t.table_name;
-- 결과:
--   access_logs=log_id, categories=category_id, customers=customer_id,
--   employees=employee_id, order_items=order_item_id, orders=order_id,
--   payments=payment_id, products=product_id, reviews=review_id, tally=n
-- 해설: GROUP_CONCAT 은 여러 행을 한 문자열로 합칩니다 (복합 PK 대응).
--       ORDER BY 를 GROUP_CONCAT "안에" 쓴다는 점에 주목하세요 → Step 06


-- ── 문제 8. 최고 금액 주문의 상세 내역
SELECT
  o.order_id,
  c.name           AS customer,
  o.order_date,
  o.total_amount,
  p.name           AS product,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS line_amount
FROM orders o
JOIN customers   c  ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products    p  ON p.product_id  = oi.product_id
WHERE o.order_id = (SELECT order_id FROM orders ORDER BY total_amount DESC LIMIT 1)
ORDER BY line_amount DESC;
-- 해설: 서브쿼리로 "최고 금액 주문의 ID" 를 먼저 구한 뒤 그 주문의 상세를 조인했습니다.
--       ORDER BY total_amount DESC LIMIT 1 로 최댓값 행을 찾는 것은 흔한 패턴입니다.
--       (MAX(total_amount) 를 쓰면 "그 값이 몇인지"는 알지만 "어느 행인지"는 모릅니다)
--       동점이 있으면 LIMIT 1 은 그 중 하나만 고릅니다. 전부 원하면 Step 17 의 RANK() 를 쓰세요.

SELECT 'Step 03 solution 완료' AS msg;