Sql server® 2012 t-sql fundamentals

 

CHAPTER 6

 

Set Operators

 

 205

■

■

Desired output:

custid      empid 

----------- ----------- 

1           1 

3           3 

5           8 

5           9 

6           9 

7           6 

9           1 

12          2 

16          7 

17          1 

20          7 

24          8 

25          1 

26          3 

32          4 

38          9 

39          3 

40          2 

41          2 

42          2 

44          8 

47          3 

47          4 

47          8 

49          7 

55          2 

55          3 

56          6 

59          8 

63          8 

64          9 

65          3 

65          8 

66          5 

67          5 

70          3 

71          2 

75          1 

76          2 

76          5 

80          1 

81          1 

81          3 

81          4 

82          6 

84          1 

84          3 

84          4 

88          7 

89          4 

 

(50 row(s) affected)

www.it-ebooks.info

---

206  

Microsoft SQL Server 2012 T-SQL Fundamentals

3

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

and February 2008.

■

■

Tables involved: Sales.Orders

■

■

Desired output:

custid      empid 

----------- ----------- 

20          3 

39          9 

46          5 

67          1 

71          4 

 

(5 row(s) affected)

4

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

and February 2008 but not in 2007.

■

■

Tables involved: Sales.Orders

■

■

Desired output:

custid      empid 

----------- ----------- 

67          1 

46          5 

 

(2 row(s) affected)

5 (Optional, advanced)

You are given the following query.

SELECT country, region, city 

FROM HR.Employees 

 

UNION ALL 

 

SELECT country, region, city 

FROM Production.Suppliers;

www.it-ebooks.info

---

 

CHAPTER 6

 

Set Operators

 

 207

You are asked to add logic to the query so that it guarantees that the rows from Employees are 

returned in the output before the rows from Suppliers. Also, within each segment, the rows should be 

sorted by country, region, and city.

■

■

Tables involved: HR.Employees and Production.Suppliers

■

■

Desired output:

country         region          city 

--------------- --------------- --------------- 

UK              NULL            London 

UK              NULL            London 

UK              NULL            London 

UK              NULL            London 

USA             WA              Kirkland 

USA             WA              Redmond 

USA             WA              Seattle 

USA             WA              Seattle 

USA             WA              Tacoma 

Australia       NSW             Sydney 

Australia       Victoria        Melbourne 

Brazil          NULL            Sao Paulo 

Canada          Québec          Montréal 

Canada          Québec          Ste-Hyacinthe 

Denmark         NULL            Lyngby 

Finland         NULL            Lappeenranta 

France          NULL            Annecy 

France          NULL            Montceau 

France          NULL            Paris 

Germany         NULL            Berlin 

Germany         NULL            Cuxhaven 

Germany         NULL            Frankfurt 

Italy           NULL            Ravenna 

Italy           NULL            Salerno 

Japan           NULL            Osaka 

Japan           NULL            Tokyo 

Netherlands     NULL            Zaandam 

Norway          NULL            Sandvika 

Singapore       NULL            Singapore 

Spain           Asturias        Oviedo 

Sweden          NULL            Göteborg 

Sweden          NULL            Stockholm 

UK              NULL            London 

UK              NULL            Manchester 

USA             LA              New Orleans 

USA             MA              Boston 

USA             MI              Ann Arbor 

USA             OR              Bend 

 

(38 row(s) affected) 

www.it-ebooks.info

---

208  

Microsoft SQL Server 2012 T-SQL Fundamentals

Solutions

This section provides solutions to the Chapter 6 exercises.

1

T-SQL supports a SELECT statement based on constants with no FROM clause. Such a SELECT state-

ment returns a table with a single row. For example, the following statement returns a row with a 

single column called n with the value 1.

SELECT 1 AS n;

Here’s the output of this statement.

n 

----------- 

1 

 

(1 row(s) affected)

By using the UNION ALL operator, you can unify the result sets of multiple statements like the 

one just mentioned, each returning a row with a different number in the range 1 through 10, like 

the following.

SELECT 1 AS n 

UNION ALL SELECT 2 

UNION ALL SELECT 3 

UNION ALL SELECT 4 

UNION ALL SELECT 5 

UNION ALL SELECT 6 

UNION ALL SELECT 7 

UNION ALL SELECT 8 

UNION ALL SELECT 9 

UNION ALL SELECT 10;

Tip

   SQL Server supports an enhanced VALUES clause that you might be familiar with in the 

context of the INSERT statement. The VALUES clause is not restricted to representing a sin-

gle row; it can represent multiple rows. Also, the VALUES clause is not restricted to INSERT 

statements but can be used to define a table expression with rows based on constants. As 

an example, here’s how you can use the VALUES clause to provide a solution to this exercise 

instead of using the UNION ALL operator.

SELECT n 

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS Nums(n);

I will provide details about the VALUES clause and row value constructors in Chapter 8, 

“Data Modification,” as part of the discussion of the INSERT statement.

www.it-ebooks.info

---