7. SQL Básico

SQL, o «Lenguaje de consulta estructurado», es un medio para formular preguntas y actualizar datos en bases de datos relacionales. Ya has visto SQL cuando creamos nuestra primera base de datos. Recuerde:

SELECT postgis_full_version();

Pero ésa era una pregunta sobre la base de datos. Ahora que hemos cargado datos en nuestra base de datos, vamos a utilizar SQL para hacer preguntas a los datos. Por ejemplo,

«¿Cómo se llaman los barrios de Nueva York?»

Abra la ventana de consulta SQL en pgAdmin haciendo clic en el botón «Herramienta de consulta».

Luego, introduzca la siguiente consulta en la ventana de consulta

SELECT name FROM nyc_neighborhoods;

y haga clic en el botón Ejecutar consulta (el botón Play).

La consulta se ejecutará durante unos (mili)segundos y devolverá los 129 resultados.

Pero, ¿qué ha ocurrido exactamente? Para entenderlo, empecemos por los cuatro «verbos» de SQL,

• SELECT, devuelve las filas que cumplen los criterios de una consulta

• INSERT, añade nuevas filas a una tabla

• UPDATE, modifica las filas existentes en una tabla

• DELETE, elimina filas de una tabla

Trabajaremos casi exclusivamente con SELECT para hacer preguntas a tablas utilizando funciones espaciales.

7.1. Consultas SELECT

Una consulta SELECT suele tener la forma:

SELECT some_columns FROM some_data_source WHERE some_condition;

Nota

Para una sinopsis de todos los parámetros SELECT, véase la documentación PostgresSQL.

some_columns puede ser nombres de columnas o funciones que se aplican a los valores de las columnas . some_data_source puede ser una tabla o una tabla compuesta a partir de «juntar» dos tablas usando una llave o una condición. some_condition es un filtro que restringe el número de filas retornadas.

«¿Cuál es el nombre de los barrios de Brooklyn?».

Volvemos a nuestra tabla nyc_neighborhoods pero utilizando un filtro. La tabla contiene todos los barrios de Nueva York, pero sólo queremos los de Brooklyn.

SELECT name
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

La consulta durará aún menos (mili)segundos y devolverá los 23 resultados.

A veces necesitaremos aplicar una función a los resultados de nuestra consulta. Por ejemplo,

«¿Cuántas letras tienen los nombres de cada uno de los barrios de Brooklyn?».

Afortunadamente, PostgreSQL tiene una función que calcula la longitud de una cadena de texto, char_length(string).

SELECT char_length(name)
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

A menudo, no nos interesan las filas individuales sino una estadística que se aplique a todas ellas. Así, conocer la longitud de los nombres de los barrios puede ser menos interesante que conocer la longitud media de los nombres. Las funciones que toman varias filas y devuelven un único resultado se denominan funciones «agregadas» o de «agregación».

PostgreSQL dispone de una serie de funciones de agregación integradas, entre las que se incluyen avg() para valores medios y stddev() para desviaciones estándar.

«¿Cuál es la media estándar de la longitud de letras en los nombres de todos los barrios de Brooklyn?».

SELECT avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

         avg         |       stddev
---------------------+--------------------
 11.7391304347826087 | 3.9105613559407395

Las funciones de agregación de nuestro último ejemplo se aplicaron a todas las filas del conjunto de resultados. ¿Qué ocurre si queremos que los resúmenes se realicen sobre grupos más pequeños dentro del conjunto de resultados global? Para ello añadimos una cláusula GROUP BY. Las funciones agregadas a menudo necesitan una sentencia GROUP BY añadida para agrupar el conjunto de resultados por una o más columnas.

«¿Cuál es la media de la cantidad de letras en los nombres de todos los barrios de Nueva York, desglosado por distritos?»

SELECT boroname, avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  GROUP BY boroname;

Incluimos la columna boroname en el resultado de salida para poder determinar qué estadística se aplica a qué distrito. En una consulta agregada, sólo se pueden mostrar columnas que sean (a) miembros de la cláusula de agrupación o (b) funciones agregadas.

   boroname    |         avg         |       stddev
---------------+---------------------+--------------------
 Brooklyn      | 11.7391304347826087 | 3.9105613559407395
 Manhattan     | 11.8214285714285714 | 4.3123729948325257
 The Bronx     | 12.0416666666666667 | 3.6651017740975152
 Queens        | 11.6666666666666667 | 5.0057438272815975
 Staten Island | 12.2916666666666667 | 5.2043390480959474

7.2. Lista de funciones

avg(expresión): Función de agregación de PostgreSQL que devuelve la media de una columna numérica.

char_length(cadena): Función de PostgreSQL que devuelve el número de caracteres de una cadena.

stddev(expresión): Función de agregación de PostgreSQL que devuelve la desviación estándar de los valores de entrada.