Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
14. Aliases for tables
Other subqueries


Wow! That wasn't an easy problem, so congratulations! Now, there may be examples where the same table is used both in the main query and in the correlated subquery. Try to figure out what the following example returns:

FROM City AS MainCity
WHERE MainCity.Population > (
  FROM City AS AverageCity    
  WHERE AverageCity.CountryID = MainCity.CountryID

In this example, for each country, we want to find cities with a population greater than the average population of all cities in that country. The problem is that we look for cities in the main clause and check the average population value for cities in the subquery. The same table appears twice—no good.

This is why we must use aliases for tables. Take a look: in the subquery, we put

... FROM City AS AverageCity ...

and in the main query, we put:

... FROM City AS MainCity ...

As you can see, we gave two different temporary names (aliases) to the City table, one in the main query and one in the subquery. The alias comes after the table name, separated by a space. Remember, no commas here!


For each country, find all information about cities in that country with a rating higher than the average rating of all cities in that country.

Stuck? Here's a hint!


FROM City MainCity
WHERE MainCity.Rating > (
  FROM City AverageCity   
  WHERE AverageCity.CountryID = MainCity.CountryID