Structured Query Language

Una aproximación práctica

Diego Roselli [[email protected]]

Sentencia SELECT


SELECT <lista_tablas>
 FROM <nombre_tabla>
 [WHERE <condición>
 [GROUP BY <lista_columnas>]
 [HAVING <condición>]
 [ORDER BY <lista_columnas> | <lista_números_columna>]

Proyección


Todos los atributos 



SELECT * FROM libros

Seleccionando los atributos 



SELECT titulo, nro_lib, edicion, año_edic FROM libros

Valores únicos

Para evitar filas duplicadas en el resultado, se incluye la palabra DISTINCT en la sentencia SELECT. 


SELECT DISTINCT nro_ced
  FROM prestamos

Ejecutando múltiples sentencias

Para separar, se usa el carácter "punto y coma" (;) que en Mysql es requerido, aún cuando. 


SELECT * FROM libros;

SELECT titulo, nro_lib, edicion, año_edic FROM libros;

SELECT nro_ced, nro_ejem, fch_prest, fch_dev
  FROM prestamos;

Selección: cláusula WHERE


SELECT titulo, nro_lib, edicion
  FROM libros
 WHERE año_edic = 2000

Los literales alfanuméricos, deben escribirse entre comillas o apóstrofos.

En el caso de los númericos, Mysql admite que se escriban entre comillas o apóstrofos, pero otros fabricantes no.

Operadores relacionales

= igual
!= o <> distinto
> mayor
>= mayor o igual
< menor
<= menor o igual

Valores nulos (NULL)



SELECT nro_ejem, nro_ced, fch_prest, fch_vto
  FROM prestamos
 WHERE fch_dev IS NULL;
					

SELECT *
  FROM prestamos
 WHERE fch_dev IS NOT NULL;

Si el valor de una columna puede ser nulo, se requiere un manejo especial.

Deben usarse las expresiones IS NULL o IS NOT NULL

Palabras clave de la cláusula WHERE


AND Operador lógico
OR Operador lógico
[NOT] BETWEEN Encuentra un rango de valores
[NOT] IN Encuentra un subconjunto de valores
IS [NOT] NULL Identifica valores nulos
[NOT] LIKE Realiza búsquedas de texto variables

AND y OR



SELECT *
  FROM libros
 WHERE edición = 1 AND año_edic > 2000
					


SELECT *
  FROM libros
 WHERE edición > 1 OR año_edic < 2000

Rangos y subconjuntos



SELECT *
  FROM libros
 WHERE año_edic BETWEEN 2000 AND 2005
					


SELECT *
  FROM libros
 WHERE año_edic IN (2000, 2001, 2002, 2003, 2004, 2005)

Operadores de búsqueda de caracteres


CarácterSignificado
% Reemplazo de uno más caracteres
_ Reemplazo de un carácter
\ Especifica que el carácter que sigue a continuación, es un literal

LIKE



SELECT ape1_lec, nom1_lec, nro_ced
  FROM lectores
 WHERE ape1_lec LIKE "GON%"

Se obtendrán los lectores cuyo primer apellido empiece con "GON" 


SELECT ape1_lec, nom1_lec, nro_ced
  FROM lectores
 WHERE ape1_lec LIKE "PE_A"

El guion bajo sustituye cualquier carácter. Tanto PEÑA como PENA satisfacen la condición.

Cláusula ORDER BY


SELECT ape1_lec, nom1_lec, nro_ced
  FROM lectores
 ORDER BY  ape1_lec, nom1_lec

Y es lo mismo que: 


SELECT ape1_lec, nom1_lec, nro_ced
  FROM lectores
 ORDER BY 1, 2

También se puede obtener un orden descendente: 


SELECT nro_ced, ape1_lec, nom1_lec
  FROM lectores
 ORDER BY nro_ced DESC

Expresiones aritméticas



SELECT NroArticulo, NombreArticulo, precio, precio * 1.22
  FROM articulos
 ORDER BY precio DESC

Funciones


ROUND, TRUNC son ejemplos, pero en particular, en Mysql, hay muchas otras. 


SELECT NroArticulo, NombreArticulo, precio, 
ROUND(precio * 1.22, 2),
TRUNC (precio * 1.22, 2)
  FROM articulos
 ORDER BY precio DESC

Etiquetas



SELECT NroArticulo, NombreArticulo, precio, precio * 0.22 IVA
  FROM articulos
 ORDER BY IVA

La etiqueta IVA reemplazá el encabezado predefinido de la consulta.

Función COUNT



SELECT COUNT(*) 
  FROM libros;

Se cuenta la cantidad de tuplas de la tabla libros.

Función SUM



SELECT SUM(precio)
  FROM articulos

Se suman los precios de todos los artículos.



SELECT SUM(precio * 0.22) IVA_total
  FROM articulos

Se suma el total de IVA.

Otras Funciones de agregación



SELECT MAX(precio) FROM articulos;

Precio máximo.



SELECT MIN(precio) FROM articulos;

Precio mínimo.



SELECT AVG(precio) FROM articulos;

Precio promedio.

Resumen de funciones de agregación

COUNT(*) Cuenta las filas que satisfacen la condición.
COUNT (DISTINCT <columna>) Cuenta la cantidad de valores únicos de <columna>, que satisfacen la condición.
SUM(<columna> / <expresión>) Suma los valores de la columna dada, o de la expresión, para las filas que satisfacen la condición.
SUM(DISTINCT <columna>) Suma los valores únicos de la columna dada, para las filas que satisfacen la condición.

Resumen de funciones de agregación

AVG(<columna> / <expresión>) Obtiene el promedio de los valores de la columna dada, o de la expresión, para las filas que satisfacen la condición.
AVG(DISTINCT <columna>) Obtiene el promedio de los valores únicos de la columna dada, para las filas que satisfacen la condición.
MAX(<columna> / <expresión>) Obtiene el valor máximo de la columna dada, o de la expresión, para las filas que satisfacen la condición.
MIN(<columna> / <expresión>) Obtiene el valor mínimo de la columna dada, o de la expresión, para las filas que satisfacen la condición.

GROUP BY

Produce una única fila de resultados, para cada grupo de filas con el mismo valor en una columna dada.

La cantidad de grupos depende de la cantidad de valores únicos de esa columna.


SELECT nro_ejem, COUNT(*) 
FROM prestamos
GROUP BY nro_ejem

De forma similar, ordenando por ejemplar: 


SELECT nro_ejem, COUNT(*) 
FROM prestamos
GROUP BY 1
ORDER BY 1;

GROUP BY


Y ordenando por la cantidad, descendente, obtenemos una lista de los ejemplares más prestados... 


SELECT nro_ejem, COUNT(*) 
FROM prestamos
GROUP BY 1
ORDER BY 2 DESC;

Cláusula HAVING

Permite aplicar condiciones sobre los grupos, después de que son creados.

Esta cláusula va, necesariamente, en conjunción con GROUP BY.



SELECT nro_ejem, COUNT(*)
FROM prestamos
GROUP BY 1
HAVING COUNT(*) > 10
ORDER BY 2 DESC;

Se obtienen los ejemplares prestados más de diez veces.

Varias tablas

Dos tablas


SELECT título, edición, fch_res
  FROM libros
 INNER JOIN reservas
    ON reservas.nro_lib = libros.nro_lib
 WHERE estado = 'Cumplida';

Más tablas


SELECT ape1_lec, nom1_lec, reservas.nro_ced, título, edición, fch_res
  FROM libros
 INNER JOIN reservas
    ON reservas.nro_lib = libros.nro_lib
 INNER JOIN lectores
    ON reservas.nro_ced = lectores.nro_ced
 WHERE estado = 'Cumplida';

Outer Joins



SELECT título, edición, cod_isbn, nom_edit
  FROM libros
  LEFT OUTER JOIN editoriales
    ON editoriales.nro_edit = libros.nro_edit
 WHERE año_edic = 2000

El join devolvería las tuplas de los libros que tienen editorial.

El outer join (reunión externa) recuperará todas las tuplas de libros. En aquellas en las que no exista, los datos de la editorial saldrán con valores nulos.

SELF-Join



SELECT l1.título, l1.nro_lib, l2.nro_lib
  FROM libros l1
 INNER JOIN libros l2
    ON l1.nro_edit = l2.nro_edit
 WHERE l1.nro_lib != l2.nro_lib

Usamos alias para renombrar la tabla (l1, l2).

UNION


SELECT titulo, cod_isbn
  FROM reservas
  INNER JOIN libros
  ON reservas.nro_lib = libros.nro_lib

UNION

SELECT titulo, cod_isbn
  FROM prestamos
 INNER JOIN ejemplares
 ON ejemplares.nro_ejem = prestamos.nro_ejem
 INNER JOIN libros
 ON ejemplares.nro_lib = libros.nro_lib
ORDER BY 1;

Los libros prestados o reservados.

UNION ALL


SELECT título, cod_isbn
  FROM reservas
 INNER JOIN libros
 ON reservas.nro_lib = libros.nro_lib

UNION ALL

SELECT título, cod_isbn
  FROM prestamos
 INNER JOIN ejemplares
 ON ejemplares.nro_ejem = prestamos.nro_ejem
 INNER JOIN libros
 ON ejemplares.nro_lib = libros.nro_lib
ORDER BY 1;

ALL hace que sí aparezcan las filas que están repetidas.

Creando una columna «al vuelo»


SELECT título, cod_isbn, 'Reserva' tipo
  FROM reservas
  INNER JOIN libros
  ON reservas.nro_lib = libros.nro_lib

UNION

SELECT título, cod_isbn, 'Prestamo' tipo
  FROM prestamos
 INNER JOIN ejemplares
 ON ejemplares.nro_ejem = prestamos.nro_ejem
 INNER JOIN libros
 ON ejemplares.nro_lib = libros.nro_lib
ORDER BY 1;

Subqueries

Sentencias SELECT anidadas 


SELECT título, cod_isbn 
 FROM libros 
 INNER JOIN autores_libros 
 ON autores_libros.nro_lib = libros.nro_lib 
 INNER JOIN autores on autores.nro_aut = autores_libros.nro_aut 
 WHERE autores_libros.nro_aut = (
	SELECT nro_aut 
	FROM autores 
	WHERE nom_aut = 'TRAVERSONI, ALFREDO'
 );

El SELECT interior es ejecutado primero. No debe contener ORDER by y devuelve una sola columna, expresión o agregación.

No olvidar poner los subqueries entre paréntesis.

Si el subquery devuelve múltiples filas

No se puede usar un operador de comparación (=, >, < != ...).

Debe usarse «IN» o «NOT IN». 


SELECT título, cod_isbn 
 FROM libros 
 INNER JOIN autores_libros 
 ON autores_libros.nro_lib = libros.nro_lib 
 INNER JOIN autores on autores.nro_aut = autores_libros.nro_aut 
 WHERE autores_libros.nro_aut IN (
	SELECT nro_aut 
	FROM autores 
	WHERE nom_aut LIKE 'TRAV%'
 );

[NOT] EXISTS

Similar a IN. Se verifica si existe al menos una fila como resultado del subquery.

Veamos los lectores que no han hecho reservas, usando NOT IN:


SELECT ape1_lec, nom1_lec, nro_ced
FROM lectores
WHERE nro_ced NOT IN (
	SELECT nro_ced
	FROM reservas
)

[NOT] EXISTS

Los lectores que no han hecho reservas, usando NOT EXISTS:


SELECT ape1_lec, nom1_lec, nro_ced
FROM lectores
WHERE NOT EXISTS (
	SELECT * FROM reservas
	WHERE reservas.nro_ced = lectores.nro_ced
)

The End

Ejercicios

Ejercicio 1

Obtener una lista de libros ordenada por autor, con la cantidad de ejemplares de cada uno. Datos: nombre del autor, título, edición, año, nombre de la editorial y la cantidad de ejemplares. 


SELECT nom_aut, título, edición, año_edic, nom_edit, COUNT(*) cantidad
FROM autores_libros
INNER JOIN autores
ON autores.nro_aut = autores_libros.nro_aut
INNER JOIN libros
ON autores_libros.nro_lib = libros.nro_lib
INNER JOIN ejemplares
ON ejemplares.nro_lib = libros.nro_lib
INNER JOIN editoriales
ON editoriales.nro_edit = libros.nro_edit
GROUP BY 1,2,3,4,5
ORDER BY nom_aut;

Ejercicio 2

Obtener apellidos, nombres y número de cédula, de la persona que más atrasos tuvo en el mes de febrero de 2010.


SELECT ape1_lec, ape2_lec, nom1_lec, nom2_lec, COUNT(*) atrasos
FROM prestamos
INNER JOIN lectores
ON lectores.nro_ced = prestamos.nro_ced
WHERE fch_dev > fch_vto
  AND fch_prest BETWEEN "2010-02-01" AND "2010-02-28"
GROUP BY 1,2,3,4
ORDER BY 5 DESC
LIMIT 1;

Ejercicio 3

Obtener una lista alfabética, de lectores que no tuvieron ningún atraso en las devoluciones de préstamos del mes de febrero de 2010, o tuvieron a lo sumo una. Datos: apellidos, nombres y cantidad de atrasos.


SELECT ape1_lec, ape2_lec, nom1_lec, nom2_lec, COUNT(*) cantidadAtrasos
FROM prestamos
INNER JOIN lectores
ON lectores.nro_ced = prestamos.nro_ced
WHERE fch_dev > fch_vto
  AND fch_prest BETWEEN "2010-02-01" AND "2010-02-28"
GROUP BY 1, 2, 3, 4
HAVING COUNT(*) < 2
UNION
SELECT ape1_lec, ape2_lec, nom1_lec, nom2_lec, 0 cantidadAtrasos
FROM lectores
WHERE nro_ced NOT IN (
  SELECT nro_ced 
    FROM prestamos
  WHERE fch_dev > fch_vto
    AND fch_prest BETWEEN "2010-02-01" AND "2010-02-28"
  )
ORDER BY 1;

Ejercicio 4

Obtener los libros que nunca han sido reservados. Datos: título, edición, año de edición y cantidad de ejemplares. Orden: cantidad de ejemplares.


SELECT título, edición, año_edic, COUNT(*) cantEjemplares
FROM libros
INNER JOIN ejemplares
ON ejemplares.nro_lib = libros.nro_lib
WHERE libros.nro_lib NOT IN (
	SELECT nro_lib FROM reservas
)
GROUP BY 1,2,3
ORDER BY 4,1;

Ejercicio 5

Obtener una lista de libros con la cantidad de préstamos que tiene cada uno. La lista debe obtenerse ordenada por cantidad de préstamos en orden descendente, y después por orden alfabético del título. Datos: título, nombre del autor, edición, año, nombre de la editorial y la cantidad de préstamos. 


SELECT título Titulo, IFNULL(nom_aut, 'Anonimo') Autor, edición,
 año_edic Año, IFNULL(nom_edit, 'S/edit.') Editorial,
 COUNT(*) Cantidad
 FROM prestamos
 INNER JOIN ejemplares
 ON ejemplares.nro_ejem = prestamos.nro_ejem
 INNER JOIN libros
 ON libros.nro_lib = ejemplares.nro_lib
 LEFT OUTER JOIN autores_libros
 ON libros.nro_lib = autores_libros.nro_lib
 LEFT OUTER JOIN autores
 ON autores.nro_aut = autores_libros.nro_aut
 LEFT OUTER JOIN editoriales
 ON editoriales.nro_edit = libros.nro_edit
GROUP BY 1,2,3,4,5
ORDER BY 6 DESC, 1

Ejercicio 6

Obtener un comparativo de cantidades de préstamos realizados mes a mes, entre los años 2010 y 2011. 


CREATE TEMPORARY TABLE anio1
 SELECT YEAR(fch_prest) anio, MONTH(fch_prest) mes, COUNT(*) cantidad
  FROM prestamos 
 WHERE YEAR(fch_prest) = 2010
 GROUP BY 1,2;

CREATE TEMPORARY TABLE anio2
 SELECT YEAR(fch_prest) anio, MONTH(fch_prest) mes, COUNT(*) cantidad
  FROM prestamos 
 WHERE YEAR(fch_prest) = 2011
 GROUP BY 1,2;

SELECT
 CASE anio1.mes
   WHEN 1 THEN 'Ene'
   WHEN 2 THEN 'Feb'
   WHEN 3 THEN 'Mar'
   WHEN 4 THEN 'Abr'
   WHEN 5 THEN 'May'
   WHEN 6 THEN 'Jun'
   WHEN 7 THEN 'Jul'
   WHEN 8 THEN 'Ago'
   WHEN 9 THEN 'Set'
   WHEN 10 THEN 'Oct'
   WHEN 11 THEN 'Nov'
   WHEN 12 THEN 'Dic'
 END mes,
 anio1.cantidad Anio2010,
 IFNULL(anio2.cantidad, 0) Anio2011,
 IFNULL(anio2.cantidad, 0)-anio1.cantidad Diferencia, 
 CONCAT((IFNULL(anio2.cantidad, 0)/anio1.cantidad-1)*100, '%') Variacion 
 FROM anio1
 LEFT JOIN anio2
 ON anio1.mes = anio2.mes
ORDER BY anio1.mes;

--
-- Otra forma
--
 SELECT ELT(anio1.mes, 'Ene', 'Feb', 'Mar', 'Abr', 'May', 'Jun', 'Jul', 'Ago', 'Set', 'Oct', 'Nov', 'Dic') mes, 
 anio1.cantidad Anio2010,
 IFNULL(anio2.cantidad, 0) Anio2011,
 IFNULL(anio2.cantidad, 0)-anio1.cantidad Diferencia, 
 CONCAT ((IFNULL(anio2.cantidad, 0)/anio1.cantidad-1)*100, '%') Variacion 
 FROM anio1
 LEFT JOIN anio2
 ON anio1.mes = anio2.mes
ORDER BY anio1.mes

Ejercicio 7

Emitir un reporte que muestre los “movimientos” de préstamos y reservas de cada lector, ocurridos en el mes de febrero de 2010. Los datos deben ser: apellido y nombre del lector, cédula, fecha del préstamo o reserva y título del libro. Debe mostrarse ordenado por lector y por fecha, y cada línea debe especificar si el movimiento es de préstamo o reserva. 


SET @fch_ini = '2010-02-01';
SET @fch_fin = '2010-02-28';

SELECT ape1_lec Apellido, nom1_lec Nombre, reservas.nro_ced CI,
       fch_res Fecha, 'Reserva' Movimiento, título Titulo
 FROM reservas
 INNER JOIN lectores
 ON lectores.nro_ced = reservas.nro_ced
 INNER JOIN libros
 ON libros.nro_lib = reservas.nro_lib
 WHERE fch_res BETWEEN @fch_ini AND @fch_fin
UNION
SELECT ape1_lec Apellido, nom1_lec Nombre, prestamos.nro_ced CI,
       fch_prest Fecha, 'Prestamo' Movimiento, título Titulo
 FROM prestamos
 INNER JOIN ejemplares
 ON prestamos.nro_ejem = ejemplares.nro_ejem
 INNER JOIN libros
 ON libros.nro_lib = ejemplares.nro_lib
 INNER JOIN lectores
 ON lectores.nro_ced = prestamos.nro_ced
 WHERE fch_prest BETWEEN @fch_ini AND @fch_fin
ORDER BY 1,2,4,5,6
;

Ejercicio 8

Obtener los veinte libros que han sido más reservados. Datos necesarios: número de libro, título, autor, edición, año de edición, cantidad de reservas y cantidad de ejemplares. Cuando no hay autor, no debe haber nada en la columna. Orden: cantidad de reservas y número de libro. 


SELECT reservas.nro_lib,
 título,
 edición,
 año_edic,
 IFNULL(nom_aut, '') Autor,
 COUNT(*) `Cantidad reservas`,
 ( SELECT COUNT(*)
  FROM ejemplares
  WHERE nro_lib = reservas.nro_lib ) AS `Cantidad ejemplares`
 FROM reservas
 INNER JOIN libros
 ON libros.nro_lib = reservas.nro_lib
 LEFT OUTER join autores_libros
 ON autores_libros.nro_lib = libros.nro_lib
 LEFT OUTER JOIN autores
 ON autores_libros.nro_aut = autores.nro_aut
GROUP BY 1,2,3,4,5,7
ORDER BY 6 DESC, 1
LIMIT 20;

Ejercicio 9

Obtener los libros que no hayan tenido más de treinta préstamos. Datos necesarios: número de libro, título, autor, edición, año de edición, cantidad de préstamos y fecha del último préstamo. Cuando no hay autor, debe figurar “Anónimo”. El orden debe ser por la cantidad de préstamos, ascendente. 


SELECT ejemplares.nro_lib, título, edición, año_edic,
 IFNULL(nom_aut, 'Anónimo') Autor,
 COUNT(*) `Cantidad préstamos`,
 MAX(fch_prest) `Fecha último préstamo`
FROM prestamos
INNER JOIN ejemplares
ON ejemplares.nro_ejem = prestamos.nro_ejem
INNER JOIN libros
ON libros.nro_lib = ejemplares.nro_lib
LEFT OUTER join autores_libros
ON autores_libros.nro_lib = libros.nro_lib
LEFT OUTER JOIN autores
ON autores_libros.nro_aut = autores.nro_aut
GROUP BY 1,2,3,4,5
HAVING COUNT(*) <= 30
UNION
SELECT ejemplares.nro_lib, título, edición, año_edic,
IFNULL(nom_aut, 'Anónimo') Autor,
0, ''
FROM ejemplares
 INNER JOIN libros
 ON libros.nro_lib = ejemplares.nro_lib
 LEFT OUTER join autores_libros
 ON autores_libros.nro_lib = libros.nro_lib
 LEFT OUTER JOIN autores
 ON autores_libros.nro_aut = autores.nro_aut
WHERE nro_ejem NOT IN (
    SELECT nro_ejem
    FROM prestamos
    )
ORDER BY 6,1

Ejercicio 10

Obtener todos los libros de la editorial que tiene la mayoría de los libros de la biblioteca. Los datos necesarios son: número de libro, título, edición, año de edición, nombre de la editorial. El orden debe ser por título alfabético. 


-- Una temporal para obtener una única fila:
-- la de la editorial que tiene la mayoría de los libros.
CREATE TEMPORARY TABLE LibrosPorEditorial
SELECT nro_edit, COUNT(*) cantidad
FROM libros
WHERE nro_edit IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1
;
SELECT nro_lib, título, edición, año_edic, nom_edit
FROM libros
INNER JOIN editoriales
ON editoriales.nro_edit = libros.nro_edit
INNER JOIN LibrosPorEditorial
ON LibrosPorEditorial.nro_edit = libros.nro_edit
ORDER BY 2
;

-- Otra solución, con subquery.
SELECT nro_lib, título, edición, año_edic, nom_edit
FROM libros
INNER JOIN editoriales
ON editoriales.nro_edit = libros.nro_edit
WHERE libros.nro_edit = (
    SELECT nro_edit
    FROM libros
    GROUP BY nro_edit
    ORDER BY COUNT(*) DESC
    LIMIT 1
)
ORDER BY 2