Chapters 6-10, Life things

Well since there are about 17 chapters in this book I am going to try to split up the homework section by chapters of 5.  Previously I wrote a homework assignment that incorporated chapters 1-5, I'm going to do the same thing in my next post about chapters 6 through 10...  Chapter 7 (XML) will be favored as I write out this homework, but creating views, Inline Functions, creating/altering/deleting objects, insert/update/deleting rows of data, and adding constraints, will be in there as well.  Look for this homework in the next few days.

Tonight I'm going to do a few homework assignments from chapter 3, 4, and 5... maybe one from each and then relax.  Things have been crazy since my setback/injury trying to play catch up and having little time to get on blogger.  I always try to read the chapter I'm working on once through first, then go back and look at the lessons individually to help me absorb the material which leads to less posts because the time that I have to blog is used for reading.  Also, these last few chapters have been different then chapters 3, 4, and 5 due to the fact that there is still a lot of content, but the ability to work out the content with hours in TSQL seems to be less specific... maybe I'm not being creative enough.

Anyways, I've gotta "keep on keeping on" to get through this book.... enough with any excuses that I have.  I mentioned in the forums that I would like to test out this Friday (3/28), but with roughly 6 chapters left to go that's a very tall task.  None the less I'll be inspired after SQL Saturday #287 next weekend.. I'll for sure make a post from there.

Cheers... stay on the war path!

UPDATE, here is the homework I did today, mixed in a few different homework assignments, I'm only posting the answers, because I want to hold my self accountable for doing the homework, and not just saying that I did it....:

SELECT DATEDIFF(MONTH,MIN(hiredate),MAX(hiredate))
FROM hr.Employees
--
WITH CTE AS (SELECT orderid, custid, shippeddate,
DATEDIFF(DAY,shippeddate,GETDATE()) AS diff
FROM sales.orders)
SELECT orderid, custid, shippeddate,
'since shipped' =
CASE
WHEN diff > 1 THEN diff
WHEN diff IS NULL THEN '-999999'
END
FROM CTE
--
SELECT custid, orderid,
AVG(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS lstfour
FROM sales.OrderValues
--
SELECT custid, orderdate, orderid, SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS UNBOUNDED PRECEDING) AS runtot
FROM sales.OrderValues
--
WITH CTE AS(
SELECT orderid, shipperid, freight,
ROW_NUMBER() OVER(PARTITION BY shipperid
ORDER BY freight DESC, orderid) AS rankpership
FROM sales.Orders)
SELECT *
FROM CTE
WHERE rankpership <= 3
--
SELECT o.custid, c.city, c.country, COUNT(*) AS Swedeorders
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, c.country), ())