SQL · STATION: nombres y patrones (1)

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.

La tabla STATION trae veinte ejercicios seguidos. Los primeros ocho van de contar y de buscar patrones en los nombres.

STATION

The STATION table is described as follows:

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

Weather Observation Station 1

Query a list of CITY and STATE from the STATION table.

Solución

En este caso tendremos un acercamiento a la nueva tabla, ¿Que habrá en CITY y STATE?

SELECT CITY, STATE FROM STATION;

¿Quieres que te pase la tabla completa o solo un extracto?

Por lo que vemos tenemos 402 filas, no puse todos los datos del table porque no tendría mucho sentido con el fin de este blog.

Weather Observation Station 2

Query the following two values from the STATION table:

1. The sum of all values in LAT_N rounded to a scale of 2 decimal places.
2. The sum of all values in LONG_W rounded to a scale of 2 decimal places.

Solución

Usamos la función SUM para sumar todos los valores de las columnas LAT_N y LONG_W. Luego, para redondear a dos decimales, usamos ROUND, que recibe como argumentos la expresión a redondear y la cantidad de decimales deseada.

SELECT ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) FROM STATION;

Weather Observation Station 3

Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.

Solución

Usaremos DISTINCT para eliminar ciudades duplicadas en los resultados, y aplicamos la condición WHERE ID % 2 = 0 para filtrar solo aquellos registros cuyo ID sea un número par, ya que el operador módulo (%) devuelve el residuo de la división entre 2

SELECT DISTINCT CITY FROM STATION WHERE ID % 2 = 0;

... (resultado truncado, hay más filas)

Weather Observation Station 4

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

For example, if there are three records in the table with CITY values 'New York', 'New York', 'Bengaluru', there are 2 different city names: 'New York' and 'Bengaluru'. The query returns 1, because

total number of records — number of unique city names = 3−2=13−2=1

Solución

Usaremos conceptos vistos anteriormente como DISTINCT y uno nuevo llamado COUNT, con el cual podremos realizar el conteo de cuántos registros existen, con o sin duplicados, envolviendo DISTINCT. Realizamos la resta con el conteo mayor menos el conteo menor, así podremos obtener un número entero y no uno negativo. En este caso nos daría 13 y no -13.

SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;

Weather Observation Station 5

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

Sample Input

For example, CITY has four entries: DEF, ABC, PQRS and WXY.

Sample Output

ABC 3
PQRS 4

Explanation

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths 3, 3, 4  and  3. The longest name is PQRS, but there are 3 options for shortest named city. Choose ABC, because it comes first alphabetically.

Note
You can write two separate queries to get the desired output. It need not be a single query.

Solución

En este nuevo ejercicio tenemos nuevos conceptos por mirar. Primero tenemos LENGTH, el cual nos dirá la longitud del nombre de la ciudad. Luego tenemos ORDER BY, donde nos podemos estar perdiendo.

Como vemos, se nos piden dos cosas: la ciudad con el nombre más corto y la ciudad con el nombre más largo. Para esto, debemos ordenar de forma ascendente (con el tamaño más pequeño, siendo de menor a mayor) y descendente (el tamaño más grande, siendo de mayor a menor). Una vez que ordenemos la tabla, debemos sacar alfabéticamente tanto el nombre más corto como el más largo. Así que, por ejemplo, si hay dos nombres que empiezan por A, tendremos más orden al mirar los resultados.

Por último tenemos LIMIT, como nos interesa un solo resultado limitamos la consulta por un solo registro.

SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC, CITY ASC LIMIT 1;
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 1;

Weather Observation Station 6

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

Solución

Bueno, ya sabemos que para eliminar duplicados tenemos DISTINCT. Ahora, la nueva función que veremos será SUBSTRING, la cual nos ayudará para sacar la primera letra. Para más información de su sintaxis, tenemos esta guía.

En todo caso, el primer número que vemos en la consulta sería por dónde vamos a empezar. Por ejemplo, si tenemos New York, empezaríamos por la N, y para poder seleccionar lo que queremos que nos muestre sería el tercer campo. En este caso, volvemos a poner uno porque solo nos interesa la primera letra para poder saber si se encuentra en la lista de vocales, y con IN podremos recorrer esa lista para saber si coincide con lo que buscamos.

SELECT DISTINCT CITY FROM STATION
WHERE SUBSTRING(CITY, 1, 1) IN ('a', 'e', 'i', 'o', 'u');

... (resultado truncado, hay más filas)

Weather Observation Station 7

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

Solución

Ahora, usando todo lo que sabemos, hacemos un pequeño ajuste. Si sabemos que el segundo argumento de la función SUBSTRING es donde empezamos a contar, solo debemos sacar la longitud del nombre de las ciudades para obtener la última letra.

SELECT DISTINCT CITY FROM STATION
WHERE SUBSTRING(CITY, LENGTH(CITY), 1) IN ('a', 'e', 'i', 'o', 'u');

... (resultado truncado, hay más filas)

Weather Observation Station 8

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

Solución

Buscando si había una forma más simple en vez de usar SUBSTRING, encontré RIGHT y LEFT, los cuales realizan lo mismo, pero obtienen directamente la primera y última letra según dónde estemos posicionados: si en la izquierda o derecha de la palabra. Para casos específicos como este, en los que no necesitamos tanto control como tener más de una letra, este caso de uso de la función me parece perfecto.

SELECT DISTINCT CITY FROM STATION
WHERE RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u')
AND LEFT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u');

También tenemos la versión SUBSTRING

SELECT DISTINCT CITY FROM STATION
WHERE SUBSTRING(CITY, LENGTH(CITY), 1) IN ('a', 'e', 'i', 'o', 'u')
AND SUBSTRING(CITY, 1, 1) IN ('a', 'e', 'i', 'o', 'u');

... (resultado truncado, hay más filas)