Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 93

10424       11493.20 

10817       11490.70 

10889       11380.00 

10417       11283.20 

10897       10835.24 

10353       10741.60 

10515       10588.50 

10479       10495.60 

10540       10191.70 

10691       10164.80 

 

(14 row(s) affected)

5

Write a query against the Sales.Orders table that returns the three shipped-to countries with the high-

est average freight in 2007.

■

■

Tables involved: TSQL2012 database and the Sales.Orders table

■

■

Desired output:

shipcountry     avgfreight 

--------------- --------------------- 

Austria         178.3642 

Switzerland     117.1775 

Sweden          105.16 

 

(3 row(s) affected)

6

Write a query against the Sales.Orders table that calculates row numbers for orders based on order 

date ordering (using the order ID as the tiebreaker) for each customer separately.

■

■

Tables involved: TSQL2012 database and the Sales.Orders table

■

■

Desired output (abbreviated):

custid      orderdate               orderid     rownum 

----------- ----------------------- ----------- -------------------- 

1           2007-08-25 00:00:00.000 10643       1 

1           2007-10-03 00:00:00.000 10692       2 

1           2007-10-13 00:00:00.000 10702       3 

1           2008-01-15 00:00:00.000 10835       4 

1           2008-03-16 00:00:00.000 10952       5 

1           2008-04-09 00:00:00.000 11011       6 

2           2006-09-18 00:00:00.000 10308       1 

2           2007-08-08 00:00:00.000 10625       2 

2           2007-11-28 00:00:00.000 10759       3 

2           2008-03-04 00:00:00.000 10926       4 

... 

 

(830 row(s) affected)

www.it-ebooks.info

---