Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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.

image text in transcribed

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Databases questions