Restaurant Reservation System for Table, Order, and Payment Management

Restaurant Reservation System:

The Restaurant Reservation System project is designed to manage table reservations, customer details, table availability, and meal orders in a streamlined and efficient way. This project will create a database with tables to track reservations, customers, tables, orders, and payments. By building SQL queries to manage table availability, generate reports, and handle reservation details, this system provides a comprehensive solution for restaurant management.

Creating the Database in MySQL or PostgreSQL:

Create the Database:

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

Create the Tables:

Customers Table:

This table stores customer information, including a unique identifier for each customer, their name, contact number, and email address. It helps identify and maintain customer records for reservations and orders.

Structure:

Code:

-- Create Customers table
CREATE TABLE Customers (
    customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,   -- Unique customer ID
    name VARCHAR(50) NOT NULL,                             -- Customer's full name
    contact VARCHAR(15),                                   -- Customer's contact number
    email VARCHAR(50)                                      -- Customer's email
);

Tables Table :

This table records details of the restaurant's tables, including each table's unique ID, assigned table number, seating capacity, and current status (e.g., Available or Reserved). It is essential for managing table availability.

Structure:

Code:

-- Create Tables table
CREATE TABLE Tables (
    table_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,      -- Unique table ID
    table_number INT NOT NULL,                             -- Table number in the restaurant
    seating_capacity INT,                                  -- Number of seats per table
    status ENUM('Available', 'Reserved') DEFAULT 'Available'  -- Table status
);

Reservations Table:

This table tracks reservation details by linking customer and table information. Each reservation entry includes the customer and table IDs, reservation date, and time slot, allowing the restaurant to manage and organize bookings.

Structure:

Code:

-- Create Reservations table
CREATE TABLE Reservations (
    reservation_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique reservation ID
    customer_id INT,                                         -- Foreign key referencing Customers
    table_id INT,                                            -- Foreign key referencing Tables
    reservation_date DATE,                                   -- Date of the reservation
    time_slot TIME,                                          -- Reservation time slot
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (table_id) REFERENCES Tables(table_id)
);

Orders Table:

This table stores details of orders associated with specific reservations. It includes the order ID, reservation ID, list of items ordered, and the total amount of each order, enabling the tracking of items requested by each customer during their visit.

Structure:

Code:

-- Create Orders table
CREATE TABLE Orders (
    order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,        -- Unique order ID
    reservation_id INT,                                      -- Foreign key referencing Reservations
    order_items TEXT,                                        -- List of items ordered
    total_amount DECIMAL(10, 2),                             -- Total amount of the order
    FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id)
);

Payments Table:

This table logs payment transactions related to each order. It includes fields for the payment ID, associated order ID, amount paid, payment date, and payment status, ensuring that the restaurant can monitor payment completion and outstanding balances.

Structure:

Code:

-- Create Payments table
CREATE TABLE Payments (
    payment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,      -- Unique payment ID
    order_id INT,                                            -- Foreign key referencing Orders
    amount_paid DECIMAL(10, 2),                              -- Amount paid for the order
    payment_date DATE,                                       -- Payment date
    payment_status ENUM('Completed', 'Pending') DEFAULT 'Pending', -- Payment status
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

Inserting Data:

Add some sample data to the tables.

Inserting Data into Customers Table:

-- Insert sample customers
INSERT INTO Customers (name, contact, email) VALUES
('Peter Hogg', '1234567890', '[email protected]'),
('Malcom Knott', '0987654321', '[email protected]');

Inserting Data into Tables Table:

-- Insert sample tables
INSERT INTO Tables (table_number, seating_capacity, status) VALUES
(1, 4, 'Available'),
(2, 2, 'Available');

Inserting Data into Reservations Table:

-- Insert sample reservations
INSERT INTO Reservations (customer_id, table_id, reservation_date, time_slot) VALUES
(1, 1, '2024-11-01', '18:00:00'),
(2, 2, '2024-11-01', '19:00:00');

Inserting Data into Orders Table:

-- Insert sample orders
INSERT INTO Orders (reservation_id, order_items, total_amount) VALUES
(1, 'Pizza, Pasta', 20.50),
(2, 'Burger, Fries', 15.00);

Inserting Data into Payments Table:

-- Insert sample payments
INSERT INTO Payments (order_id, amount_paid, payment_date, payment_status) VALUES
(1, 20.50, '2024-11-01', 'Completed'),
(2, 15.00, '2024-11-01', 'Completed');

Basic Functionalities:

• Add New Reservation: Allow administrators to add new reservations.
• Assign Tables: Allocate tables based on availability.
• Manage Orders: Record orders associated with a reservation.
• Process Payments: Track payment statuses for completed orders.
• Generate Reports: View table availability, daily reservations, customer data, and payment records.

Writing Queries for Functionality:

Query-1: Display All Reservations

SELECT * 
       -- Selects all columns from the Reservations table.

FROM Reservations;
       -- Specifies the Reservations table as the data source.

Explanation:

This query retrieves all records from the Reservations table, displaying each reservation’s full details, including customer information, reservation date, time slot, and table assignment. It is useful for reviewing all reservations in the system without any filtering criteria.

Output:

reservation_id	customer_id	table_id	reservation_date	time_slot
1	1	1	2024-11-01	18:00:00
2	2	2	2024-11-01	19:00:00

Query-2: Check Available Tables

SELECT * 
       -- Selects all columns from the Tables table.

FROM Tables
       -- Specifies the Tables table as the data source.

WHERE status = 'Available';
       -- Filters results to only include rows where the table's status is set to 'Available.'

Explanation:

This query retrieves all available tables from the Tables table by selecting only those with a status marked as 'Available.' It provides details for each available table, such as table number, seating capacity, and any other attributes in the table, which is useful for checking seating availability in real time.

Output:

table_id	table_number	seating_capacity	status
1	1	4	Available
2	2	2	Available

Query-3: Reservations by Customer

SELECT Reservations.*
       -- Selects all columns from the Reservations table for display.

FROM Reservations
       -- Specifies Reservations as the main table.

JOIN Customers ON Reservations.customer_id = Customers.customer_id
       -- Joins the Reservations table with the Customers table based on matching customer IDs.

WHERE Customers.name = 'Peter Hogg';
       -- Filters the results to show only reservations for the customer named 'Peter Hogg.'

Explanation:

This query retrieves all reservation details specifically for the customer named 'Peter Hogg.' By joining the Reservations table with the Customers table on the customer ID, it links each reservation to the corresponding customer. This is useful for viewing the reservation history of an individual customer.

Output:

reservation_id	customer_id	table_id	reservation_date	time_slot
1	1	1	2024-11-01	18:00:00

Query-4: Daily Reservation Summary

SELECT reservation_date,
       -- Selects the reservation date to group and display reservations by day.

       COUNT(*) AS total_reservations
       -- Counts the number of reservations for each day and labels it as total_reservations.

FROM Reservations
       -- Specifies the Reservations table as the data source.

GROUP BY reservation_date;
       -- Groups the results by each unique reservation date to aggregate the reservation count for each day.

Explanation:

This query calculates the total number of reservations made for each day. By grouping the results by reservation_date, it provides a daily summary, showing the count of reservations on each date. This is useful for analyzing reservation patterns over time.

Output:

reservation_date	total_reservations
2024-11-01	2

Query-5: Total Revenue from Orders

	SELECT SUM(total_amount) AS total_revenue
       -- Sums up the 'total_amount' from all records in the Orders table, representing total revenue, and labels it as 'total_revenue'.

FROM Orders;
       -- Specifies the Orders table as the source of the data.

Explanation:

This query calculates the total revenue generated from all orders by summing the total_amount column in the Orders table. It provides a single, overall revenue figure, which is helpful for understanding the restaurant’s total earnings from orders over time.

Output:

total_revenue
35.50	

Query-6: Outstanding Payments

SELECT * 
       -- Selects all columns from the Payments table.

FROM Payments 
       -- Specifies the Payments table as the source of data.

WHERE payment_status = 'Pending';
       -- Filters the results to include only records where the payment status is 'Pending'.

Explanation:

This query retrieves all payment records from the Payments table where the status is marked as 'Pending.' It shows all details for these records, allowing the user to quickly identify any outstanding payments that need to be processed or followed up with customers.

Output:

Output not generated for insufficient data

Query-7: Reservations for a Specific Date

SELECT * 
       -- Selects all columns from the Reservations table.

FROM Reservations
       -- Specifies the Reservations table as the source of data.

WHERE reservation_date = '2024-11-01';
       -- Filters results to include only reservations made on '2024-11-01'.

Explanation:

This query retrieves all reservation records for the specified date, "2024-11-01." It includes all columns in the Reservations table, making it easy to view details such as customer information, time slots, and table assignments for that day. This query is useful for quickly checking bookings and managing reservations on a specific date.

Output:

reservation_id	customer_id	table_id	reservation_date	time_slot
1	1	1	2024-11-01	18:00:00
2	2	2	2024-11-01	19:00:00

Query-8: Customer Order History

SELECT Customers.name, 
       -- Selects the name of the customer from the Customers table.

       Orders.order_items, 
       -- Selects the items ordered by the customer from the Orders table.

       Orders.total_amount 
       -- Selects the total amount for each order made by the customer from the Orders table.

FROM Orders
       -- Specifies the Orders table as the primary table.

JOIN Reservations ON Orders.reservation_id = Reservations.reservation_id 
       -- Joins the Reservations table to link each order with its respective reservation.

JOIN Customers ON Reservations.customer_id = Customers.customer_id 
       -- Joins the Customers table to retrieve the customer details associated with each reservation.

WHERE Customers.name = 'Malcom Knott';
       -- Filters the results to show only the orders made by the customer named 'Malcom Knott'.

Explanation:

This query retrieves the order history for the customer named "Malcom Knott" by displaying each order's items and total amount. It achieves this by joining the Orders table with the Reservations and Customers tables based on matching reservation and customer IDs. This is useful for viewing specific customer orders, helping analyze individual spending patterns and preferences.

Output:

name	order_items	total_amount
Malcom Knott	Burger, Fries	15.00

Query-9: Table Occupancy Status

SELECT table_number, 
       -- Selects the table_number column from the Tables table to identify each table.

       status 
       -- Selects the status column from the Tables table to show whether a table is available or reserved.

FROM Tables;
       -- Specifies the Tables table, which contains information about the tables in the restaurant.

Explanation:

This query retrieves the table numbers along with their occupancy status from the Tables table. It helps to quickly check which tables are available or reserved, aiding in table management and planning within the restaurant.

Output:

table_number	status
1	Available
2	Available

Query-10: Reservations by Time Slot

SELECT time_slot, 
       -- Selects the time_slot column from the Reservations table to group the data by reservation time.

       COUNT(*) AS reservation_count 
       -- Counts the number of reservations made for each time slot.

FROM Reservations 
       -- Specifies the Reservations table, which holds the reservation details.

GROUP BY time_slot;
       -- Groups the results by the time_slot to count the number of reservations for each unique time.

Explanation:

This query retrieves the number of reservations made for each time slot from the Reservations table. It groups the reservations by time slot and counts how many reservations exist for each one. This helps to understand which time slots are more popular or in-demand.

Output:

time_slot	reservation_count
18:00:00	1
19:00:00	1

Query-11: Payment Summary

SELECT payment_status, 
       -- Selects the payment status, which could be values like 'Pending', 'Completed', etc.

       COUNT(*) AS payment_count 
       -- Counts the number of payments for each status, providing the total number of payments in each category.

FROM Payments 
       -- Specifies the Payments table where payment information is stored.

GROUP BY payment_status;
       -- Groups the results by payment_status to count the payments for each specific status.

Explanation:

This query summarizes the different payment statuses by counting how many payments fall under each status (such as 'Pending', 'Completed', etc.) in the Payments table. It helps businesses track the number of payments in each category, which can be useful for analyzing payment processing and outstanding transactions.

Output:

payment_status	payment_count
Completed	2

Query-12: Daily Revenue Report

SELECT payment_date, 
       -- Selects the date on which the payment was made.

       SUM(amount_paid) AS daily_revenue 
       -- Calculates the total revenue (sum of all payments) for each day.

FROM Payments 
       -- Specifies the Payments table, which contains the payment information.

GROUP BY payment_date;
       -- Groups the results by payment_date to aggregate the total revenue for each individual date.

Explanation:

This query calculates the total revenue collected each day by summing up the payment amounts from the Payments table. It groups the data by payment date, which allows businesses to see daily revenue totals, essential for tracking financial performance on a day-to-day basis.

Output:

payment_date	daily_revenue
2024-11-01	35.50	

Query-13: Order Details for Reservation

SELECT order_items, 
       -- Selects the items that were ordered as part of the order.

       total_amount 
       -- Selects the total amount for the order to show the full price of all items ordered.

FROM Orders 
       -- Specifies the Orders table as the source of the data.

WHERE reservation_id = 1;
       -- Filters the results to show only the orders related to the reservation with ID 1.

Explanation:

This query retrieves the ordered items and the total amount for a specific reservation, in this case, reservation ID 1. It helps in reviewing what items were ordered and how much was paid for that specific reservation. This type of query is useful for tracking orders linked to particular reservations, allowing restaurants or businesses to track individual reservation expenditures and item choices.

Output:

order_items	total_amount
Pizza, Pasta	20.50	

Query-14: Top Customers by Spending

SELECT Customers.name, 
       -- Selects the customer's name to display each customer's spending.

       SUM(Payments.amount_paid) AS total_spent 
       -- Calculates the total amount paid by each customer and labels it as total_spent.

FROM Payments 
       -- Specifies the Payments table as the initial source.

JOIN Orders ON Payments.order_id = Orders.order_id 
       -- Joins the Orders table based on matching order IDs to link payments with their respective orders.

JOIN Reservations ON Orders.reservation_id = Reservations.reservation_id 
       -- Joins the Reservations table to connect each order to its reservation.

JOIN Customers ON Reservations.customer_id = Customers.customer_id 
       -- Joins the Customers table to link reservations with customers based on customer IDs.

GROUP BY Customers.name 
       -- Groups the results by customer name to calculate each customer's total spending.

ORDER BY total_spent DESC;
       -- Orders the results in descending order of total spending to show top spenders first.

Explanation:

This query calculates the total spending for each customer by summing up all payments linked to their orders and reservations. By grouping the results by customer name, the query identifies each customer’s total expenditure. The results are ordered in descending order by total_spent, which highlights the top-spending customers. This insight is beneficial for identifying high-value customers, which can assist in targeted marketing or special service offerings.

Output:

name	total_spent
Peter Hogg	20.50
Malcom Knott	15.00	

Query-15: Available Tables by Seating Capacity

SELECT * 
       -- Selects all columns from the Tables table.

FROM Tables 
       -- Specifies the Tables table as the source.

WHERE status = 'Available' 
       -- Filters the results to include only tables that are marked as "Available."

AND seating_capacity >= 4;
       -- Adds an additional filter to include only tables with a seating capacity of 4 or more.

Explanation:

This query retrieves all available tables from the Tables table that can accommodate at least four people. It filters by checking that the status is set to "Available" and the seating_capacity is greater than or equal to 4. This information is useful when a reservation requires a larger seating arrangement, ensuring the tables meet the necessary seating capacity.

Output:

table_id	table_number	seating_capacity	status
1	1	4	Available

Query-16: Average Spending per Customer

SELECT Customers.name, 
       -- Selects each customer's name.

       AVG(Payments.amount_paid) AS avg_spent
       -- Calculates the average amount spent by each customer.

FROM Payments
JOIN Orders ON Payments.order_id = Orders.order_id
       -- Joins Payments and Orders tables on order_id to match payment records with specific orders.

JOIN Reservations ON Orders.reservation_id = Reservations.reservation_id
       -- Joins Orders and Reservations tables on reservation_id to link orders to specific reservations.

JOIN Customers ON Reservations.customer_id = Customers.customer_id
       -- Joins Reservations and Customers tables on customer_id to associate reservations with specific customers.

GROUP BY Customers.name
       -- Groups the results by each customer's name to calculate the average spending per customer.

ORDER BY avg_spent DESC;
       -- Orders the results in descending order based on the average amount spent, showing highest spenders first.

Explanation:

This query calculates the average spending for each customer by joining the Payments, Orders, Reservations, and Customers tables. It groups data by customer name and computes the average amount spent by each customer. Finally, it orders the results by average spending in descending order to display the top spenders at the top. This helps identify high-value customers based on their average spending.

Output:

name	avg_spent
Peter Hogg	20.500000
Malcom Knott	15.000000

Query-17: Revenue by Time Slot

SELECT Reservations.time_slot, 
       -- Selects the time slot for each reservation.

       SUM(Orders.total_amount) AS revenue
       -- Calculates the total revenue for each time slot by summing up the 'total_amount' from the Orders table.

FROM Orders
JOIN Reservations ON Orders.reservation_id = Reservations.reservation_id
       -- Joins the Orders table with the Reservations table based on the reservation_id to access time slot information for each order.

GROUP BY Reservations.time_slot
       -- Groups the results by each time slot to calculate total revenue for each one.

ORDER BY revenue DESC;
       -- Orders the results in descending order based on revenue, displaying the highest revenue time slots first.

Explanation:

This query retrieves the total revenue generated in each time slot by grouping reservations by their time slots and summing the total amounts from orders. After calculating the total revenue per time slot, it orders the results in descending order, so the time slots with the highest revenue appear at the top. This can help in identifying the most profitable times for reservations.

Output:

time_slot	revenue
18:00:00	20.50
19:00:00	15.00

Query-18: Frequent Reservation Times by Customer

SELECT Customers.name, 
       -- Selects each customer's name.

       Reservations.time_slot, 
       -- Selects the reservation time slot for each customer.

       COUNT(*) AS reservation_count
       -- Counts the number of reservations for each customer at each time slot, labeled as 'reservation_count'.

FROM Reservations
JOIN Customers ON Reservations.customer_id = Customers.customer_id
       -- Joins the Reservations table with the Customers table to access customer details associated with each reservation.

GROUP BY Customers.name, Reservations.time_slot
       -- Groups the data by customer name and time slot to calculate reservation counts for each combination.

HAVING reservation_count > 2
       -- Filters results to only include customer and time slot combinations where the reservation count is more than 2.

ORDER BY reservation_count DESC;
       -- Orders the results in descending order based on the reservation count, showing the most frequent reservations first.

Explanation:

This query lists customers who have made more than two reservations for specific time slots. By grouping data by customer name and time slot, the query calculates how many times each customer reserved each slot. Filtering with HAVING reservation_count > 2 ensures only frequent reservations are shown, and ordering in descending order highlights the most frequent reservation patterns first.

Output:

Output not generated for insufficient data

Query-19: Top 5 Most Popular Reservation Dates

SELECT reservation_date, 
       -- Selects the reservation date to view when reservations were made.

       COUNT(*) AS reservation_count
       -- Counts the number of reservations for each date and labels it as 'reservation_count'.

FROM Reservations
GROUP BY reservation_date
       -- Groups the results by each reservation date to aggregate reservation counts per date.

ORDER BY reservation_count DESC
       -- Orders the results in descending order based on the number of reservations per date, with the highest count first.

LIMIT 5;
       -- Limits the output to the top 5 dates with the most reservations.

Explanation:

This query retrieves the top five dates with the highest number of reservations from the Reservations table. By grouping reservations by date and counting each group, it calculates the daily reservation totals. Ordering by reservation_count in descending order ensures that the dates with the most reservations appear first, providing insights into peak reservation days.

Output:

reservation_date	reservation_count
2024-11-01	2

Query-20: Monthly Revenue Summary

SELECT DATE_FORMAT(payment_date, '%Y-%m') AS month, 
       -- Formats the payment_date to display only the year and month (e.g., '2024-11'), assigning it an alias 'month'.

       SUM(amount_paid) AS total_revenue
       -- Calculates the total revenue by summing all payments for each month.

FROM Payments
GROUP BY month
       -- Groups the results by each unique month, enabling aggregation of revenue by month.

ORDER BY month;
       -- Orders the results chronologically by month.

Explanation:

This query generates a monthly revenue report by formatting payment_date to the year-month level and summing the amount_paid for each month. The GROUP BY clause groups payments by month, and the ORDER BY clause arranges the results chronologically, allowing for an organized view of monthly revenue trends. This query is helpful for analyzing payment and revenue patterns on a monthly basis.

Output:

month	total_revenue
2024-11	35.50

Query-21: Customer's Most Ordered Items

SELECT Customers.name, 
       Orders.order_items, 
       COUNT(*) AS times_ordered
       -- Counts the number of times each item has been ordered by the specified customer.

FROM Orders
JOIN Reservations ON Orders.reservation_id = Reservations.reservation_id
       -- Joins Orders and Reservations tables on reservation_id to access reservation details linked to each order.
       
JOIN Customers ON Reservations.customer_id = Customers.customer_id
       -- Joins Reservations and Customers tables on customer_id to associate each reservation with a specific customer.
       
WHERE Customers.name = 'Peter Hogg'
       -- Filters the results to show data only for the customer named 'Peter Hogg'.
       
GROUP BY Orders.order_items
       -- Groups results by each unique item ordered, to get the count of each item for the customer.
       
ORDER BY times_ordered DESC;
       -- Orders the results in descending order of the times each item was ordered, with the most frequently ordered items first.

Explanation:

This query retrieves a list of items ordered by the customer “Peter Hogg” along with the frequency of each item ordered. By joining the Orders, Reservations, and Customers tables, the query associates orders with the customer’s reservations. Grouping by order_items enables counting each item ordered, and the result is ordered by times_ordered in descending order to show the most frequently ordered items at the top. This information is useful for understanding a customer's ordering preferences.

Output:

name	order_items	times_ordered
Peter Hogg	Pizza, Pasta	1

Query-22: Total Revenue by Table

SELECT Tables.table_number, 
       SUM(Orders.total_amount) AS total_revenue
       -- Calculates the total revenue generated by each table by summing up the total_amount 
       -- from the Orders table, grouping the results by table.

FROM Orders
JOIN Reservations ON Orders.reservation_id = Reservations.reservation_id
       -- Joins Orders and Reservations tables based on reservation_id, allowing retrieval 
       -- of reservation details associated with each order.
       
JOIN Tables ON Reservations.table_id = Tables.table_id
       -- Joins Reservations and Tables tables based on table_id to link each reservation to its specific table.
       
GROUP BY Tables.table_number
       -- Groups results by each table's table_number to calculate revenue per table.
       
ORDER BY total_revenue DESC;
       -- Orders the results in descending order based on total revenue, showing the highest revenue-generating tables first.

Explanation:

This query calculates and ranks each table in the restaurant based on the revenue generated from orders associated with that table. It joins the Orders, Reservations, and Tables tables to link orders to specific tables through reservations. The SUM function totals the revenue (total_amount) per table, grouping by table_number and then ordering results in descending order of revenue. This query is useful for identifying the tables that generate the most revenue, providing insights into table performance.

Output:

table_number	total_revenue
1	20.50
2	15.00

Query-23: Percentage of Reserved vs. Available Tables

SELECT 
    (SELECT COUNT(*) FROM Tables WHERE status = 'Reserved') * 100.0 / COUNT(*) AS reserved_percentage,
    -- Calculates the percentage of tables that are marked as 'Reserved' by dividing the count of reserved tables 
    -- by the total count of tables and multiplying by 100 for a percentage result.

    (SELECT COUNT(*) FROM Tables WHERE status = 'Available') * 100.0 / COUNT(*) AS available_percentage
    -- Calculates the percentage of tables that are marked as 'Available' by dividing the count of available tables 
    -- by the total count of tables and multiplying by 100 for a percentage result.

FROM Tables;
-- Specifies the Tables table as the source of data, providing the total count for percentage calculations.

Explanation:

This query calculates the percentage of tables in two different statuses—‘Reserved’ and ‘Available’—from the Tables table. It uses subqueries to count the number of tables in each status and divides each count by the total number of tables, retrieved using COUNT(*). Multiplying by 100 converts the results into percentages, displaying the proportion of tables that are reserved and available. This query is helpful for gaining a quick overview of table occupancy in the restaurant.

Output:

reserved_percentage	available_percentage
0.00000	100.00000

Query-24: Reservations by Date and Time Slot for a Specific Customer

SELECT reservation_date, time_slot
-- Selects the reservation date and time slot for display.

FROM Reservations
-- Specifies the Reservations table as the primary source of data.

JOIN Customers ON Reservations.customer_id = Customers.customer_id
-- Joins the Reservations table with the Customers table on the customer_id to link each reservation to its customer.

WHERE Customers.name = 'Malcom Knott'
-- Filters the results to only include reservations made by the customer named 'Malcom Knott'.

ORDER BY reservation_date, time_slot;
-- Orders the results by reservation date and time slot for chronological display.

Explanation:

This query retrieves all reservations made by a customer named "Jane Smith" from the Reservations table. Using a JOIN between the Reservations and Customers tables on the customer_id column, the query links each reservation to its corresponding customer. The WHERE clause filters for only those entries associated with "Jane Smith." Finally, the results are sorted by reservation_date and time_slot to provide a chronological view of her reservations. This query is useful for viewing a customer’s reservation history in an organized timeline.

Output:

reservation_date	time_slot
2024-11-01	19:00:00

Query-25: Revenue Change from Previous Day

SELECT payment_date, 
       -- Selects the date of each payment.

       SUM(amount_paid) AS total_revenue,
       -- Calculates the total revenue collected for each payment date.

       LAG(SUM(amount_paid), 1) OVER (ORDER BY payment_date) AS previous_day_revenue,
       -- Uses the LAG window function to retrieve the revenue from the previous day, ordered by payment date.

       SUM(amount_paid) - LAG(SUM(amount_paid), 1) OVER (ORDER BY payment_date) AS revenue_difference
       -- Computes the difference in revenue by subtracting the previous day's revenue from the current day's total.

FROM Payments
-- Specifies the Payments table as the source of data.

GROUP BY payment_date;
-- Groups the results by each payment date to ensure revenue is aggregated per day.

Explanation:

This query calculates daily revenue totals and compares them to the previous day's revenue to highlight revenue changes over time. It groups entries by payment_date and sums amount_paid to find the total revenue for each day. The LAG function, with an ORDER BY on payment_date, fetches the revenue total from the prior day for comparison, allowing us to compute the revenue_difference. This query provides insight into day-over-day revenue fluctuations by showing both the previous day's revenue and the net change from one day to the next.

Output:

payment_date	total_revenue	previous_day_revenue	revenue_difference
2024-11-01	35.50	NULL	NULL

Query-26: Customer Loyalty by Total Reservations

SELECT Customers.name, COUNT(Reservations.reservation_id) AS total_reservations
-- Selects customer names and counts the total number of reservations made by each customer.

FROM Customers
-- Specifies the Customers table as the main source of data.

JOIN Reservations ON Customers.customer_id = Reservations.customer_id
-- Performs an inner join to link each customer with their reservations, matching on customer_id.

GROUP BY Customers.name
-- Groups results by each customer's name to calculate the reservation count for each individual.

ORDER BY total_reservations DESC;
-- Orders the results by the reservation count in descending order, showing customers with the most reservations first.

Explanation:

This query identifies customers and the number of reservations they have made, ordered by the highest number of reservations. It starts by selecting each customer’s name and counting their reservations using the COUNT function. The JOIN clause connects the Customers and Reservations tables on customer_id, linking each customer to their reservations. Grouping by Customers.name allows the query to aggregate reservation counts per customer. Finally, the results are sorted in descending order by total_reservations, showing customers with the highest number of reservations at the top.

Output:

name	total_reservations
Peter Hogg	1
Malcom Knott	1

Query-27: Orders with High Spending per Reservation

SELECT Orders.reservation_id, Orders.total_amount
-- Selects reservation IDs and their corresponding total amounts from the Orders table.

FROM Orders
-- Specifies that data is retrieved from the Orders table.

WHERE Orders.total_amount > (
    -- Filters results to only include orders where the total amount is greater than the average order amount.

    SELECT AVG(total_amount) FROM Orders
    -- Subquery calculates the average total amount of all orders.
)

ORDER BY Orders.total_amount DESC;
-- Sorts the results by total amount in descending order, so the highest total amounts are displayed first.

Explanation:

This query retrieves orders with total amounts higher than the average for all orders. It begins by selecting the reservation_id and total_amount from the Orders table. The WHERE clause filters out orders whose total_amount is below the average, calculated using a subquery that finds the average total_amount from all records in the Orders table. The results are then ordered in descending order of total_amount, placing the largest amounts at the top.

Output:

reservation_id	total_amount
1	20.50

Query-28: Peak Times for Reservations

SELECT time_slot, COUNT(*) AS reservation_count
FROM Reservations
GROUP BY time_slot
-- Group reservations by each time slot and count the number of reservations for each time slot.

HAVING reservation_count > (
    -- Filter results to only show time slots where the reservation count exceeds the average.

    SELECT AVG(reservation_count) FROM (
        -- Calculate the average reservation count across all time slots.

        SELECT time_slot, COUNT(*) AS reservation_count
        FROM Reservations
        GROUP BY time_slot
        -- Inner query groups by time slot and counts reservations for each, providing reservation counts to calculate the average.
        
    ) AS subquery
)
-- Compare each time slot's reservation count with the average count, keeping only those that are above average.

ORDER BY reservation_count DESC;
-- Sort results in descending order of reservation count, showing the most booked time slots first.

Explanation:

This query identifies the time slots with above-average reservation counts. First, it groups the Reservations by each time_slot and counts the reservations per slot. The HAVING clause filters out any time slots with reservation counts below the overall average, calculated using a subquery that groups and counts reservations by time slot. Finally, the results are ordered by reservation count in descending order, so the most popular time slots appear at the top.

Output:

Output not generated for insufficient data

Query-29: Customers with Pending Payments

SELECT DISTINCT Customers.name
-- Selects unique customer names

FROM Customers
JOIN Reservations ON Customers.customer_id = Reservations.customer_id
-- Joins Customers and Reservations tables using customer_id

JOIN Orders ON Reservations.reservation_id = Orders.reservation_id
-- Joins Reservations and Orders tables using reservation_id

JOIN Payments ON Orders.order_id = Payments.order_id
-- Joins Orders and Payments tables using order_id

WHERE Payments.payment_status = 'Pending';
-- Filters to include only records with pending payments

Explanation:

This query retrieves the names of customers with outstanding payments. By joining the Customers, Reservations, Orders, and Payments tables, it links each customer to their reservations, associated orders, and payment details. The WHERE clause filters for payments marked as 'Pending,' and DISTINCT ensures each customer name appears only once, providing a list of unique customers who have pending balances. This query helps identify customers with unpaid orders.

Output:

Output not generated for insufficient data

Query-30: Revenue Comparison between Weekends and Weekdays

SELECT 
    -- Check if the payment date falls on a weekend (Sunday or Saturday) or a weekday
    CASE WHEN DAYOFWEEK(payment_date) IN (1, 7) THEN 'Weekend' ELSE 'Weekday' END AS day_type,
    
    -- Calculate the sum of all payments for each day type
    SUM(amount_paid) AS total_revenue
FROM 
    Payments
GROUP BY 
    -- Group the results by the day type (Weekend or Weekday) to aggregate revenue accordingly
    day_type;

Explanation:

This query categorizes each payment_date as either 'Weekend' or 'Weekday' by using the DAYOFWEEK function to identify weekends (Sunday and Saturday, represented by 1 and 7). It then calculates total_revenue for each day_type by summing up amount_paid in the Payments table. Grouping by day_type allows for a breakdown of revenue into weekend and weekday totals, providing insights into revenue patterns across different days.

Output:

day_type	total_revenue
Weekday	35.50

SQL Code Editor: