• 01. African Cities
• 02. Population Census
• 03. Average Population of Each Continent

• 01-03 사용 테이블(CITY, COUNTRY)

01. 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.

-- 내 문제 풀이(정답)
SELECT CITY.NAME
FROM CITY 
	 INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE CONTINENT = 'Africa';

02. 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.

-- 내 문제 풀이(정답)
SELECT SUM(CITY.POPULATION)
FROM CITY 
	 INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE CONTINENT = 'Asia'

03. 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.

-- 내 문제 풀이(정답)
SELECT COUNTRY.CONTINENT
	 , FLOOR(AVG(CITY.POPULATION))
FROM CITY 
	 INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT;