Chapter 5 answers

Here are the answers to the homework questions... keep working on these on a daily basis even if you begin learning new things... it's always great to refresh!

SELECT custid, orderid, val,
SUM(val) OVER(PARTITION BY custid) AS custid,
SUM(val) OVER() AS Gratot
FROM sales.OrderValues
--
SELECT custid, orderid, val,
CAST(100 * val / (SUM(val) OVER(PARTITION BY custid)) AS NUMERIC (5,2)) AS custpct,
CAST(100 * val / (SUM(val) OVER()) AS NUMERIC (5,2)) AS Totpct
FROM sales.ordervalues
--
SELECT custid, orderid, val,
SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS UNBOUNDED PRECEDING) AS runningtot
FROM sales.ordervalues
--
SELECT orderid, custid, val,
AVG(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN 3 PRECEDING
AND CURRENT ROW) AS lastfour
FROM sales.OrderValues
--
WITH cte AS(
SELECT shipperid, freight, orderid,
ROW_NUMBER() OVER(PARTITION BY shipperid
ORDER BY freight DESC, orderid) AS rankpership
FROM sales.Orders)
SELECT * FROM cte
WHERE rankpership <= 3
--
USE TSQL2012
IF OBJECT_ID('fa') IS NOT NULL DROP TABLE fa;
GO
WITH freightavg AS(
SELECT custid, shipperid, freight
FROM sales.Orders)
SELECT *
INTO fa
FROM freightavg
PIVOT(AVG(freight) FOR shipperid IN ([1], [2], [3])) AS p;
SELECT * FROM fa
WHERE custid BETWEEN 5 AND 10
--
SELECT custid, shipperid, freight
FROM sales.freighttotals
UNPIVOT(freight FOR shipperid IN ([1], [2], [3])) AS un
--
SELECT o.custid, c.country, c.city, COUNT(*) AS swededorders
FROM sales.Orders AS o
INNER JOIN sales.Customers AS c
ON o.custid = c.custid
WHERE c.country LIKE 'Sweden'
GROUP BY o.custid, c.country, c.city
--
SELECT o.custid, c.city, COUNT(*) AS swededorders
FROM sales.Orders AS o
INNER JOIN sales.Customers AS c
ON o.custid = c.custid
WHERE c.country LIKE 'Sweden'
GROUP BY GROUPING SETS ((o.custid, c.city), ())