테이블 구조

-- 회원 정보
CREATE TABLE members (
    member_id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

-- 주문 정보
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    member_id INT,
    amount DECIMAL(10, 2),
    order_date DATE,
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

데이터 예시

-- members 테이블
INSERT INTO members VALUES
(1, 'Alice', 'Seoul'),
(2, 'Bob', 'Busan'),
(3, 'Charlie', 'Seoul'),
(4, 'David', 'Incheon');

-- orders 테이블
INSERT INTO orders VALUES
(101, 1, 120.50, '2024-01-10'),
(102, 1, 80.00, '2024-02-15'),
(103, 2, 50.00, '2024-02-20'),
(104, 3, 300.00, '2024-03-05'),
(105, 3, 20.00, '2024-03-10');

✅ 실습 문제

1️⃣ JOIN 실습

각 회원이 주문한 금액과 함께 이름과 도시를 출력하세요.

sql
복사편집
SELECT m.name, m.city, o.amount, o.order_date
FROM members m
JOIN orders o ON m.member_id = o.member_id;

2️⃣ GROUP BY 실습

회원별로 총 주문 금액을 계산하세요.

sql
복사편집
SELECT m.name, SUM(o.amount) AS total_amount
FROM members m
JOIN orders o ON m.member_id = o.member_id
GROUP BY m.name;

3️⃣ HAVING 실습

총 주문 금액이 100 이상인 회원만 출력하세요.

sql
복사편집
SELECT m.name, SUM(o.amount) AS total_amount
FROM members m
JOIN orders o ON m.member_id = o.member_id
GROUP BY m.name
HAVING SUM(o.amount) >= 100;