Fall 2019 Assignment 12 Name __________________________ Using the AdventureWorks2012 database for all questions. (30 points) Using MAX subquery 1. For this questions you are building a subquery to...

Check assignment file


Fall 2019 Assignment 12Name __________________________ Using the AdventureWorks2012 database for all questions. (30 points) Using MAX subquery 1. For this questions you are building a subquery to find the PurchaseOrder that has the lastest order date. For the Purchasing.PurchaseOrderHeader, select the maximum order date. (6 pts) a. Paste your query here. b. What is the Date? c. Now you are going to embed the query you just wrote in the WHERE statement of a new query. This is just like the query on page 4 of Chapter 11 Key Points. Now build your select statement with the PurchaseOrderID and the orderdate From Purchasing.PurchaseOrderHeader where orderdate = (your query from a) You should have one row in your result set. Paste your query here. d. Write a simple query showing all the data for that purchaseorderid so you can check that it has the date from step b. Paste the query here. Using MIN subquery 2. For this questions you are building a subquery to find the PurchaseOrder numbers that have the earliest order date. For the Purchasing.PurchaseOrderHeader select the minimum order date. (6 pts) a. Paste your query here. b. What is the Date? c. Embed your query into the WHERE statement like you did for 1 c. In the SELECT statement include the Purchase Order Id and order date. You should have four rows in your result set. Paste your query here. Investigating Tables and their relationships 3. For the following tables, how many rows does each table contains. (3 pts) I don’t need to see your queries, just list your counts next to each table. a. Production.Product b. Production.ProductInventory c. Production.Location 4. For the following tables, indicate the Primary key or CPK. (3 pts) a. Production.Product b. Production.ProductInventory c. Production.Location 5. Using the tables listed in question 4 which table will you link to which table and what field would you use to link the tables. (2 pts) Table Table Fieldname to link them 6. From the Sales.SalesOrderHeader, (5 pts) a. What is the average freight cost on all sales order? b. Paste your query here. c. What is the maximum freight cost on all sales order? d. Paste your query here. e. What is the mimimum freight cost on all sales order ? f. Paste your query here. g. Using more than one function in a single query. In one query, calculate the minimum, maximum and average freight while giving each calculation an alias. Paste your query here. Like page 431 ed 3 or 456 ed 4. 7. Using the Purchasing. PurchaseOrderHeader, (5 pts) a. What is the orderdate on the oldest order? b. Paste your query here. c. What is the smallest Employeeid? d. Paste your query here. e. What is the last shipdate? f. Paste your query here. g. What is the average amount of taxes? h. Paste your query here.