Single-Table Queries in Microsoft SQL Server 2012

Solutions

This section provides the solutions to the exercises for this chapter, accompanied by explanations where needed.

1

You might have considered using the YEAR and MONTH functions in the WHERE clause of your solution query, like this.

USE TSQL2012;

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 6;

This solution is valid and returns the correct result. However, I explained that if you apply manipulation on the filtered column, in most cases SQL Server can’t use an index efficiently if such manipulation exists on that column. Therefore, I advise using a range filter instead.

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate >= '20070601'
  AND orderdate < '20070701';

2

In SQL Server 2012 you can use the EOMONTH function to address this task, like this.

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = EOMONTH(orderdate);

Prior to SQL Server 2012 the solution is more complex. As part of the discussion about date and time functions, I provided the following expression format to calculate the last day of the month corresponding to a specified date.

DATEADD(month, DATEDIFF(month, '19991231', date_val), '19991231')

This expression first calculates the difference in terms of whole months between an anchor last day of some month (December 31, 1999 in this case) and the specified date. Call this difference diff. By adding diff months to the anchor date, you get the last day of the specified date’s month. Here’s the full solution query, returning only orders for which the order date is equal to the last day of the month.

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231');

3

This exercise involves using pattern matching with the LIKE predicate. Remember that the percent sign (%) represents a character string of any size, including an empty string. Therefore, you can use the pattern ‘%a%a%’ to express at least two occurrences of the character a anywhere in the string. Here’s the full solution query.

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE '%a%a%';

4

This exercise is quite tricky, and if you managed to solve it correctly, you should be proud of yourself. A subtle requirement in the request might be overlooked or interpreted incorrectly. Observe that the request said “return orders with total value greater than 10,000” and not “return orders with value greater than 10,000.” In other words, the individual order detail row shouldn’t meet the requirement. Instead, the group of all order details within the order should meet the requirement. This means that the query shouldn’t have a filter in the WHERE clause like this.

WHERE quantity * unitprice > 10000

Rather, the query should group the data by order ID and have a filter in the HAVING clause like this.

HAVING SUM(quantity*unitprice) > 10000

Here’s the complete solution query.

SELECT orderid, SUM(qty*unitprice) AS totalvalue
FROM Sales.OrderDetails
GROUP BY orderid
HAVING SUM(qty*unitprice) > 10000
ORDER BY totalvalue DESC;

5

Because the request involves activity in the year 2007, the query should have a WHERE clause with the appropriate date range filter (orderdate >= ‘20070101’ AND orderdate < ‘20080101’). Because the request involves average freight values per shipping country and the table can have multiple rows per country, the query should group the rows by country, and calculate the average freight. To get the three countries with the highest average freights, the query should specify TOP (3), based on logical order of average freight descending. Here’s the complete solution query.

SELECT TOP (3) shipcountry, AVG(freight) AS avgfreight
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101'
GROUP BY shipcountry
ORDER BY avgfreight DESC;

Remember that in SQL Server 2012 you can use the standard OFFSET-FETCH option instead of the proprietary TOP option. Here’s the revised solution using OFFSET-FETCH.

SELECT shipcountry, AVG(freight) AS avgfreight
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101'
GROUP BY shipcountry
ORDER BY avgfreight DESC
OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;

6

Because the exercise requests that the row number calculation be done for each customer separately, the expression should have PARTITION BY custid. In addition, the request was to use logical ordering by orderdate, with orderid as a tiebreaker. Therefore, the OVER clause should have ORDER BY orderdate, orderid. Here’s the complete solution query.

SELECT custid, orderdate, orderid,
  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
ORDER BY custid, rownum;

7

You can handle the conditional logic required by this exercise by using a CASE expression. Using the simple CASE expression form, you specify the titleofcourtesy attribute right after the CASE keyword; list each possible title of courtesy in a separate WHEN clause followed by the THEN clause and the gender; and in the ELSE clause, specify ‘Unknown’.

SELECT empid, firstname, lastname, titleofcourtesy,
  CASE titleofcourtesy
    WHEN 'Ms.'  THEN 'Female'
    WHEN 'Mrs.' THEN 'Female'
    WHEN 'Mr.'  THEN 'Male'
    ELSE             'Unknown'
  END AS gender
FROM HR.Employees;

You can also use the searched CASE form with two predicates—one to handle all cases where the gender is female and one for all cases where the gender is male—and an ELSE clause with ‘Unknown’.

SELECT empid, firstname, lastname, titleofcourtesy,
  CASE
    WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
    WHEN titleofcourtesy = 'Mr.'           THEN 'Male'
    ELSE                                        'Unknown'
  END AS gender
FROM HR.Employees;

8

By default, SQL Server sorts NULL marks before non-NULL values. To get NULL marks to sort last, you can use a CASE expression that returns 1 when the region column is NULL and 0 when it is not NULL. Non-NULL marks get 0 back from the expression; therefore, they sort before NULL marks (which get 1). This CASE expression is used as the first sort column. The region column should be specified as the second sort column. This way, non-NULL marks sort correctly among themselves. Here’s the complete solution query.

SELECT custid, region
FROM Sales.Customers
ORDER BY
  CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;