Writing Homework Help

BONUS POINTS FOR 24HR EARLY SUBMISSION.  THEY WILL BE APPLIED ONLY IF ALL QUESTIONS WERE ANSWERED COMPLETELY – NO BONUS POINTS FOR MISSING FILES.

This is an INDIVIDUAL assignment – do not discuss answers with other class members other than general questions on slack.

Write the SQL scripts (queries) to answer all questions. 

Use the Family Adventure Resorts ERD and schema that is provided in the Week 4 module page on Canvas.
See the PDF, “Calculating the Invoice” in Week 6 module for hints on how to perform the calculations needed for Question 4.

Execute each query. Take a screenshot of each query/result; copy it to a Word file – one screenshot per page (similar to the example shown below, cropping out the unnecessary areas).

All screen shots will be inserted into ONE document in the correct numerical order (1, 2, 3, …).  When finished, save your Word document as a .pdf file. 

The following table aliases are recommended:

activities = afamilies = finvoice = icleaning = clguests = ginvoicedetails = idcondos = cguides = gupersonnel = pcondostays = cshousekeeping = hreservations = r

For column aliases and views, if you are asked to include them, they will be (shown in parenthesis).  

THIS CONTENT IS PROTECTED AND MAY NOT BE SHARED, UPLOADED, SOLD, OR DISTRIBUTED

Question 110 pts

Create a view june_activities to accomplish the following:

Retrieve all activities that the guests participated in June, 2019.  

Your output should include: activity description (popular activities in june 2019),  the activity type (activity type) and the number of people who participated in every activity (number_of_participants). 

Do not include duplicate records in the output.
Display your results alphabetically by type.

(Please leave the below text box as blank.  Grading will be done based on your scripts file and screen shots file)

Question2

Write a transaction script to store the following data:
New guests, Trudy & Martin Winters from Mankato Minnesota, will be staying at the resort this fall.  They can be reached at 218-555-5872.  They have 2 children; Trevor, who was 6 on June 2, 2016 (born on June 2, 2010) and Brooke, who turned 9 on Jan 15, 2016 (born on Jan 15, 2007).  Store Trevor first.
They have not yet decided if they want to participate in any activities.  The resort will assign them guest number G24 and place them in condo 5 (i.e., Building A, Unit 105).  The Winters’ will arrive for staying one week starting October 31st, 2020.
(Ensure that string values are stored using the right case.  For example, A105 should not be stored as a105.)
After the data has been stored in each table,  retrieve those new records  in each table to ensure that the data was entered correctly.
hint: order matters when inserting the data into the tables.

(Please leave the below text box as blank.  Grading will be done based on your scripts file and screen shots file)

Question 3

Create a trigger assignhousekeeping to accomplish the following:

When a new condo is added to the database, automatically add a new record to the cleaning table.

The new condo will be cleaned by Janice Avery the day it is added to the condo table; use today’s date as the DateCleaned.  

Show screenshots of your trigger code (no hard-coding) along with screenshots of:

The record has been correctly added to the cleaning table.

The record has been correctly added to the condos table.

Use this insert statement to test your trigger:

Insert into condos
                           values (99, ‘Z’, ‘101’, 1030, 2, 1, 999.00);

(Please leave the below text box as blank.  Grading will be done based on your scripts file and screen shots file)

Question 4

Procedures to – CalcateMcLeanFinalInvoice

The McLean family is challenging their total bill for their stay starting August 3, 2019.  Retrieve all invoice data (including details) for the McLean family for invoice 20065, for their stay starting August 3, 2019.
Use a sales tax of 8% when calculating the total billed.

See the PDF, “Calculating the Invoice” in Week 6 module for hints on how to obtain these results.

Follow the approach as given below:

1. Create the following 4 intermediate/temporary tables (use the create table statement as provided below):

    a. McLeanActivityTotals:

     b. McLeanDetailTotals:

      c. McLeanCondoFees:

       d.  McLeanTotalFees:

2. Create the following 5 procedures:

     a. PopulateMcLeanActivityTotals:  This procedure should insert records into McLeanActivityTotals table by retrieving all activities for the invoice number 20065.  Retrieve the inserted data from the table and include in your submission.  Inserted data should be similar to below image. 

     b. PopulateMcLeanDetailTotals: This procedure should insert record into McLeanDetailTotals table by selecting data from the table populated in the previous step, i.e. McLeanActivityTotals, for invoice number 20065.  Retrieve the inserted data from the table and include in your submission.  Inserted data should be similar to below image.

 

   c. PopulateMcLeanCondoFees: This procedure should insert record into McLeanCondoFees table by selecting appropriate data to calculate the total condo fees for invoice number 20065 (for the stay starting August 3, 2019).  Retrieve the inserted data from the table and include in your submission.  Inserted data should be similar to below image.

     d. PopulateMcLeanTotalFees: This procedure should insert record into McLeanTotalFees table by selecting data populated in steps b & c above for invoice number 20065.  Retrieve the inserted data from the table and include in your submission.  Inserted data should be similar to below image.

     e. CalculateMcLeanFinalInvoice: This procedure should use the information populated in steps b, c, & d to output breakdown of the invoice amount for invoice number 20065 (for the stay starting August 3, 2019) as per the sample output below. 

Take screenshots of all procedure code and results.

(Please leave the below text box as blank.  Grading will be done based on your scripts file and screen shots file)

Question 5

Upload your screen shots file.  Label the filename: firstInitialLastName.pdf
      (i.e. KMoser.pdf)

3 points for a complete set of screenshots
2 points for not quite complete
1 point for incomplete
0 points for missing file

Question 6

Upload your scripts file.  Label the filename: firstInitialLastName.txt
(i.e. KMoser.txt)