(Solved): CIS 365 – Business Database Systems HW9.2 – Advanced DML (75 points)...
CIS 365 – Business Database Systems
HW9.2 – Advanced DML (75 points)
This is an individual exercise, NOT a group assignment.
1. Show all the guests stays in 2019 who didn’t reserve any activities. The result will show the guest ID, first name, last name, and reservation ID (should be Null). (15 points)
2. Show all the guide's full name, hired date, their manager’s full name, manager’s hired date, and personnel type. (15 points)
3. Display Condos in building A that have more expensive weekly fee than the average of all condos in FamCation. Display Condo ID, Weekly Fee, and Average Weekly Fee in the query result. Label the average fee as "Average Weekly Fee" and only display 2 significant digits. (15 points)
4. Use the UNION clause to list the highest weekly fee for building A, lowest weekly fee for B, and average weekly fee for C. Display the "Category" (Highest Weekly Fee, Lowest Weekly Fee, Average Weekly Fee), "Building number" and "Weekly fee" as showed below. The result is sorted by building number. (15 points)
5. Recreate a new_Condo_A table by inserting all building A records from the Condo table. Write a CASE statement to change the weeklyfee in New_Condo_A by adding 15% weeklyfee to condos with 3 bedrooms, 10% to condos with 2 bedrooms, and 5% to condos with 1 bedroom. Display the query result BEFORE the change and AFTER the changes. Show all the attributes in New_Condo_A. (15 points)
Due Date: EOD Thursday 11/4
• Take a screenshot of your query statement and result in MS SQL. Paste all queries/results in your Word doc. (SnagIt available in ASU software tool library is a good screenshot tool)
• Convert the entire document into one pdf file for submission.
• Save your file name as HW9.2-Student Name.pdf.
• Submissions must be posted to Canvas to be graded.