SQL 호텔예약시스템 예제 기록

-- 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. 모든 고객의 이름과 이메일 조회
2. 특정 방 번호(예: 101)의 방 정보 조회
3. 오늘 체크인하는 예약 목록 조회
4. 모든 서비스의 이름과 가격 조회
5. 특정 고객(예: ID 3)의 모든 예약 조회

중급 문제

1. 각 예약에 대한 고객 이름과 방 번호 조회
2. 특정 날짜(예: '2024-05-10')에 예약된 모든 방과 고객 정보 조회
3. 각 방 유형별 평균 요금 계산
4. 특정 서비스를 이용한 모든 예약 조회 (예: 서비스 ID가 1)
5. 각 고객별 이용한 서비스의 총 비용 계산

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

1. 최고 요금 방의 예약 내역 조회
2. 각 방 유형별 최소, 최대, 평균 요금 조회
3. 가장 많이 예약된 방의 ID와 예약 횟수 조회
4. 가장 많은 예약을 한 고객의 이름과 예약 횟수
5. 각 예약에서 이용된 총 서비스 비용 계산
6. 특정 고객(예: ID 3)의 총 예약 비용 계산 (객실 + 서비스)
7. 각 고객별 체크인 횟수가 가장 많은 달
8. 각 방 유형별로 가장 비싼 방 정보 조회
9. 최근 1년간 각 고객별 평균 예약 기간 조회
10. 특정 기간('2024-01-01'부터 '2024-12-31') 동안 가장 많이 사용된 서비스

 

 

 

개인적인 풀이

 

-- 초급 1번

SELECT g.name, g.email

FROM Guests as g;

 

-- 초급 2번

SELECT r.*

FROM Rooms as r

WHERE r.room_number = 101;

 

-- 초급 3번

SELECT b.*

FROM Bookings b

WHERE DATE(b.check_in_date) = CURRENT_DATE();

 

-- 초급 4번

SELECT s.name, s.price

FROM Services s;

 

-- 초급 5번

SELECT b.*

FROM Bookings b

WHERE guest_id = 3;

 

-- 중급 1번

SELECT g.name, r.room_number

FROM Bookings b

LEFT JOIN Guests g

ON g.guest_id = b.guest_id

LEFT JOIN Rooms r

ON r.room_id = b.room_id;

 

-- 중급 2번

SELECT r.*, g.*

FROM Bookings b

JOIN Guests g

ON g.guest_id = b.guest_id

JOIN Rooms r

ON r.room_id = b.room_id

WHERE DATE(b.check_in_date) = '2024-05-10%';

 

-- 중급 3번

SELECT r.room_type, AVG(r.rate)

FROM Rooms r

GROUP BY r.room_type;

 

-- 중급 4번

SELECT b.*

FROM Bookings b

LEFT JOIN BookingServices bs

ON bs.booking_id = b.booking_id

WHERE bs.service_id = 1;

 

-- 중급 5번

SELECT g.name, SUM(s.price) AS pri

FROM Guests g

LEFT JOIN Bookings b

ON b.guest_id = g.guest_id

LEFT JOIN BookingServices bs

ON bs.booking_id = b.booking_id

LEFT JOIN Services s

ON s.service_id = bs.service_id

GROUP BY g.name;

 

-- 고급 1번

SELECT b.*

FROM Bookings b

JOIN Rooms r

ON r.room_id = b.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번

SELECT b.room_id, COUNT(b.booking_id) AS cnt

FROM Bookings b

GROUP BY b.room_id

HAVING cnt =(

SELECT MAX(room_max) FROM (

SELECT COUNT(b.booking_id) as room_max

FROM Bookings b

GROUP BY b.room_id

) as SQ

);

 

-- 고급 4번

SELECT g.name, COUNT(b.booking_id) AS cnt

FROM Guests g

JOIN Bookings b

ON b.guest_id = g.guest_id

GROUP BY g.name

HAVING cnt = (

SELECT MAX(guest_max) FROM(

SELECT COUNT(b.booking_id) AS guest_max

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

LEFT JOIN BookingServices bs

ON bs.booking_id = b.booking_id

LEFT JOIN Services s

ON s.service_id = bs.service_id

GROUP BY b.booking_id;

 

-- 고급 6번

select g.name, r.rate * datediff(b.check_out_date, b.check_in_date) + total_service_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 (

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

;

 

-- 고급 7번

 

SELECT g.name,

COUNT(b.booking_id) AS cnt,

MONTH(b.check_in_date) as ext_month

FROM Guests g

LEFT JOIN Bookings b

ON b.guest_id = g.guest_id

GROUP BY g.name, ext_month

HAVING COUNT(*) = (

SELECT MAX(max_cnt) FROM (

SELECT g.name AS gname,

COUNT(b.booking_id) AS max_cnt,

MONTH(b.check_in_date) as ext_month2

FROM Guests g

LEFT JOIN Bookings b

ON b.guest_id = g.guest_id

GROUP BY g.name, ext_month2

) as sq

WHERE sq.gname = g.name

GROUP BY sq.gname

);

 

select * from guests g

join (

select guest_id , month(check_in_date) as month, count(*) as check_in_count from bookings b

group by b.guest_id, month(check_in_date)

) as t

on t.guest_id = g.guest_id

join (

select guest_id , max(check_in_count) as m from (

select guest_id , month(check_in_date) as month, count(*) as check_in_count from bookings b

group by b.guest_id, month(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번

SELECT g.name, AVG(DATEDIFF(b.check_out_date, b.check_in_date))

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번

SELECT s.name, COUNT(s.service_id) AS cnt

FROM Services s

JOIN BookingServices bs

ON bs.service_id = s.service_id

JOIN Bookings b

ON b.booking_id = bs.booking_id

WHERE DATE(b.check_in_date) BETWEEN '2024-01-01' AND '2024-12-31'

GROUP BY s.name

HAVING cnt = (

SELECT MAX(bs_cnt) FROM(

SELECT COUNT(bs.booking_service_id) as bs_cnt

FROM BookingServices bs

JOIN Bookings b

ON b.booking_id = bs.booking_id

WHERE DATE(b.check_in_date) BETWEEN '2024-01-01' AND '2024-12-31'

GROUP BY bs.service_id

) as sq

);

 

고급 7번은 그저 레전드... 본인 풀이 + 강사님 풀이..

꼭 다시 풀어보고 WHERE 절은 씹뜯맛즐 필수...