[SQL : HackerRank/Leetcode] 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.

Input Format

The CITY and COUNTRY tables are described as follows:

 

>> 문제 푼 코드 

select city.NAME
from city
    INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Africa'

www.hackerrank.com/challenges/african-cities/problem?h_r=internal-search

African Cities | HackerRank

 

2. Asian Population

---

>> 문제

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.

Input Format

The CITY and COUNTRY tables are described as follows: 위 문제의 테이블과 같다. 

 

>> 문제 푼 코드

SELECT SUM(CITY.population)
FROM CITY
    INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.CODE
WHERE CONTINENT = 'Asia'

www.hackerrank.com/challenges/asian-population/problem

Asian Population | HackerRank

 

 

3. Average Population of Each Continent

+)  그룹바이와 INNER JOIN 을 함께 연산

---

>> 문제

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.

Input Format

The CITY and COUNTRY tables are described as follows: 위 문제의 테이블과 같다. 

 

>> 문제 푼 코드

SELECT COUNTRY.CONTINENT , FLOOR(AVG(CITY.POPULATION))
FROM CITY
    INNER JOIN COUNTRY ON COUNTRY.CODE = CITY.COUNTRYCODE
GROUP BY COUNTRY.CONTINENT