Question

The owner of the B&B would like to know how much each room contributes to total revenues. In this regard, she would like a list of rooms (room ID and room name), and how much revenue each room brought in for 2019 (even including rooms that did not bring in any revenue; i.e., they weren't booked in 2019). Room revenue (for a given booking) are found in the total field in Bookings. You only need to consider bookings where check-in is in 2019 (and hence don't filter for check-out dates that might occur in 2020). You do not need to consider any booking discounts that were given (i.e., the discount field in Bookings). She would like this list sorted from most revenue to least revenue.

In a single SQL statement, write the query that will provide the owner with this information.

Consider the following physical entity-relationship model for a small bed and breakfast (B&B) that has multiple locations in a popular Canadian city: Bed_Types PK bed_id type INT It VARCHAR(30) Room_Beds PK, FK room_id INT PK, FK bed_id INT Rooms Bookings PK book_id CHAR(10) FK cust_id CHAR(8) FK room_id INT check_in DATE check_out DATE requests TEXT discount DECIMAL(2,2) total DECIMAL(8,2) HPK room_id INT FH FK loc_id INT room_num CHAR(3) room_name VARCHAR(30) capacity TINYINT rack_rate DECIMAL(6,2) Locations Customers PK loc_id INT H HPK cust_id name VARCHAR(30) name address TEXT CHAR(8) VARCHAR(30) VARCHAR(30) CHAR(15) email phone manager CHAR(10) VARCHAR(30) phone

Answer 1

Select Rooms.room_id, room_name, sum(total) as revenue from Rooms left join Bookings on Rooms.room_id = Bookings.room_id group by Rooms.room_id, room_name having year(check_in) = 2019 order by revenue desc;

Do ask if any doubt. Please up-vote.