Sql server® 2012 t-sql fundamentals

154  

Microsoft SQL Server 2012 T-SQL Fundamentals

6

You can solve this problem by querying the Customers table and using EXISTS and NOT EXISTS predi-

cates with correlated subqueries to ensure that the customer placed orders in 2007 but not in 2008. 

The EXISTS predicate returns TRUE only if at least one row exists in the Orders table with the same 

customer ID as in the outer row, within the date range representing the year 2007. The NOT EXISTS 

predicate returns TRUE only if no row exists in the Orders table with the same customer ID as in the 

outer row, within the date range representing the year 2008. Here’s the complete solution query.

SELECT custid, companyname 

FROM Sales.Customers AS C 

WHERE EXISTS 

  (SELECT * 

   FROM Sales.Orders AS O 

   WHERE O.custid = C.custid 

     AND O.orderdate >= '20070101' 

     AND O.orderdate < '20080101') 

  AND NOT EXISTS 

  (SELECT * 

   FROM Sales.Orders AS O 

   WHERE O.custid = C.custid 

     AND O.orderdate >= '20080101' 

     AND O.orderdate < '20090101');

7

You can solve this exercise by nesting EXISTS predicates with correlated subqueries. You write the 

outermost query against the Customers table. In the WHERE clause of the outer query, you can use 

the EXISTS predicate with a correlated subquery against the Orders table to filter only the current 

customer’s orders. In the filter of the subquery against the Orders table, you can use a nested EXISTS 

predicate with a subquery against the OrderDetails table that filters only order details with product 

ID 12. This way, only customers who placed orders that contain product 12 in their order details are 

returned. Here’s the complete solution query.

SELECT custid, companyname 

FROM Sales.Customers AS C 

WHERE EXISTS 

  (SELECT * 

   FROM Sales.Orders AS O 

   WHERE O.custid = C.custid 

     AND EXISTS 

       (SELECT * 

        FROM Sales.OrderDetails AS OD 

        WHERE OD.orderid = O.orderid 

          AND OD.ProductID = 12));

www.it-ebooks.info

---