Time for a break? No? Then here comes the next exercise!
Correlated subqueries can be used to find the best object in a certain category. Here we select orchestras which have the best rating among orchestras coming from the same city:
FROM orchestras o1
WHERE rating = (SELECT MAX(rating) FROM orchestras o2 WHERE o1.city_origin = o2.city_origin)
In the subquery, we select the maximal rating for all orchestras which come from the same city as the orchestra in the outer query. Note that we use aliases
o2 to distinguish between subquery orchestra and outer query orchestra. In the outer query we select orchestras which have
rating equal to the rating found in the subquery.