[SQL] HackerRank: INNER JOIN 문제

 

1. 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'

 

#  city 테이블, country 테이블 둘 다 name이라는 컬럼을 가지고 있음

    ->  city.name ; 어떤 테이블의 컬럼을 출력할 것인지 명확하게 해줘야 함!

 

---

 

2. 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'

 

---

 

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

 

 

# 그룹별 집계함수 값이 필요 -> GROUP BY 사용

# GROUP BY에 사용한 컬럼명 SELECT에서 출력해야 함(⭐⭐⭐⭐)