[중앙정보기술인재개발원] 25일차

예제 4: 호텔 예약 시스템

-- Guests 테이블 생성
-- 이 테이블은 호텔의 모든 손님 정보를 저장합니다.
-- 각 손님은 고유 ID, 이름, 전화번호, 이메일 주소를 가지고 있습니다.
CREATE TABLE Guests (
);

-- Rooms 테이블 생성
-- 이 테이블은 호텔의 모든 방 정보를 저장합니다.
-- 각 방은 고유 ID, 방 번호, 방 종류, 일일 요금을 가지고 있습니다.
CREATE TABLE Rooms (
);

-- Bookings 테이블 생성
-- 이 테이블은 모든 예약 정보를 저장합니다.
-- 예약은 고유 ID, 손님 ID, 방 ID, 체크인 날짜, 체크아웃 날짜를 가지고 있습니다.
CREATE TABLE Bookings (
);

-- Services 테이블 생성
-- 이 테이블은 호텔에서 제공하는 서비스 목록을 저장합니다.
-- 각 서비스는 고유 ID, 이름, 가격을 가지고 있습니다.
CREATE TABLE Services (
);

-- BookingServices 테이블 생성
-- 이 테이블은 예약과 관련된 서비스 사용 정보를 저장합니다.
-- 각 레코드는 고유 ID, 예약 ID, 서비스 ID를 가지고 있습니다.
CREATE TABLE BookingServices (
);

 

- 테이블 생성

-- Guests 테이블 생성
-- 이 테이블은 호텔의 모든 손님 정보를 저장합니다.
-- 각 손님은 고유 ID, 이름, 전화번호, 이메일 주소를 가지고 있습니다.
CREATE TABLE Guests (
	guest_id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	phone_number VARCHAR(15),
	email VARCHAR(100)
);

-- Rooms 테이블 생성
-- 이 테이블은 호텔의 모든 방 정보를 저장합니다.
-- 각 방은 고유 ID, 방 번호, 방 종류, 일일 요금을 가지고 있습니다.
CREATE TABLE Rooms (
	room_id INT AUTO_INCREMENT PRIMARY KEY,
	room_number VARCHAR(10) NOT NULL,
	room_type ENUM('Single', 'Double', 'Suite') NOT NULL,
	rate INT NOT NULL
);

-- Bookings 테이블 생성
-- 이 테이블은 모든 예약 정보를 저장합니다.
-- 예약은 고유 ID, 손님 ID, 방 ID, 체크인 날짜, 체크아웃 날짜를 가지고 있습니다.
CREATE TABLE Bookings (
	booking_id INT AUTO_INCREMENT PRIMARY KEY,
	guest_id INT,
	room_id INT,
	check_in_date DATE NOT NULL,
	check_out_date DATE NOT NULL,
	FOREIGN KEY (guest_id) REFERENCES  Guests(guest_id),
	FOREIGN KEY (room_id) REFERENCES  Rooms(room_id)
);

-- Services 테이블 생성
-- 이 테이블은 호텔에서 제공하는 서비스 목록을 저장합니다.
-- 각 서비스는 고유 ID, 이름, 가격을 가지고 있습니다.
CREATE TABLE Services (
	service_id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	price INT NOT NULL
);

-- BookingServices 테이블 생성
-- 이 테이블은 예약과 관련된 서비스 사용 정보를 저장합니다.
-- 각 레코드는 고유 ID, 예약 ID, 서비스 ID를 가지고 있습니다.
CREATE TABLE BookingServices (
	booking_service_id INT AUTO_INCREMENT PRIMARY KEY,
	booking_id INT,
	service_id INT,
	FOREIGN KEY (booking_id) REFERENCES  Bookings(booking_id),
	FOREIGN KEY (service_id) REFERENCES  Services(service_id)
);

 

- 더미 데이터 삽입

INSERT INTO Guests (name, phone_number, email) VALUES
('John Doe', '123-456-7890', 'john.doe@example.com'),
('Jane Smith', '234-567-8901', 'jane.smith@example.com'),
('Alice Johnson', '345-678-9012', 'alice.johnson@example.com'),
('Bob Brown', '456-789-0123', 'bob.brown@example.com'),
('Charlie Davis', '567-890-1234', 'charlie.davis@example.com'),
('Diana Evans', '678-901-2345', 'diana.evans@example.com'),
('Frank Green', '789-012-3456', 'frank.green@example.com'),
('Grace Hall', '890-123-4567', 'grace.hall@example.com'),
('Henry Adams', '901-234-5678', 'henry.adams@example.com'),
('Ivy Brooks', '012-345-6789', 'ivy.brooks@example.com'),
('Jack Clark', '123-456-7890', 'jack.clark@example.com');
INSERT INTO Rooms (room_number, room_type, rate) VALUES
('101', 'Single', 100.00),
('102', 'Double', 150.00),
('103', 'Suite', 200.00),
('104', 'Single', 110.00),
('105', 'Double', 160.00),
('106', 'Suite', 210.00),
('107', 'Single', 120.00),
('108', 'Double', 170.00),
('109', 'Suite', 220.00),
('110', 'Single', 130.00),
('111', 'Double', 180.00);
INSERT INTO Bookings (guest_id, room_id, check_in_date, check_out_date) VALUES
(1, 1, '2024-05-01', '2024-05-03'),
(2, 2, '2024-05-02', '2024-05-04'),
(3, 3, '2024-05-03', '2024-05-05'),
(4, 4, '2024-05-04', '2024-05-06'),
(5, 5, '2024-05-05', '2024-05-07'),
(6, 6, '2024-05-06', '2024-05-08'),
(7, 7, '2024-05-07', '2024-05-09'),
(8, 8, '2024-05-08', '2024-05-10'),
(9, 9, '2024-05-09', '2024-05-11'),
(10, 10, '2024-05-10', '2024-05-12'),
(1, 1, '2024-05-11', '2024-05-13');
INSERT INTO Services (name, price) VALUES
('Room Cleaning', 20.00),
('Breakfast', 15.00),
('Airport Shuttle', 50.00),
('Spa', 70.00),
('Gym Access', 10.00),
('Pool Access', 25.00),
('Dinner', 30.00),
('Laundry Service', 15.00),
('Guided Tour', 60.00),
('Late Checkout', 40.00),
('Early Checkin', 35.00);
INSERT INTO BookingServices (booking_id, service_id) VALUES
(1, 1),
(1, 2),
(2, 3),
(3, 4),
(4, 5),
(5, 6),
(6, 7),
(7, 8),
(8, 9),
(9, 10),
(10, 11),
(11, 1),
(11, 2);

 

초급 문제

1. 모든 고객의 이름과 이메일 조회

SELECT g.name, g.email
FROM Guests g;

 

2. 특정 방 번호(예: 101)의 방 정보 조회

SELECT r.room_number
FROM Rooms r
WHERE r.room_number = 101;

 

3. 오늘 체크 안하는 예약 목록 조회

SELECT *
FROM Bookings b
WHERE check_in_date = CURDATE();

 

4. 모든 서비스의 이름과 가격 조회

SELECT s.name, s.price
FROM Services s;

 

5. 특정 고객(예: ID 3)의 모든 예약 조회

SELECT b.*
FROM Bookings b
WHERE b.guest_id = 3;

 

중급 문제

1. 각 예약에 대한 고객 이름과 방 번호 조회

SELECT b.booking_id, g.name, r.room_id
FROM Bookings b
LEFT JOIN Guests g
ON b.guest_id = g.guest_id
LEFT JOIN Rooms r
ON b.room_id = r.room_id;

 

2. 특정 날짜(예: '2024-05-10')에 예약된 모든 방과 고객 정보 조회

SELECT r.room_number, g.name
FROM Bookings b
JOIN Rooms r
ON r.room_id = b.room_id
JOIN Guests g
ON g.guest_id = b.guest_id
WHERE '2024-05-10'BETWEEN b.check_in_date AND b.check_out_date;

 

3. 각 방 유형별 평균 요금 계산

SELECT r.room_type, AVG(r.rate)
FROM Rooms r
GROUP BY r.room_type;

 

4. 특정 서비스를 이용한 모든 예약 조회 (예: 서비스 ID가 1)

SELECT b.*
FROM Bookings b
JOIN BookingServices bs
ON bs.booking_id = b.booking_id
WHERE bs.service_id = 1;

 

5. 각 고객별 이용한 서비스의 총 비용 계산

SELECT g.name, SUM(s.price)
FROM Guests g
JOIN Bookings b
ON g.guest_id = b.guest_id
JOIN BookingServices bs
ON b.booking_id = bs.booking_id
JOIN Services s
ON bs.service_id = s.service_id
GROUP BY g.name;

 

고급 문제 (서브쿼리, 집계함수 및 복잡한 JOIN 활용)

1. 최고 요금 방의 예약 내역 조회

SELECT b.booking_id, r.room_id, r.room_number, r.room_type
FROM Bookings b
JOIN Rooms r
ON b.room_id = r.room_id
WHERE r.rate = (
	SELECT MAX(r.rate)
	FROM Rooms r
);

 

2. 각 방 유형별 최소, 최대, 평균 요금 조회

SELECT r.room_type, MIN(r.rate), MAX(r.rate), AVG(r.rate)
FROM Rooms r
GROUP BY r.room_type;

 

3. 가장 많이 예약된 방의 ID와 예약 횟수 조회

SELECT b.room_id, COUNT(*) as cnt
FROM Bookings b
GROUP BY b.room_id
HAVING cnt = (
	SELECT MAX(cnt)
	FROM (
		SELECT b.room_id, COUNT(*) as cnt
		FROM Bookings b
		GROUP BY b.room_id
	)as sq
);

 

4. 가장 많은 예약을 한 고객의 이름과 예약 횟수

SELECT g.name, COUNT(*)
FROM Bookings b
JOIN Guests g
ON g.guest_id = b.guest_id
GROUP BY g.name
HAVING count(*) = (
	SELECT MAX(cnt)
	FROM(
		SELECT g.name, COUNT(*) as cnt
		FROM Bookings b
		JOIN Guests g
		ON g.guest_id = b.guest_id
		GROUP BY g.name
	)as sq
);

 

5. 각 예약에서 이용된 총 서비스 비용 계산

SELECT b.booking_id, SUM(s.price)
FROM Bookings b
JOIN BookingServices bs
ON b.booking_id = bs.booking_id
JOIN Services s
ON bs.service_id = s.service_id
GROUP BY b.booking_id;

 

6. 특정 고객(예: ID 3)의 총 예약 비용 계산 (객실 + 서비스)

SELECT g.name, 
SUM(r.rate * DATEDIFF(b.check_out_date, b.check_in_date) + 
COALESCE (total_service_price, 0) )
AS total_amount 
FROM guests g 
JOIN bookings b
ON b.guest_id = g.guest_id 
JOIN rooms r
ON r.room_id = b.room_id 

LEFT JOIN ( 
 SELECT booking_id, sum(s.price) as total_service_price
 FROM services s 
 JOIN bookingservices bs
 ON bs.booking_service_id  = s.service_id
 GROUP by booking_id 
) AS sq ON b.booking_id  = sq.booking_id
WHERE g.guest_id = 3
GROUP BY g.name
;

-- 아래는 작은 단위부터 해결하기 위해 사용한 쿼리문입니다.
SELECT g.name, r.rate, datediff(b.check_out_date, b.check_in_date), s.price
as total_amount 
FROM guests g
JOIN bookings b
ON b.guest_id = g.guest_id
JOIN rooms r
ON r.room_id = b.room_id 
JOIN bookingservices bs
ON bs.booking_id = b.booking_id 
JOIN services s
ON s.service_id = bs.service_id 
WHERE g.guest_id = 3;

SELECT b.booking_id, sum(s.price)
FROM bookings b 
JOIN bookingservices bs
ON bs.booking_id = b.booking_id 
JOIN services s
ON s.service_id = bs.service_id 
GROUP BY b.booking_id ;

 

7. 각 고객별 체크인 횟수가 가장 많은 달

SELECT g.name, t.month, t.check_in_count
FROM Guests g
JOIN (
	SELECT b.guest_id, MONTH(b.check_in_date) as month, COUNT(*) as check_in_count
	FROM Bookings b
	GROUP BY b.guest_id, MONTH(b.check_in_date)
) as t 
ON t.guest_id = g.guest_id
JOIN (
	SELECT guest_id, MAX(check_in_count) as m
	FROM (
		SELECT b.guest_id, MONTH(b.check_in_date) as month, COUNT(*) as check_in_count
		FROM Bookings b
		GROUP BY b.guest_id, MONTH(b.check_in_date)
	) as sq GROUP BY guest_id
) as tx
ON t.guest_id = tx.guest_id AND t.check_in_count = tx.m
ORDER BY g.name;

 

8. 각 방 유형별로 가장 비싼 방 정보 조회

SELECT r.room_type, r.rate
FROM Rooms r
WHERE (r.room_type, r.rate) IN (
	SELECT r.room_type, max(r.rate)
	FROM Rooms r
	GROUP BY r.room_type)
ORDER BY r.room_type;

 

9. 최근 1년간 각 고객별 평균 예약 기간 조회

SELECT g.name, AVG(DATEDIFF(b.check_out_date, b.check_in_date)) AS avg_days
FROM Bookings b
JOIN Guests g
ON g.guest_id = b.guest_id
WHERE b.check_in_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) and CURDATE()
GROUP BY g.name;

 

10. 특정 기간('2024-01-01'부터 '2024-12-31')동안 가장 많이 사용된 서비스

SELECT s.name, COUNT(*) as service_count
FROM Bookings b
JOIN BookingServices bs
ON b.booking_id = bs.booking_id
JOIN Services s
ON s.service_id = bs.service_id
WHERE b.check_in_date BETWEEN '2024-01-01' and '2025-01-01'
GROUP BY s.name
HAVING service_count = (
	SELECT MAX(cnt)
	FROM (
	SELECT COUNT(*) as cnt
	FROM Bookings b
	JOIN BookingServices bs
	ON b.booking_id = bs.booking_id
	JOIN Services s
	ON s.service_id = bs.service_id
	WHERE b.check_in_date BETWEEN '2024-01-01' and '2025-01-01'
	GROUP BY s.name
	)as sq
);