HACKERRANK 12일차

Population Census

SELECT SUM(ci.POPULATION) 
FROM CITY AS ci
JOIN COUNTRY AS co ON ci.COUNTRYCODE = co.CODE
WHERE co.CONTINENT LIKE 'Asia';

JOIN 문 테이블 별칭 채워서 훨씬 이해하기 쉽게 가능

African Cities

SELECT ci.NAME
FROM CITY AS ci
JOIN COUNTRY AS co ON ci.COUNTRYCODE = co.CODE
WHERE co.CONTINENT LIKE 'Africa';

Average Population of Each Continent

틀린 코드 1

SELECT co.CONTINENT, ROUND(AVG(co.POPULATION), 1)
FROM COUNTRY AS co
JOIN CITY AS ci ON ci.COUNTRYCODE = co.CODE
GROUP BY co.CONTINENT;

결과
Asia 469998743.6
Oceania 18886000.0
Europe 50104210.0
South America 139134200.0
Africa 42506375.0

틀린코드 2

SELECT co.CONTINENT, ROUND(AVG(ci.POPULATION), 0)
FROM COUNTRY AS co
JOIN CITY AS ci ON ci.COUNTRYCODE = co.CODE
GROUP BY co.CONTINENT;

결과
Asia 693038
Oceania 109190
Europe 175138
South America 147435
Africa 274439

맞은 코드

SELECT co.CONTINENT, FLOOR(AVG(ci.POPULATION))
FROM COUNTRY AS co
JOIN CITY AS ci ON ci.COUNTRYCODE = co.CODE
GROUP BY co.CONTINENT;

결과
Asia 693038
Oceania 109189
Europe 175138
South America 147435
Africa 274439

rounded down to the nearest integer :