SQL · JOIN entre CITY y COUNTRY

Esta serie mantiene la mente fresca resolviendo, uno a uno, los ejercicios de HackerRank. Cada entrada toma un tema concreto y lo agota; todas las consultas están en MySQL salvo donde se indique.

Te invito a intentar cada ejercicio antes de leer la solución.

Aquí entran dos tablas a la vez. CITY.CountryCode y COUNTRY.Code son la clave que las une.

The CITY table is described as follows:

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:

CITY

COUNTRY

Solución

Realizamos una coincidencia entre ambas tablas usando la columna CountryCode de la tabla City y la columna Code de la tabla Country. Una vez que sabemos que coinciden, unimos ambas tablas con INNER JOIN, seleccionamos solo el nombre de la ciudad (city.name) y filtramos con WHERE para quedarnos únicamente con las ciudades que pertenecen al continente 'Africa'.

SELECT city.nameFROM cityINNER JOIN countryON country.code = city.CountryCodeWHERE country.continent = 'Africa';

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.

Solución

Necesitamos obtener dos cosas: el nombre de cada continente y el promedio de la población de sus ciudades, pero redondeado hacia abajo. Como los datos están en dos tablas distintas, CITY y COUNTRY, primero tenemos que unirlas. La coincidencia se hace entre CITY.CountryCode y COUNTRY.Code, que son las columnas que comparten. Usamos un INNER JOIN para que solo aparezcan las filas donde haya coincidencia en ambas tablas.

En el SELECT ponemos country.continent para mostrar el continente, y FLOOR(AVG(city.population)) para calcular el promedio de la población de las ciudades de ese continente y redondearlo hacia abajo con FLOOR. La función AVG calcula el promedio, y FLOOR lo convierte al entero más cercano hacia abajo.

Como queremos el promedio por cada continente, agrupamos los resultados con GROUP BY country.continent. Así, por cada continente distinto, se calcula su promedio correspondiente.

SELECT country.continent, FLOOR(AVG(city.population))
FROM city
INNER JOIN country
ON country.code = city.CountryCode
GROUP BY country.continent;

Population Census

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

Solución

En este pequeño INNER JOIN lo que hacemos es realizar la sumatoria de todas las poblaciones de cada ciudad que este en el continente asiático.

SELECT SUM(city.population)
FROM city
INNER JOIN country
ON country.code = city.CountryCode
WHERE country.continent = 'Asia';

STATION

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.