SQL Projects: Hotel Booking System - Database Design and Management

Hotel Booking System:

The Hotel Booking System is a comprehensive solution to manage hotel reservations, room availability, guest information, payments, and staff records. This system allows efficient handling of bookings, check-ins, check-outs, and payment tracking. This project will cover database design, table creation, inserting sample data, implementing core functionalities, and providing SQL queries for generating insights.

Creating the Database in MySQL or PostgreSQL:

Create the Database:

-- Create the database for the Movie Rental System
CREATE DATABASE HotelBookingDB;
USE HotelBookingDB;

Create the Tables:

Rooms Table:

The Rooms table is designed to store information about hotel rooms. It includes a unique identifier for each room, its type (e.g., Single or Suite), the nightly price, and availability status, indicating whether the room is available for booking.

Structure:

Code:

-- Create the Rooms table
CREATE TABLE Rooms (
    room_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique room identifier
    room_type VARCHAR(50) NOT NULL,          -- Type of room
    price DECIMAL(10, 2) NOT NULL,           -- Price per night
    is_available BOOLEAN DEFAULT TRUE        -- Room availability status
);

Guests Table :

The Guests table stores essential information about each hotel guest. It includes a unique guest identifier, the guest's full name, email address (which must be unique), contact number, and the date they registered, helping manage guest details effectively.

Structure:

Code:

-- Create the Guests table
CREATE TABLE Guests (
    guest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each guest
    name VARCHAR(25) NOT NULL,               -- Full name of the guest
    email VARCHAR(25) UNIQUE,                -- Email address (must be unique)
    phone VARCHAR(15),                        -- Contact number
    join_date DATE DEFAULT (CURRENT_DATE)     -- Guest join date
);

Bookings Table:

The Bookings table holds information on each hotel booking. It includes a unique booking ID, references to the guest and room, check-in and check-out dates, and the total amount charged. This table links guests with their booked rooms, ensuring accurate reservation management.

Structure:

Code:

-- Create the Bookings table
CREATE TABLE Bookings (
    booking_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,   -- Unique identifier for each booking
    guest_id INT,                                -- Foreign key to Guests table
    room_id INT,                                 -- Foreign key to Rooms table
    check_in DATE NOT NULL,                      -- Check-in date
    check_out DATE NOT NULL,                     -- Check-out date
    total_amount DECIMAL(10, 2) NOT NULL,        -- Total amount for the booking
    FOREIGN KEY (guest_id) REFERENCES Guests(guest_id), -- Linking to Guests
    FOREIGN KEY (room_id) REFERENCES Rooms(room_id)     -- Linking to Rooms
);

Payments Table:

The Payments table records payment details for each booking. It includes a unique payment ID, links to the booking, the amount paid, the payment date, and the status (Paid or Unpaid). This table helps manage and track payment information for reservations efficiently.

Structure:

Code:

-- Create the Payments table
CREATE TABLE Payments (
    payment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each payment
    booking_id INT,                             -- Foreign key to Bookings table
    amount_paid DECIMAL(10, 2) NOT NULL,        -- Amount paid
    payment_date DATE DEFAULT (CURRENT_DATE),   -- Payment date
    payment_status ENUM('Paid', 'Unpaid') DEFAULT 'Unpaid', -- Payment status
    FOREIGN KEY (booking_id) REFERENCES Bookings(booking_id) -- Link to Bookings
);

Staff Table:

The Staff table stores information about hotel staff members. It includes a unique staff ID, the staff member's full name, and their role (e.g., Manager or Receptionist). This table helps manage staff records and their responsibilities within the hotel.

Structure:

Code:

-- Create the Staff table
CREATE TABLE Staff (
    staff_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each staff member
    name VARCHAR(25) NOT NULL,               -- Full name of the staff member
    role VARCHAR(100)                         -- Role of the staff member
);

Inserting Data:

Add some sample data to the tables.

Inserting Data into Rooms Table:

-- Insert rooms into Rooms table
INSERT INTO Rooms (room_type, price) VALUES
('Single', 100.00),
('Double', 150.00),
('Suite', 300.00);

Inserting Data into Guests Table:

-- Insert guests into Guests table
INSERT INTO Guests (name, email, phone) VALUES
('Paul Adam', '[email protected]', '1234567890'),
('Devon McGraw', '[email protected]', '0987654321');

Inserting Data into Bookings Table:

-- Insert booking records into Bookings table
INSERT INTO Bookings (guest_id, room_id, check_in, check_out, total_amount) VALUES
(1, 1, '2024-11-01', '2024-11-05', 400.00),
(2, 2, '2024-11-10', '2024-11-15', 750.00);

Inserting Data into Payments Table:

-- Insert payments into Payments table
INSERT INTO Payments (booking_id, amount_paid, payment_status) VALUES
(1, 400.00, 'Paid'),
(2, 750.00, 'Paid');

Inserting Data into Staff Table:

-- Insert staff into Staff table
INSERT INTO Staff (name, role) VALUES
('Stain Hill', 'Manager'),
('Steave Harris', 'Receptionist');

Basic Functionalities:

• Add New Bookings
• Register New Guests
• Process Payments
• Assign Rooms
• Track Room Availability
• Check-in and Check-out
• Generate Reports for Revenue and Booking History

Writing Queries for Functionality:

Query-1: List Available Rooms

SELECT *                  -- Selects all columns
FROM Rooms                -- Specifies the Rooms table as the data source
WHERE is_available = TRUE; -- Filters results to include only available rooms (where is_available is true)

Explanation:

This query retrieves all rooms from the Rooms table that are currently available for booking. By setting the is_available condition to TRUE, it ensures that only rooms marked as available appear in the result set.

Output:

room_id	room_type	price	is_available
1	Single	100.00	1
2	Double	150.00	1
3	Suite	300.00	1

Query-2: Get Booking Details for a Guest

SELECT *                    -- Selects all columns
FROM Bookings               -- Specifies the Bookings table as the data source
WHERE guest_id = 1;         -- Filters results to show only bookings for the guest with ID 1

Explanation:

This query fetches all booking records associated with a specific guest, identified by guest_id = 1, from the Bookings table. It retrieves details like booking dates, room information, and status,

Output:

booking_id	guest_id	room_id	check_in	check_out	total_amount
1	1	1	2024-11-01	2024-11-05	400.00

Query-3: Total Revenue from Bookings

SELECT SUM(total_amount) AS total_revenue  -- Calculates the sum of all total_amount values, labeling it as total_revenue
FROM Bookings;                             -- Specifies the Bookings table as the data source

Explanation:

This query calculates the total revenue generated from all bookings by summing up the total_amount for each record in the Bookings table. The result is labeled total_revenue, providing a quick overview of income from bookings.

Output:

total_revenue
1150.00

Query-4: Room Occupancy Report

SELECT room_type,                      -- Selects the type of room from the Rooms table
       COUNT(*) AS occupancy_count     -- Counts the number of bookings per room type and labels it as occupancy_count
FROM Bookings                          -- Specifies the Bookings table as the main data source
JOIN Rooms ON Bookings.room_id = Rooms.room_id  -- Joins Rooms table to get room type by matching room_id in both tables
GROUP BY room_type;                    -- Groups the results by room type to get a count for each type

Explanation:

This query generates a report on room occupancy by counting how many times each room type has been booked. It achieves this by joining the Bookings and Rooms tables using room_id, then grouping by room_type. The result gives an occupancy_count for each room type, offering insight into the popularity of different types of rooms.

Output:

room_type	occupancy_count
Single	1
Double	1

Query-5: Payment Status by Booking

	SELECT b.booking_id,                  -- Retrieves the unique ID for each booking
       b.total_amount,                -- Selects the total booking amount from the Bookings table
       p.amount_paid,                 -- Retrieves the amount paid from the Payments table
       p.payment_status               -- Shows the payment status (e.g., Paid, Pending)
FROM Bookings b                       -- Specifies Bookings as the main table with alias 'b'
LEFT JOIN Payments p ON b.booking_id = p.booking_id;  -- Left joins Payments table on matching booking_id values

Explanation:

This query provides a breakdown of payment statuses for each booking by displaying the booking ID, total amount, amount paid, and payment status. It uses a LEFT JOIN between Bookings and Payments on booking_id, ensuring that each booking is shown, even if no payment has been made. This view is useful to identify unpaid or partially paid bookings.

Output:

booking_id	total_amount	amount_paid	payment_status
1	400.00	400.00	Paid
2	750.00	750.00	Paid

Query-6: Calculate Occupancy Rate for Each Room Type

SELECT r.room_type,                           -- Selects the room type from the Rooms table
       COUNT(b.booking_id) AS total_bookings, -- Counts total bookings for each room type
       (COUNT(b.booking_id) / (DATEDIFF(CURDATE(), MIN(b.check_in)))) * 100 AS occupancy_rate
       -- Calculates occupancy rate based on bookings since the earliest check-in date
FROM Rooms r                                  -- Specifies Rooms as the main table with alias 'r'
LEFT JOIN Bookings b ON r.room_id = b.room_id -- Left joins Bookings table to Rooms on matching room_id values
GROUP BY r.room_type;                         -- Groups the results by room type to calculate occupancy per type

Explanation:

This query calculates the occupancy rate for each room type, providing insights into room utilization. It does this by counting bookings for each room type and dividing it by the total number of days since the earliest check-in. The DATEDIFF function calculates the date range, while the division and multiplication by 100 yield the occupancy rate as a percentage.

Output:

room_type	total_bookings	occupancy_rate
Single	1	-25.0000
Double	1	-7.6923
Suite	0	NULL

Query-7: Find All Guests with Multiple Bookings

SELECT g.guest_id,                     -- Selects the unique identifier for each guest
       g.name,                         -- Retrieves the name of each guest
       COUNT(b.booking_id) AS booking_count -- Counts total bookings made by each guest
FROM Guests g                          -- Specifies Guests as the main table with alias 'g'
JOIN Bookings b ON g.guest_id = b.guest_id -- Joins Bookings table to Guests on matching guest_id values
GROUP BY g.guest_id                    -- Groups results by guest_id to aggregate bookings per guest
HAVING booking_count > 1;              -- Filters results to only show guests with more than one booking

Explanation:

This query identifies guests who have made multiple bookings, providing the guest's ID, name, and the total number of bookings they've made. By joining the Bookings table with Guests on guest_id and grouping by each guest, we can use HAVING to filter for those with a booking_count greater than one.

Output:

Output not generated for insufficient data

Query-8: Calculate Average Booking Duration by Room Type

SELECT r.room_type,                              -- Selects the room type for each room
       AVG(DATEDIFF(b.check_out, b.check_in)) AS avg_duration -- Calculates average stay duration by room type
FROM Rooms r                                     -- Specifies Rooms as the main table with alias 'r'
JOIN Bookings b ON r.room_id = b.room_id         -- Joins Bookings table to Rooms on matching room_id values
GROUP BY r.room_type;                            -- Groups results by room type to calculate averages per type

Explanation:

This query calculates the average duration of stay for each room type, providing insights into which room types are booked for longer periods. It uses the DATEDIFF function to find the duration of each booking (difference between check_out and check_in), and then AVG to find the mean duration for each room type. Grouping by room_type allows for results segmented by room category, aiding in understanding booking patterns and preferences.

Output:

room_type	avg_duration
Single	4.0000
Double	5.0000

Query-9: Total Revenue per Room Type

SELECT r.room_type,                                 -- Selects each room type
       SUM(b.total_amount) AS total_revenue         -- Calculates total revenue from bookings per room type
FROM Rooms r                                        -- Specifies Rooms as the primary table with alias 'r'
JOIN Bookings b ON r.room_id = b.room_id            -- Joins Bookings table to Rooms on matching room_id values
GROUP BY r.room_type;                               -- Groups results by room type to summarize revenue per type

Explanation:

This query calculates the total revenue generated for each room type. By joining the Rooms and Bookings tables on the common room_id, it accesses total_amount from each booking, then sums up this value for each room type. Grouping by room_type provides aggregated revenue information by category.

Output:

room_type	total_revenue
Single	400.00
Double	750.00

Query-10: List All Overdue Check-Outs (Where Check-Out Date is Past and Room is Still Marked as Booked)

SELECT b.booking_id,                          -- Retrieves the unique booking ID
       g.name,                                 -- Retrieves guest's name associated with the booking
       b.check_out,                            -- Retrieves check-out date from the booking
       r.room_type                             -- Retrieves the type of room for the booking
FROM Bookings b                                -- Specifies Bookings as the main table with alias 'b'
JOIN Guests g ON b.guest_id = g.guest_id       -- Joins Guests table to access guest names using guest_id
JOIN Rooms r ON b.room_id = r.room_id          -- Joins Rooms table to get room details using room_id
WHERE b.check_out < CURDATE()                  -- Filters for bookings where check-out date is in the past
  AND r.is_available = FALSE;                  -- Filters for rooms still marked as unavailable (booked)

Explanation:

This query identifies all overdue check-outs by listing bookings where the scheduled check-out date has already passed, but the room remains marked as unavailable (indicating it’s still booked). The query joins the Bookings, Guests, and Rooms tables to retrieve booking ID, guest name, check-out date, and room type for these cases.

Output:

Output not generated for insufficient data

Query-11: Identify High-Spending Guests (Guests Who Have Spent More Than a Given Amount)

SELECT g.guest_id,                         -- Retrieves the unique guest ID
       g.name,                             -- Retrieves guest name
       SUM(b.total_amount) AS total_spent  -- Calculates total spending by summing up the total amount per guest
FROM Guests g                              -- Specifies Guests as the main table with alias 'g'
JOIN Bookings b ON g.guest_id = b.guest_id -- Joins Bookings table to get booking details using guest_id
GROUP BY g.guest_id                        -- Groups results by each guest's unique ID
HAVING total_spent > 1000;                 -- Filters for guests whose total spending exceeds 1000

Explanation:

This query identifies high-spending guests by calculating each guest's total spending based on the bookings they have made. By summing the total_amount for each guest and using a HAVING clause, it filters guests who have spent more than a specified amount (in this case, 1000). The result displays each high-spending guest’s ID, name, and total spending, which helps in identifying valuable guests for potential loyalty programs or special offers.

Output:

Output not generated for insufficient data

Query-12: Get Upcoming Check-Ins for the Next 7 Days

SELECT b.booking_id,               -- Retrieves unique ID for each booking
       g.name,                     -- Retrieves guest's name
       b.check_in,                 -- Retrieves check-in date for each booking
       r.room_type                 -- Retrieves type of room booked
FROM Bookings b                    -- Specifies Bookings table as the main table with alias 'b'
JOIN Guests g ON b.guest_id = g.guest_id  -- Joins Guests table to retrieve guest details using guest_id
JOIN Rooms r ON b.room_id = r.room_id     -- Joins Rooms table to retrieve room details using room_id
WHERE b.check_in BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY); -- Filters for check-ins within the next 7 days

Explanation:

This query lists upcoming check-ins scheduled within the next seven days. Using the WHERE clause, it filters for bookings with a check_in date between today (CURDATE()) and seven days from today (DATE_ADD(CURDATE(), INTERVAL 7 DAY)). The result includes the booking ID, guest’s name, check-in date, and room type, making it useful for preparing the hotel for expected arrivals within the week.

Output:

booking_id	name	check_in	room_type
1	Paul Adam	2024-11-01	Single

Query-13: Generate Monthly Revenue Report for the Current Year

SELECT MONTH(b.check_in) AS month,           -- Retrieves the month from check-in dates, labeled as 'month'
       SUM(b.total_amount) AS monthly_revenue -- Calculates total revenue for each month
FROM Bookings b                               -- Specifies Bookings table as the main table with alias 'b'
WHERE YEAR(b.check_in) = YEAR(CURDATE())      -- Filters records to include only the current year based on check-in dates
GROUP BY month                                -- Groups results by month to get revenue totals per month
ORDER BY month;                               -- Orders the results in chronological order by month

Explanation:

This query produces a monthly revenue report for the current year, focusing on the check_in date in each booking. The MONTH() function extracts the month from each check_in date, while SUM(b.total_amount) aggregates the booking amounts to calculate total monthly revenue. Grouped by month and ordered chronologically, the report is ideal for tracking revenue trends across months within the current year.

Output:

month	monthly_revenue
11	1150.00

Query-14: List Rooms that Have Not Been Booked in the Past 6 Months

SELECT r.room_id, r.room_type                  -- Selects room ID and room type to display rooms
FROM Rooms r                                   -- Specifies Rooms table as the main table with alias 'r'
LEFT JOIN Bookings b ON r.room_id = b.room_id  -- Performs a LEFT JOIN between Rooms and Bookings tables on room ID
     AND b.check_in >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) -- Filters bookings to those with check-in dates within the last 6 months
WHERE b.booking_id IS NULL;                    -- Filters to only show rooms without bookings in the last 6 months

Explanation:

This query identifies rooms that have not been booked in the last 6 months. By using a LEFT JOIN between the Rooms and Bookings tables, the query retrieves all rooms while only including bookings made within the last 6 months. The WHERE clause filters out any rows where a matching booking record exists, thereby displaying only rooms with no recent bookings.

Output:

room_id	room_type
3	Suite

Query-15: Find Most Popular Room Type Based on Bookings

SELECT room_type, COUNT(*) AS bookings_count  -- Selects room type and counts bookings per room type
FROM Bookings b                               -- Specifies Bookings table as the main table with alias 'b'
JOIN Rooms r ON b.room_id = r.room_id         -- Joins Rooms table on room ID to get room type information
GROUP BY r.room_type                          -- Groups results by room type to count bookings per type
ORDER BY bookings_count DESC                  -- Orders results by booking count in descending order
LIMIT 1;                                      -- Limits results to the top 1 room type with the highest booking count

Explanation:

This query finds the most popular room type based on the number of bookings. By joining the Bookings table with the Rooms table on room ID, it retrieves the room type for each booking. The GROUP BY clause groups bookings by room type, and COUNT(*) calculates the number of bookings for each type. The ORDER BY clause sorts the results by booking count in descending order, while LIMIT 1 restricts the output to the top result, providing the room type with the highest booking frequency. This insight can help identify which room type is in highest demand.

Output:

room_type	bookings_count
Single	1

Query-16: Calculate Total Unpaid Amounts by Booking ID

SELECT b.booking_id,  -- Selects the booking ID
       (b.total_amount - COALESCE(SUM(p.amount_paid), 0)) AS unpaid_amount  -- Calculates the unpaid amount by subtracting the total paid amount from the total amount due
FROM Bookings b  -- Specifies Bookings table as the main table with alias 'b'
LEFT JOIN Payments p ON b.booking_id = p.booking_id  -- Left joins Payments table on booking ID to include all bookings, even those without payments
GROUP BY b.booking_id  -- Groups results by booking ID to aggregate payment amounts
HAVING unpaid_amount > 0;  -- Filters results to only include bookings with an unpaid amount greater than zero

Explanation:

This query calculates the total unpaid amounts for each booking in the hotel system. It starts by selecting the booking_id from the Bookings table and computes the unpaid amount by subtracting the total paid amount (summed from the Payments table) from the total_amount due for each booking. The COALESCE function ensures that if there are no associated payments for a booking, it treats the sum as zero. By grouping the results by booking_id, it aggregates the payment information, and the HAVING clause filters the results to show only those bookings that have an unpaid amount greater than zero.

Output:

Output not generated for insufficient data

Query-17: Identify Top 5 Guests by Spending

SELECT g.guest_id,  -- Selects the guest ID
       g.name,  -- Selects the guest's name
       SUM(b.total_amount) AS total_spent  -- Calculates the total amount spent by each guest
FROM Guests g  -- Specifies Guests table with alias 'g'
JOIN Bookings b ON g.guest_id = b.guest_id  -- Joins Bookings table on guest ID to link each booking to a guest
GROUP BY g.guest_id  -- Groups results by guest ID to aggregate total spending per guest
ORDER BY total_spent DESC  -- Orders guests by total amount spent in descending order
LIMIT 5;  -- Limits the results to the top 5 guests

Explanation:

This query identifies the top 5 guests in terms of total spending on bookings. By joining the Guests table with the Bookings table based on guest_id, the query associates each guest with their booking records. It then calculates the total spending for each guest using SUM(b.total_amount), and groups the results by guest_id to get a total amount spent per guest. The ORDER BY total_spent DESC sorts the results in descending order of total spending, and LIMIT 5 restricts the output to the top 5 guests with the highest spending.

Output:

guest_id	name	total_spent
2	Devon McGraw	750.00
1	Paul Adam	400.00

Query-18: Calculate Room Occupancy for Each Month

SELECT MONTH(b.check_in) AS month,  -- Extracts the month from check-in date
       r.room_type,  -- Selects the room type
       COUNT(b.booking_id) AS occupied_days  -- Counts the number of bookings as occupied days
FROM Bookings b  -- Specifies Bookings table with alias 'b'
JOIN Rooms r ON b.room_id = r.room_id  -- Joins Rooms table on room ID to associate each booking with its room type
GROUP BY month, r.room_type  -- Groups results by month and room type for monthly occupancy per room type
ORDER BY month;  -- Orders the output by month for a sequential view of occupancy over the year

Explanation:

This query calculates the monthly occupancy for each room type, helping track how many days each type of room was booked each month. By joining the Bookings and Rooms tables, it links each booking to its respective room type. The MONTH(b.check_in) function extracts the month from each booking’s check-in date, and COUNT(b.booking_id) counts the number of bookings, representing the days a room type was occupied in that month. Grouping by both month and room_type gives occupancy counts for each room type monthly, and ordering by month presents results in chronological order.

Output:

month	room_type	occupied_days
11	Double	1
11	Single	1

Query-19: Find All Payments with Late Payments Status (Payments Made After Check-Out)

SELECT p.payment_id,  -- Retrieves the unique payment identifier
       b.booking_id,  -- Retrieves the booking ID associated with the payment
       g.name,  -- Retrieves the guest's name associated with the booking
       p.payment_date,  -- Shows the date when the payment was made
       b.check_out  -- Shows the scheduled check-out date for the booking
FROM Payments p  -- Specifies Payments table with alias 'p'
JOIN Bookings b ON p.booking_id = b.booking_id  -- Joins Bookings table on booking ID to link payments with their bookings
JOIN Guests g ON b.guest_id = g.guest_id  -- Joins Guests table to include guest details
WHERE p.payment_date > b.check_out;  -- Filters for payments made after the check-out date, indicating late payment status

Explanation:

This query identifies payments made after the scheduled check-out date, indicating potential late payments. By joining the Payments, Bookings, and Guests tables, the query provides detailed information about each late payment, including the payment ID, booking ID, guest name, payment date, and the original check-out date. The WHERE clause filters records where the payment_date is later than the check_out date.

Output:

Output not generated for insufficient data

SQL Code Editor: