(Solved): CIS 365 – Business Database Systems | HW8 – Basic SQL DML (100 points)...

CIS 365 – Business Database Systems
HW8 – Basic SQL DML (100 points)

This is an individual exercise, NOT a group assignment.

1. Based on the same table definition of Condo, create two new tables, New_Condo_A and New_Condo_B, by using the SELECT * INTO statement with zero row. Provide screen shots of your select into statements and the newly created tables with zero row. A similar example is in your W7 slide. (10 points)

2. Use INSERT INTO statement to populate New_Condo_A with building A records in Condo. Likewise, use another INSERT INTO statement to populate New_Condo_B with building B records in Condo. Screen shot your insert into statements, and select statements to display all newly inserted records in New_Condo_A and New_Condo_B. A similar example is in your W7 slide. (10 points)

[Note: INSERT INTO in Q2 is different from the SELECT * INTO in Q1.

Q1 creates two new empty tables; Q2 inserts records into the newly created tables.]

You should have 15 rows in New_Condo_A, 9 rows in New_Condo_B

3. Update records in New_Condo_A by adding 15% to the weeklyfee for all 3 bedrooms condos.

Display these three updated records. (10 points)

Only 3 condos in New_Condo_A have 3 bedrooms, so these 3 need to be updated.

4. Based on the result of Q3, delete records in New_Condo_A having the weeklyfee less than the average weeklyfee. Display all records left in New_Condo_A. (10 points)

Two ways to complete this question
a. Use two separate queries: 1) get the average fee first, 2) delete condos < the average fee
b. Use one query to delete condos < the average. You will get extra 5 points.

6 rows less than the average will get deleted based on the requirement
After 6 rows are deleted, New_Condo_A has the following 9 rows left.

5. Use the FamCation CONDO table to answer this question. Show all the condos in building A that costs more than $800 per week. Display Condo ID, Building Number, and Weekly Fee. List the most expensive condo first. Change the column name of WeeklyFee to ‘Expensive A Condos’. (15 points)
9 records in query result

6. Show all the reservations made in 2017 with an activity ID starting with ‘HB’ that were not led by guide
RH01 or MR01 (15 points).
7 records in query result

1. Display the most number of years, the least number of years, and the average number of years FamCation Resort employees have been with the company? Label the columns as ‘Most Years’, ‘Least Years’, and ‘Average Years’.
To standardize the answer, exclude the 5 ‘salary’ employees from the select statement as students might enter different hire dates for these employees. If you reload your database with the solution scripts, salary employees are already excluded. (15 points)

2. Increase the average PPP by 8% for all activities in the select statement, and display the increased average PPP in two columns. The first column shows the result with two significant digits, and the second column shows a rounded full integer. Label these two columns, Avg PPP in Decimal and Avg PPP in Integer, respectively. (15 points)
Avg PPP in Decimal = 18.90
Avg PPP in Integer = 19

Due Date: EOD Wednesday 10/20

• Take a screenshot of your query statement and result in MS SQL. Paste all queries/results in your Word doc.

• Convert the entire document into one pdf file for submission.

• Save your file name as HW8-Student Name.pdf.

• Submissions must be posted to Canvas to be graded.