Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 95

8           NULL 

9           NULL 

11          NULL 

... 

 

(91 row(s) affected)

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

tion 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 manipu-

lation on the filtered column, in most cases SQL Server can’t use an index efficiently if such manipula-

tion 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 cor-

responding to a specified date.

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

www.it-ebooks.info

---

96  

Microsoft SQL Server 2012 T-SQL Fundamentals

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;

www.it-ebooks.info

---