Question
a) Alice also wants to see a list containing cottage_id, cottage_name, booking_date and booking_price of all bookings in which the booking_price was greater than the
a) Alice also wants to see a list containing cottage_id, cottage_name, booking_date and booking_price of all bookings in which the booking_price was greater than the average booking_price for that cottage.
b) The loyalty program is being rolled out by Touristas Marketing team, so they want to find out the customers who have booked with them the longest. They need a list containing full name (first and last name together) and the number of days that elapsed between their first and last bookings for customers with at least 2 booking.
c) To determine which facilities are more popular, Alice wants to see a list containing the facility name and the number of times that cottages containing that facility have ever been booked
d) Tourista needs to pay tax on all its bookings made in 2017. The tax rate for booking price under 500 is 10% while it is 20% for booking price of 500 or more. Generate a report containing the id and name of cottage, its booking price(s), the applicable percentage of tax, and the amount of tax. Add a tax amount column and tax total column.
e) Provide her a report that shows the customers full name (first and last name together), the cottage name, the region name of the cottage, the date of booking (in the format 15th of December, 2017), the booking price, and the difference between the booking price of that cottage and the average booking price of all cottages (to the nearest 1 decimal place in the format $yy.y e.g. 25.6), arranged in descending order by full name.
f) Alice also wants to know which months are busier than others and how much money Tourista is making in those months. She wants a report that provides a monthly breakdown of the number of bookings, the total booking price and the average booking price correct to two decimal places.
custome Facility Facility_ID INT(11) Facility-Name VARCHAR(45) Customer ID INT Customer-LName VARCHAR(45) Customer-FName VARCHAR(45) Customer. MInitial VARCHAR(5) Customer Email VARCHAR(45) Customer-Phone VARCHAR(15) Indexes Facility-IsShared TINYINT(1 Indexes Cottage-Facility Cottage Booking Booking_ID INT(1 Booking_Date DATE Booking_Price INT(11) Cottage_ID INT(11) CF_Num INT (11 Facility-ID INT(11) Cottage-ID INT(1 Cottage_Name VARCHAR(45) Cottage-Num Rooms INT(11 ) Region_Code INT(11) Indexes Indexes CustomerID INT Cottage-ID INT(1 Indexes - Region Region_Code INT(11) Region-Name VARCHAR (45) Indexes
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started