SQL statements Solution

This exercise is based on the Sales.SalesOrderHeader table in the AdventureWorks2008 database.  This table can be found on the SQL server via remote desktop.

 
 

Please write the SQL statements to answer the following questions.

 

1.    How many customers who made at least one order in this period?

 

2.    What is the total net sales (before tax and freight) in this period?

 

3.    What is the average shipping cost for online orders? (Hint: online orders have OnlineOrderFlag=1)

 

4.    Show the number of orders placed by each customer and sort the result by the number of orders in descending order

 

5.    Show the earliest order date, the most recent order date, and the total amount for each customer.  Sort the result by CustomerID in ascending order

 

6.    Show the number of orders, total sales amount, and average order amount by order type (online vs. store)

 

7.    Show the net sales (before tax and freight) by territory and sales person. Orders without associated sales person should not be included. Sort the results by territoryid and salespersonid in ascending orders

 

8.    Are there any customers who have both online and offline orders?

 

9.    What is the average tax rate by territory? Did the company charge a higher tax rate for online orders?

 

10.     Show the top 10 busiest order days (i.e., the days having the largest number of orders) during this period?

 

Save the script file as Week 4 Ex 2.sql and submit this file.