Single-Table Queries in Microsoft SQL Server 2012
- 7/15/2012
Exercises
This section provides exercises to help you familiarize yourself with the subjects discussed in Chapter 2. Solutions to the exercises appear in the section that follows.
You can find instructions for downloading and installing the TSQL2012 sample database in the Appendix.
1
Write a query against the Sales.Orders table that returns orders placed in June 2007.
Tables involved: TSQL2012 database and the Sales.Orders table
Desired output (abbreviated):
orderid orderdate custid empid ----------- ----------------------- ----------- ----------- 10555 2007-06-02 00:00:00.000 71 6 10556 2007-06-03 00:00:00.000 73 2 10557 2007-06-03 00:00:00.000 44 9 10558 2007-06-04 00:00:00.000 4 1 10559 2007-06-05 00:00:00.000 7 6 10560 2007-06-06 00:00:00.000 25 8 10561 2007-06-06 00:00:00.000 24 2 10562 2007-06-09 00:00:00.000 66 1 10563 2007-06-10 00:00:00.000 67 2 10564 2007-06-10 00:00:00.000 65 4 ... (30 row(s) affected)
2
Write a query against the Sales.Orders table that returns orders placed on the last day of the month.
Tables involved: TSQL2012 database and the Sales.Orders table
Desired output (abbreviated):
orderid orderdate custid empid ----------- ----------------------- ----------- ----------- 10269 2006-07-31 00:00:00.000 89 5 10317 2006-09-30 00:00:00.000 48 6 10343 2006-10-31 00:00:00.000 44 4 10399 2006-12-31 00:00:00.000 83 8 10432 2007-01-31 00:00:00.000 75 3 10460 2007-02-28 00:00:00.000 24 8 10461 2007-02-28 00:00:00.000 46 1 10490 2007-03-31 00:00:00.000 35 7 10491 2007-03-31 00:00:00.000 28 8 10522 2007-04-30 00:00:00.000 44 4 ... (26 row(s) affected)
3
Write a query against the HR.Employees table that returns employees with last name containing the letter a twice or more.
Tables involved: TSQL2012 database and the HR.Employees table
Desired output:
empid firstname lastname ----------- ---------- -------------------- 9 Zoya Dolgopyatova (1 row(s) affected)
4
Write a query against the Sales.OrderDetails table that returns orders with total value (quantity * unitprice) greater than 10,000, sorted by total value.
Tables involved: TSQL2012 database and the Sales.OrderDetails table
Desired output:
orderid totalvalue ----------- --------------------- 10865 17250.00 11030 16321.90 10981 15810.00 10372 12281.20 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 highest 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)
7
Using the HR.Employees table, figure out the SELECT statement that returns for each employee the gender based on the title of courtesy. For ‘Ms. ‘ and ‘Mrs.’ return ‘Female’; for ‘Mr. ‘ return ‘Male’; and in all other cases (for example, ‘Dr. ‘) return ‘Unknown’.
Tables involved: TSQL2012 database and the HR.Employees table
Desired output:
empid firstname lastname titleofcourtesy gender ----------- ---------- -------------------- ------------------------- ------- 1 Sara Davis Ms. Female 2 Don Funk Dr. Unknown 3 Judy Lew Ms. Female 4 Yael Peled Mrs. Female 5 Sven Buck Mr. Male 6 Paul Suurs Mr. Male 7 Russell King Mr. Male 8 Maria Cameron Ms. Female 9 Zoya Dolgopyatova Ms. Female (9 row(s) affected)
8
Write a query against the Sales.Customers table that returns for each customer the customer ID and region. Sort the rows in the output by region, having NULL marks sort last (after non-NULL values). Note that the default sort behavior for NULL marks in T-SQL is to sort first (before non-NULL values).
Tables involved: TSQL2012 database and the Sales.Customers table
Desired output (abbreviated):
custid region ----------- --------------- 55 AK 10 BC 42 BC 45 CA 37 Co. Cork 33 DF 71 ID 38 Isle of Wight 46 Lara 78 MT ... 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 11 NULL ... (91 row(s) affected)