You will be working with a set of tables for a Hotel chain. You can access these tables by using

the HOTEL database on the MySQL server (mo.its.rmit.edu.au). You are to prepare 12 SQL
query statements that will provide answers to the following 12 requests for information.

1. List all transactions made on the New Year’s Day in 2021, showing the transaction no,
transaction time, booking_id, service description and guest name. Please show this data in
transaction time order, from oldest to newest.

2. Show how many active bookings have been made for each room in the Alto Hotel on
Bourke. Only need to show the room number (not room_id), floor number, number of beds
and the count of active bookings.

3. Show how many bookings each guest has made or tried to make for less than 7 days. Please
include the guest Name.

4. Create a view called ‘CheckingInToday’ that displays details of ACTIVE bookings that are
checking in today for the Alto Hotel on Bourke. The view should have the following
columns: Booking ID, Guest Name, Room Number, Price. (Note: Use the current_date()
function to get today’s date).
5. List rooms in any of the hotels that have a linked room, displaying all the details of both the
room and also the linked room (hint: self join).
6. List ALL the services provided across all the hotels, showing the total quantity ordered and
the total cost, for each service. Also show the service ID, service description and the name
of the service type. Show this in highest cost to lowest cost order.
7. Show details of the cheapest and most expensive rooms in the hotels, in one query, that have
one bed only and are on level 4 of the hotels. Please show the cheapest room(s) details first.
8. Create a view called BookingCost, with the following columns: Booking_id, Guest_id,
RoomNo, TotalCost. The total cost for the booking needs to be calculated by working out
the number of days the booking was for (using date_in and date_out) and multiplying that
by the price of the room.

9. Create a view called ServiceCost, that displays a summary for each booking, displaying the
amount of money that each booking has spent in relation to room services. This view
should contain EVERY booking, and show the booking_id, guest_id, and total cost of
service used.

10. Provide the guest that has spent the most money at our hotels, in total (includes room cost
and cost of services used). Display the guest’s name as well as the guest_id.

11. There is a big conference in the December 2022, near our hotel Alto Hotel on Bourke, they
will have 80 people coming. Write a query to display details of the rooms that are available
(not booked) in the hotel for the entire month of September 2022. Need to consider
bookings that:
• are checking in on or after the 1st of September and checked out on or September 30th
September;
• are checked in before 1st of September and checkout sometime in September;
• are checked in sometime in September and are checking out after the 31st September.
12. Display any guests in the hotel database that has never made an active booking. Show the
customer’s full name and email address, we plan on offering them a discount.
13. Choose one questions (from questions 1-12 of this assignment) and create a visualisation,
using a tool like Excel, Power BI, Tableau or Orange. Attach the created image ONLY to
your submission. The visualisation will be judged on how well it represents the question
chosen, how clear the various axis and legends are label, and the appropriateness of the title
of the visualisation. Feel free to tweak the output of the query to improve visualisation you
want to generate.
(2 marks)
14. Produce a report of your own design and write a query to solve it. Marks will be awarded
for report design (i.e., How useful is the report), complexity of the query and originality.
Please provide:
a) Business question
b) SQL query
c) Visualisation (using a tool like Excel, Tableau or Orange) - attached image only to
submission
Note: you cannot use the MySQL LIMIT operator because it is NOT standard SQL.

I have to create SQL queries for the below statements:

 

CASE STUDY Sustainable Hotel Infrastructure Tactic (SHIT) This special set of hotels are focused on achieving their corporate goals of attaining 6 star ratings for environmental sustainability. They will examine the zone they have their hotel located and look to build or renovate a hotel that will incorporate various sustainability concepts to reduce their carbon footprint and provide their guests a comfortable experience. -1 PARKE The SHIT look to advice provided by the Sustainable Hospitality Alliance to support their missiong to a create sustainable hotel service: https://sustainablehospitalityalliance.org/resource/environmental-management-for-hotels/ Sustainable Hospitality Alliance The basic function of the hotel is to take bookings for rooms that are available for a given set of days. It is extremely important that rooms are NOT double booked. There are limited number of rooms in each hotel. Currently, the SHIT group have three hotels, but they are looking to expand this under the sustainability umbrella. Guests who use the hotel to stay a night or a number of nights maybe charged for various service provided by the hotel, from the restaurant, rooms service, minibar items, laundry, etc. Each room comes with various existing facilities. This data is used for providing information on the website, answering guest queries and management reporting.