7.5 Exercise (See Section A.3 for the solution to the exercise.)

Here is an unrealistically complex query to thoroughly test your understanding of tuning queries with subqueries. Figure 7-36 is a more complex and difficult query diagram than you will likely find in a year of intensive SQL tuning. If you can handle this, you will easily handle any subquery scenario you will ever find in real life, so give it a shot!

If you don't get it right the first time, come back and try again after more practice and review.

Figure 7-36. A complex problem with multiple subqueries

Fill in the missing ratios for the correlated joins. Assume that t=5 (the rowcount of the whole query, including the NOT EXISTS subquery), while q=50 (the rowcount of the query with the NOT EXISTS condition removed). Find the best join order, including all the tables in the subqueries and in the outer query.