Lab 3 Questions: Use the content of the AdventureWorks sample database.

Lab 3-1 /* Modify the following query to add a column that identifies the frequency of repeat customers. The new column will contain the following values based on the number of orders during 2007:

'One Time' for the order count = 1

'Regular' for the order count range of 2-5

'Loyal' for the order count greater than >5

Give the new column an alias to make the report more readable. */

SELECT CustomerID, ROUND(SUM(TotalDue), 2) [Total Purchase], COUNT(SalesOrderid) [Total # of Orders] FROM Sales.SalesOrderHeader WHERE DATEPART(year, OrderDate) = 2007 GROUP BY CustomerID;

Lab 3-2 /* Modify the following query to add a ranking column with gaps based on the total purchase in the descending order. Also partition by territory. Give the new column an alias to make the report more attractive. */

SELECT CustomerID, TerritoryID, ROUND(SUM(TotalDue), 2) [Total Purchase], COUNT(SalesOrderid) [Total # of Orders] FROM Sales.SalesOrderHeader WHERE DATEPART(year, OrderDate) = 2007 GROUP BY CustomerID, TerritoryID;

Lab 3-3 /* Retrieve the product id and product name of the top selling (by total quantity sold) product of each date. Sort the returned data by date in the ascending order. */

Lab 3-4 /* Write a query to retrieve the territory id, territory name, and total sale amount for each territory. Use TotalDue of SalesOrderHeader to calculate the total sale. Sort the returned data by the total sale in the descending order. */

Lab 3-5 /* Write a query that returns the salesperson(s) who received the highest bonus amount and calculate the highest bonus amounts percentage of the total bonus amount for salespeople. Your solution must be able to retrieve all salespersons who received the highest bonus amount assuming there may be more than one salesperson who received the highest bonus amount. Include the salespersons last name and first name, highest bonus amount, percentage in the report. */