ALL USING SQL DATA SHEET VIEW IN ACCESS

10. Read SQLCourse2 Step #9 - Mathematical Functions then complete the following queries a. Display the item and per unit price for each order in the items_ordered table. (Hint: The "price" field is the amount paid for all items - divide the price by the quantity and the display should be PricePerUnit). (Hint#2: Do NOT use SUM or GROUP BY on this query they are not necessary and would lead to incorrect results] Save as Q9-1 (32 records) b. Modify the query to round the calculation to 2 decimals and display alphabetically by item. Re-save Q9-1. (32 records) C. Using the items_ordered table, display the item and average per unit price for each item (calculated as: for each item add up all of the prices and all of the quantities ordered to calculate average per unit price for each item). The final display should be the item and the display of AvgPricePerUnit. Round the result to two decimals, only display items with the rounded average between 4 and 16 (inclusive) and display reverse alphabetical order by item. Save as 09-2 (9 records) 11. Table Joins - BASED ON LECTURE about INNER JOIN (do not follow Step #10 on JOIN as it shows a couple of forms of the Join, and we request you use the INNER JOIN syntax showed in today's lecture), complete the query: a. Write a query using a join to determine which items were ordered by each of the customers in the customers table. Select the customerid, firstname, lastname, orderdate, item, and price for everything each customer purchased in the items_ordered table. Save as Q10-1 (32 records) NOTE - you must use the form of the JOIN demonstrated in the lecture. 12. Using the concept of Table Joins from the lecture: a. Copy Q10-1 and name it Q11-1. Modify the query to remove price from the display. Then make further modifications to exclude customers with the last name of Gray, Dalton or Moore. Save the query (Q11-1) (17 records) customers