SQL Lab: filtros¶

Ejercicio sql-lab-filters-annex.pdf · PDF

Alguien te da una caja de zapatos con 949 cartas de Pokémon y te pregunta: ¿cuál es el eléctrico más rápido? No te pones a leer 949 cartas en voz alta. Sacas las eléctricas, las ordenas por velocidad, miras la carta de arriba y lees su nombre.

Cuatro movimientos con las manos. En una base de datos esos cuatro movimientos tienen nombre, y juntos forman una frase que la base de datos entiende. En la lección anterior conectaste Superset a la base de datos de clase y subiste la tabla pokemon. Ahora aprendes a pedirle menos: menos columnas, menos filas, en un orden útil. En el videojuego de crafteo de este módulo sigues en la fase de farmear y lootear, pero un buen jugador no se lleva el mapa entero a casa, solo lo que necesita la receta.

Una caja de zapatos con 949 cartas¶

Una consulta es una pregunta escrita en un lenguaje que la base de datos entiende. Ese lenguaje es SQL (Structured Query Language).

Una analogía para toda esta página: la tabla pokemon es una caja de zapatos con cartas coleccionables.

La caja contiene 949 cartas, y cada carta tiene 22 líneas impresas. En esta lección usamos ocho de ellas:

Abre SQL → SQL Lab en Superset, elige la base de datos Dades classe y el esquema public, y ya puedes escribir. Ejecutas una consulta con el botón Run o con Ctrl+Enter.

La forma de una consulta¶

Una consulta tiene hasta cinco líneas, siempre en este orden. Cada una es un movimiento de tus manos.

flowchart LR
    A["<b>FROM</b><br>abre la caja:<br>949 cartas"] --> B["<b>WHERE</b><br>saca las cartas<br>que quieres"]
    B --> C["<b>ORDER BY</b><br>ordena el montón"]
    C --> D["<b>LIMIT</b><br>quédate con las<br>de arriba"]
    D --> E["<b>SELECT</b><br>lee solo las líneas<br>que has pedido"]

Esta es la pregunta del principio de la página, escrita como consulta:

SELECT pokemon, speed
FROM pokemon
WHERE type_1 = 'electric'
ORDER BY speed DESC
LIMIT 1;

Una fila, dos columnas. La respuesta, y nada más. Las siguientes secciones construyen esa frase línea a línea.

SELECT: lee solo las líneas que necesitas¶

SELECT elige las columnas del resultado. FROM nombra la tabla de la que vienen.

SELECT * FROM pokemon te entrega las 22 columnas de las 949 filas. Sirve para echar un primer vistazo, y no sirve como respuesta. Nombra las columnas en su lugar:

SELECT pokemon, type_1, attack FROM pokemon;

Sigue devolviendo 949 filas, pero solo tres columnas. Las cuatro primeras:

WHERE: saca cartas de la caja¶

WHERE se queda solo con las filas para las que una condición es verdadera. Una condición es una pregunta de sí o no que se hace a cada fila, una por una.

SELECT pokemon, type_1, attack FROM pokemon WHERE type_1 = 'fire';

Esto devuelve 59 filas: charmander, charmeleon, charizard, vulpix... La condición type_1 = 'fire' se preguntó 949 veces, y 59 cartas dijeron que sí.

El = es un operador de comparación. Hay seis:

Dos reglas causan la mayoría de los errores de la primera semana de SQL:

Dos condiciones: AND, OR¶

Un operador lógico une dos condiciones. AND: las dos deben ser verdaderas. OR: basta con una.

SELECT pokemon, type_1, type_2 FROM pokemon
WHERE type_1 = 'dragon' AND type_2 = 'flying';

Seis filas: dragonite, altaria, salamence, rayquaza y las formas Mega de los dos últimos. Cambia AND por OR y pregunta algo más amplio:

SELECT pokemon, type_1, type_2 FROM pokemon
WHERE type_1 = 'dragon' OR type_2 = 'dragon';

61 filas: todos los Pokémon que son dragón en cualquiera de las dos casillas. AND hace el resultado más pequeño, OR lo hace más grande.

ORDER BY y LIMIT: ordena el montón, quédate con lo de arriba¶

ORDER BY ordena el resultado por una columna. LIMIT se queda solo con las primeras filas.

SELECT pokemon, type_1, attack FROM pokemon
WHERE attack > 150
ORDER BY attack DESC;

19 filas, las más fuertes primero. Juntas, estas dos líneas responden a cualquier pregunta de "top N":

SELECT pokemon, attack FROM pokemon ORDER BY attack DESC LIMIT 5;

Fíjate en las dos últimas filas. Tres Pokémon tienen un ataque de 180: deoxys-attack, groudon-primal y rayquaza-mega. Solo caben dos en un top 5, y la base de datos eligió como quiso. Tu pantalla puede mostrar otra pareja, y los dos tendríais razón.

DISTINCT: conocer una columna¶

DISTINCT elimina las filas repetidas del resultado, de modo que cada valor distinto aparece una sola vez.

Antes de filtrar por una columna, averigua qué valores puede tomar:

SELECT DISTINCT type_1 FROM pokemon ORDER BY type_1;

18 filas, de bug a water: los 18 tipos, cada uno una vez. Ahora sabes que el tipo se escribe fire, no Fire ni FIRE. DISTINCT es la cura para la trampa de las cero filas de más arriba.

Cuatro filtros especiales¶

Los operadores de comparación cubren los números y el texto exacto. Otros cuatro filtros cubren el resto.

LIKE usa dos comodines, símbolos que representan texto que no conoces: % es cualquier texto (también ninguno), _ es exactamente un carácter. 'pika%' significa "empieza por pika": pikachu y sus doce disfraces (pikachu-rock-star, pikachu-phd...).

IN es una forma corta de varios OR: type_1 = 'fire' OR type_1 = 'water' OR type_1 = 'grass'. Pon NOT delante para lo contrario: type_1 NOT IN ('fire', 'water', 'grass') devuelve 680 filas, y 269 + 680 = 949. Nadie se ha perdido.

BETWEEN incluye los dos extremos: speed BETWEEN 100 AND 120 es lo mismo que speed >= 100 AND speed <= 120. Los dos devuelven 126. Con > y < obtendrías 83, porque los Pokémon con exactamente 100 y exactamente 120 quedan fuera.

IS NULL encuentra los blancos: los 439 Pokémon con un solo tipo. IS NOT NULL encuentra los otros 510. Este merece su propia sección.

NULL: la línea en blanco de la carta¶

NULL significa desconocido: aquí no hay nada escrito. No es cero, y no es un texto vacío.

Ejecuta esto y cuenta:

SELECT pokemon, type_1, type_2 FROM pokemon WHERE type_2 = NULL;

0 filas. Ningún error. ¿Han desaparecido los 439 Pokémon de un solo tipo?

No. Piensa en dos cartas con una línea en blanco. ¿Los dos blancos son iguales? No puedes saberlo: puede que ahí vaya algo, y no sabes qué. Así que cuando comparas cualquier cosa con NULL, la base de datos responde "desconocido", y WHERE solo conserva las filas donde la respuesta es un sí claro.

Por eso SQL tiene una pregunta aparte para los blancos. Recuérdalo: IS NULL, nunca = NULL.

Respuestas incorrectas que parecen correctas¶

Todas las trampas de esta página tienen algo en común: ninguna da error. La base de datos hace exactamente lo que escribiste y devuelve un número creíble.

La tercera, con detalle. Quieres los Pokémon de fuego o agua con un ataque superior a 100:

SELECT COUNT(*) FROM pokemon
WHERE type_1 = 'fire' OR type_1 = 'water' AND attack > 100;

79. La base de datos lo leyó como "todos los Pokémon de fuego (59), más los de agua con ataque superior a 100 (20)". Añade paréntesis alrededor de la parte del OR:

SELECT COUNT(*) FROM pokemon
WHERE (type_1 = 'fire' OR type_1 = 'water') AND attack > 100;

34. Las mismas palabras, otra pregunta. (COUNT(*) cuenta filas; abre la siguiente lección.)

Pruébalo: la caza de la Pokédex¶

Abre SQL Lab y responde a cada pregunta con una consulta. Escribe primero la consulta, luego abre la solución y compara tu número.

Si SQL Lab responde con un cuadro rojo, lee el mensaje: normalmente nombra la palabra con la que tropezó. Mira justo antes de esa palabra por si falta una comilla o hay una palabra clave mal escrita. Y ejecuta una consulta cada vez: con varias en el editor, solo ves el resultado de la última.

Ideas clave¶

• Una consulta tiene una forma fija: SELECT (columnas) · FROM (tabla) · WHERE (filas) · ORDER BY (orden) · LIMIT (recorte).
• WHERE hace una condición a cada fila. El texto va entre comillas simples y distingue mayúsculas de minúsculas; los números no llevan comillas.
• AND estrecha, OR amplía. AND se aplica primero: cuando los mezcles, usa paréntesis.
• ORDER BY ... DESC LIMIT n responde a "top N", pero LIMIT corta los empates de forma arbitraria.
• DISTINCT muestra los valores distintos de una columna: úsalo antes de filtrar.
• LIKE busca un patrón, IN una lista, BETWEEN un rango con los dos extremos, IS NULL un blanco. = NULL nunca es verdadero.
• Los errores peligrosos no dan error. Devuelven un número que parece correcto.

Comprobación rápida¶

Hasta ahora cada respuesta era una lista de cartas. ¿Y si la pregunta es "¿cuántos Pokémon de fuego hay?", y luego lo mismo para los 18 tipos a la vez? Siguiente: SQL Lab: grupos.