Table Expressions in Microsoft SQL Server 2008 T-SQL
This section provides solutions to the exercises in the preceding section.
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;
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;
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;
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.
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;
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
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;
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
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;