SQL로 복잡한 쿼리 실습하기

1. 테이블 확장

1) 기존 테이블

CREATE TABLE users (
    id SERIAL,
    name VARCHAR(255)
);

2) 컬럼 추가

• users 테이블에 새로운 정보를 추가
  • email : 사용자의 이메일 주소
  • created_ad : 계정 생성 날짜
  • status  : 계정 상태 (활성 / 비활성)

ALTER TABLE users
ADD COLUMN email VARCHAR(255);

ALTER TABLE users
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAP;

ALTER TABLE users
ADD COLUMN status ENUM('active', 'inactive') DEFAULT 'active';

3) 추가 테이블 생성

• products : 상품 저장 테이블
• orders : 사용자 주문 정보 저장 테이블

CREATE TABLE products (
   id SERIAL,
   name VARCHAR(255),
   price DECIMAL(10,2),
   PRIMARY KEY (id)
);

CREATE TABLE orders (
  id SERIAL,
  user_id BIGINT,
  product_id BIGINT,
  quantity INT,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

2. 데이터 삽입

1) users 테이블

INSERT INTO users (name, email, status) VALUES
('Alice', 'alice@example.com', 'active'),
('Bob', 'bob@example.com', 'inactive'),
('Charlie', 'charlie@example.com', 'active');

2) products 테이블

INSERT INTO products (name, price) VALUES
('Laptop', 1000.00),
('Phone', 500.00),
('Tablet', 300.00);

3) orders 테이블

INSERT INTO orders (user_id, product_id, quantity) VALUES
(1, 1, 1),
(1, 2, 2),
(2, 3, 1),
(3, 1, 3);

---

3. 쿼리 실습

1) 조건 검색(where)

• 활성 상태(active)인 사용자만 조회한다.

SELECT * FROM users
WHERE status = 'active';

2) 데이터 결합(JOIN)

• 사용자와 주문 내역을 함께 조회한다.

SELECT 
    users.name, 
    orders.id AS order_id, 
    products.name AS product_name, 
    orders.quantity, 
    orders.order_date
FROM orders
JOIN users ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id;

3) 그룹화(GROUP BY)

• 사용자별 주문 개수를 집계한다.

SELECT 
    users.name, 
    COUNT(orders.id) AS total_orders
FROM orders
JOIN users ON orders.user_id = users.id
GROUP BY users.name;

4) 정렬(ORDER BY)

• 주문 내역을 주문 날짜 기준 내림차순 정렬

SELECT 
    users.name, 
    products.name AS product_name, 
    orders.quantity, 
    orders.order_date
FROM orders
JOIN users ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id
ORDER BY orders.order_date DESC;

---

테스트 후 데이터를 초기화 하고 싶을 때

TRUNCATE TABLE orders;
TRUNCATE TABLE products;
TRUNCATE TABLE users;

---

추가 문제

• 특정 날짜 이후에 생성된 계정을 가진 사용자 조회

SELECT * FROM users
WHERE created_at > '2023-01-01';

• 사용자별 총 주문 금액 계산

SELECT 
    users.name, 
    SUM(products.price * orders.quantity) AS total_spent
FROM orders
JOIN users ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id
GROUP BY users.name;