Hackerrank SQL: Basic Join

African Cities

The CITY and COUNTRY tables are described as follows:

Directions: 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.

My Solution:

SELECT 
    CITY.NAME
FROM CITY
JOIN COUNTRY
ON CITY.CountryCode = COUNTRY.Code
WHERE CONTINENT = 'Africa';

Output

Qina
Warraq al-Arab
Kempton Park
Alberton
Klerksdorp
Uitenhage
Brakpan
Libreville

Average Population of Each Continent

Directions: 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.

Solution:

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

I used floor() in this query because I we needed to round down to the nearest integer. Floor doesn’t round down or up — simply drops the decimal value and returns an integer. We could not use ROUND() because ROUND() will round up or down based on the decimal value. I then joined the COUNTRY and CITY table on the code columns in each table.