예제 3: 레스토랑 예약 시스템
-- Customers 테이블 생성
-- 식당의 고객 정보를 저장합니다.
-- 고객의 고유 식별자, 이름, 전화번호, 이메일 주소가 포함됩니다.
CREATE TABLE Customers (
);
-- Tables 테이블 생성
-- 식당의 테이블 정보를 관리합니다.
-- 각 테이블은 고유 식별자와 수용 가능한 최대 인원 수를 가집니다.
CREATE TABLE Tables (
);
-- Reservations 테이블 생성
-- 고객의 테이블 예약 정보를 기록합니다.
-- 예약 ID, 고객 ID, 테이블 ID, 예약 시간 등의 정보를 포함하며, 예약은 특정 고객과 테이블에 연결됩니다.
CREATE TABLE Reservations (
);
-- Orders 테이블 생성
-- 각 예약 동안 발생한 주문을 추적합니다.
-- 주문 ID, 예약 ID(옵션), 주문 시간을 기록합니다.
CREATE TABLE Orders (
);
-- MenuItems 테이블 생성
-- 메뉴 아이템에 대한 정보를 저장합니다.
-- 메뉴 아이템의 고유 ID, 이름, 설명, 가격이 포함됩니다.
CREATE TABLE MenuItems (
);
-- OrderDetails 테이블 생성
-- 주문 상세 정보를 기록합니다.
-- 각 주문의 상세한 아이템과 수량 정보가 저장됩니다.
CREATE TABLE OrderDetails (
);
초급
1. 문제: 식당 데이터베이스의 모든 고객 정보를 조회하세요. (이름, 전화번호, 이메일)
SELECT c.name, c.phone_number, c.email
FROM Customers c;
2. 문제: 특정 고객(예: 홍길동)의 모든 예약 정보를 조회하세요.
SELECT r.*
FROM Reservations r
JOIN Customers c
ON r.customer_id = c.customer_id
WHERE c.name = '홍길동';
3. 문제: 특정 날짜(예: 2024년 5월 10일)의 모든 예약 정보를 조회하세요.
SELECT r.*
FROM Reservations r
WHERE DATE(r.reservation_time) = '2024-05-10';
4. 문제: 가장 인원 수용이 큰 테이블의 모든 정보를 조회하세요.
SELECT t.*
FROM Tables t
WHERE t.capacity =
(
SELECT MAX(capacity)
FROM tables
);
5. 문제: 특정 메뉴 아이템(예: 스테이크)의 설명을 업데이트하세요.
UPDATE MenuItems m
SET m.description = '값싸고 맛있는 스테이크'
WHERE m.name = '스테이크';
6. 문제: 특정 예약(예: 예약 ID가 5)에 속한 모든 주문을 조회하세요.
SELECT o.*
FROM Orders o
WHERE o.reservation_id = 5;중급
1. 문제: 각 테이블별로 몇 번 예약되었는지 집계하세요.
SELECT t.table_id, COUNT(r.reservation_id)
FROM Tables t
LEFT JOIN Reservations r
ON t.table_id = r.table_id
GROUP BY t.table_id;
2. 문제: 모든 메뉴 아이템의 평균 가격을 계산하세요.
SELECT AVG(m.price)
FROM MenuItems m;
3. 문제: 각 고객별로 몇 번 예약했는지 집계하세요.
SELECT c.name, COUNT(r.reservation_id) AS cnt
FROM Customers c
LEFT JOIN Reservations r
ON c.customer_id = r.customer_id
GROUP BY c.name
ORDER BY cnt DESC;고급 (복잡한 조인 및 서브쿼리)
1. 문제: 가장 많은 예약을 한 고객의 이름과 그 예약 수를 조회하세요.
SELECT c.name, count(r.reservation_id) as cnt
FROM Customers c
JOIN Reservations r
ON r.customer_id = c.customer_id
GROUP BY c.name
HAVING cnt = (
SELECT MAX(reservation_count) as max_reservation_count
FROM(
SELECT COUNT(*) as reservation_count
FROM Reservations r
JOIN Customers c
ON c.customer_id = r.customer_id
GROUP BY c.customer_id
) as sq
);
2. 문제: 특정 기간 동안의 총 예약 수를 계산하세요. (예: 2024년 5월 1일부터 5월 31일까지)
SELECT COUNT(*) AS '2024-05-01 ~ 2024-05-31'
FROM Reservations r
WHERE r.reservation_time BETWEEN '2024-05-01' AND '2024-05-31';
3. 문제: 최근 예약된 순으로 고객 목록을 출력하세요.
SELECT r.reservation_time, c.name
FROM Customers c
JOIN Reservations r
ON r.customer_id = c.customer_id
ORDER BY reservation_time DESC;
4. 문제: 특정 고객(예: 고객 ID가 3)의 총 주문 금액을 계산하세요.
SELECT c.name, SUM(m.price * od.quantity)
FROM Customers c
JOIN Reservations r
ON c.customer_id = r.customer_id
JOIN Orders o
ON r.reservation_id = o.reservation_id
JOIN OrderDetails od
ON o.order_id = od.order_id
JOIN MenuItems m
ON od.menu_item_id = m.menu_item_id
WHERE c.customer_id = 3;
5. 문제: 모든 메뉴 아이템 중 가장 비싼 아이템을 찾으세요.
SELECT m.name, m.price
FROM MenuItems m
WHERE m.price = (SELECT MAX(price)
FROM MenuItems);
6. 문제: 최대 주문 금액을 가진 예약 찾기 : 각 예약에 대한 총 주문 금액을 계산하고, 그 중에서 최대 금액을 가진 예약의 ID와 금액을 조회하세요.
SELECT reservation_id, SUM(price * quantity) AS total_amount
FROM Orders o
JOIN OrderDetails od
ON o.order_id = od.order_id
JOIN MenuItems m
ON od.menu_item_id = m.menu_item_id
GROUP BY reservation_id
HAVING total_amount = (
SELECT MAX(total_amount)
FROM (
SELECT reservation_id, SUM(price * quantity) AS total_amount
FROM Orders
JOIN OrderDetails
ON Orders.order_id = OrderDetails.order_id
JOIN MenuItems
ON OrderDetails.menu_item_id = MenuItems.menu_item_id
GROUP BY reservation_id
) AS sq
);
7. 문제: 모든 주문에서 가장 많이 주문된 메뉴 아이템의 이름과 총 주문 수량을 조회하세요.
SELECT m.name, SUM(od.quantity) as total_ordered
FROM MenuItems m
JOIN OrderDetails od
ON m.menu_item_id = od.menu_item_id
GROUP BY m.name
HAVING total_ordered = (
SELECT MAX(qt)
FROM (
SELECT SUM(od.quantity) as qt
FROM OrderDetails od
GROUP BY menu_item_id
) as sq
);
8. 문제: 2024년 5월 10일에 가장 많이 예약된 테이블의 ID와 예약 횟수를 조회하세요.
SELECT r.table_id, COUNT(*) as cnt
FROM Reservations r
WHERE DATE(r.reservation_time) = '2024-05-10'
GROUP BY r.table_id
HAVING cnt = (
SELECT MAX(max_table_count)
FROM(
SELECT COUNT(*) as max_table_count
FROM Reservations r
WHERE DATE(r.reservation_time) = '2024-05-10'
GROUP BY r.table_id
) as sq
);
9. 문제: 각 고객별로 모든 주문에 대한 평균 주문 금액을 계산하세요.
SELECT c.name, COALESCE(AVG(m.price * od.quantity), 0)
FROM Customers c
LEFT JOIN Reservations r
ON c.customer_id = r.customer_id
LEFT JOIN Orders o
ON r.reservation_id = o.reservation_id
LEFT JOIN OrderDetails od
ON o.order_id = od.order_detail_id
LEFT JOIN MenuItems m
ON od.menu_item_id = m.menu_item_id
GROUP BY c.name;
10. 문제: 가장 많은 종류의 다른 메뉴 아이템을 주문한 고객의 이름과 주문한 메뉴 아이템의 수를 조회하세요.
SELECT DISTINCT c.name, COUNT(od.menu_item_id) as cnt
FROM Customers c
JOIN Reservations r
ON c.customer_id = r.customer_id
JOIN Orders o
ON r.reservation_id = o.reservation_id
JOIN OrderDetails od
ON o.order_id = od.order_id
GROUP BY c.name
ORDER BY cnt DESC
limit 1;
