本次数据库代写是根据现有的数据库数据，完成sql查询

COMP1350 2020-Assignment 2 Part 1

Task Descriptions
Section-One
This section has 13 questions. Each of the questions are worth 5 marks.
Task 1 (5 marks):
Create these tables based on the schema provided: VillaType, Villa, Villa_Reservation,
Reservation, Payment and Customer.
Insert at least 5 records into each of the tables. No extra marks will be provided for adding
more records in, but more records may be needed depending on the query results for
different questions.
Task 2 (5 marks):
Write a query to print all the details (ID, Name, Cost) of the villa. Note that the cost must
be prefixed with a ‘$’ sign. Sort the records in order of price with the most expensive villa
at the top of the list.
Task 3 (5 marks):
Write a query to print the reservation details (VillaID, check in and out dates, along with
the number of days) that each of the villas in the reservation are reserved for.
Task 4 (5 marks):
Write a query to print the all details (Name, Cost) of the villa if they are two-bedroom
villas. You will need to have the phrase ‘two-bedroom’ in the description of villa type.
Task 5 (5 marks):
Write a query to print the ReservationID if the reservation was paid for within 5 days
from the date of reservation. Please ensure no duplicate results are included.
Task 6 (5 marks):
Using a subquery, print Customer names and phone numbers, if they have made a
reservation (reservation date) within the last 6 months calculated from today
(Today here implies the date the query is run. Must not hardcode the date)
Task 7 (5 marks):
Rewrite Task 6 using a Join.
COMP1350 2020-Assignment 2 Part 1
8
Task 8 (5 marks):
Write a query to print all the names of customers who have booked villas that costs less
than $1000 per day. Please ensure no duplicate results are included in the result.
Task 9 (5 marks):
Write a query to print the total amount of payments that have been made for each
reservation. Sort the records in order of the total payments made with the most paid
reservations at the top of the list. (UPDATED)
Task 10 (5 marks):
Write a query to print the reservation details (ID, Date) along with the number of villas
that have been booked for each reservation, but only show the reservation details if the
number of villas reserved are more than one.
Task 11 (5 marks):
Write a query to print the details of all villas which have never been booked.
Task 12 (5 marks):
Write a query to print the details of any payment that is more $1500. Only include the
payments that have been made in either January of any year or in any months in the year
of 2020 or the year of 2018. Sort the results by payment amount in descending order.
Task 13 (5 marks):
Write a query to print the details of any payment that has been made on a reservation of
a one-bedroom villa by a customer whose surname begins with J.
Section-Two
This section has 2 questions. Each of the questions are worth 5 marks. You may be eligible
for partial marks if there are errors in your answers. To be able to answer the questions,
you will have to create and populate the following tables based on the schema provided:
Activity, Staff, ActivityBooking
Task 14 (5 marks):
Write a query to print the ReservationID and the total amount that it has costed (Cost of
villa per night * number of days it has been reserved for). Only include reservations that
exceed a total amount of $10,000.
Task 15 (5 marks):
Write a query to print the names of the customers who have made bookings of outdoor
activities those of which have a cost that is strictly less than the average cost of outdoor
activities. The average should include both outdoor and package activities.
COMP1350 2020-Assignment 2 Part 1
9
Section-Three
This section has 2 questions. Each of the questions are worth 5 marks. You may be eligible
for partial marks if there are errors in your answers. To be able to answer the questions,
you will have to create and populate the ‘SupportStaff’ table based on the schema
provided.
Task 16 (5 marks):
Write a query to print the names of the customers and all the activities they have booked
in the afternoon (after mid-day and before 4pm) along with the names of the guides. Only
include guides who are Managers.
Task 17 (5 marks):
Write a query to print the names of Staff and their managers, only if the managers manage
2 staff or more
Section-Four
This section has 2 questions. Each of the questions are worth 5 marks. You may be eligible
for partial marks if there are errors in your answers. To be able to answer the questions,
you will have to create the ‘Package’ table based on the schema provided
Task 18 (5 marks):
Write a query to print the details of activity booking (ActivityID, ReservationID, Time of
the Activity Reservation, Name of the Activity) and the names of staff involved in the
activities. This should involve the guide and all the support staff involved
Task 19 (5 marks):
List the details of package activities (id, name and cost) along with the details (id, name
and cost) of its least expensive sub activities (UPDATED)
Task 20 (5 marks):
Write a query to list the details of reservation (id) along with the total cost (in currency
format) of both activities calculated from the booking (number of people * the cost of an
activity) and villa reservation (number of days* Cost per night) for each of the villa) for
each of the reservation. If the reservation doesn’t involve activity booking, 0 must be
displayed then 0 must be displayed as the cost. Look at the example below of a sample
output.