(Solved): CIS 365 W6A1 - Advanced SQL Queries (146 points) ...
W5A1 - Joins/Group/Nest: 3 bonus points for early submission
W6A1 - Advanced SQL Queries
(146 points)
Read all instructions carefully
Write the SQL scripts (queries) to answer the following questions. Use the Family Adventure Resorts tables in your database. Your queries will be copied in the SQL Editor window pane in MySQL from/to your notepad file (your notepad file includes queries only without results). All results will be shown in the results window pane. Take a screenshot of each query/result – one per page - (similar to the example shown below, cropping out the unnecessary areas) and insert into a Word document - must be read without magnifying glasses!
Incorrect screenshot formats = 5 points deduction!
Insert snapshots into ONE document in the correct numerical order (Q1, Q2, Q3, ...). When finished, save your Word document as a .pdf file. The .pdf file and notepad file will be submitted as:
YourLastName-W6A1.pdf and YourLastName-W6A1.txt
You must submit both the PDF file and the Notepad file (missing file = 20 point deduction!)
Be sure to use comment statements for each query; the assignment number, and each full question including the question number and points (10 point loss for missing comments). A comment begins with /* and ends with */ - see image below). Use column and table aliases (in joins) as shown in videos to simplify your queries or to make your output readable. Create queries as if you cannot see the data (start with what you know – no hardcoding! – let the DBMS find the values for you). Scripts coded as shown in lectures; separate lines (clauses) of code and indent for readability/ease of troubleshooting & grading .
|
One query/result per screenshot - show the entire script with comments and 5 lines of the result in the corresponding panes. (If the entire result does not fit in one screen after adjusting the panes, make sure the number of rows in the result shows as in the circled area above. Total records result must show in the first tab (do not execute the comments). Keep the correct numbering sequence of the questions in your submission. Resize the window as long as all areas are displayed and can be read. You can change your pages to landscape orientation if the fit is better. |
|
Do not execute the comment lines; highlight your query only and click the ‘Query’ button for single statement execution or ‘Query All’ for multiple statement execution. |
|
Example |
 |
Show all columns of data (do not concatenate names). Column aliases/views required are listed in bold italic black font. Join type is stated in parenthesis. If joining, use inner or outer joins (no simple joins, no nesting). For fully nested queries, no joins allowed – the statement must be fully nested unless otherwise stated!
Q1. (8) (Join) We're getting ready for the new season. When was the last time each condo was cleaned? Display theBuilding Number, Unit, and Date Last Cleaned. Order by the unit number within the building. (No need to check the date as 2019)
Q2. (7) (Join) How many children does each guest have? List the guests’ first name (Guest), the spouse name (Spouse), the family name (Family), and the number of children labeled Number of Children.
List the smallest families first.
Q3. (13) (Join) List the number of times each family stayed at the resort in 2019 and the total weekly fees collected from each family for the rented condos. Include the guest number, family last name and label other output as Number of Stays and Total Collected. Display the result by highest fee collected first. Don't forget to check the date.
Q4. (10) (Join) List all activity types and descriptions that the guests participated in during July of 2019.
Label the description Popular Activities in July, 2019. Do not include duplicate listings. Store the result in July2019Activities. Show the result by activity type.
Q5. (12) Write the fully nested query to display the city and state of all guests who reserved a rafting activity in July or August. Display by state.
Q6. (14) Unit C305 had some items left after the last family stayed during the week of August 3, 2019. Write a fully nested query to obtain a list of the children’s first names and ages. Label the names, Children Staying in C305 August 3rd Week. List oldest child first.
Q7. (10) (Join) How much money did each guest spend on activities in 2019? Include the guest's last name and ‘Money Spent on Activities in 2019’. Include only those guests spending at least $250.00.
Display the highest values first.
Q8. (15) Write a fully nested query to display those guides who lead activities that are more than 10 miles. List their first and last name and years employed with the resort. Store the result in TenMileGuides. Display the result by employee who has been with the resort the longest amount of time first.
Q9. (12) Write a transaction script to store the following data:
New guests, Martin & Trudy Winters from Mankato Minnesota will be staying at the resort the week starting June 6th, 2020. They have 2 children; Trevor, who was 5 on June 2, 2015 and Brooke, who turned 8 on Jan 15, 2015. They have not yet decided if they want to participate in any activities. We will assign them guest number G24 and place them in condo 5, A105. They can be reached at 218-555-5872.
Display each record – only the records added.
Q10. (8) (Join) Which housekeepers were assigned a condo they did not clean; who cleaned it? Display the condo number, the assigned housekeeper’s number & first & last name, the date cleaned, and the number & first & last name of the cleaning housekeeper (who cleaned it).
Q11. (4) (Join) Display all guests who have ever participated in an activity, and include those guests who have not participated. Display the guest number and last name, the reservation number, the activity number, the date of the activity, and the number who participated in the activity.
List the results by the reservation number.
Hint: this requires only 1 statement. Do not test for 2019.
Q12. (5) (Join) Display all guests who have NEVER participated in an activity. Display the guest number and last name, the reservation number, the activity number, the date of the activity, and the number who participated in the activity. (Show all activities for each guest)
List the results by the reservation number.
Hint: This is a modification of Q11. Do not test for 2019. */
Q13. (9) When a new condo is added to the database, automatically add a new record to the cleaning table.
The new condo was cleaned by AJ01 the day it was added to the condo table; use today's date for the DateCleaned. Show that the record has been correctly added to the cleaning table.
Use this insert statement to test your trigger:
Insert into condos values (99, 'Z', '101', 1030, 2, 1, 999.00); */.
Q14. (19) Retrieve all invoice data (including details) for the McLean family (see data required on EERD). They are challenging their total bill.
Hint: Create a series of views; calculate the activity totals, then the invoice details totals, then the invoice condo fees, and finally the total billed. Do not hardcode; start with what you know.
Include all attributes in the output as shown on the ERD.
See the PDF, “Hints of how to calculate the invoice here” below the assignment instructions.
There are several correct ways to code the queries for each question. The points allotted for the questions should be used as a guideline only; I don’t necessarily assign only one point per line; I could assign points for correct use of column or table aliases, calculations, etc. Basically, code the statement to retrieve the correct results and the points will take care of themselves.
Expert Answer
Buy This Answer $15
-- OR --
Subscribe $20 / Month