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

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *