레스토랑 예약 시스템 관련 예제 기록

예제 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 (

);

 

 

모델링 및 더미데이터

-- Customers 테이블 생성

-- 식당의 고객 정보를 저장합니다.

-- 고객의 고유 식별자, 이름, 전화번호, 이메일 주소가 포함됩니다.

CREATE TABLE Customers (

customer_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

phone_number VARCHAR(15),

email VARCHAR(100)

);

 

-- Tables 테이블 생성

-- 식당의 테이블 정보를 관리합니다.

-- 각 테이블은 고유 식별자와 수용 가능한 최대 인원 수를 가집니다.

CREATE TABLE Tables (

table_id INT AUTO_INCREMENT PRIMARY KEY,

capacity INT NOT NULL

);

 

-- Reservations 테이블 생성

-- 고객의 테이블 예약 정보를 기록합니다.

-- 예약 ID, 고객 ID, 테이블 ID, 예약 시간 등의 정보를 포함하며, 예약은 특정 고객과 테이블에 연결됩니다.

CREATE TABLE Reservations (

reservation_id INT AUTO_INCREMENT PRIMARY KEY,

customer_id INT,

table_id INT,

reservation_time DATETIME NOT NULL,

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),

FOREIGN KEY (table_id) REFERENCES Tables(table_id)

);

 

-- Orders 테이블 생성

-- 각 예약 동안 발생한 주문을 추적합니다.

-- 주문 ID, 예약 ID(옵션), 주문 시간을 기록합니다.

CREATE TABLE Orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,

reservation_id INT,

FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id),

order_time DATETIME NOT NULL

);

 

-- MenuItems 테이블 생성

-- 메뉴 아이템에 대한 정보를 저장합니다.

-- 메뉴 아이템의 고유 ID, 이름, 설명, 가격이 포함됩니다.

CREATE TABLE MenuItems (

menu_item_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

description TEXT,

price INT NOT NULL

);

 

-- OrderDetails 테이블 생성

-- 주문 상세 정보를 기록합니다.

-- 각 주문의 상세한 아이템과 수량 정보가 저장됩니다.

CREATE TABLE OrderDetails (

order_detail_id INT AUTO_INCREMENT PRIMARY KEY,

order_id INT,

menu_item_id INT,

quantity INT NOT NULL,

FOREIGN KEY (order_id) REFERENCES Orders(order_id),

FOREIGN KEY (menu_item_id) REFERENCES MenuItems (menu_item_id)

);

 

-- Customers 테이블에 더미 데이터 추가

INSERT INTO Customers (name, phone_number, email) VALUES

('홍길동', '010-1234-5678', 'hong@domain.com'),

('김민수', '010-2345-6789', 'minsu@domain.com'),

('이하나', '010-3456-7890', 'hana@domain.com'),

('최예진', '010-4567-8901', 'yejin@domain.com'),

('박지원', '010-5678-9012', 'jiwon@domain.com'),

('정수민', '010-6789-0123', 'sumin@domain.com'),

('유진아', '010-7890-1234', 'jina@domain.com'),

('남지현', '010-8901-2345', 'jihyun@domain.com'),

('오혜진', '010-9012-3456', 'hyejin@domain.com'),

('한예슬', '010-0123-4567', 'yesul@domain.com');

 

-- Tables 테이블에 더미 데이터 추가

INSERT INTO Tables (capacity) VALUES

(2), (4), (4), (2), (6),

(6), (8), (8), (10), (10);

 

-- Reservations 테이블에 더미 데이터 추가

INSERT INTO Reservations (customer_id, table_id, reservation_time) VALUES

(1, 1, '2024-05-01 18:00:00'),

(2, 2, '2024-05-02 18:00:00'),

(3, 3, '2024-05-03 18:00:00'),

(4, 4, '2024-05-04 18:00:00'),

(5, 5, '2024-05-05 18:00:00'),

(6, 6, '2024-05-06 18:00:00'),

(7, 7, '2024-05-07 18:00:00'),

(8, 8, '2024-05-08 18:00:00'),

(9, 9, '2024-05-09 18:00:00'),

(10, 10, '2024-05-10 18:00:00');

 

-- Orders 테이블에 더미 데이터 추가

INSERT INTO Orders (reservation_id, order_time) VALUES

(1, '2024-05-01 18:30:00'),

(2, '2024-05-02 18:30:00'),

(3, '2024-05-03 18:30:00'),

(4, '2024-05-04 18:30:00'),

(5, '2024-05-05 18:30:00'),

(6, '2024-05-06 18:30:00'),

(7, '2024-05-07 18:30:00'),

(8, '2024-05-08 18:30:00'),

(9, '2024-05-09 18:30:00'),

(10, '2024-05-10 18:30:00');

 

-- MenuItems 테이블에 더미 데이터 추가

INSERT INTO MenuItems (name, description, price) VALUES

('스테이크', '최고급 소고기 스테이크', 30000),

('파스타', '이탈리안 스타일 파스타', 15000),

('샐러드', '신선한 채소 샐러드', 12000),

('피자', '마르게리타 피자', 20000),

('치킨', '후라이드 치킨', 18000),

('타코', '멕시칸 스타일 타코', 16000),

('리조또', '버섯 리조또', 17000),

('수프', '양송이 크림 수프', 8000),

('빵', '신선한 구운 빵', 5000),

('와인', '고급 레드 와인', 40000);

 

-- OrderDetails 테이블에 더미 데이터 추가

INSERT INTO OrderDetails (order_id, menu_item_id, quantity) VALUES

(1, 1, 2),

(2, 2, 2),

(3, 3, 3),

(4, 4, 1),

(5, 5, 2),

(6, 6, 3),

(7, 7, 1),

(8, 8, 4),

(9, 9, 2),

(10, 10, 1);

 

문제

 

초급

1. 문제: 식당 데이터베이스의 모든 고객 정보를 조회하세요. (이름, 전화번호, 이메일)
2. 문제: 특정 고객(예: 홍길동)의 모든 예약 정보를 조회하세요.
3. 문제: 특정 날짜(예: 2024년 5월 10일)의 모든 예약 정보를 조회하세요.
4. 문제: 가장 인원 수용이 큰 테이블의 모든 정보를 조회하세요.
5. 문제: 특정 메뉴 아이템(예: 스테이크)의 설명을 업데이트하세요.
6. 문제: 특정 예약(예: 예약 ID가 5)에 속한 모든 주문을 조회하세요.

중급

1. 문제: 각 테이블별로 몇 번 예약되었는지 집계하세요.
2. 문제: 모든 메뉴 아이템의 평균 가격을 계산하세요.
3. 문제: 각 고객별로 몇 번 예약했는지 집계하세요.

고급 (복잡한 조인 및 서브쿼리)

1. 문제: 가장 많은 예약을 한 고객의 이름과 그 예약 수를 조회하세요.
2. 문제: 특정 기간 동안의 총 예약 수를 계산하세요. (예: 2024년 5월 1일부터 5월 31일까지)
3. 문제: 최근 예약된 순으로 고객 목록을 출력하세요.
4. 문제: 특정 고객(예: 고객 ID가 3)의 총 주문 금액을 계산하세요.
5. 문제: 모든 메뉴 아이템 중 가장 비싼 아이템을 찾으세요.
6. 문제: 최대 주문 금액을 가진 예약 찾기 : 각 예약에 대한 총 주문 금액을 계산하고, 그 중에서 최대 금액을 가진 예약의 ID와 금액을 조회하세요.
7. 문제: 모든 주문에서 가장 많이 주문된 메뉴 아이템의 이름과 총 주문 수량을 조회하세요.
8. 문제: 2024년 5월 10일에 가장 많이 예약된 테이블의 ID와 예약 횟수를 조회하세요.
9. 문제: 각 고객별로 모든 주문에 대한 평균 주문 금액을 계산하세요.
10. 문제: 가장 많은 종류의 다른 메뉴 아이템을 주문한 고객의 이름과 주문한 메뉴 아이템의 수를 조회하세요.

 

개인적인 풀이

-- 초급 1번

SELECT c.name, c.phone_number, c.email

FROM Customers c;

 

-- 초급 2번

SELECT r.*

FROM Reservations r

JOIN Customers c

ON c.customer_id = r.customer_id

WHERE c.name = '홍길동';

 

-- 초급 3번

SELECT r.*

FROM Reservations r

WHERE DATE(reservation_time) = '2024-05-10';

 

-- 초급 4번

SELECT t.*

FROM Tables t

ORDER BY t.capacity DESC

LIMIT 1;

 

-- 초급 5번

UPDATE MenuItems

SET MenuItems.description = '고기가 부드러워요.'

WHERE MenuItems.name = '스테이크';

 

-- 초급 6번

SELECT o.*

FROM Orders o

JOIN Reservations r

ON r.reservation_id = o.order_id

WHERE o.reservation_id = 5;

 

-- 중급 1번

SELECT t.table_id, COUNT(r.table_id)

FROM Reservations r

JOIN Tables t

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.customer_id)

FROM Customers c

LEFT JOIN Reservations r

ON r.customer_id = c.customer_id

GROUP BY c.name;

 

-- 고급 1번

SELECT c.name, COUNT(r.customer_id)

FROM Customers c

JOIN Reservations r

ON r.customer_id = c.customer_id

GROUP BY c.name

ORDER BY COUNT(r.customer_id) DESC

LIMIT 1;

 

-- 고급 2번

SELECT COUNT(r.reservation_id)

FROM Reservations r

WHERE reservation_time BETWEEN '2024-05-01' AND '2024-05-31';

 

-- 고급 3번

SELECT c.name, r.reservation_time

FROM Customers c

JOIN Reservations r

ON r.customer_id = c.customer_id

ORDER BY r.reservation_time DESC;

 

-- 고급 4번

SELECT SUM(m.price * od.quantity)

FROM MenuItems m

JOIN OrderDetails od

ON od.menu_item_id = m.menu_item_id

JOIN Orders o

ON o.order_id = od.order_id

JOIN Reservations r

ON r.reservation_id = o.reservation_id

JOIN Customers c

ON c.customer_id = r.customer_id

WHERE c.customer_id = 3;

 

-- 고급 5번

SELECT m.name

FROM MenuItems m

WHERE m.price = (SELECT MAX(m.price) FROM MenuItems m);

 

-- 고급 6번

SELECT r.reservation_id, SUM(m.price * od.quantity) AS cnt

FROM Reservations r

JOIN Orders o

ON o.reservation_id = r.reservation_id

JOIN OrderDetails od

ON od.order_id = o.order_id

JOIN MenuItems m

ON m.menu_item_id = od.menu_item_id

GROUP BY r.reservation_id

ORDER BY cnt DESC

LIMIT 1;

 

-- 고급 7번

SELECT mi.name, SUM(od.quantity)

FROM MenuItems mi

JOIN OrderDetails od

ON od.menu_item_id = mi.menu_item_id

GROUP BY mi.name

ORDER BY SUM(od.quantity) DESC

LIMIT 1;

 

SELECT mi.name, sum(od.quantity) as total_ordered

FROM MenuItems as mi

JOIN OrderDetails od

ON od.menu_item_id = mi.menu_item_id

GROUP BY mi.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번

SELECT t.table_id, COUNT(r.reservation_id)

FROM Tables t

JOIN Reservations r

ON r.table_id = t.table_id

WHERE DATE(r.reservation_time) = '2024-05-10'

GROUP BY t.table_id

ORDER BY COUNT(r.reservation_id) DESC

LIMIT 1;

 

 

SELECT t.table_id, COUNT(r.reservation_id) as cnt

FROM Tables t

JOIN Reservations r

ON r.table_id = t.table_id

WHERE DATE(r.reservation_time) = '2024-05-10'

GROUP BY t.table_id

HAVING cnt = (

SELECT MAX(cnt2) FROM (

SELECT COUNT(r.reservation_id) as cnt2

FROM Reservations r

GROUP BY table_id

) as sq

);

 

-- 고급 9번

SELECT c.name, COALESCE(AVG(mi.price * od.quantity), 0)

FROM Customers c

LEFT JOIN Reservations r

ON r.customer_id = c.customer_id

LEFT JOIN Orders o

ON o.reservation_id = r.reservation_id

LEFT JOIN OrderDetails od

ON od.order_id = o.order_id

LEFT JOIN MenuItems mi

ON mi.menu_item_id = od.menu_item_id

GROUP BY c.name;

 

-- 고급 10번

SELECT DISTINCT c.name, COUNT(mi.menu_item_id) AS cont

FROM Customers c

JOIN Reservations r

ON r.customer_id = c.customer_id

JOIN Orders o

ON o.reservation_id = r.reservation_id

JOIN OrderDetails od

ON od.order_id = o.order_id

JOIN MenuItems mi

ON mi.menu_item_id = od.menu_item_id

GROUP BY c.name

HAVING cont = (

SELECT MAX(cnt) FROM(

SELECT COUNT(mi.menu_item_id) as cnt

FROM MenuItems mi

JOIN OrderDetails od

ON od.menu_item_id = mi.menu_item_id

JOIN Orders o

ON o.order_id = od.order_id

JOIN Reservations r

ON r.reservation_id = o.reservation_id

JOIN Customers c

ON c.customer_id = r.customer_id

GROUP BY c.name

) as sq

);