19 Ejercicios de Funciones Agregadas

Resuelva estos 19 ejercicios de funciones agregadas de SQL y perfeccione sus conocimientos de SQL. Practique el uso de funciones agregadas con GROUP BY, HAVING, subconsultas y mucho más. Incluye una solución y una explicación detallada para cada ejercicio.

Las funciones agregadas son una parte importante de SQL. Te permiten calcular diferentes estadísticas y generar informes que no habrías podido hacer con operaciones de una sola fila como el filtrado. Es importante practicar las funciones agregadas a menudo, ya que son una parte crucial de tus habilidades SQL. Empecemos

Este artículo incluye 19 nuevos ejercicios de funciones agregadas utilizando nuestra base de datos de bibliotecas. Cubriremos la agrupación con GROUP BY, el filtrado de datos con HAVING, el uso de funciones de agregación dentro de subconsultas y la agregación multinivel. También refrescaremos sus conocimientos sobre el trabajo con JOINs y NULLs y sobre el ordenamiento de datos por varias estadísticas.

Una vez que esté listo para aplicar estos conceptos en escenarios reales, consulte nuestro curso de Informes SQL Básicos. Cubre todos los conceptos de funciones agregadas que practicarás en este artículo, además de otros temas importantes como el uso de CASE WHEN.

También te recomendamos que pruebes nuestros retos mensuales en Práctica mensual de SQL Track. Se trata de una colección de ejercicios prácticos, que se publican todos los meses, diseñados específicamente para mantener sus conocimientos de SQL actualizados.

¿Qué son las funciones agregadas?

Lasfunciones agregadas realizan cálculos sobre un conjunto de valores y devuelven un único valor como resultado. Funciones agregadas comunes son:

• SUM() - Calcula la suma de todos los valores de cada grupo.
• AVG() - Calcula el valor medio de todos los valores de cada grupo.
• COUNT() - Devuelve el número de valores de cada grupo.
• MIN() y MAX() - Devuelven los valores más pequeño y más grande (respectivamente) de cada grupo.

Estas funciones son especialmente útiles cuando se crean informes en los que es necesario calcular varias métricas.

¿Necesita una explicación más detallada? Consulte nuestra hoja de trucos sobre funciones agregadas SQL, que cubre todas las funciones agregadas, sus casos de uso y sus interacciones con GROUP BY.

¿Por qué Practicar las Agregaciones SQL?

La práctica regular le ayuda a mantener sus habilidades afiladas, permitiéndole analizar y manipular datos más rápida y eficientemente. A medida que practique más, podrá resolver problemas más complejos y optimizar mejor sus consultas.

Practicar SQL es vital si quieres trabajar como analista de datos, desarrollador de bases de datos o en cualquier otro puesto en el que se manejen muchos datos. Consulte nuestro artículo sobre ejercicios GROUP BY para perfeccionar sus habilidades de creación de informes.

El conjunto de datos

Echemos un vistazo al conjunto de datos con el que trabajaremos para estas preguntas de práctica.

El conjunto de datos consta de cinco tablas: book, author, book_author, patron, y book_loan. Este es el esquema:

La información sobre los libros se almacena en la tabla book. Tiene las siguientes columnas:

• book_id - Un ID único para cada libro y la clave principal de la tabla.
• title - El título del libro.
• publication_year - El año de publicación del libro. Puede ser NULL si se desconoce.
• genre - El género del libro, por ejemplo, "Fantasía" o "Misterio".
• pages - El número de páginas del libro.

He aquí una instantánea de los datos de la tabla:

book_id	title	publication_year	genre	pages
1	1984	1949	Political Fiction	328
2	Animal Farm	1945	Political Fiction	112
3	The Hobbit	1937	Fantasy	310
4	The Fellowship of the Ring	1954	Fantasy	423

La información sobre los autores se almacena en la tabla author. Tiene las siguientes columnas:

• author_id - Un ID único para cada autor y la clave primaria de la tabla.
• author_name - Nombre completo o seudónimo del autor.
• country - País del autor.

Estos son algunos de los datos de la tabla:

author_id	author_name	country
1	George Orwell	United Kingdom
2	J.R.R. Tolkien	United Kingdom
3	Isaac Asimov	United States
4	Agatha Christie	United Kingdom

En la tabla se almacenan datos sobre las personas que toman libros prestados de la biblioteca patron. Tiene las siguientes columnas:

• patron_id - Un identificador único para cada usuario y la clave principal de la tabla.
• patron_name - El nombre completo del usuario.
• registration_date - La fecha en la que el usuario se registró en el sistema de bibliotecas.

Estos son algunos de los datos de la tabla:

patron_id	patron_name	registration_date
1	Alice Johnson	2024-01-15
2	Bob Smith	2024-03-22
3	Charlie Brown	2024-05-10
4	David Wilson	2024-06-01

La relación de muchos a muchos entre los autores y los libros que escribieron se almacena en la tabla book_author tabla. Tiene las siguientes columnas

• book_author_id - Un ID único para cada par libro-autor y la clave primaria de la tabla.
• author_id - El ID del autor.
• book_id - El ID del libro que escribió el autor.

Estos son algunos de los datos de la tabla:

book_author_id	author_id	book_id
1	1	1
2	1	2
3	2	3
4	2	4

La relación múltiple entre los usuarios y los libros que han tomado prestados se almacena en la tabla book_loan tabla. Tiene las siguientes columnas:

• loan_id - Un ID único para cada préstamo y la clave primaria de la tabla.
• book_id - El ID del libro prestado.
• patron_id - El ID del usuario que tomó prestado el libro.
• loan_date - La fecha en que se emitió el préstamo del libro.
• due_date - La fecha en la que debe devolverse el libro.
• return_date - La fecha real de devolución del libro.

Estos son algunos de los datos de la tabla:

loan_id	book_id	patron_id	loan_date	due_date	return_date
1	1	1	2024-01-20	2024-02-20	2024-02-15
2	8	1	2024-02-01	2024-03-01	2024-02-28
3	3	2	2024-02-10	2024-03-10	2024-03-05
4	4	3	2024-03-15	2024-04-10	2024-04-15

Vuelve a esta sección si olvidas los nombres de las tablas o columnas al resolver los ejercicios. Si necesita consejos de sintaxis, tenga a mano nuestra hoja de trucos SQL para análisis de datos. Cubre todas las herramientas que pueden ayudarte a resolver estos ejercicios. Incluso puedes descargarla en formato PDF e imprimirla, ¡para que te ayude con tus futuros ejercicios!

Practica estas Funciones Agregadas SQL

Resuelve los ejercicios por tu cuenta, luego revisa las soluciones debajo de cada ejercicio. También hay explicaciones adicionales para cada solución si te quedas atascado.

Ejercicio 1: Libros en el Sistema

Ejercicio: Contar el número de libros registrados en la base de datos.

Solución:

SELECT COUNT(book_id)
FROM book;

Explicación: Utilizamos la función COUNT() para obtener el número de filas de la book tabla.

Observe dos cosas. En primer lugar, la consulta no tiene la cláusula WHERE, por lo que no se filtra ninguna fila; se cuentan todas las filas de la tabla. En segundo lugar, la consulta no tiene la cláusula GROUP BY. Cuando se utiliza una función agregada sin GROUP BY, todas las filas se colocan en un grupo y la función se aplica a todas las filas de este grupo. Por lo tanto, nuestra consulta cuenta todas las filas de la tabla book tabla.

Ejercicio 2: Libros no devueltos

Ejercicio: Contar cuántos libros aún no han sido devueltos (es decir, libros que no tienen fecha de devolución).

Solución:

SELECT COUNT(loan_id)
FROM book_loan
WHERE return_date IS NULL;

Explicación: Los libros que no han sido devueltos no tendrán un return_date en la book_loan su return_date es NULL. Utilizamos esta condición en la cláusula WHERE para seleccionar sólo los libros que aún no han sido devueltos.

Utilizamos la función COUNT() para contar el número de filas del conjunto de datos resultante. Tenga en cuenta que el argumento utilizado con la función COUNT() es importante. Aquí ponemos loan_id como argumento, lo que indica a la base de datos que cuente todos los valores de las columnas loan_id.

Alternativamente, podríamos haber utilizado COUNT(*) y simplemente contar todas las filas del conjunto de resultados, dando el mismo resultado. Sin embargo, COUNT(return_date) no sería apropiado: el resultado sería 0. Todos los return_dates en el resultado son NULL debido a la condición return_date IS NULL.

De nuevo, no hay GROUP BY en esta consulta, por lo que la función COUNT() contará todos los préstamos en los que la fecha de devolución esté vacía.

Ejercicio 3: Libros por género

Ejercicio: Para cada género, muestre el nombre del género y el número de libros de ese género.

Solución:

SELECT
  genre,
  COUNT(book_id)
FROM book
GROUP BY genre;

Explicación: Este es el ejercicio más básico de GROUP BY. De la tabla bookseleccionamos el género.

Para asegurarnos de que la función COUNT() devuelve un resultado distinto para cada género, dividimos el conjunto de datos en grupos utilizando GROUP BY género. Esto creará grupos basados en los valores de la columna genre; los libros con el mismo valor en género van al mismo grupo.

La función COUNT() trabajará en cada grupo por separado, contando el número de libros en cada grupo.

Si necesita que le refresquemos la memoria sobre cómo trabajar con GROUP BY y las funciones agregadas, consulte nuestra Visión general completa de las funciones GROUP BY y agregadas.

Ejercicio 4: Autores por país

Ejercicio: Para cada país, muestre su nombre y el número de autores asociados a él.

Solución:

SELECT
  country,
  COUNT(author_id)
FROM author
GROUP BY country;

Explicación: Este es otro ejercicio básico de GROUP BY. Seleccionamos los datos de la tabla autor y los agrupamos por los valores de la columna country. Luego aplicamos COUNT(author_id) a cada grupo para contar los autores procedentes de ese país.

Ejercicio 5: Rangos de Páginas por Género

Ejercicio: Para cada género, muestre cuatro columnas: el nombre del género, el número mínimo y máximo de páginas de los libros de ese género, y el difference entre el mayor y el menor número de páginas de cada libro.

Solución:

SELECT
  genre,
  MIN(pages),
  MAX(pages),
  MAX(pages) - MIN(pages) AS difference
FROM book
GROUP BY genre;

Explicación: Este es otro ejercicio básico de GROUP BY. Seleccionamos los datos de la tabla autor y los agrupamos por los valores de la columna país. Luego aplicamos COUNT(author_id) a cada grupo para contar los autores que provienen de ese país.

Ejercicio 5: Rangos de páginas por género

Ejercicio: Para cada género, muestre cuatro columnas: el nombre del género, el número mínimo y máximo de páginas de los libros de ese género, y la diferencia entre el mayor y el menor número de páginas de cada libro.

Solución:

Explicación: Para obtener las estadísticas de cada género, agrupa los datos de la book tabla por la columna genre.

Utiliza las funciones de agregación MIN(pages) y MAX(pages) para calcular el número mínimo y máximo de páginas. En la tercera columna, utiliza MIN(pages) - MAX(pages) para calcular la diferencia de cada grupo. Finalmente, renombre la última columna a difference utilizando AS.

Ejercicio 6: Géneros de Libros Grandes

Ejercicio: Para cada género, muestre el número medio de páginas de todos los libros de ese género. Sólo muestre los géneros en los que el libro medio tiene más de 250 páginas. Nombre la columna de páginas promedio avg_pages.

Solución:

SELECT
  genre,
  AVG(pages) AS avg_pages
FROM book
GROUP BY genre
HAVING AVG(pages) >= 250;

Explicación: Este ejercicio es similar al anterior: agrupamos los libros por género y calculamos el número medio de libros de cada género utilizando AVG(). Sin embargo, aquí hay un elemento nuevo: HAVING. HAVING se utiliza para filtrar grupos y encontrar grupos para los que una función agregada satisface una determinada condición. En nuestro caso, buscamos grupos (géneros) en los que el número medio de páginas sea superior o igual a 250.

Recuerde que HAVING funciona de forma diferente a WHERE. WHERE se utiliza para filtrar filas individuales antes de agrupar, mientras que HAVING se utiliza para filtrar filas después de agrupar. Puede leer sobre la diferencia entre HAVING y WHERE en nuestro artículo HAVING vs. WHERE en SQL: Lo que debes saber.

Ejercicio 7: Géneros Modernos

Ejercicio: Muestre el año medio de publicación para cada género de libros. Redondee el año a un número entero. Muestre sólo los géneros cuyo año medio de publicación sea posterior a 1940.

Solución:

SELECT
  genre,
  ROUND(AVG(publication_year))
FROM book
GROUP BY genre
HAVING ROUND(AVG(publication_year)) > 1940;

Explicación: Este ejercicio es similar al anterior: agrupamos los libros por genre y calculamos el año medio de publicación mediante AVG(publication_year). A continuación, filtramos por géneros con un año medio de agregación superior a 1940 utilizando HAVING.

Ejercicio 8: Libros con varios autores

Ejercicio: Para los libros que han sido escritos por más de un autor, muestre el título de cada libro y el número de autores.

Solución:

SELECT 
  title,
  COUNT(author_id)
FROM book b
JOIN book_author ba
  ON b.book_id = ba.book_id
GROUP BY b.book_id
HAVING COUNT(author_id) > 1;

Explicación: En primer lugar, tenemos que encontrar los autores de cada libro. Para ello, unimos las tablas book y book_author en su dirección común book_id. Esto combinará los datos de cada libro con los datos de sus autores: una fila por cada combinación libro-autor.

A continuación, agrupamos las filas por book_id: todas las filas relacionadas con el mismo libro se encuentran en el mismo grupo. Así, todos los autores de cada libro estarán en el mismo grupo. A continuación, aplicamos la función COUNT(author_id) para contar los autores de cada grupo.

Por último, filtramos los libros con un solo autor utilizando HAVING COUNT(author_id) > 1.

Ejercicio 9: Último préstamo de cada libro

Ejercicio: Para cada libro, muestre su title y la fecha más reciente en que fue prestado. Nombre la segunda columna last_loaned. Muestre NULL en la segunda columna para todos los libros que nunca han sido prestados.

Solución:

SELECT
  book.title,
  MAX(book_loan.loan_date) AS last_loaned
FROM book
LEFT JOIN book_loan 
  ON book.book_id = book_loan.book_id
GROUP BY 
  book.book_id,
  book.title;

Explicación: Utilice LEFT JOIN para unir las columnas book y book_loan para asegurarse de que los libros que nunca se han prestado también se incluyen en el conjunto de resultados. Agrupe los resultados por book_id y book_title.

Tenga en cuenta que no debe agrupar el resultado sólo por el título; si dos libros tienen el mismo título, se pondrían erróneamente en el mismo grupo. Agrupe por book_id (ya que identifica unívocamente cada libro) y title (porque SQL arroja un error si una columna no agregada en SELECT no se pone en GROUP BY). Puedes leer sobre ello en nuestro artículo 7 errores comunes de GROUP BY.

Para obtener la última loan_date, utilice MAX(loan_date). Las fechas posteriores se tratan como "mayores". Si no hay préstamos de libros para este libro, todas sus fechas de préstamo serán NULL y la función MAX() devolverá NULL para este libro.

Ejercicio 10: Préstamos de libros por mes

Ejercicio: Muestre cuántos préstamos de libros se emitieron cada mes de cada año. Muestre tres columnas:

• La parte del año y del mes de loan_date como números en las dos primeras columnas. Nómbralas loan_year y loan_month
• Una columna contando cuántos libros se prestaron ese mes.

Ordena el resultado por el año y luego por el mes, mostrando primero las fechas más antiguas.

Solución:

SELECT
  EXTRACT(YEAR FROM loan_date) AS loan_year,
  EXTRACT(MONTH FROM loan_date) AS loan_month,
  COUNT(loan_id)
FROM book_loan
GROUP BY 
  EXTRACT(MONTH FROM loan_date),
  EXTRACT(YEAR FROM loan_date)
ORDER BY 
  loan_year,
  loan_month;

Explicación: Utilizamos EXTRACT(YEAR FROM loan_date) y EXTRACT(MONTH FROM loan_date) para obtener las partes de año y mes de loan_date. Volvemos a utilizar EXTRACT() en GROUP BY para agrupar los préstamos de los mismos meses.

Utilizamos la función COUNT() para calcular el número de préstamos realizados en cada mes.

Por último, ordenamos los resultados por loan_year y loan_month. Observe que puede utilizar los alias de columna en la sentencia ORDER BY. En la sentencia GROUP BY, sin embargo, todavía tiene que utilizar las funciones; cuando se procesa esta cláusula, la función EXTRACT() (y por tanto las nuevas columnas) todavía no se han definido. Puedes leer más sobre este tema en nuestro artículo SQL Orden de Operaciones.

Ejercicio 11: Libros Populares

Ejercicio: Para cada libro, muestre su title, el número de veces que ha sido prestado y el número de usuarios diferentes que han tomado prestado el libro. Nombra las dos últimas columnas times_loaned y different_patrons.

Solución:

SELECT
  title,
  COUNT(loan_id) AS times_loaned,
  COUNT(DISTINCT patron_id) AS different_patrons
FROM book b
LEFT JOIN book_loan bl
  ON b.book_id = bl.book_id
GROUP BY
  b.title,
  b.book_id;

Explicación: Para encontrar los préstamos de cada libro, hay que unir las tablas book y book_loan. Utilice LEFT JOIN para asegurarse de que los libros que nunca se han prestado también aparecen en el resultado.

Queremos agrupar los préstamos de cada libro, así que tenemos que agrupar tanto por book_id como por el libro title (por la misma razón que discutimos en el Ejercicio 9).

Queremos contar el número de veces que el libro fue prestado y el número de usuarios diferentes que tomaron prestado el libro. Para ello, tenemos que utilizar la función COUNT() dos veces.

Primero, usamos COUNT(loan_id) para contar el número de préstamos del libro.

El segundo uso de COUNT() es más interesante: queremos contar los diferentes usuarios que han tomado prestado el libro. Si alguien tomó prestado el mismo libro varias veces, queremos contarlo sólo una vez. Para ello utilizamos COUNT(DISTINCT patron_id). El uso de DISTINCT garantizará que, incluso si un usuario toma prestado el mismo libro varias veces, su ID sólo se cuente una vez.

Puede obtener más información sobre este tema leyendo ¿Cuál es la diferencia entre COUNT(*), COUNT(1), COUNT(column) y COUNT(DISTINCT)?

Ejercicio 12: Libros Atrasados

Ejercicio: Para cada cliente, muestre su nombre y la cantidad de libros que tiene (tenían) atrasados (es decir, con fecha de devolución posterior a la fecha de vencimiento).

Solución:

SELECT
  patron_name,
  COUNT(book_id) AS overdue_books
FROM patron p
LEFT JOIN book_loan bl
  ON p.patron_id = bl.patron_id AND return_date > due_date
GROUP BY
  p.patron_id,
  patron_name;

Explicación: Unir patron y book_loan utilizando LEFT JOIN para asegurarse de que los usuarios que no tienen préstamos de libros vencidos también se incluyen en el resultado.

Para seleccionar sólo los préstamos cuya fecha de devolución sea posterior a la fecha de vencimiento, utilice una condición de unión combinada: ON p.patron_id = bl.patron_id AND return_date > due_date. La primera parte sólo unirá las filas que estén realmente relacionadas. La segunda parte se utiliza como un filtro adicional para unir sólo en lugares donde return_date > due_date.

Tenga en cuenta que esto es diferente de utilizar una cláusula WHERE más adelante en la consulta. La cláusula WHERE descartará todas las filas en las que loan_id IS NULL. Sin embargo, queremos mantener esas filas para incluir a los usuarios que no tienen libros atrasados.

Agrupamos las filas por patron_id y patron_name (por las mismas razones que en el Ejercicio 9). Por último, utilizamos COUNT(book_id) para contar los libros atrasados de cada usuario. COUNT() devolverá 0 para los usuarios que nunca han realizado préstamos y para los usuarios que siempre han devuelto sus libros a tiempo.

Ejercicio 13: Media de autores por género

Ejercicio: Para cada género, muestre su nombre y el número medio de autores que tienen los libros de ese género. Nombra la segunda columna average_authors_per_book

Solución:

WITH number_of_authors AS (
  SELECT
    book_id,
    COUNT(author_id) AS author_count
  FROM book_author
  GROUP BY book_id
)
SELECT
  genre,
  AVG(author_count) AS average_authors_per_book
FROM number_of_authors na
JOIN book b
  ON na.book_id = b.book_id
GROUP BY genre;

Explicación: Aquí usamos una construcción llamada expresión común de tabla (CTE). Puede obtener más información sobre las CTE en nuestra Guía de expresiones comunes de tabla.

En resumen, una CTE le permite crear un conjunto de resultados temporal con nombre que puede utilizar en la consulta. Puede crear una CTE utilizando esta sintaxis:

WITH <cte_name> AS (query)

Cualquier consulta dentro del paréntesis actuará como una tabla virtual llamada cte_name y será accesible para la consulta principal (la sentencia SELECT después del paréntesis de cierre de la CTE).

En la ETC, calculamos el número de autores de cada libro. Seleccionamos book_id y el recuento de autores. Se trata de una consulta similar a la del Ejercicio 8.

En la consulta externa, unimos nuestro CTE number_of_authors con la tabla book para mostrar el género de cada libro. Luego utilizamos AVG(author_count) y GROUP BY género para obtener el resultado final.

Si no desea utilizar una CTE, puede obtener el mismo resultado utilizando una subconsulta:

SELECT
  genre,
  AVG(author_count) AS average_authors_per_book
FROM (
  SELECT
    book_id,
    COUNT(author_id) AS author_count
  FROM book_author
  GROUP BY book_id) AS na
  JOIN book b
    ON na.book_id = b.book_id
  GROUP BY genre;

Ejercicio 14: Número de páginas leídas por los usuarios

Ejercicio: Para cada cliente, muestre su nombre y el número total de páginas que ha leído (es decir, el número de páginas de todos los libros que ha tomado prestados). Suponemos que han leído todos los libros. Incluya todos los libros, incluso los que aún no han devuelto.

Mostrar sólo los resultados de los usuarios que hayan leído más de 1.000 páginas.

Solución:

SELECT
  patron_name,
  SUM(pages) AS total_pages_read
FROM book b
JOIN book_loan bl
  ON b.book_id = bl.book_id
JOIN patron p
  ON p.patron_id = bl.patron_id
GROUP BY 
  p.patron_id,
  p.patron_name
HAVING SUM(pages) > 1000;

Explicación: Una tres tablas utilizando un JOIN regular : patron, book_loan, y book. Utilice SUM(pages) para sumar el número de páginas de todos los libros prestados por el usuario. Filtre con HAVING SUM(pages) > 1000 para mostrar sólo los usuarios que han leído más de 1000 páginas.

Nota: Dado que queremos mostrar los usuarios que han leído más de 1.000 páginas, no hay necesidad de utilizar LEFT JOIN o FULL JOIN aquí. Los usuarios que hayan leído 0 páginas serán filtrados con nuestra condición HAVING de todas formas.

Ejercicio 15: Usuarios sin libros prestados

Ejercicio: Mostrar el número total de usuarios que nunca han tomado libros prestados.

Solución:

SELECT COUNT(p.patron_id)
FROM patron p
WHERE NOT EXISTS (
  SELECT * FROM book_loan
  WHERE patron_id = p.patron_id
);

Explicación: Para mostrar sólo los usuarios que nunca han tomado libros prestados, filtre el resultado con una cláusula WHERE NOT EXISTS. Para cualquier usuario que nunca haya tomado libros prestados, no existirá una entrada book_loan entrada con su dirección id. Utilice una subconsulta para encontrar un conjunto de préstamos de libros para cada usuario y, a continuación, utilice el resultado de esa subconsulta en la cláusula WHERE NOT EXISTS. Esto garantizará que todos los usuarios del conjunto resultante no tengan ningún préstamo de libroscorrespondiente.

Por último, utilice la función COUNT() para contar los seleccionados patron_ids.

Ejercicio 16: Autores y públicos

Ejercicio: Para cada autor, muestre cuántos usuarios diferentes han tomado prestado su libro.

Solución:

SELECT
  a.author_name,
  COUNT(DISTINCT patron_id) AS distinct_patrons
FROM author a
JOIN book_author ba
  ON a.author_id = ba.author_id
JOIN book b
  ON b.book_id = ba.book_id
LEFT JOIN book_loan bl
  ON bl.book_id = b.book_id
GROUP BY
  a.author_id,
  a.author_name;

Explicación: Unir cuatro tablas: author, book_author, booky book_loan. Utilice un JOIN regular con las tres primeras tablas y un LEFT JOIN entre book y book_loan. El LEFT JOIN asegurará que incluso si el libro nunca ha sido prestado, seguirá apareciendo en el resultado.

SELECT El resultado de la búsqueda se basa en el nombre del autor y utiliza COUNT(DISTINCT patron_id) para contar todos los usuarios que han tomado libros prestados. Si los libros del autor nunca han sido prestados, COUNT() devolverá 0.

Agrupe el resultado por el ID y el nombre del autor para evitar los errores de los que hablamos antes.

Ejercicio 17: Los libros más antiguos

Ejercicio: Encuentre el libro o libros más antiguos de la base de datos (es decir, el libro o libros con el publication_year más antiguo). Muestre sólo dos columnas: title y publication_year.

Recuerde que puede haber más de un libro con el año de publicación más antiguo.

Solución:

SELECT 
  title,
  publication_year
FROM book
WHERE publication_year = (
  SELECT MIN(publication_year)
  FROM book
);

Explicación: Utilice una subconsulta para encontrar los libros más antiguos. Seleccione sólo el title y el año de publicación de los libros que tengan el publication_year igual al año de publicación más antiguo del sistema. Puede encontrar el publication_year más antiguo con MIN(publication_year). Utilice esta expresión dentro de una subconsulta y, a continuación, compare el publication_year de cada libro con el resultado de la subconsulta.

Ejercicio 18: Patrones más activos

Ejercicio: Encuentre los nombres de todos los usuarios que han tomado prestados un número de libros superior a la media. Indique el número de libros prestados junto con su nombre.

Solución:

SELECT 
  patron_name,
  COUNT(*) AS loan_count
FROM patron
JOIN book_loan 
  ON patron.patron_id = book_loan.patron_id
GROUP BY patron_name
HAVING COUNT(*) > (
  SELECT COUNT(*)
  FROM book_loan
) / (
  SELECT COUNT(*)
  FROM patron
);

Explicación: Une los patron y book_loan y agrupe los resultados por nombre e ID del usuario. Para mostrar sólo los usuarios que han tomado prestados un número de libros superior a la media, utilice la cláusula HAVING comparando el número de préstamos del usuario actual con el número medio de libros prestados por usuario. La media se obtiene dividiendo el número total de préstamos entre el número total de usuarios.

Debido a que / en SQL es una división entera (lo que significa que el resto es descartado) use > (mayor que), no >= (mayor o igual) para comparar los valores en la cláusula HAVING.

Ejercicio 19: El autor más productivo

Ejercicio: Encontrar el autor que escribió más libros.

Solución:

WITH authors_books_count AS (
  SELECT
    author_id,
    COUNT(*) AS book_count
  FROM book_author
  GROUP BY author_id
)
SELECT 
  author_name,
  book_count
FROM author
JOIN authors_books_count abc
  ON author.author_id = abc.author_id
WHERE book_count = (
  SELECT MAX(book_count)
  FROM authors_books_count
);

Explicación: En el CTE authors_books_count, encontramos el número de libros que ha escrito cada autor usando el id de ese autor.

En la consulta externa, seleccionamos los autores cuyo número de libros es igual al número máximo de libros. Utilizamos una subconsulta y la función MAX() para seleccionar el recuento máximo de libros del CTE y compararlo con book_count para cada autor.

¿Ansioso por Más Práctica de Funciones Agregadas SQL?

Y aquí termina este conjunto de prácticas de funciones agregadas SQL. ¡Ahora tus conocimientos de funciones agregadas están al día! Puedes comprobar tus conocimientos teóricos con estas Preguntas de Entrevista GROUP BY.

Hemos cubierto diferentes funciones agregadas, GROUP BY, HAVING ¡y mucho más! Hemos practicado diferentes tipos de JOINs, subconsultas y el trabajo con NULLs. ¿Tienes ganas de más? Echa un vistazo a los ejercicios en nuestro enorme Ejercicio de SQL Track; ¡tiene más de 100 horas de práctica de SQL! O prueba nuestro Práctica mensual de SQLque ofrece un nuevo curso práctico de SQL cada mes.