SQL101-Homework Assignment #6 Answer Key

Here are the answers for homework #6.  Let me know if you have any questions!

USE AdventureWorks
GO

1.    How many rows are there in Sales.SalesOrderHeader?

SELECT COUNT(*)
FROM Sales.SalesOrderHeader

2.    How many Rows are there in Sales.SalesOrderDetail, that are also in Sales.SalesOrderHeader (SalesOrderID is the common/shared column)?

SELECT COUNT(*)
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh
     on sod.SalesOrderID = soh.SalesOrderID

3.    How many sales orders (sales.salesOrderHeader) were made by Customer name (you can relate salesOrderHeader to Person.contact on soh.customerID = c.contactID)

SELECT
       c.FirstName + ' ' + c.LastName
     , COUNT(*)
FROM sales.SalesOrderHeader soh
INNER JOIN Person.Contact c
     on soh.CustomerID = c.ContactID
GROUP BY
     c.FirstName + ' ' + c.LastName

4.    Using your last query, how much were those sales worth (sum subtotals)?

SELECT
       c.FirstName + ' ' + c.LastName
     , COUNT(*)
     , SUM(soh.SubTotal) as subtotal
FROM sales.SalesOrderHeader soh
INNER JOIN Person.Contact c
     on soh.CustomerID = c.ContactID
GROUP BY
     c.FirstName + ' ' + c.LastName