Home > Sample chapters > Programming > SQL Server

Single-Table Queries in Microsoft SQL Server 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)