En este documento voy a mostrar las bases de las consultas SQL en una tabla simple. Voy a utilizar principalmente PostgreSQL, pero cuando existan diferencias, mostraré tambien la versión SQLite.

1 SELECT y FROM

1.1 Ejemplo basico de consulta

La consulta más simple consiste en el uso de las clausulas SELECT y FROM

SELECT pais, superficie_km2 
FROM countries;

Table 1: 3 records
pais	superficie_km2
Chile	756626
Colombia	1142000
Francia	549134

Notas: las variables en SELECT están separadas por coma, y la consulta termina por ;

1.2 Uso de `*`

Para evitar de escribir todas las variables de una tabla, podemos utilizar “*“:

SELECT * 
FROM countries;

Table 2: 3 records
pais	sur_america	superficie_km2
Chile	TRUE	756626
Colombia	TRUE	1142000
Francia	FALSE	549134

1.3 Sensibilidad a las mayusculas y uso de las comillas

1.3.1 Nombres de las clausulas

SQL es sensible al uso de las mayúsculas y minúsculas, sin embargo, los nombres de las clausulas se pueden escribir de cualquier manera. Usualmente las escribemos en mayuscula, para que las consultas sean más claras para leer…

select *
frOm countries;

Table 3: 3 records
pais	sur_america	superficie_km2
Chile	TRUE	756626
Colombia	TRUE	1142000
Francia	FALSE	549134

1.3.2 Comillas `"`

En SQL, los nombres de los objetos deberían escribirse con comillas:

SELECT "pais", "superficie_km2"
FROM "countries";

Table 4: 3 records
pais	superficie_km2
Chile	756626
Colombia	1142000
Francia	549134

Sin embargo, todo como funciona como si el sistema pasaría todo a minúsculas cuando no utilizamos las comillas.

SELECT PaIs, SuPerficie_km2
FROM countries;

Table 5: 3 records
pais	superficie_km2
Chile	756626
Colombia	1142000
Francia	549134

Así que si cambiamos mayusculas y minusculas en una consulta con comillas ", obtenemos un error (el error depende del sistema y de la interfaz que utilicen).

Pueden intentar eso en DBeaver con postgreSQL

SELECT "PaIs","Superficie_km2"
FROM countries;

En SQLite, no van a tener un error!

1.4 Cambiar el nombre de las columnas

Por utilizar la clausula AS, pueden cambiar los nombres de las columnas en las clausulas

SELECT pais, superficie_km2 AS "¿Que tan grande es?, digamos... ¿en km2?"
FROM countries;

Table 6: 3 records
pais	¿Que tan grande es?, digamos… ¿en km2?
Chile	756626
Colombia	1142000
Francia	549134

Realmente, la clausula AS es facultativa:

SELECT pais tierra, superficie_km2 km2
FROM countries;

Table 7: 3 records
tierra	km2
Chile	756626
Colombia	1142000
Francia	549134

1.5 Schemas

Existe una noción en PostgreSQL que es el “schema”. Las tablas se pueden organizar en diferentes partes en la base de datos, cada una de esas partes es un “schema”.

Así que la manera completa de llamar una tabla en una clausula FROM es “nombre_schema”.”nombre_tabla”. Sin embargo existe una forma para que PostgreSQL busque automáticamente los nombres de las tablas en uno o varios schema. Por defecto, el schema “public” está configurado así, y la tabla “countries” está en este schema.

SELECT pais tierra, superficie_km2 km2
FROM countries;

Table 8: 3 records
tierra	km2
Chile	756626
Colombia	1142000
Francia	549134

En SQLite, no existen los schemas, si uno quiere separar las tablas, tiene que utilizar varios archivos (¡pero es posible trabajar en varios archivos en una misma sesión!)

1.6 Añadir datos en SELECT

Todos los datos que utilizamos en SELECT no tienen que estar en una tabla de la base de datos

1.6.1 Añadir una columna

Podemos añadir una columna con el contenido que queremos

SELECT *, 12 AS mi_numero_favorito
FROM countries;

Table 9: 3 records
pais	sur_america	superficie_km2	mi_numero_favorito
Chile	TRUE	756626	12
Colombia	TRUE	1142000	12
Francia	FALSE	549134	12

1.6.2 Añadir texto con comillas `'`

En SQL, para diferenciar los objetos de las cadenas de carácter, utilizamos las comillas ' para esas ultimas.

SELECT *, 'lo que quiera' AS "Puedo añadir"
FROM countries;

Table 10: 3 records
pais	sur_america	superficie_km2	Puedo añadir
Chile	TRUE	756626	lo que quiera
Colombia	TRUE	1142000	lo que quiera
Francia	FALSE	549134	lo que quiera

1.6.3 Añadir una columna de calculo

SELECT *, superficie_km2/2 mitad
FROM countries;

Table 11: 3 records
pais	sur_america	superficie_km2	mitad
Chile	TRUE	756626	378313
Colombia	TRUE	1142000	571000
Francia	FALSE	549134	274567

1.6.4 Utilizar `||`

El operador || permite pegar (“concatenar”) cadenas de caracter:

La tabla espeletia contiene la taxonomía del genero Espeletia para Colombia

SELECT *
FROM espeletia;

Table 12: Displaying records 1 - 10
rank	genus	spec_epith	subsp	var	form
FORM	Espeletia	almorzana	NA	NA	latifolia
FORM	Espeletia	argentea	NA	NA	phaneractis
FORM	Espeletia	brassicoidea	NA	NA	constricta
FORM	Espeletia	brassicoidea	NA	NA	minorifolia
FORM	Espeletia	brassicoidea	NA	NA	fo. contracta
FORM	Espeletia	grandiflora	NA	NA	longiligulata
FORM	Espeletia	grandiflora	NA	NA	multiflora
FORM	Espeletia	grandiflora	NA	NA	fo. longiligulata
FORM	Espeletia	grandiflora	NA	NA	fo. multiflora
FORM	Espeletia	grandiflora	NA	NA	fo. reducta

Podemos concatenar las columnas genus y spec_epith para obtener los nombres de especies:

SELECT *, genus ||' '|| spec_epith species
FROM espeletia;

Table 13: Displaying records 1 - 10
rank	genus	spec_epith	subsp	var	form	species
FORM	Espeletia	almorzana	NA	NA	latifolia	Espeletia almorzana
FORM	Espeletia	argentea	NA	NA	phaneractis	Espeletia argentea
FORM	Espeletia	brassicoidea	NA	NA	constricta	Espeletia brassicoidea
FORM	Espeletia	brassicoidea	NA	NA	minorifolia	Espeletia brassicoidea
FORM	Espeletia	brassicoidea	NA	NA	fo. contracta	Espeletia brassicoidea
FORM	Espeletia	grandiflora	NA	NA	longiligulata	Espeletia grandiflora
FORM	Espeletia	grandiflora	NA	NA	multiflora	Espeletia grandiflora
FORM	Espeletia	grandiflora	NA	NA	fo. longiligulata	Espeletia grandiflora
FORM	Espeletia	grandiflora	NA	NA	fo. multiflora	Espeletia grandiflora
FORM	Espeletia	grandiflora	NA	NA	fo. reducta	Espeletia grandiflora

1.6.5 Sin tablas

En postgreSQL y SQLite, la clausula FROM no es obligatoría (Nota: no es el caso en todos los “sabores” de SQL)

SELECT '¡Viene de mi cerebro, no de una tabla!' AS "frase cualquiera";

Table 14: 1 records
frase cualquiera
¡Viene de mi cerebro, no de una tabla!

1.6.6 Crear una tabla en FROM

La clausula FROM usualmente contiene una tabla de la base de datos, pero no es una regla stricta, se pueden utilizar otras cosas.

Para mostrar un caso extremo en PostgreSQL (¡no funciona en SQLite!):

SELECT * 
FROM (VALUES('a',1),('b',2),('c',3)) AS tabla(letra, numero);

Table 15: 3 records
letra	numero
a	1
b	2
c	3

2 WHERE

La clausula WHERE permite filtrar las filas de una tabla, acepta tipos booleanos:

SELECT *
FROM countries
WHERE sur_america;

Table 16: 2 records
pais	sur_america	superficie_km2
Chile	TRUE	756626
Colombia	TRUE	1142000

2.1 WHERE y operadores de comparación

Si WHERE acepta tipos booleanos, también se pueden utilizar operadores de comparación booleanos(postgreSQL, SQLite

SELECT *
FROM countries
WHERE superficie_km2 < 1000000;

Table 17: 2 records
pais	sur_america	superficie_km2
Chile	TRUE	756626
Francia	FALSE	549134

2.1.1 `=`

SELECT *
FROM countries
WHERE pais = 'Chile';

Table 18: 1 records
pais	sur_america	superficie_km2
Chile	TRUE	756626

2.1.2 `<>` “es diferente de”

SELECT *
FROM countries
WHERE pais <> 'Chile';

Table 19: 2 records
pais	sur_america	superficie_km2
Colombia	TRUE	1142000
Francia	FALSE	549134

2.1.3 `IN`

SELECT *
FROM countries
WHERE pais IN ('Chile', 'Francia');

Table 20: 2 records
pais	sur_america	superficie_km2
Chile	TRUE	756626
Francia	FALSE	549134

2.2 WHERE y operadores booleanos

Se pueden utilizar operadores booleanos) para combinar condiciones:

SELECT *
FROM countries
WHERE sur_america AND superficie_km2 < 1000000;

Table 21: 1 records
pais	sur_america	superficie_km2
Chile	TRUE	756626

3 ORDER BY

3.1 Simple

Los resultados de una consulta se pueden ordenar con la clausula ORDER BY

Esta clausula puede tener las opciones DESC (descending) y ASC (ascending).

SELECT *
FROM countries
ORDER BY superficie_km2 ASC;

Table 22: 3 records
pais	sur_america	superficie_km2
Francia	FALSE	549134
Chile	TRUE	756626
Colombia	TRUE	1142000

SELECT *
FROM countries
ORDER BY superficie_km2 DESC;

Table 23: 3 records
pais	sur_america	superficie_km2
Colombia	TRUE	1142000
Chile	TRUE	756626
Francia	FALSE	549134

3.2 Multiple

Se pueden hacer sorteos complejos asociando varias variables

SELECT *
FROM countries
ORDER BY sur_america,superficie_km2 DESC;

Table 24: 3 records
pais	sur_america	superficie_km2
Francia	FALSE	549134
Colombia	TRUE	1142000
Chile	TRUE	756626

3.3 Nota sobre los ordenes de los datos

En las bases de datos SQL, el orden del los datos usualmente no es fijo, depende en parte de las estadísticas de uso de los datos. Entonces, cuidado, ¡applicar dos veces la misma consulta no resulta siempre en el mismo resultado en terminos de sorteo!

4 LIMIT

Siempre se puede limitar el numero de resultados en una consulta, para eso se utiliza la clausula LIMIT. Es una buena practica utilizar LIMIT para probar algunas consultas largas y complejas, para averiguar que los resultados parecen a lo que queremos.

SELECT *
FROM countries
LIMIT 2;

Table 25: 2 records
pais	sur_america	superficie_km2
Chile	TRUE	756626
Colombia	TRUE	1142000

5 Funciones agregativas

Las funciones agregrativas permiten tratar información sobre más de un registro (una fila) de una tabla. ## COUNT

COUNT permite contar las filas.

SELECT count(pais) num_pais
FROM countries;

Table 26: 1 records
num_pais
3

Esa consulta puede parecer un poco rara: ¿para que contar las filas de la columna país? y no de la tabla completa. Es cierto, poner el nombre de la columna tiene mucho más sentido cuando se utiliza con otras clausulas como DISTINCT o FILTER (que vamos a ver más tarde). Por esa razon tambien se utiliza seguido el *:

SELECT count(*)
FROM countries
WHERE sur_america;

Table 27: 1 records
count
2

5.1 GROUP BY

Podemos asociar todas las funciones agregativas con una clausula GROUP BY que permite agrupar las filas. La tabla de resultados tiene una fila por asociación unica de los valores que están en la clausula GROUP BY. Tambien es importante saber que cuando utilizamos la clausula GROUP BY, la clausula SELECT solo acepta las variables que están en la clausula GROUP BY o funciones agregativas.

Por ejemplo la tabla bogota_chingaza es un ejemplo simplificado de tabla de abundancia de especies en un formato de base de datos:

SELECT *
FROM bogota_chingaza;

Table 28: 5 records
unidad_muestreo	especie	abundancia
Bogotá	Canis lupus	3000
Bogotá	Felis catus	1000
Chingaza	Tremarctos ornatus	50
Chingaza	Canis lupus	100
Chingaza	Odocoileus virginianus	200

Para calcular el numero de especies por sitio:

SELECT unidad_muestreo,count(especie)
FROM bogota_chingaza
GROUP BY unidad_muestreo;

Table 29: 2 records
unidad_muestreo	count
Bogotá	2
Chingaza	3

5.2 SUM

Para calcular la suma de individuo por especie

SELECT especie ,sum(abundancia)
FROM bogota_chingaza
GROUP BY especie;

Table 30: 4 records
especie	sum
Tremarctos ornatus	50
Odocoileus virginianus	200
Canis lupus	3100
Felis catus	1000

5.3 AVG

Para calcular el promedio de abundancia de las especies en cada unidad de muestreo

SELECT unidad_muestreo ,AVG(abundancia)
FROM bogota_chingaza
GROUP BY unidad_muestreo;

Table 31: 2 records
unidad_muestreo	avg
Bogotá	2000.0000
Chingaza	116.6667

5.4 Concatenar texto

Tambien se puede agregar el texto de varios filas en asociación con GROUP BY

SELECT unidad_muestreo , STRING_AGG(especie, ', ') list_esp
FROM bogota_chingaza
GROUP BY unidad_muestreo;

Table 32: 2 records
unidad_muestreo	list_esp
Bogotá	Canis lupus, Felis catus
Chingaza	Tremarctos ornatus, Canis lupus, Odocoileus virginianus

5.5 Concatenar ARRAYS (PostgreSQL)

En postgreSQL existe una clase de objetos particulares que se llama “ARRAY” (https://www.postgresql.org/docs/current/arrays.html).

Podemos aggregar un array en asociación con la clausula GROUP BY:

SELECT especie , ARRAY_AGG(abundancia) list_abund
FROM bogota_chingaza
GROUP BY especie;

Table 33: 4 records
especie	list_abund
Tremarctos ornatus	{50}
Odocoileus virginianus	{200}
Canis lupus	{3000,100}
Felis catus	{1000}

5.6 HAVING

La clausula WHERE no funciona para probar condiciones sobre los valores agregados: hay que utilizar la clausula HAVING

SELECT especie , SUM(abundancia) tot_abund
FROM bogota_chingaza
GROUP BY especie
HAVING SUM(abundancia) >= 1000
;

Table 34: 2 records
especie	tot_abund
Canis lupus	3100
Felis catus	1000

Consultas simples en SQL

1 SELECT y FROM

1.1 Ejemplo basico de consulta

1.2 Uso de `*`

1.3 Sensibilidad a las mayusculas y uso de las comillas

1.3.1 Nombres de las clausulas

1.3.2 Comillas `"`

1.4 Cambiar el nombre de las columnas

1.5 Schemas

1.6 Añadir datos en SELECT

1.6.1 Añadir una columna

1.6.2 Añadir texto con comillas `'`

1.6.3 Añadir una columna de calculo

1.6.4 Utilizar `||`

1.6.5 Sin tablas

1.6.6 Crear una tabla en FROM

2 WHERE

2.1 WHERE y operadores de comparación

2.1.1 `=`

2.1.2 `<>` “es diferente de”

2.1.3 `IN`

2.2 WHERE y operadores booleanos

3 ORDER BY

3.1 Simple

3.2 Multiple

3.3 Nota sobre los ordenes de los datos

4 LIMIT

5 Funciones agregativas

5.1 GROUP BY

5.2 SUM

5.3 AVG

5.4 Concatenar texto

5.5 Concatenar ARRAYS (PostgreSQL)

5.6 HAVING

6 DISTINCT

7 CASE

Consultas simples en SQL

1 SELECT y FROM

1.1 Ejemplo basico de consulta

1.2 Uso de *

1.3 Sensibilidad a las mayusculas y uso de las comillas

1.3.1 Nombres de las clausulas

1.3.2 Comillas "

1.4 Cambiar el nombre de las columnas

1.5 Schemas

1.6 Añadir datos en SELECT

1.6.1 Añadir una columna

1.6.2 Añadir texto con comillas '

1.6.3 Añadir una columna de calculo

1.6.4 Utilizar ||

1.6.5 Sin tablas

1.6.6 Crear una tabla en FROM

2 WHERE

2.1 WHERE y operadores de comparación

2.1.1 =

2.1.2 <> “es diferente de”

2.1.3 IN

2.2 WHERE y operadores booleanos

3 ORDER BY

3.1 Simple

3.2 Multiple

3.3 Nota sobre los ordenes de los datos

4 LIMIT

5 Funciones agregativas

5.1 GROUP BY

5.2 SUM

5.3 AVG

5.4 Concatenar texto

5.5 Concatenar ARRAYS (PostgreSQL)

5.6 HAVING

6 DISTINCT

7 CASE

1.2 Uso de `*`

1.3.2 Comillas `"`

1.6.2 Añadir texto con comillas `'`

1.6.4 Utilizar `||`

2.1.1 `=`

2.1.2 `<>` “es diferente de”

2.1.3 `IN`