El siguiente contenido fue elaborado por @_nhsz como guía para las clases de undefined school Son bienvenidos los issues y PRs para mejorar el contenido, corregir errores, etc.

👉 Si te resultó útil, se agradece que lo compartas para que le llegue a más gente!

• Bases de Datos
• SQL

• Intro
• Comandos y cláusulas
• DDL: Comandos para modificar el schema
  • CREATE DATABASE
  • CREATE TABLE
  • ALTER
  • DROP
• DML: Comandos para trabajar con los datos
  • INSERT
  • SELECT
    • DISTINCT
  • ORDER BY
  • WHERE
  • LIMIT
  • OFFSET
  • UPDATE
  • DELETE
• Operadores
  • Comparación
  • AND
  • OR
  • NOT
  • IN/NOT IN
  • LIKE
  • BETWEEN
  • IS/IS NOT NULL
• Alias
• Comentarios
• Expresiones
• Funciones de agregación
• Índices
• Ejercicios

SQL (Structured Query Language), es un lenguaje que utilizamos para interactuar con una base de datos relacional y realizar operaciones de tipo CRUD (Create, Read, Update, Delete), como crear bases de datos, crear tablas, insertar datos en estas tablas, seleccionar datos específicos que cumplan con ciertos criterios, combinar datos, eliminar datos, etc, es decir, consultar, manipular y transformar datos de una base de datos relacional.

👉 SQL nos permite entonces, responder preguntas específicas sobre los datos almacenados en la DB.

A las bases de datos relacionales también se las conoce coloquialmente como bases de datos SQL. Existen muchas, SQLite, MySQL, Postgres, Oracle, Microsoft SQL Server, etc. Todas estas tienen soporte para el standard SQL (que es lo que vamos a utilizar) y además, cada implementación o engine agrega sus propias features y tipos de datos (no standard).

⚠️ Nota 1: las instrucciones deben siempre terminar con ;. Es indiferente si las escribimos en una sola línea o en varias (SQL va a ignorar los saltos de línea, tabs y espacios), utilizando indentación para que resulte más legible.

⚠️ Nota 2: debemos asegurarnos de poner todos los strings dentro de comillas simples ('), no dobles. SQL interpreta las comillas dobles como el nombre de una tabla y las simples como un valor string.

👉 Vamos a llamar consulta o query a cada instrucción que termina con ;. Una query es una sentencia que declara qué información estamos buscando, dónde encontrarla dentro de la base de datos (qué tabla) y opcionalmente, cómo transformar esta información antes de retornarla.

Ver Introduction to SQL

Una query está compuesta por comandos y cláusulas.

• Comandos: son los que utilizamos para crear y definir nuevas bases de datos, campos e índices. También para seleccionar, insertar, eliminar y actualizar datos, generar consultas para ordenar, filtrar y extraer datos de la base de datos.
• Cláusulas: son condiciones de modificación utilizadas para definir los datos que desea seleccionar o manipular. El orden de las cláusulas importa.

Los comandos DDL (Data Definition Language) son aquellos que utilizamos para crear, modificar y eliminar tablas, columnas y bases de datos.

Es el comando que utilizamos para crear una nueva base de datos.

CREATE DATABASE testingdb;

También podríamos escribir la instrucción en 2 líneas, porque lo importante es el ;.

CREATE DATABASE testingdb;

👉 SQL no es case sensitive es decir, no diferencia entre mayúsculas y minúsculas, por lo que podríamos escribir la query anterior de la forma

create database testingdb;

Por convención, se suele utilizar mayúsculas para comandos y cláusulas y minúsculas para el resto.

Es el comando que utilizamos para crear una nueva tabla.

CREATE TABLE movies ( id SERIAL PRIMARY KEY, title VARCHAR(100), overview VARCHAR, release_date DATE, remove_this CHAR(1) );

Es un comando que nos permite modificar una tabla.

Para agregar una nueva columna, usamos ALTER con ADD

ALTER TABLE movies ADD rate DECIMAL(4, 2);

Para eliminar una columna, usamos ALTER con DROP COLUMN

ALTER TABLE movies DROP COLUMN remove_this;

Para cambiar el tipo de dato

ALTER TABLE post ALTER COLUMN posted_on TYPE VARCHAR(100);

o las restricciones (setear valores por default, campo no nulleable, etc)

ALTER TABLE post ALTER COLUMN posted_on SET DEFAULT CURRENT_TIMESTAMP;

Es un comando que nos permite eliminar tablas o la base de datos entera.

Para eliminar una tabla, usamos DROP TABLE

DROP TABLE movies;

Para eliminar una db, usamos DROP DATABASE

DROP DATABASE testingdb;

Los comandos DML (Data Manipulation Language) son aquellos que utilizamos para crear, leer, modificar, manipular y eliminar datos de registros (filas).

Es el comando que utilizamos para insertar valores en una tabla.

INSERT INTO movies (title, overview, release_date, rate) VALUES ('Gattaca', 'In a future society in the era of indefinite eugenics, humans are set on a life course depending on their DNA. Young Vincent Freeman is born with a condition that would prevent him from space travel, yet is determined to infiltrate the GATTACA space program.', '1997-10-24', 7.50);

👉 Podemos insertar varias filas a la vez, separando por comas. Esta operación es mucho más eficiente que insertar de a una fila por vez.

INSERT INTO post (user_id, post_text) VALUES (1, 'Hello, World!') (2, 'Hello again, world!');

Es el comando que utilizamos para seleccionar/obtener valores de una o más tablas.

Si queremos traer todas las columnas de la tabla movies, usamos el *

SELECT * FROM movies;

Si queremos ver sólo los títulos, tenemos que especificar la columna (en este caso, title)

SELECT title FROM movies;

También podemos traer varias columnas. El orden en el que las seleccionemos será el orden en el que vendrán los resultados

SELECT title, rate FROM movies;

Si queremos filtrar datos (filas) duplicados, podemos utilizar DISTINCT junto con SELECT

SELECT DISTINCT cause FROM earthquake;

Es la cláusula que utilizamos para ordenar valores por cierto campo. Si no utilizamos ORDER BY, el orden por default de los valores de una tabla es según el id de las filas.

Tenemos que especificar por qué columna queremos ordenar.

Por default, ordena de forma ascendente (ASC).

SELECT title, rate FROM movies ORDER BY rate;

Podemos especificar el orden agregando ASC ó DESC al final

SELECT title, rate FROM movies ORDER BY rate DESC;

También podemos ordenar por múltiples campos. En el siguiente ejemplo, ordenaríamos primero por state y luego (entre registros que tengan el mismo valor de state) por first_name, de forma descendiente. Podemos utilizar ASC y DESC de forma separada para cada campo

SELECT * FROM customers ORDER BY state DESC, first_name DESC;

Es la cláusula que utilizamos para establecer las condiciones o criterios que deben cumplir los campos que queremos seleccionar. Nos permite especificar las filas que nos interesan y por lo tanto, funciona como un filtro

SELECT title, rate FROM movies WHERE rate > 7;

Combinándola con el ORDER BY, podemos hacer

SELECT title, rate FROM movies WHERE rate > 7 ORDER BY rate DESC;

👉 Como dijimos al principio, el orden de las cláusulas importa: SELECT, FROM, WHERE y ORDER BY siempre deben usarse en ese orden y no en otro, sino tendremos un error de sintaxis y la instrucción no va a ejecutarse.

Es la cláusula que nos permite limitar la cantidad de resultados (filas) a mostrar. Por ejemplo, si sólo nos interesa el primer resultado, podemos hacer

SELECT title, rate FROM movies WHERE rate > 7 LIMIT 1;

Opcionalmente (por ejemplo, si queremos utilizar paginación), podemos proveer (como primer parámetro) un OFFSET para saltear algunos registros

Por ejemplo, si queremos limitar los resultados a 3 y saltear los primeros 6 registros, podemos hacer

SELECT * FROM customers LIMIT 6, 3 /* offset: 6, limit: 3 */

Es el comando que utilizamos para actualizar el valor de un campo de una tabla determinada. Se usa junto con SET, para especificar los valores nuevos y WHERE, para especificar qué campo queremos modificar.

UPDATE movies SET rate = 8.00 WHERE title = 'Gattaca';

⚠️ Es importante no olvidarnos del WHERE, si no vamos a modificar todas las filas de la tabla! (salvo que estemos buscando hacer eso)

También podemos modificar varios campos simultáneamente

UPDATE secret_user SET code_name = 'Neo 2.0', salary = 115000 WHERE user_id = 7;

Es el comando que utilizamos para eliminar registros de una tabla.

👉 DELETE elimina registros (filas), no columnas. Para hacer esto último, tendríamos que utilizar ALTER junto con DROP COLUMN

DELETE FROM movies WHERE title = 'Gattaca';

⚠️ No te olvides de poner el WHERE en el DELETE FROM!

• mayor (>)
• mayor o igual (>=)
• menor (<)
• menor o igual (<=)
• igualdad (=)
• desigualdad (!= o <>)

Podemos utilizar AND para combinar varios criterios que deben cumplirse en el WHERE

SELECT title, rate FROM movies WHERE rate >= 3 AND rate <= 7;

Podemos utilizar AND para establecer distintos criterios, de los que al menos 1 debe cumplirse en el WHERE

SELECT title, rate FROM movies WHERE rate <= 4 OR rate >= 7;

👉 Al igual que en JavaScript, los diferentes operadores pueden combinarse para definir criterios más complejos

SELECT * FROM customers WHERE birthdate > '1990-01-01' OR points > 100;

Sirve para negar un criterio y obtener el opuesto, para obtener todos aquellos que no lo cumplan

SELECT * FROM customers WHERE NOT (birthdate > '1990-01-01' OR points > 100);

En este caso, la cláusula

 NOT (birthdate > '1990-01-01' OR points > 100)

es equivalente a hacer

WHERE birthdate <= '1990-01-01' AND points <= 100

porque si negamos cada parte, tenemos

NOT (birthdate > '1990-01-01') => (birthdate <= '1990-01-01') NOT (OR) => AND NOT (points > 100) => (points <= 100) 

Es útil cuando un campo puede matchear con varios valores posibles, algo que haríamos utilizando varios OR

Por ejemplo, en lugar de hacer

SELECT * FROM customers WHERE state = 'VA' OR state = 'MI' OR state = 'FL';

podemos utilizar IN para simplificar

SELECT * FROM customers WHERE state IN ('VA', 'MI', 'FL');

También se puede negar, para obtener el complemento. Si nos interesan aquellos customers que no pertenecen al estado de 'VA', 'MI' o 'FL', hacemos

SELECT * FROM customers WHERE state NOT IN ('VA', 'MI', 'FL');

Sirve para obtener aquellas filas que matcheen cierto patrón de caracteres.

Por ejemplo, si queremos obtener todos aquellos customers cuyo apellido empiece con 'b', podemos hacer

SELECT * FROM customers WHERE last_name LIKE 'b%'

El símbolo % significa que no nos interesan qué caracteres (ni cuántos, incluyendo 0) vengan después. El % puede estar en cualquier parte del patrón (al principio, entre otros caracteres o al final).

Por ejemplo, si nos interesan aquellos customers cuyo apellido tenga una letra 'b' en cualquier parte del apellido, podemos hacer

SELECT * FROM customers WHERE last_name LIKE '%b%'

👉 Notas que estamos usando 'b%' como patrón, es indistinto si usamos mayúsculas o minúsculas ('b%' o 'B%'), no es case_sensitive

Si en cambio queremos indicar que antes (o después) de cierto caracter puede haber sólo una cantidad exacta, utilizamos _.

Entonces si queremos obtener aquellos customers cuyo apellido tenga exactamente 1 caracter (cualquiera) antes de la letra 'b' y cualquier caracter después, podemos hacer

SELECT * FROM customers WHERE last_name LIKE '_b%'

Si queremos obtener aquellos customers cuyo apellido tenga exactamente 5 caracteres (cualesquiera), finalizando con la letra 'b', podemos hacer

SELECT * FROM customers WHERE last_name LIKE '____b'

En resumen:

• % representa cualquier cantidad de caracteres
• _ representa 1 único caracter

👉 Ver más detalles sobre PostgreSQL LIKE

Se utiliza para obtener resultados que se encuentren dentro de cierto rango (numérico, fechas, etc)

SELECT * FROM customers WHERE points BETWEEN 100 AND 500;

Esto es equivalente a hacer

SELECT * FROM customers WHERE points >= 100 AND points <= 500;

Representa la ausencia de valor definido. Por ejemplo, si nos interesan sólo aquellos customers con el número de teléfono definido,

SELECT * FROM customers WHERE phone IS NOT NULL;

Para traer resultados donde un campo es nulo, ya sea porque no nos interesa el valor de este campo o queremos saber si faltan ciertos datos, la query es análoga, esta vez utilizando IS NULL. Por ejemplo, si nos interesan saber a qué customers les falta el número de teléfono, podemos hacer

SELECT * FROM customers WHERE phone IS NULL;

Podemos utilizar un alias para una tabla o columna, de forma temporal. Se suelen utilizar para que el nombre de las columnas resulte más legible o descriptivo.

Un alias sólo existe temporalmente, al ejecutar una query, no estamos modificando una tabla ni nada similar.

Por ejemplo, si queremos utilizar un alias para una columna, hacemos

SELECT column_name AS alias_name FROM table_name;

y si queremos utilizar un alias para una tabla, hacemos

SELECT column_name(s) FROM table_name AS alias_name;

Si queremos utilizar un alias con espacios, tenemos que ponerlo entre comillas (simples o dobles)

SELECT column_name(s) FROM table_name AS 'alias name';

Podemos comentar código SQL agregando -- delante. Como siempre, el código comentado no se ejecuta.

SELECT title, rate FROM movies; -- WHERE rate > 7 -- LIMIT 1;

También podemos comentar varias líneas a la vez, usando /* */

SELECT title, rate FROM movies; /*  WHERE rate > 7 LIMIT 1;  */

Podemos utilizar expresiones para hacer consultas con una lógica un poco más compleja. Estas expresiones pueden por ejemplo, utilizar operaciones matemáticas de aritmética básica y operaciones con strings o fechas. Se recomienda utilizar alias para que las expresiones resulten más legibles.

SELECT first_name, last_name, points + 10 AS total_points FROM customers;

Los operadores aritméticos que podemos utilizar son

• suma (+)
• resta (-)
• multiplicación (*)
• división (/)
• módulo (resto de la división) (%)

En las operaciones aritméticas, los operadores de multiplicación (*) y división (/) tienen precedencia sobre el resto (al igual que en JavaScript). Si queremos forzar cierto orden de ejecución, podemos utilizar paréntesis.

Por ejemplo, si quisiéramos primero realizar la suma y luego la multiplicación, deberíamos hacer

SELECT first_name, last_name, (points + 10) * 100 FROM customers;

Las funciones de agregación nos permiten efectuar operaciones sobre un conjunto de resultados, devolviendo un único valor agregado para todos ellos, como pueden ser la cantidad de filas, máximo, mínimo, promedio, etc.

• COUNT: devuelve la cantidad total de filas seleccionadas por la query
• MIN: devuelve el mínimo del campo que especifiquemos
• MAX: devuelve el máximo del campo que especifiquemos
• SUM: suma los valores del campo que especifiquemos (sólo se puede utilizar con datos de tipo numérico)
• AVG: devuelve el valor promedio del campo que especifiquemos (sólo se puede utilizar con datos de tipo numérico)

Por ejemplo, si queremos saber la cantidad total de filas de la tabla earthquake, podemos utilizar COUNT con el selector *

SELECT COUNT(*) FROM earthquake;

Si queremos conocer el rango de fechas, podemos utilizar MIN y MAX con el selector occurred_on, donde este último representa la fecha en la que ocurrió el evento

SELECT MIN(occurred_on), MAX(occurred_on) FROM earthquake;

Para indexar la tabla person por las columnas first_name y last_name, hacemos

CREATE INDEX person_first_name_last_name_idx ON person (first_name, last_name);

En este caso, el nombre del índice es person_first_name_last_name_idx. Como convención, se sugiere utilizar <NOMBRE-TABLA_NOMBRE-COLUMNA(S)_idx> para nombrar los índices.

1. Instalar PostgreSQL (Ver PostgreSQL - Instalación).
2. Práctica con SQLBolt.
3. Práctica en Codewars - SQL for Beginners.
4. Práctica en PostgreSQL Exercises.