Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

6.8. 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 statement 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)

Using the UNION ALL set operation, you can unify the result sets of multiple such statements, each returning a row with a different number in the range 1 through 10, like so:

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

As an aside, SQL Server 2008 enhances the VALUES clause that you may be familiar with in the context of the INSERT statement in two ways. Instead of restricting it to representing a single row, now a single VALUES can represent multiple rows. Also, instead of restricting it to INSERT statements, the VALUES clause can now 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 set operations:

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 as part of the discussion of the INSERT statement.


2 You can solve this exercise by using the EXCEPT set operation. The left input is a query that returns customer and employee pairs that had order activity in January 2008. The right input is a query that returns customer and employee pairs that had order activity in February 2008. Here’s the solution query:
USE TSQLFundamentals2008;

SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'

EXCEPT

SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301';

3 While Exercise 2 requested customer and employee pairs that had activity in one period but not another, this exercise concerns customer and employee pairs that had activity in both periods. So this time, instead of using the EXCEPT set operation, you need to use the INTERSECT set operation, like so:
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'

INTERSECT

SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301';

4 This exercise requires you to combine set operations. To return customer and employee pairs that had order activity in both January 2008 and February 2008 you need to use the INTERSECT set operation, as in Exercise 3. To exclude customer and employee pairs that had order activity in 2007 from the result, you need to use the EXCEPT set operation between the result and a third query. The solution query looks like this:
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
INTERSECT

SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301'

EXCEPT

SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101';

Keep in mind that the INTERSECT set operation precedes EXCEPT. In our case, the default precedence is also the desired precedence, so you don’t need to intervene by using parentheses. But you may prefer to add those for clarity:

(SELECT custid, empid
 FROM Sales.Orders
 WHERE orderdate >= '20080101' AND orderdate < '20080201'

 INTERSECT

 SELECT custid, empid
 FROM Sales.Orders
 WHERE orderdate >= '20080201' AND orderdate < '20080301')

 EXCEPT

 SELECT custid, empid
 FROM Sales.Orders
 WHERE orderdate >= '20070101' AND orderdate < '20080101';

5 The problem here is that the individual queries are not allowed to have ORDER BY clauses, and for a good reason. You can solve the problem by adding a result column based on a constant to each of the queries involved in the set operation (call it sortcol). In the query against Employees, specify a smaller constant than the one you specify in the query against Suppliers. Define a table expression based on the query with the set operation, and in the ORDER BY clause of the outer query, specify sortcol as the first sort column, followed by country, region, and city. Here’s the complete solution query:
SELECT country, region, city
FROM (SELECT 1 AS sortcol, country, region, city
      FROM HR.Employees

      UNION ALL

      SELECT 2, country, region, city
      FROM Production.Suppliers) AS D
ORDER BY sortcol, country, region, city;


  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint