Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Practice Set in MS SQL Server

Introduction
Exercises

Instruction

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

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

SELECT CatId
FROM Cats C
WHERE CatId IN 
  (SELECT OwnedCatId 
   FROM Owner 
   WHERE Wage > 5000 AND OwnedCatId = C.CatId)

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 
   Name, 
   (SELECT AVG(Age) FROM Cats C2 WHERE C2.Name = C1.Name)
FROM Cats C1

Exercise

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 CountryOrigin 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.