Sql server® 2012 t-sql fundamentals

 

CHAPTER 5

 

Table Expressions

 

 189

4-1

This exercise is a preliminary step for 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 the following.

USE TSQL2012; 

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 em-

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

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;

Note that in Chapter 7, “Beyond the Fundamentals of Querying,” you will learn about new tech-

niques to compute running totals by using window functions.

www.it-ebooks.info

---