Greetings, after reading through chapter 5 completely now, I think I should write out some homework that will integrate just about everything that I've done but focus primarily on Lesson 3 since I haven't done individual homework for that lesson yet.
Here are a few:
1) Return for each order the customer ID, order ID, and order value, using the window function, the query should also return a grand total for all values, and customer total. Use Sales.ordervalues view
Pg. 173
2) Use query from question 1 and turn customer total, and grand total into percentage called pctcust, and pcttotal
pg. 173
3) Create a running total from the beginning of a current customer's activity until the current order. Partition using custid, order the window by order date, orderid.
pg 174
4)Write query against the sales.ordervalues view that returns per each custid, and orderid the moving average value of the customers last four orders.
pg 181
5)Write a query against the sales.orders table and filter the three orders with the highest freight values per each shipper (shipperid) using ordered as the tie breaker
pg. 181
6)From earlier post: USE TSQL2012 and create a table called 'freightavg' that uses selects custid, shipperid, and freight from sales.orders. Pivot data in freightavg to find the the average freight totals for customers with custid's between 5 and 10….. with shipperid of 1, 2, 3. Label this table as Fa
pg. 166
7)Unpivot custid, shipperid, freight from the sales.freighttotals table as unpivoted (need to create sales.freighttotals for this, see book)
Pg 167
8)Write a query that computes the number of orders per each customer, and their city's for customers in Sweden…UPDATE(3/23/14) Use sales.orders to find custid, use sales.customers to find city & country
Answer pg. 160
9)Use query from question 8, and add a grand count. Use grouping sets for this
Answer pg 161
CREDIT: Querying Microsoft SQL Server 2012 BY Itzik Ben-Gan, Dejan Sarka, Ron Talmage
No comments:
Post a Comment