Sql server® 2012 t-sql fundamentals

 

CHAPTER 4

 

Subqueries

 

 147

This time, the query returns the expected result.

shipper_id  companyname 

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

2           Shipper ETYNR 

3           Shipper ZHISN

When you’re done, run the following code for cleanup.

IF OBJECT_ID('Sales.MyShippers', 'U') IS NOT NULL 

  DROP TABLE Sales.MyShippers;

Conclusion

This chapter covered subqueries. It discussed self-contained subqueries, which are independent of 

their outer queries, and correlated subqueries, which are dependent on their outer queries. Regard-

ing the results of subqueries, I discussed scalar and multivalued subqueries. I also provided a more 

advanced section as optional reading, in which I covered returning previous and next values, using 

running aggregates, and dealing with misbehaving subqueries. Remember to always think about the 

three-valued logic and the importance of prefixing column names in subqueries with the source table 

alias.

The next chapter focuses on table subqueries, also known as table expressions.

Exercises

This section provides exercises to help you familiarize yourself with the subjects discussed in this 

chapter. The sample database TSQL2012 is used in all exercises in this chapter.

1 

Write a query that returns all orders placed on the last day of activity that can be found in the 

Orders table.

■

■

Tables involved: Sales.Orders

■

■

Desired output:

orderid     orderdate                   custid      empid 

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

11077       2008-05-06 00:00:00.000     65          1 

11076       2008-05-06 00:00:00.000     9           4 

11075       2008-05-06 00:00:00.000     68          8 

11074       2008-05-06 00:00:00.000     73          7

www.it-ebooks.info

---