Task 1 (5 marks):

Create the above tables based on the list provided above: booking, cruise, model, qualifications,

roster, schedule, staff, tour, and vessel.

Insert at least five records into each of the tables. No extra marks will be provided for adding more

records, but more records may be needed depending on the query results for different questions.

For each of the questions you must have two results displayed. Please take this in consideration

when inserting values within your tables.

Task 2 (5 marks):

Write a query to print the details (TourName, TourType, TourCost) of the tour. Note that the cost

must be prefixed with a '$' sign when printing the results. Sort the records in the order of the tour

with the highest cost at the top of the list.

Task 3 (5 marks):

Write a query to print the booking details - VesselID, FromDate, ToDate, along with the number of

days.

Task 4 (5 marks):

Write a query to print the vessel details if the models name is Model-200 and the purchase year

was after 2015. Use equi-join to answer this question.

Task 5 (5 marks):

Write a query to print the name of the vessel if they have been booked for 10 days or above and

their purchase year is between the years 2015 and 2020. Please ensure no duplicate results are

included. Use Join Using to answer this question.

Task 6 (5 marks):

Using a subquery print the name of the model and their capacity if the vessel purchase year was

within the last 5 years.

Task 7 (5 marks):

Using a join, print the name of all staff members and their position if their last rostered date was

within the last 5 years. Also include the name of all staff members (even if they have no managers)

and their retrospective managers name.

Task 8 (5 marks):

Write a query to print the number of cruises allocated to a vessel. Print the vessel name and rename

both columns. Sort the records in order of the number of vessels with the highest number at the top

of the list.

Task 9 (5 marks):

Print the details of all cruises which have never been scheduled.

Task 10 (5 marks):

Write a query to print the CruiseName and the TourName and calculate the total amount of the

cruise. The data saved in the table are exclusive of taxes. Your query should include the 10% tax.

Only include if the total cost of the tour of the cruise exceeds $3000.

Task 11 (5 marks):

Show the StaffName, along with the number of skills, if the number of skills is more than one.

Task 12 (5 marks):

Write a query to print the names of Staff, the maximum number of days, Tour Cost, updated Tour

Cost (which has increased by 2.7%), that have used a Business Tour Type. Please rename the column

names and ensure that all numerical columns have 2 decimal places only. You must use join on to

write your answer.

Task 13 (5 marks):

Write a query to print the details of any tour that is more than $250. Only include the allocation that

have been scheduled in either January of any year or any month in the years 2020 or 2022. Sort the

results by the cost of tour in descending order.

SECTION B (10 Marks)

This section has 2 questions. Each of the questions is 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 list provided above: Service and ServiceDock.

Task 14 (5marks):

Write a query to print the names of the service dock that have had a cruise with more than one

source location.

Task 15 (5marks):

Write a query to print the cruise details (CruiseID, CruiseName) and calculate the total number of

days per cruise. Only include if the total number of days for the cruise exceeds 10 days and has a

basic tour.

SECTION C (10 Marks)

This section has 2 questions. Each of the questions is 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 list provided above: Location and Route.

Task 16 (5marks):

Write a query to display the average cost of earnings rostered in the fourth quarter of 2022. Round

this to the nearest whole dollar. Assume that the earning is received at the end of the rostered date.

Task 17 (5marks):

Write a query to print the names of the staff members who have been rostered on a cruise that start

in the afternoon (between 12pm -3pm) and end before midday (between 6am 12pm) along with

the name of the manager and the CruiseName.

SECTION D (15 Marks)

This section has 3 questions. Each of the questions is worth 5 marks. You may be eligible for partial

marks if there are errors in your answers.

Task 18 (5marks):

Write a query to display the CruiseName, SourceLocationName and the SourceDestinationName

alongside their IDs, if the cruise is either departing or arriving in Sydney.

Task 19: (5marks):

Write a query to print year, average salary of the staff per year and the names of the staff who make

an average cost lesser than the salary cost of cruise per year.

Task 20 (5marks):

List the count of the staff and cruise per service dock (ServiceDockName). Only include Customers

whose surnames do not start with the letter E

Please answer questions according to diagram and mention every step to get a thumbsup

anase waser \begin{tabular}{|l|l|l|} \hline QUALIFICATIONS & Comment/Description & Sample Data \\ \hline Column-Name & A unique identifier referencing StaffiD in the Staff Table & S01 \\ \hline SkillName (PK) & The qualification skill of the crew Staff & Swimming \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline VESSEL & Comment/Description & Sample Data \\ \hline Column-Name & A unique identifier for the Vessel & V01 \\ \hline VesseliD (PK) & A name for the Vessel & Vessel Diamond \\ \hline VesselName & A 4-digit number that identifies the year of purchase of the vehicle & 2010 \\ \hline VPurchaseYear & A unique identifier referencing ModeliD in the Model Table & M01 \\ \hline ModelID (FK) & \end{tabular} BOOKING \begin{tabular}{|l|l|l|} \hline \hline Column-Name & Comment/Description & Sample Data \\ \hline VessellD (PK, FK) & A unique identifier referencing VesseliD in the Vessel Table & V01 \\ \hline CruiselD (PK, FK) & A unique identifier referencing CruiselD in the Cruise Table & C01 \\ \hline \end{tabular} TOUR \begin{tabular}{|l|l|l|} \hline \hline Column-Name & Comment/Description & Sample Data \\ \hline CruiselD (PK, FK) & A unique identifier for the referencing CruiselD in the Cruise Table & CO1 \\ \hline TourCode (PK) & A unique identifier for the Tour & T01 \\ \hline TourName & A name for the Tour & HiraethBusiness \\ \hline TourCost & A price for the Tour per day & $300.00 \\ \hline TourType & Identifying the type of tour being conducted & Deluxe \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline STAFF \\ \hline Column-Name & Comment/Description & Sample Data \\ \hline StaffID (PK) & A unique identifier for the Staff & S01 \\ \hline StaffName & A name for the Staff & Lachlan Macquarie \\ \hline ManagerID & Identifies the Staffs Members Manager who & SO2 \\ \hline is also a Staff member & \\ \hline Position & Identifying the role of the Staff members & Pilot \\ \hline StaffPay & A price for the Tour per hour & $80.00 \\ \hline \end{tabular} ROSTER \begin{tabular}{|l|l|l|} \hline \hline Column-Name & Comment/Description & Sample Data \\ \hline SchedulelD (PK, FK) & A unique identifier referencing ScheduleID in the Schedule Table & SCO1 \\ \hline StaffiD (PK, FK) & A unique identifier referencing StaffiD in the Staff Table & SO1 \\ \hline StartDateTime & The rostered start date and time & 20220919 \\ \hline & & 18:30:00 \\ \hline EndDateTime & The rostered end date and time & 20220922 \\ & & 10:00:00 \\ \hline \end{tabular} CRUISE \begin{tabular}{|l|l|l|} \hline Column-Name & Comment/Description & Sample Data \\ \hline CruiselD (PK) & A unique identifier for the Cruise & C01 \\ \hline CruiseName & A name for the Cruise & HiraethCruise \\ \hline NumOfDays & Identifying the number of the days the cruise will operate & 10 \\ \hline RoutelD (FK) & A unique identifier referencing RouteID in the Route Table & R01 \\ \hline \end{tabular} SCHEDULE \begin{tabular}{|l|l|l|} \hline \hline Column-Name & Comment/Description & Sample Data \\ \hline ScheduleID (PK) & A unique identifier for the Schedule & SC01 \\ \hline StartDate & The schedule date & 20220913 \\ \hline MaxCapacity & The maximum capacity a schedule can manage & 100 \\ \hline CruiselD (FK) & A unique identifier referencing CruiselD in the Cruise Table & CO1 \\ \hline \end{tabular} Case Background Hiraeth Cruises has now moved to relational databases (Thanks to your design). They are having trouble creating tables and writing a few queries. You have been employed to construct and implement the database and write queries for some common searches. Comments are provided for you to understand the columns and are not to be added to the database. Do not change the table or column names whilst creating the tables. Penalties will apply if you do so. anase waser \begin{tabular}{|l|l|l|} \hline QUALIFICATIONS & Comment/Description & Sample Data \\ \hline Column-Name & A unique identifier referencing StaffiD in the Staff Table & S01 \\ \hline SkillName (PK) & The qualification skill of the crew Staff & Swimming \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline VESSEL & Comment/Description & Sample Data \\ \hline Column-Name & A unique identifier for the Vessel & V01 \\ \hline VesseliD (PK) & A name for the Vessel & Vessel Diamond \\ \hline VesselName & A 4-digit number that identifies the year of purchase of the vehicle & 2010 \\ \hline VPurchaseYear & A unique identifier referencing ModeliD in the Model Table & M01 \\ \hline ModelID (FK) & \end{tabular} BOOKING \begin{tabular}{|l|l|l|} \hline \hline Column-Name & Comment/Description & Sample Data \\ \hline VessellD (PK, FK) & A unique identifier referencing VesseliD in the Vessel Table & V01 \\ \hline CruiselD (PK, FK) & A unique identifier referencing CruiselD in the Cruise Table & C01 \\ \hline \end{tabular} TOUR \begin{tabular}{|l|l|l|} \hline \hline Column-Name & Comment/Description & Sample Data \\ \hline CruiselD (PK, FK) & A unique identifier for the referencing CruiselD in the Cruise Table & CO1 \\ \hline TourCode (PK) & A unique identifier for the Tour & T01 \\ \hline TourName & A name for the Tour & HiraethBusiness \\ \hline TourCost & A price for the Tour per day & $300.00 \\ \hline TourType & Identifying the type of tour being conducted & Deluxe \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline STAFF \\ \hline Column-Name & Comment/Description & Sample Data \\ \hline StaffID (PK) & A unique identifier for the Staff & S01 \\ \hline StaffName & A name for the Staff & Lachlan Macquarie \\ \hline ManagerID & Identifies the Staffs Members Manager who & SO2 \\ \hline is also a Staff member & \\ \hline Position & Identifying the role of the Staff members & Pilot \\ \hline StaffPay & A price for the Tour per hour & $80.00 \\ \hline \end{tabular} ROSTER \begin{tabular}{|l|l|l|} \hline \hline Column-Name & Comment/Description & Sample Data \\ \hline SchedulelD (PK, FK) & A unique identifier referencing ScheduleID in the Schedule Table & SCO1 \\ \hline StaffiD (PK, FK) & A unique identifier referencing StaffiD in the Staff Table & SO1 \\ \hline StartDateTime & The rostered start date and time & 20220919 \\ \hline & & 18:30:00 \\ \hline EndDateTime & The rostered end date and time & 20220922 \\ & & 10:00:00 \\ \hline \end{tabular} CRUISE \begin{tabular}{|l|l|l|} \hline Column-Name & Comment/Description & Sample Data \\ \hline CruiselD (PK) & A unique identifier for the Cruise & C01 \\ \hline CruiseName & A name for the Cruise & HiraethCruise \\ \hline NumOfDays & Identifying the number of the days the cruise will operate & 10 \\ \hline RoutelD (FK) & A unique identifier referencing RouteID in the Route Table & R01 \\ \hline \end{tabular} SCHEDULE \begin{tabular}{|l|l|l|} \hline \hline Column-Name & Comment/Description & Sample Data \\ \hline ScheduleID (PK) & A unique identifier for the Schedule & SC01 \\ \hline StartDate & The schedule date & 20220913 \\ \hline MaxCapacity & The maximum capacity a schedule can manage & 100 \\ \hline CruiselD (FK) & A unique identifier referencing CruiselD in the Cruise Table & CO1 \\ \hline \end{tabular} Case Background Hiraeth Cruises has now moved to relational databases (Thanks to your design). They are having trouble creating tables and writing a few queries. You have been employed to construct and implement the database and write queries for some common searches. Comments are provided for you to understand the columns and are not to be added to the database. Do not change the table or column names whilst creating the tables. Penalties will apply if you do so