Skip to content

shannonlowder.com

Menu
  • About
  • Biml Interrogator Demo
  • Latest Posts
Menu

SQL101-Homework Assignment #6 Answer Key

Posted on April 7, 2006March 1, 2011 by slowder

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

Leave a Reply Cancel reply

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

  • Career Development
  • Data Engineering
  • Data Science
  • Infrastructure
  • Microsoft SQL
  • Modern Data Estate
  • Personal
  • Random Technology
© 2023 shannonlowder.com | Powered by Minimalist Blog WordPress Theme