I'm new to SQL thus the question. I'm trying to query the following. Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.
The table schemas are
City: id, name, countryside, population
Country: code, name,continent, population
INNER JOIN ON
COUNTRY.CODE = CITY.COUNTRYCODE;
Ok. Here is the solution.
Select Country.Continent, floor(Avg(city.population)) From Country Inner Join City On Country.Code = City.CountryCode Group By Country.Continent;
Here, We have to group by Continent so as to have a result set for continent being the key identifier and then applying AVG function to the population for the cities belonging to this continent.