Average Population of Each Continent
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.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.
Desired Output
Asia 693038
Europe 175138
Oceania 109189
South America 147435
Africa 274439
Answer
SELECT CO.CONTINENT, FLOOR(AVG(CI.POPULATION))
FROM CITY CI
INNER JOIN COUNTRY CO
ON CI.COUNTRYCODE = CO.CODE
GROUP BY CO.CONTINENT
Population Census
Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is ‘Asia’.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.
Note2: Tables are same as above.
Desired Output
27028484
Answer
SELECT SUM(CI.POPULATION)
FROM CITY CI
INNER JOIN COUNTRY CO
ON CI.COUNTRYCODE = CO.CODE
WHERE CO.CONTINENT = 'Asia'
African Cities
Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is ‘Africa’.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.
Note2: Tables are same as above.
Desired Output
Qina
Warraq al-Arab
Kempton Park
Alberton
Klerksdorp
Uitenhage
Brakpan
Libreville
Answer
SELECT CITY.NAME
FROM CITY
INNER JOIN COUNTRY
ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'AFRICA'