Sql server® 2012 t-sql fundamentals

 

CHAPTER 5

 

Table Expressions

 

 187

Solutions

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

1-1

This exercise is just a preliminary step required for the next exercise. This step involves writing a query 

that returns the maximum order date for each employee.

USE TSQL2012; 

 

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 the following.

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 for the next exercise. It requires you to query the Orders table and 

calculate row numbers based on orderdate, orderid ordering, like the following.

SELECT orderid, orderdate, custid, empid, 

  ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum 

FROM Sales.Orders;

www.it-ebooks.info

---