-- Question i: Display the customers who have not made any reservation
since the beginning of 2023.
SELECT
[Link],
[Link],
[Link],
[Link]
FROM
Customer c
LEFT JOIN
Booking b ON [Link] = [Link] AND [Link] >=
'2023-01-01'
WHERE
[Link] IS NULL;
-- Question ii: Display the monthly revenue for the year 2023 and sort the
results in descending order by monthly revenue.
SELECT
DATE_FORMAT([Link], '%Y-%m') AS Month,
SUM([Link]) AS MonthlyRevenue
FROM
Invoice i
WHERE
YEAR([Link]) = 2023
GROUP BY
Month
ORDER BY
MonthlyRevenue DESC;
-- Question iii: Display all customers who have made more than 3
reservations in the year of 2023.
SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS NumberOfReservations
FROM
Customer c
JOIN
Booking b ON [Link] = [Link]
WHERE
YEAR([Link]) = 2023
GROUP BY
[Link], [Link], [Link], [Link]
HAVING
COUNT([Link]) > 3;
-- Question iv: Display the average rating and total number of reviews for
each room type at the Kuala Lumpur branch.
SELECT
[Link],
AVG([Link]) AS AverageRating,
COUNT([Link]) AS TotalReviews
FROM
Rating rat
JOIN
Booking b ON [Link] = [Link]
JOIN
Branch br ON [Link] = [Link]
JOIN
BookingRoom br_room ON [Link] = br_room.BookingID
JOIN
Room r ON br_room.RoomID = [Link]
JOIN
RoomType rt ON [Link] = [Link]
WHERE
[Link] = 'Kuala Lumpur'
GROUP BY
[Link];
-- Question v: Display the total revenue generated by each branch for the
year 2023.
SELECT
[Link],
SUM([Link]) AS TotalRevenue
FROM
Invoice i
JOIN
Booking b ON [Link] = [Link]
JOIN
Branch br ON [Link] = [Link]
WHERE
YEAR([Link]) = 2023
GROUP BY
[Link]
ORDER BY
TotalRevenue DESC;
-- Question vi: Display the names of customers and the total amount they
spent in the year of 2023.
SELECT
[Link],
[Link],
SUM([Link]) AS TotalSpent
FROM
Customer c
JOIN
Booking b ON [Link] = [Link]
JOIN
Invoice i ON [Link] = [Link]
WHERE
YEAR([Link]) = 2023
GROUP BY
[Link], [Link], [Link]
ORDER BY
TotalSpent DESC;
-- Question vii: Display the average and total spending by gender for the
year 2023.
SELECT
[Link],
SUM([Link]) AS TotalSpending,
AVG([Link]) AS AverageSpending
FROM
Invoice i
JOIN
Booking b ON [Link] = [Link]
JOIN
Customer c ON [Link] = [Link]
WHERE
YEAR([Link]) = 2023
GROUP BY
[Link];
-- Question viii: Display the branch id, address, manager’s name, and the
total number of rooms for each branch.
SELECT
[Link],
[Link],
([Link] || ' ' || [Link]) AS ManagerName, -- Using ||
for standard SQL concatenation
COUNT([Link]) AS TotalNumberOfRooms
FROM
Branch br
JOIN
Manager m ON [Link] = [Link]
JOIN
Room r ON [Link] = [Link]
GROUP BY
[Link], [Link], ManagerName;
-- Question ix: Display the customer with the highest number of reservations
and show their average amount spent for the year 2023.
SELECT
[Link],
[Link],
COUNT([Link]) AS NumberOfReservations,
AVG([Link]) AS AverageAmountSpent
FROM
Customer c
JOIN
Booking b ON [Link] = [Link]
JOIN
Invoice i ON [Link] = [Link]
WHERE
YEAR([Link]) = 2023
GROUP BY
[Link], [Link], [Link]
ORDER BY
NumberOfReservations DESC
LIMIT 1;
-- Question x: Display the customers who have stayed at more than one
branch in 2023.
SELECT
[Link],
[Link],
[Link]
FROM
Customer c
JOIN
Booking b ON [Link] = [Link]
WHERE
YEAR([Link]) = 2023
GROUP BY
[Link], [Link], [Link], [Link]
HAVING
COUNT(DISTINCT [Link]) > 1;