19 exercícios sobre funções agregadas

Resolva esses 19 exercícios de função agregada SQL e aprimore suas habilidades em SQL! Pratique o uso de funções agregadas com GROUP BY, HAVING, subconsultas e muito mais. Inclui uma solução e uma explicação detalhada para cada exercício.

As funções agregadas são uma parte importante do SQL. Elas permitem que você calcule diferentes estatísticas e gere relatórios que não seriam possíveis com operações de uma única linha, como a filtragem. É importante praticar as funções agregadas com frequência, pois elas são uma parte crucial de seu conjunto de habilidades em SQL. Vamos começar!

Este artigo inclui 19 exercícios de funções de agregação totalmente novos usando nosso banco de dados de biblioteca. Abordaremos o agrupamento com GROUP BY, a filtragem de dados com HAVING, o uso de funções de agregação em subconsultas e a agregação em vários níveis. Também atualizaremos seus conhecimentos sobre como trabalhar com JOINs e NULLs e como ordenar dados por várias estatísticas.

Quando você estiver pronto para aplicar esses conceitos em cenários do mundo real, confira nosso curso Basic SQL Reporting ! Ele aborda todos os conceitos de função agregada que você praticará neste artigo, além de outros tópicos importantes, como o uso de CASE WHEN.

Também recomendamos que você experimente nossos desafios mensais em nossa trilha Desafio SQL do Mês . Trata-se de uma coleção de exercícios práticos - com novos exercícios publicados todos os meses - projetados especificamente para manter suas habilidades em SQL afiadas e atualizadas.

O que são funções agregadas?

As funções agregadas realizam cálculos em um conjunto de valores e retornam um único valor como resultado. As funções agregadas comuns são:

• SUM() - Calcula a soma de todos os valores em cada grupo.
• AVG() - Calcula o valor médio de todos os valores em cada grupo.
• COUNT() - Retorna o número de valores em cada grupo.
• MIN() e MAX() - Retornam o menor e o maior valor (respectivamente) em cada grupo.

Essas funções são especialmente úteis na criação de relatórios em que você precisa calcular várias métricas.

Precisa de uma explicação mais detalhada? Confira nossa folha de dicas sobre funções agregadas SQL, que aborda todas as funções agregadas, seus casos de uso e suas interações com GROUP BY.

Por que praticar agregações de SQL?

A prática regular ajuda a manter suas habilidades afiadas, permitindo que você analise e manipule dados com mais rapidez e eficiência. Ao praticar mais, você poderá resolver problemas mais complexos e otimizar melhor suas consultas.

Praticar SQL é vital se você estiver buscando uma carreira como analista de dados, desenvolvedor de banco de dados ou qualquer outro cargo que lide com muitos dados. Consulte nosso artigo sobre exercícios GROUP BY para aprimorar ainda mais suas habilidades de geração de relatórios.

O conjunto de dados

Vamos dar uma olhada no conjunto de dados com o qual trabalharemos nestas perguntas práticas.

O conjunto de dados consiste em cinco tabelas: book author , book_author, patron, e book_loan. Aqui está o esquema:

As informações sobre livros são armazenadas na tabela book. Ela tem as seguintes colunas:

• book_id - Um ID exclusivo para cada livro e a chave primária da tabela.
• title - O título do livro
• publication_year - O ano em que o livro foi publicado. Pode ser NULL se for desconhecido.
• genre - O gênero do livro, por exemplo, "Fantasia" ou "Mistério".
• pages - O número de páginas do livro.

Aqui está um instantâneo dos dados na tabela:

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

As informações sobre os autores são armazenadas na tabela author. Ela tem as seguintes colunas:

• author_id - Um ID exclusivo para cada autor e a chave primária da tabela.
• author_name - O nome completo ou pseudônimo do autor.
• country - O país do autor.

Aqui estão alguns dos dados da tabela:

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

Os dados sobre as pessoas que pegam livros emprestados na biblioteca são armazenados na tabela patron. Ela tem as seguintes colunas:

• patron_id - Um ID exclusivo para cada usuário e a chave primária da tabela.
• patron_name - O nome completo do usuário.
• registration_date - A data em que o usuário se registrou no sistema da biblioteca.

Aqui estão alguns dos dados da tabela:

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

A relação de muitos para muitos entre os autores e os livros que eles escreveram está armazenada na tabela book_author tabela. Ela tem as seguintes colunas:

• book_author_id - Um ID exclusivo para cada par livro-autor e a chave primária da tabela.
• author_id - O ID do autor.
• book_id - O ID do livro que o autor escreveu.

Aqui estão alguns dos dados da tabela:

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

O relacionamento muitos-para-muitos entre os usuários e os livros que eles pegaram emprestados é armazenado na tabela book_loan tabela. Ela tem as seguintes colunas:

• loan_id - Um ID exclusivo para cada empréstimo e a chave primária da tabela.
• book_id - O ID do livro emprestado.
• patron_id - O ID do usuário que pegou o livro emprestado.
• loan_date - A data em que o empréstimo do livro foi emitido.
• due_date - A data em que o livro deve ser devolvido.
• return_date - A data real em que o livro foi devolvido.

Aqui estão alguns dos dados da tabela:

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

Volte a esta seção se esquecer os nomes de tabelas ou colunas ao resolver os exercícios. Se precisar de dicas de sintaxe, tenha em mãos nossa Folha de dicas de SQL para análise de dados. Ela abrange todas as ferramentas que podem ajudá-lo a resolver esses exercícios. Você pode até mesmo baixá-la em formato PDF e imprimi-la, para que ela o ajude em seus exercícios futuros!

Pratique essas funções agregadas do SQL

Resolva os exercícios por conta própria e, em seguida, confira as soluções abaixo de cada exercício. Há também explicações adicionais para cada solução, caso você tenha dúvidas.

Exercício 1: Livros no sistema

Exercício: Contar o número de livros registrados no banco de dados.

Solução:

SELECT COUNT(book_id)
FROM book;

Explicação: Usamos a função COUNT() para obter o número de linhas na tabela book tabela.

Observe dois aspectos. Primeiro, a consulta não tem a cláusula WHERE, portanto, nenhuma linha é filtrada; todas as linhas da tabela são contadas. Segundo, a consulta não tem a cláusula GROUP BY. Quando você usa uma função de agregação sem GROUP BY, todas as linhas são colocadas em um grupo e a função é aplicada a todas as linhas desse grupo. Portanto, nossa consulta conta todas as linhas da tabela book tabela.

Exercício 2: Livros não devolvidos

Exercício: Contar quantos livros ainda não foram devolvidos (ou seja, livros que não têm data de devolução).

Solução:

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

Explicação: Os livros que não foram devolvidos não terão um return_date na tabela. book_loan seu return_date é NULL. Usamos essa condição na cláusula WHERE para selecionar somente os livros que ainda não foram devolvidos.

Usamos a função COUNT() para contar o número de linhas no conjunto de dados resultante. Lembre-se de que o argumento usado com a função COUNT() é importante. Aqui, colocamos loan_id como argumento, o que diz ao banco de dados para contar todos os valores nas colunas loan_id.

Como alternativa, poderíamos ter usado COUNT(*) e simplesmente contar todas as linhas no conjunto de resultados, obtendo o mesmo resultado. Entretanto, COUNT(return_date) não seria apropriado: o resultado seria 0. Todos os return_dates no resultado são NULL por causa da condição return_date IS NULL.

Novamente, não há GROUP BY nessa consulta, portanto a função COUNT() contará todos os empréstimos em que a data de retorno estiver vazia.

Exercício 3: Livros por gênero

Exercício: Para cada gênero, mostre o nome do gênero e o número de livros desse gênero.

Solução:

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

Explicação: Esse é o exercício mais básico do site GROUP BY. Na tabela bookselecionamos o gênero.

Para garantir que a função COUNT() retorne um resultado separado para cada gênero, dividimos o conjunto de dados em grupos usando GROUP BY genre. Isso criará grupos com base nos valores da coluna genre; os livros com o mesmo valor em gênero serão colocados no mesmo grupo.

A função COUNT() trabalhará em cada grupo separadamente, contando o número de livros em cada grupo.

Se você precisar de uma atualização sobre como trabalhar com GROUP BY e funções agregadas, consulte nossa Visão geral completa das funções GROUP BY e Aggregate.

Exercício 4: Autores por país

Exercício: Para cada país, mostre seu nome e o número de autores associados a ele.

Solução:

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

Explicação: Este é outro exercício básico do site GROUP BY. Selecionamos os dados da tabela author e os agrupamos pelos valores da coluna country. Em seguida, aplicamos COUNT(author_id) a cada grupo para contar os autores provenientes desse país.

Exercício 5: Intervalos de páginas por gênero

Exercício: Para cada gênero, mostre quatro colunas: o nome do gênero, o número mínimo e máximo de páginas dos livros desse gênero e o difference entre o maior e o menor número de páginas de cada livro.

Solução:

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

Explicação: Este é outro exercício básico de GROUP BY. Selecionamos os dados da tabela author e os agrupamos pelos valores da coluna country. Em seguida, aplicamos COUNT(author_id) a cada grupo para contar os autores provenientes desse país.

Exercício 5: Intervalos de páginas por gênero

Exercício: Para cada gênero, mostre quatro colunas: o nome do gênero, o número mínimo e máximo de páginas dos livros desse gênero e a diferença entre o maior e o menor número de páginas de cada livro.

Solução:

Explicação: Para obter estatísticas para cada gênero, agrupe os dados da tabela book pela coluna genre.

Use as funções de agregação MIN(pages) e MAX(pages) para calcular o número mínimo e máximo de páginas. Na terceira coluna, use MIN(pages) - MAX(pages) para calcular a diferença para cada grupo. Por fim, renomeie a última coluna para difference usando AS.

Exercício 6: Gêneros de grandes livros

Exercício: Para cada gênero, mostre o número médio de páginas de todos os livros desse gênero. Mostre apenas os gêneros em que o livro médio tem mais de 250 páginas. Nomeie a coluna de páginas médias avg_pages.

Solução:

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

Explicação: Este exercício é semelhante ao anterior: agrupamos os livros por gênero e calculamos o número médio de livros em cada gênero usando AVG(). Entretanto, há um novo elemento aqui: HAVING. HAVING é usado para filtrar grupos e localizar grupos para os quais uma função agregada satisfaz uma determinada condição. No nosso caso, procuramos grupos (gêneros) em que o número médio de páginas seja maior ou igual a 250.

Lembre-se de que HAVING funciona de forma diferente de WHERE. WHERE é usado para filtrar linhas individuais antes do agrupamento, enquanto HAVING é usado para filtrar linhas após o agrupamento. Você pode ler sobre a diferença entre HAVING e WHERE em nosso artigo HAVING vs. WHERE em SQL: O que você deve saber.

Exercício 7: Gêneros modernos

Exercício: Mostre o ano médio de publicação de cada gênero de livros. Arredonde o ano para um número inteiro. Mostre apenas os gêneros em que o ano médio de publicação é posterior a 1940.

Solução:

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

Explicação: Este exercício é semelhante ao anterior: agrupamos os livros por genre e calculamos o ano médio de publicação usando AVG(publication_year). Em seguida, filtramos os gêneros com um ano médio de agregação superior a 1940 usando HAVING.

Exercício 8: Livros com vários autores

Exercício: Para livros que foram escritos por mais de um autor, mostre o título de cada livro e o número de autores.

Solução:

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;

Explicação: Primeiro, temos de encontrar os autores de cada livro. Para isso, juntamos as tabelas book e book_author em seu site comum book_id. Isso combinará os dados de cada livro com os dados de seus autores: uma linha para cada combinação de livro-autor.

Em seguida, agrupamos as linhas por book_id: todas as linhas relacionadas ao mesmo livro estão no mesmo grupo. Assim, todos os autores de cada livro estarão no mesmo grupo. Em seguida, aplicamos a função COUNT(author_id) para contar os autores em cada grupo.

Por fim, filtramos todos os livros com apenas um autor usando HAVING COUNT(author_id) > 1.

Exercício 9: O último empréstimo de cada livro

Exercício: Para cada livro, mostre seu endereço title e a data mais recente em que foi emprestado. Nomeie a segunda coluna como last_loaned. Mostre NULL na segunda coluna para todos os livros que nunca foram emprestados.

Solução:

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;

Explicação: Use um LEFT JOIN para unir as colunas book e book_loan para garantir que os livros que nunca foram emprestados também sejam incluídos no conjunto de resultados. Agrupe os resultados por book_id e book_title.

Observe que você não deve agrupar o resultado apenas pelo título; se dois livros tiverem o mesmo título, eles serão erroneamente colocados no mesmo grupo. Agrupe por book_id (já que ele identifica exclusivamente cada livro) e title (porque o SQL gera um erro se uma coluna não agregada em SELECT não for colocada em GROUP BY). Você pode ler sobre isso em nosso artigo 7 Erros comuns de GROUP BY.

Para obter o loan_date mais recente, use MAX(loan_date). As datas posteriores são tratadas como "maiores". Se não houver empréstimos de livros para esse livro, todas as suas datas de empréstimo serão NULL e a função MAX() retornará NULL para esse livro.

Exercício 10: Empréstimos de livros por mês

Exercício: Mostre quantos empréstimos de livros foram emitidos em cada mês de cada ano. Mostre três colunas:

• A parte do ano e do mês do site loan_date como números nas duas primeiras colunas. Nomeie-as loan_year e loan_month
• Uma coluna contando quantos livros foram emprestados naquele mês.

Ordene o resultado pelo ano e depois pelo mês, mostrando primeiro as datas mais antigas.

Solução:

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;

Explicação: Usamos EXTRACT(YEAR FROM loan_date) e EXTRACT(MONTH FROM loan_date) para obter as partes do ano e do mês em loan_date. Usamos novamente EXTRACT() em GROUP BY para agrupar os empréstimos dos mesmos meses.

Usamos a função COUNT() para calcular o número de empréstimos feitos em cada mês.

Por fim, ordenamos os resultados por loan_year e loan_month. Observe que você pode usar os aliases de coluna na instrução ORDER BY. Na instrução GROUP BY, entretanto, você ainda precisa usar as funções; quando essa cláusula é processada, a função EXTRACT() (e, portanto, as novas colunas) ainda não foi definida. Você pode ler mais sobre esse tópico em nosso artigo SQL Order of Operations.

Exercício 11: Livros populares

Exercício: Para cada livro, mostre o endereço title, o número de vezes que ele foi emprestado e o número de usuários diferentes que pegaram o livro emprestado. Nomeie as duas últimas colunas times_loaned e different_patrons.

Solução:

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;

Explicação: Para encontrar os empréstimos de livros para cada livro, você deve unir as tabelas book e book_loan. Use o endereço LEFT JOIN para garantir que os livros que nunca foram emprestados também apareçam no resultado.

Queremos agrupar os empréstimos de cada livro, portanto, temos de agrupar por book_id e pelo livro title (pelo mesmo motivo que discutimos no Exercício 9).

Queremos contar o número de vezes que o livro foi emprestado e o número de usuários diferentes que pegaram o livro emprestado. Para fazer isso, temos de usar a função COUNT() duas vezes.

Primeiro, usamos COUNT(loan_id) para contar o número de empréstimos do livro.

O segundo uso de COUNT() é mais interessante: queremos contar os diferentes usuários que pegaram o livro emprestado. Se alguém pegou o mesmo livro emprestado várias vezes, queremos contá-lo apenas uma vez. Assim, usamos COUNT(DISTINCT patron_id). O uso do DISTINCT garantirá que, mesmo que um usuário pegue os mesmos livros emprestados várias vezes, seu ID será contado apenas uma vez.

Você pode ler mais sobre isso em Qual é a diferença entre COUNT(*), COUNT(1), COUNT(column) e COUNT(DISTINCT)?

Exercício 12: Livros vencidos

Exercício: Para cada usuário, mostre seu nome e a quantidade de livros que ele tem (tinha) em atraso (ou seja, com uma data de devolução posterior à data de vencimento).

Solução:

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;

Explicação: Junte patron e book_loan usando LEFT JOIN para garantir que os usuários que não têm nenhum empréstimo de livro vencido também sejam incluídos no resultado.

Para selecionar somente os empréstimos em que a data de devolução é posterior à data de vencimento, use uma condição de união combinada: ON p.patron_id = bl.patron_id AND return_date > due_date. A primeira parte unirá apenas as linhas que estão de fato relacionadas. A segunda parte é usada como um filtro adicional para unir apenas os locais onde return_date > due_date.

Observe que isso é diferente de usar uma cláusula WHERE posteriormente na consulta. A cláusula WHERE descartará todas as linhas em que loan_id IS NULL. No entanto, queremos manter essas linhas para incluir os usuários que não têm nenhum livro vencido.

Agrupamos as linhas por patron_id e patron_name (pelos mesmos motivos do Exercício 9). Por fim, usamos o COUNT(book_id) para contar os livros vencidos de cada usuário. O COUNT() retornará 0 para os usuários que nunca fizeram nenhum empréstimo e para os usuários que sempre devolveram seus livros no prazo.

Exercício 13: Média de autores por gênero

Exercício: Para cada gênero, mostre seu nome e o número médio de autores que os livros desse gênero têm. Nomeie a segunda coluna average_authors_per_book

Solução:

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;

Explicação: Aqui usamos uma construção chamada expressão de tabela comum (CTE). Você pode ler mais sobre CTEs em nosso Guia de expressões comuns de tabela.

Em resumo, um CTE permite que você crie um conjunto de resultados temporário nomeado que pode ser usado na consulta. Você cria um CTE usando esta sintaxe:

WITH <cte_name> AS (query)

Qualquer consulta dentro do parêntese funcionará como uma tabela virtual denominada cte_name e estará acessível à consulta principal (a instrução SELECT após o parêntese de fechamento do CTE).

No CTE, calculamos o número de autores de cada livro. Selecionamos o endereço book_id e a contagem de autores. Essa é uma consulta semelhante à do Exercício 8.

Na consulta externa, juntamos nosso CTE number_of_authors com a tabela book para exibir o gênero de cada livro. Em seguida, usamos os gêneros AVG(author_count) e GROUP BY para obter o resultado final.

Se não quiser usar um CTE, você pode obter o mesmo resultado usando uma 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;

Exercício 14: Número de páginas lidas pelos usuários

Exercício: Para cada usuário, mostre o nome e o número total de páginas que ele leu (ou seja, a contagem de páginas de todos os livros que ele pegou emprestado). Estamos supondo que eles leram cada livro por completo. Inclua todos os livros, mesmo aqueles que ainda não foram devolvidos.

Mostrar apenas os resultados dos usuários que leram mais de 1.000 páginas.

Solução:

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;

Explicação: Una três tabelas usando um JOIN regular: patron, book_loan, e book. Use SUM(pages) para somar o número de páginas de todos os livros que o usuário pegou emprestado. Filtre com HAVING SUM(pages) > 1000 para mostrar apenas os usuários que leram mais de 1.000 páginas.

Observação: Como queremos mostrar os usuários que leram mais de 1.000 páginas, não há necessidade de usar LEFT JOIN ou FULL JOIN aqui. Os usuários que leram 0 páginas serão filtrados com nossa condição HAVING de qualquer forma.

Exercício 15: Usuários sem livros emprestados

Exercício: Mostre o número total de usuários que nunca pegaram livros emprestados.

Solução:

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

Explicação: Para mostrar apenas os usuários que nunca pegaram livros emprestados, filtre o resultado com uma cláusula WHERE NOT EXISTS. Para qualquer usuário que nunca tenha pegado livros emprestados, não haverá uma entrada book_loan com o endereço id desse usuário. Use uma subconsulta para encontrar um conjunto de empréstimos de livros para cada usuário e, em seguida, use o resultado dessa subconsulta na cláusula WHERE NOT EXISTS. Isso garantirá que todos os usuários do conjunto resultante não tenham nenhum book_loanscorrespondente.

Por fim, use a função COUNT() para contar os selecionados patron_ids.

Exercício 16: Autores e públicos

Exercício: Para cada autor, mostre quantos usuários diferentes pegaram seus livros emprestados.

Solução:

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;

Explicação: Junte quatro tabelas: author, book_author, book, e book_loan. Use um JOIN regular com as três primeiras tabelas e um LEFT JOIN entre book e book_loan. O LEFT JOIN garantirá que, mesmo que o livro nunca tenha sido emprestado, ele ainda será exibido no resultado.

SELECT O COUNT(DISTINCT patron_id) garantirá que, mesmo que o livro nunca tenha sido emprestado, ele ainda será exibido no resultado. Se os livros do autor nunca tiverem sido emprestados, COUNT() retornará 0.

Agrupe o resultado pelo ID e pelo nome do autor para evitar os erros de que falamos anteriormente.

Exercício 17: Os livros mais antigos

Exercício: Encontre o(s) livro(s) mais antigo(s) no banco de dados (ou seja, o(s) livro(s) com o publication_year mais antigo). Mostre apenas duas colunas: title e publication_year.

Lembre-se de que pode haver mais de um livro com o ano de publicação mais antigo.

Solução:

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

Explicação: Use uma subconsulta para localizar os livros mais antigos. Selecione somente o endereço title e o ano de publicação dos livros que tenham o endereço publication_year igual ao ano de publicação mais baixo do sistema. Você pode encontrar o publication_year mais antigo com MIN(publication_year). Use essa expressão em uma subconsulta e compare o publication_year de cada livro com o resultado da subconsulta.

Exercício 18: Patronos mais ativos

Exercício: Encontre os nomes de todos os usuários que pegaram emprestado um número de livros acima da média. Mostre o número de livros que eles pegaram emprestado junto com seus nomes.

Solução:

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
);

Explicação: Junte o patron e book_loan e agrupe os resultados pelo nome e ID do usuário. Para mostrar apenas os usuários que pegaram emprestado um número de livros acima da média, use a cláusula HAVING comparando a contagem de empréstimos do usuário atual com o número médio de livros emprestados por usuário. Encontre essa média dividindo o número total de empréstimos pelo número total de usuários.

Como / no SQL é a divisão de números inteiros (o que significa que o restante é descartado), use > (maior que) e não >= (maior ou igual) para comparar os valores na cláusula HAVING.

Exercício 19: O autor mais produtivo

Exercício: Encontre o autor que escreveu o maior número de livros.

Solução:

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
);

Explicação: No CTE authors_books_count, encontramos o número de livros que cada autor escreveu usando o endereço id.

Na consulta externa, selecionamos os autores cuja contagem de livros é a mesma que a contagem máxima de livros. Usamos uma subconsulta e a função MAX() para selecionar a contagem máxima de livros do CTE e compará-la com o book_count de cada autor.

Quer praticar mais a função agregada do SQL?

E isso marca o fim desse conjunto de práticas de funções agregadas do SQL. Agora suas habilidades em funções agregadas estão atualizadas! Você pode verificar seu conhecimento teórico com estas perguntas da entrevista sobre GROUP BY.

Cobrimos diferentes funções de agregação, GROUP BY, HAVING e muito mais! Praticamos diferentes tipos de JOINs, subconsultas e trabalho com NULLs. Quer saber mais? Confira os exercícios em nossa enorme trilha Trilha de Práticas em SQL ; ela tem mais de 100 horas de prática de SQL! Ou experimente o nosso Desafio SQL do Mês que oferece um novo curso de prática de SQL todos os meses!