[HackerRank] SQL Basic (easy)(4)

1. Average Population

https://www.hackerrank.com/challenges/average-population/problem?isFullScreen=true

Average Population | HackerRank

Query the average population for all cities in CITY, rounded down to the nearest integer.

SELECT
    FLOOR(AVG(POPULATION)) AS AVG_POP
FROM CITY

2. Japan Population

https://www.hackerrank.com/challenges/japan-population/problem?isFullScreen=true

Japan Population | HackerRank

Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.

SELECT
    SUM(POPULATION) AS JPN_TOTAL_POP
FROM 
    CITY
WHERE COUNTRYCODE = 'JPN'

3. Population Density Difference

https://www.hackerrank.com/challenges/population-density-difference/problem?isFullScreen=true

Population Density Difference | HackerRank

Query the difference between the maximum and minimum populations in CITY.

SELECT MAX(POPULATION)- MIN(POPULATION) AS POP_DIFF
FROM CITY

4. Population Census

https://www.hackerrank.com/challenges/asian-population/problem?isFullScreen=true

Population Census | HackerRank

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

SELECT 
	SUM(CITY.POPULATION) AS SUM_POP
FROM CITY
    JOIN COUNTRY 
    ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Asia'

5. African Cities

https://www.hackerrank.com/challenges/african-cities/problem?isFullScreen=true

African Cities | HackerRank

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.

SELECT
    CITY.NAME
FROM COUNTRY
    JOIN CITY
    ON COUNTRY.CODE = CITY.COUNTRYCODE
WHERE
    COUNTRY.CONTINENT = 'Africa'

6. Average Population od Each Continent

https://www.hackerrank.com/challenges/average-population-of-each-continent/problem?isFullScreen=true

Average Population of Each Continent | HackerRank

 

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)) AS AVG_POP
FROM 
    COUNTRY
    JOIN CITY
    ON COUNTRY.CODE = CITY.COUNTRYCODE
GROUP BY COUNTRY.CONTINENT