Chapter 4 answers

Here are the answers to chapter four questions.  I need to do these over and over until I have them down.  I'm finding that developing the answer is much simpler once you understand the question clearly.  I find myself working backwards from the answer to the question a lot of the time, which is interesting to me.

The question that was number four includes windowing I believe so I omitted it.  I can come back to it after next chapter (which is on windowing).

Maybe I'll post these in the forum and see if there is an easier way of doing these...

--1)
SELECT categoryid, MIN(unitprice) AS mn
FROM production.Products
GROUP BY categoryid
--2)  NEED TO RETRY!
WITH cte AS
(
SELECT categoryid, MIN(unitprice) AS mn
FROM production.Products
GROUP BY categoryid
)
SELECT P.categoryid, P.productid, P.productname, P.unitprice
FROM production.products AS P
INNER JOIN cte as m
ON P.categoryid = m.categoryid
AND P.unitprice = m.mn
--3)
SELECT p.productname, p.productid, p.unitprice, s.supplierid, s.country
FROM production.Products as p
INNER JOIN
production.Suppliers as S
ON p.supplierid = s.supplierid
WHERE s.country LIKE 'Japan'
--5)
SELECT c.custid, c.companyname, o.orderdate, o.custid
FROM sales.customers AS c
INNER JOIN
sales.orders AS o
ON c.custid = o.custid
WHERE orderdate = '20070212' OR orderdate = '20070213'
--6) Need to practice
IF OBJECT_ID('ForQuestion6', 'IF') IS NOT NULL DROP FUNCTION
ForQuestion6;
GO
CREATE FUNCTION ForQuestion6(@empid AS INT)
RETURNS TABLE AS;
--7)
SELECT productid, productname, unitprice, supplierid
FROM production.Products
WHERE supplierid = 1
ORDER BY unitprice ASC
OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY
--8
SELECT s.supplierid, s.companyname AS supplier, A.*
FROM production.suppliers AS s
CROSS APPLY (SELECT productid, productname, unitprice, supplierid
FROM production.Products
WHERE supplierid = 1
ORDER BY unitprice ASC
OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS A
WHERE s.country = 'Japan'
--8, or this way with CTE
WITH cte AS
(SELECT productid, productname, unitprice, supplierid
FROM production.Products
WHERE supplierid = 1
ORDER BY unitprice ASC
OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY)
SELECT s.supplierid, s.companyname AS supplier, A.*
FROM production.suppliers AS s
CROSS APPLY  cte as A
WHERE s.country = 'Japan'