Sql server® 2012 t-sql fundamentals

 

CHAPTER 4

 

Subqueries

 

 153

3

You can write a self-contained subquery against the Orders table that filters orders placed on or after 

May 1, 2008 and returns only the employee IDs from those orders. Write an outer query against the 

Employees table returning employees whose IDs do not appear in the set of employee IDs returned by 

the subquery. Here’s the complete solution query.

SELECT empid, FirstName, lastname 

FROM HR.Employees 

WHERE empid NOT IN 

  (SELECT O.empid 

   FROM Sales.Orders AS O 

   WHERE O.orderdate >= '20080501');

4

You can write a self-contained subquery against the Employees table returning the country attribute 

from each employee row. Write an outer query against the Customers table that filters only customer 

rows where the country does not appear in the set of countries returned by the subquery. In the SELECT 

list of the outer query, specify DISTINCT country to return only distinct occurrences of countries, be-

cause the same country can have more than one customer. Here’s the complete solution query.

SELECT DISTINCT country 

FROM Sales.Customers 

WHERE country NOT IN 

  (SELECT E.country FROM HR.Employees AS E);

5

This exercise is similar to Exercise 1, except that in that exercise, you were asked to return orders placed 

on the last day of activity in general; in this exercise, you were asked to return orders placed on the 

last day of activity for the customer. The solutions for both exercises are similar, but here you need to 

correlate the subquery to match the inner customer ID with the outer customer ID, like this.

SELECT custid, orderid, orderdate, empid 

FROM Sales.Orders AS O1 

WHERE orderdate = 

  (SELECT MAX(O2.orderdate) 

   FROM Sales.Orders AS O2 

   WHERE O2.custid = O1.custid) 

ORDER BY custid;

You’re not comparing the outer row’s order date with the general maximum order date, but 

instead with the maximum order date for the current customer.

www.it-ebooks.info

---