1. Delete the manufacturing row in the OrgUnit table and all related rows in the Users table. When deleting the related rows in the Uses table, you must not use the organization number of the manufacturing row in a condition in your DELETE statement. Pretend that the user knows only the organization name, not the organization number. In addition, your solution should not rely on changing the design of the Expense Reporting database. In the design, the relationship from OrgUnit to Users has the default RESTRICT option for referenced rows. You cannot change the option to CASCADE. (Hint: when deleting a parent and related rows in a child table, the RESTRICT option requires deletion of the child rows before the related parent row.)

2. Rewrite the following SELECT statement. In the rewrite, change the cross product style on the ExpenseReport table to a Type I nested query.

SELECT ExpenseItem.ERNo, EINo, ExpDate, ExpAmt, ECName, ECLimit

FROM ExpenseReport, ExpenseItem, ExpCat

WHERE ExpenseReport.ERNo = ExpenseItem.ERNo

AND ExpenseItem.ECNo = ExpCat.ECNo

AND ExpDate BETWEEN '15-Sep-2020' AND '30-Sep-2020';

3. Rewrite the following SELECT statement. In the rewrite, change the cross product style on the Users and ExpenseReport tables to Type I nested queries. Your solution should have an outer query and two nested queries.

SELECT DISTINCT OrgUnit.OrgNo, OrgName

FROM OrgUnit, Users, ExpenseReport

WHERE ERSubmitDate BETWEEN '2020-09-15' AND '2020-09-30'

AND OrgUnit.OrgNo = Users.OrgNo

AND Users.UserNo = ExpenseReport.SubmitUserNo

AND UPPER(ERStatus) = 'APPROVED';

4.Rewrite the following SELECT statement to remove errors (such as incompatible data types and misspelled keywords) and improve formatting.

SELECT ExpenseReport.ERNo, UserFirstName, UserLastName,EINo,ExpDate, ExpAmt, ECName, ECLimit,

FROM ExpenseReport, ExpenseItem, ExpCat, Users

WHERE ExpenseReport.ERNo = ExpenseItem.ERNo

AND ExpenseItem.ECNo = ExpCat.ECNo

AND Users.UserNo = ExpenseReport.SubmitUserNo

AND ExpDate BETWEEN '15-Sep-2020' AND '30-Sep-2020'

AND ECLimit > '50';

2/28/2021 Expense Reporting Database Page 2 Asset 6 Tom 7 Marry 8 Jaime Sanchez Blake Garcia (303)445-3322 (303)445-3333 (303)357-4566 tsanchez@org.com mblake@org.com jgarcia@org.com 5 2 3 Asset No 1 1 2 ExpenseReport 3 AssetDesc Company Car Company Jet Company Van Company Truck Company Apartment ExpCat 4 5 SubmitUserNe. AparUser No 3 4 3 1 2 1 ERNO ERDess 1 Sales Presentation 2 Conference 3 Training Course 4 IT Group Lunch 5 Sales Presentation 6 Conference 7 Entertainment ERSubmitdate 8/10/2020 10:00 8/16/2020 10:00 9/23/2020 11:30 9/29/2020 10:00 9/30/2020 10:00 10/3/2020 11:30 10/5/2020 10:30 ERStatusRate ERStatus 8/26/2020 11:00 APPROVED 8/17/2020 10:00 APPROVED 9/25/2020 11:00 APPROVED 9/29/2020 16:30 APPROVED 10/1/2020 13:10 APPROVED 10/3/2020 15:10 PENDING 10/15/2020 14:30 DENIED 4 ECNO 1 7 4 5 5 5 4 ECName 1 Meal 2 Car Rental 3 Snack 4 Local Transportation 5 Airfare 6 Entertainment 7 Lodging 8 Admission Fee 9 Gas ECLimit 50.00 100.00 25.00 100.00 5,000.00 25.00 300.00 2,000.00 35.00 ExpenseItem EINO ExpDess 1 Car 2 Hilton 3 Gas 4 Junk Food 5 Comfort Inn 6 Airfare 7 Dinner 8 Dinner 9 Dinner OrgUnit ExpDate 8/9/2020 8/9/2020 8/9/2020 8/10/2020 8/16/2020 8/13/2020 8/15/2020 8/14/2020 8/13/2020 ExpAmt 150.00 99.00 35.00 10.00 225.00 325.00 23.94 32.50 ExpApprAmt ERNA ECNO Assetde. 150.00 1 7 99.00 1 7 20.00 1 9 1 10.00 1 3 225.00 2 7 325.00 2 5 23.94 2 1 32.50 2 1 19.64 2 1 OrgParentNo 19.64 OrgNo OrgName 1 Accounting 2 Sales 3 IT 4 IT Development 5 Accounts Receivable 6 IT Support 7 Accounts Payable 3 1 BudgetItem 3 1 Users OrgNo 3 6 UserNo UserFirst Name UserlastName UserPhone 1 Sue Herdon (206)339-3312 2 Bill Jones (303)205-8833 3 Joe Smith (303)443-9943 4 4 William Freed (303)445-3355 5 Sara Jonson (303)445-3321 UserEmail sherdon@org.com bjones@org.com jsmith@org.com wfreed@org.com sjonson@org.com BINO BUYear Qrene ECNO BIAmt 1 2020 1 1 4,000.00 2 2020 1 2 3,000.00 3 2020 1 3 2,000.00 4 2020 1 4 500.00 S 5 2020 1 5 10,000.00 6 2020 1 1 6 2,000.00 7 2020 1 7 10,000.00 8 2020 1 1 8 6,000.00 9 2020 1 9 5,000.00 10 2020 2 110,000.00 BIActual 76.08 0.00 10.32 0.00 325.00 25.00 225.00 0.00 0.00 167.00 4 1 7 2/28/2021 Expense Reporting Database Page 2 Asset 6 Tom 7 Marry 8 Jaime Sanchez Blake Garcia (303)445-3322 (303)445-3333 (303)357-4566 tsanchez@org.com mblake@org.com jgarcia@org.com 5 2 3 Asset No 1 1 2 ExpenseReport 3 AssetDesc Company Car Company Jet Company Van Company Truck Company Apartment ExpCat 4 5 SubmitUserNe. AparUser No 3 4 3 1 2 1 ERNO ERDess 1 Sales Presentation 2 Conference 3 Training Course 4 IT Group Lunch 5 Sales Presentation 6 Conference 7 Entertainment ERSubmitdate 8/10/2020 10:00 8/16/2020 10:00 9/23/2020 11:30 9/29/2020 10:00 9/30/2020 10:00 10/3/2020 11:30 10/5/2020 10:30 ERStatusRate ERStatus 8/26/2020 11:00 APPROVED 8/17/2020 10:00 APPROVED 9/25/2020 11:00 APPROVED 9/29/2020 16:30 APPROVED 10/1/2020 13:10 APPROVED 10/3/2020 15:10 PENDING 10/15/2020 14:30 DENIED 4 ECNO 1 7 4 5 5 5 4 ECName 1 Meal 2 Car Rental 3 Snack 4 Local Transportation 5 Airfare 6 Entertainment 7 Lodging 8 Admission Fee 9 Gas ECLimit 50.00 100.00 25.00 100.00 5,000.00 25.00 300.00 2,000.00 35.00 ExpenseItem EINO ExpDess 1 Car 2 Hilton 3 Gas 4 Junk Food 5 Comfort Inn 6 Airfare 7 Dinner 8 Dinner 9 Dinner OrgUnit ExpDate 8/9/2020 8/9/2020 8/9/2020 8/10/2020 8/16/2020 8/13/2020 8/15/2020 8/14/2020 8/13/2020 ExpAmt 150.00 99.00 35.00 10.00 225.00 325.00 23.94 32.50 ExpApprAmt ERNA ECNO Assetde. 150.00 1 7 99.00 1 7 20.00 1 9 1 10.00 1 3 225.00 2 7 325.00 2 5 23.94 2 1 32.50 2 1 19.64 2 1 OrgParentNo 19.64 OrgNo OrgName 1 Accounting 2 Sales 3 IT 4 IT Development 5 Accounts Receivable 6 IT Support 7 Accounts Payable 3 1 BudgetItem 3 1 Users OrgNo 3 6 UserNo UserFirst Name UserlastName UserPhone 1 Sue Herdon (206)339-3312 2 Bill Jones (303)205-8833 3 Joe Smith (303)443-9943 4 4 William Freed (303)445-3355 5 Sara Jonson (303)445-3321 UserEmail sherdon@org.com bjones@org.com jsmith@org.com wfreed@org.com sjonson@org.com BINO BUYear Qrene ECNO BIAmt 1 2020 1 1 4,000.00 2 2020 1 2 3,000.00 3 2020 1 3 2,000.00 4 2020 1 4 500.00 S 5 2020 1 5 10,000.00 6 2020 1 1 6 2,000.00 7 2020 1 7 10,000.00 8 2020 1 1 8 6,000.00 9 2020 1 9 5,000.00 10 2020 2 110,000.00 BIActual 76.08 0.00 10.32 0.00 325.00 25.00 225.00 0.00 0.00 167.00 4 1 7