表结构与数据：

练习1-1

SELECT empid, MAX(orderdate) AS maxorderdate FROM Sales.Orders GROUP BY empid;

 练习1-2

SELECT O1.empid, O2.maxorderdate, O1.orderid, O1.custid FROM Sales.Orders O1JOIN (SELECT empid, MAX(orderdate) AS maxorderdate FROM Sales.Orders GROUP BY empid) AS O2 ON O1.empid = O2.empid AND O1.orderdate = O2.maxorderdate;

练习2-1

SELECT orderid, orderdate, custid, empid, ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum FROM Sales.Orders ORDER BY orderdate, orderid;

练习2-2

WITH O AS (	SELECT orderid, orderdate, custid, empid, 	ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum 	FROM Sales.Orders)SELECT * FROM O WHERE rownum BETWEEN 11 AND 20ORDER BY orderdate, orderid;

练习3

WITH EmpsCTE AS (	SELECT empid, mgrid, firstname, lastname 	FROM HR.Employees WHERE empid = 9 	UNION ALL	SELECT P.empid, P.mgrid, P.firstname, P.lastname 	FROM EmpsCTE AS C  	JOIN HR.Employees AS P 	ON C.mgrid = P.empid) SELECT empid, mgrid, firstname, lastname FROM EmpsCTE;

练习4-1

IF OBJECT_ID('Sales.VEmpOrders') IS NOT NULL	DROP VIEW Sales.VEmpOrdersGOCREATE VIEW Sales.VEmpOrdersASSELECT empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty FROM Sales.Orders O JOIN Sales.OrderDetails D ON O.orderid = D.orderidGROUP BY empid, YEAR(orderdate);GO

练习4-2

SELECT empid, orderyear, qty, (SELECT SUM(qty) FROM Sales.VEmpOrders V2 WHERE V2.empid = V1.empid AND V2.orderyear <= V1.orderyear) AS runqty FROM Sales.VEmpOrders V1

练习5-1

IF OBJECT_ID('Production.TopProducts') IS NOT NULL 	DROP FUNCTION Production.TopProducts;GOCREATE FUNCTION Production.TopProducts (@supid AS INT, @n AS INT) RETURNS TABLE ASRETURN SELECT TOP(@n) productid, productname, unitprice FROM Production.Products WHERE supplierid = @supid ORDER BY unitprice DESC;

SELECT * FROM Production.TopProducts(5, 2);

练习5-2

SELECT S.supplierid, S.companyname, P.productid, P.productname, P.unitpriceFrom Production.Suppliers AS S CROSS APPLY Production.TopProducts(S.supplierid, 2) AS P;