Sql server® 2012 t-sql fundamentals

204  

Microsoft SQL Server 2012 T-SQL Fundamentals

Conclusion

This chapter covered set operators, including the general syntax and requirements of set operators, 

and describing in detail each supported set operator—UNION, INTERSECT, and EXCEPT. I explained 

that standard SQL supports two flavors of each operator—DISTINCT (set) and ALL (multiset)—and 

that as of SQL Server 2012, SQL Server implements the ALL flavor only with the UNION operator. I 

provided alternatives to the missing INTERSECT ALL and EXCEPT ALL operators that make use of the 

ROW_NUMBER function and table expressions. Finally, I introduced precedence among set opera-

tors, and explained how to circumvent unsupported logical query processing phases by using table 

expressions.

Exercises

This section provides exercises to help you familiarize yourself with the subjects discussed in Chapter 6. 

All exercises except for the first require you to be connected to the sample database TSQL2012. 

1

Write a query that generates a virtual auxiliary table of 10 numbers in the range 1 through 10 without 

using a looping construct. You do not need to guarantee any order of the rows in the output of your 

solution. 

■

■

Tables involved: None

■

■

Desired output:

n 

----------- 

1 

2 

3 

4 

5 

6 

7 

8 

9 

10 

 

(10 row(s) affected)

2

Write a query that returns customer and employee pairs that had order activity in January 2008 but 

not in February 2008.

■

■

Tables involved: TSQL2012 database, Sales.Orders table

www.it-ebooks.info

---