Table Expressions in Microsoft SQL Server 2008 T-SQL

Solutions

This section provides solutions to the exercises in the preceding section.

1-1

This exercise is just a preliminary step to the next exercise. This step involves writing a query that returns the maximum order date for each employee:

USE TSQLFundamentals2008;

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

1-2

This exercise requires you to use the query from the previous step to define a derived table, and join this derived table with the Orders table to return the orders with the maximum order date for each employee, like so:

SELECT O.empid, O.orderdate, O.orderid, O.custid
FROM Sales.Orders AS O
  JOIN (SELECT empid, MAX(orderdate) AS maxorderdate
        FROM Sales.Orders
        GROUP BY empid) AS D
    ON O.empid = D.empid
    AND O.orderdate = D.maxorderdate;

2-1

This exercise is a preliminary step to the next exercise. It requires you to query the Orders table and calculate row numbers based on orderdate, orderid ordering, like so:

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

2-2

This exercise requires you to define a CTE based on the query from the previous step, and filter only rows with row numbers in the range 11 through 20 from the CTE, like so:

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

You might wonder why you need a table expression here. Remember that calculations based on the OVER clause (such as the ROW_NUMBER function) are only allowed in the SELECT and ORDER BY clauses of a query, and not directly in the WHERE clause. By using a table expression you can invoke the ROW_NUMBER function in the SELECT clause, assign an alias to the result column, and refer to the result column in the WHERE clause of the outer query.

3

You can think of this exercise as the inverse of the request to return an employee and all subordinates in all levels. Here, the anchor member is a query that returns the row for employee 9. The recursive member joins the CTE (call it C)—representing the subordinate/ child from the previous level—with the Employees table (call it P)—representing the manager/parent in the next level. This way, each invocation of the recursive member returns the manager from the next level, until no next level manager is found (in the case of the CEO).

Here’s the complete solution query:

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

This exercise is a preliminary step to the next exercise. Here you are required to define a view based on a query that joins the Orders and OrderDetails tables, group the rows by employee ID and order year, and return the total quantity for each group. The view definition should look like this:

USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.VEmpOrders') IS NOT NULL
  DROP VIEW Sales.VEmpOrders;
GO
CREATE VIEW  Sales.VEmpOrders
AS

SELECT
  empid,
  YEAR(orderdate) AS orderyear,
  SUM(qty) AS qty
FROM Sales.Orders AS O
  JOIN Sales.OrderDetails AS OD
    ON O.orderid = OD.orderid
GROUP BY
  empid,
  YEAR(orderdate);
GO

4-2

In this exercise, you query the VEmpOrders view and return the running total quantity for each employee and order year. To achieve this, you can write a query against the VEmpOrders view (call it V1) that returns from each row the employee ID, order year, and quantity. In the SELECT list you can incorporate a subquery against a second instance of VEmpOrders (call it V2), that returns the sum of all quantities from the rows where the employee ID is equal to the one in V1, and the order year is smaller than or equal to the one in V1. The complete solution query looks like this:

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

5-1

This exercise requires you to define a function called fn_TopProducts that accepts a supplier ID (@supid) and a number (@n), and is supposed to return the @n most expensive products supplied by the input supplier ID. Here’s how the function definition should look:

USE TSQLFundamentals2008;
IF OBJECT_ID('Production.fn_TopProducts') IS NOT NULL
  DROP FUNCTION Production.fn_TopProducts;
GO
CREATE FUNCTION Production.fn_TopProducts
  (@supid AS INT, @n AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP(@n) productid, productname, unitprice
  FROM Production.Products
  WHERE supplierid = @supid
  ORDER BY unitprice DESC;
GO

5-2

In this exercise, you write a query against the Production.Suppliers table, and use the CROSS APPLY operator to apply the function you defined by the previous step to each supplier. Your query is supposed to return the two most expensive products for each supplier. Here’s the solution query:

SELECT S.supplierid, S.companyname, P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
  CROSS APPLY Production.fn_TopProducts(S.supplierid, 2) AS P;