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:
SELECT
*
FROM City AS MainCity
WHERE MainCity.Population > (
SELECT
AVG(AverageCity.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.
Type:
SELECT
*
FROM City MainCity
WHERE MainCity.Rating > (
SELECT
AVG(AverageCity.Rating)
FROM City AverageCity
WHERE AverageCity.CountryID = MainCity.CountryID
);
