End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Correlated Subqueries
10. Orchestras that played at home in 2003


Cool! Now we'll practice correlated subqueries. Here's a brief reminder:

Correlated subqueries refer to the outer query. Take a look:

SELECT cat_id
FROM cats c
WHERE cat_id IN 
  FROM owner 
  WHERE wage > 5000 AND owned_cat_id = c.cat_id)

In the above query we selected the IDs of cats that belong to owners who earn more than $5000. Note that the subquery refers to the table cats in the outer query: for each cat in the cats table the subquery is processed separately. The subquery can refer to tables in the outer query, but the outer query cannot refer to tables in the subquery. It's often helpful to give aliases to tables in both queries.

Just like uncorrelated subqueries, the correlated subqueries can be used in the WHERE, HAVING, or FROM clause of the query.

It is also possible to have a subquery in the SELECT clause. Such a subquery has to return exactly one row and column. Here's an example:

   (SELECT AVG(age) FROM cats c2 WHERE c2.name = c1.name)
FROM cats c1


Select the name of each orchestra that held a concert in its country of origin in 2003.

Stuck? Here's a hint!

In the outer query select the name of the orchestra. In WHERE of the outer query use IN and a correlated subquery.

WHERE country_origin IN (...)

In the subquery, select country names for concerts where a given orchestra performed in 2003. Remember to refer to the orchestras in the subquery.