19 esercizi sulle funzioni aggregate

Risolvete questi 19 esercizi sulle funzioni aggregate SQL e affinate le vostre abilità in SQL! Esercitatevi a usare le funzioni aggregate con GROUP BY, HAVING, subquery e altro ancora. Include una soluzione e una spiegazione dettagliata per ogni esercizio.

Le funzioni aggregate sono una parte importante di SQL. Consentono di calcolare diverse statistiche e di generare report che non sarebbero stati possibili con operazioni su una singola riga, come il filtraggio. È importante esercitarsi spesso con le funzioni aggregate, perché sono una parte fondamentale delle vostre abilità in SQL. Iniziamo!

Questo articolo comprende 19 nuovissimi esercizi sulle funzioni aggregate che utilizzano il nostro database di libreria. Tratteremo il raggruppamento con GROUP BY, il filtraggio dei dati con HAVING, l'uso delle funzioni aggregate nelle subquery e l'aggregazione a più livelli. Inoltre, rinfrescheremo le vostre conoscenze sull'utilizzo di JOIN e NULL e sull'ordinamento dei dati in base a diverse statistiche.

Quando sarete pronti ad applicare questi concetti in scenari reali, date un'occhiata al nostro corso Basic SQL Reporting ! Copre tutti i concetti relativi alle funzioni aggregate che metterete in pratica in questo articolo, oltre ad altri argomenti importanti come l'uso di CASE WHEN.

Vi consigliamo anche di provare le nostre sfide mensili nel nostro percorsoPratica Mensile su SQL . Si tratta di una raccolta di esercizi pratici, pubblicati ogni mese, pensati appositamente per mantenere le vostre competenze in SQL sempre aggiornate.

Cosa sono le funzioni aggregate?

Lefunzioni aggregate eseguono calcoli su un insieme di valori e restituiscono un singolo valore come risultato. Le funzioni aggregate più comuni sono:

• SUM() - Calcola la somma di tutti i valori di ciascun gruppo.
• AVG() - Calcola il valore medio di tutti i valori di ciascun gruppo.
• COUNT() - Restituisce il numero di valori in ciascun gruppo.
• MIN() e MAX() - Restituiscono i valori più piccoli e più grandi (rispettivamente) di ciascun gruppo.

Queste funzioni sono particolarmente utili per la creazione di report in cui è necessario calcolare varie metriche.

Avete bisogno di una spiegazione più dettagliata? Consultate il nostro foglio informativo sulle funzioni aggregate SQL, che illustra tutte le funzioni aggregate, i loro casi d'uso e le loro interazioni con GROUP BY.

Perché esercitarsi con le aggregazioni SQL?

L'esercizio regolare aiuta a mantenere le proprie capacità, consentendo di analizzare e manipolare i dati in modo più rapido ed efficiente. Con una maggiore pratica, sarete in grado di risolvere problemi più complessi e di ottimizzare meglio le vostre query.

Esercitarsi con l'SQL è fondamentale se si vuole intraprendere una carriera come analista di dati, sviluppatore di database o qualsiasi altra posizione che abbia a che fare con molti dati. Consultate il nostro articolo sugli esercizi GROUP BY per affinare ulteriormente le vostre capacità di reporting.

Il set di dati

Diamo un'occhiata al set di dati con cui lavoreremo per queste domande di esercitazione.

Il dataset è composto da cinque tabelle: book author , book_author, patron e book_loan. Ecco lo schema:

Le informazioni sui libri sono memorizzate nella tabella book. Essa ha le seguenti colonne:

• book_id - Un ID univoco per ogni libro e la chiave primaria della tabella.
• title - Il titolo del libro
• publication_year - L'anno di pubblicazione del libro. Può essere NULL se non è noto.
• genre - Il genere del libro, ad esempio "Fantasy" o "Mistero".
• pages - Il numero di pagine del libro.

Ecco un'istantanea dei dati contenuti nella tabella:

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

Le informazioni sugli autori sono memorizzate nella tabella author. Ha le seguenti colonne:

• author_id - Un ID univoco per ogni autore e la chiave primaria della tabella.
• author_name - Il nome completo o lo pseudonimo dell'autore.
• country - Il Paese dell'autore.

Ecco alcuni dati della tabella:

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

I dati relativi alle persone che prendono in prestito libri dalla biblioteca sono memorizzati nella tabella patron. Essa ha le seguenti colonne:

• patron_id - Un ID univoco per ogni cliente e la chiave primaria della tabella.
• patron_name - Il nome completo dell'utente.
• registration_date - La data di registrazione nel sistema bibliotecario.

Ecco alcuni dei dati contenuti nella tabella:

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 relazione molti-a-molti tra gli autori e i libri che hanno scritto è memorizzata nella tabella. book_author La tabella ha le seguenti colonne Ha le seguenti colonne:

• book_author_id - Un ID univoco per ogni coppia libro-autore e la chiave primaria della tabella.
• author_id - L'ID dell'autore.
• book_id - L'ID del libro scritto dall'autore.

Ecco alcuni dei dati contenuti nella tabella:

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

La relazione molti-a-molti tra gli avventori e i libri che hanno preso in prestito è memorizzata nella tabella book_loan tabella. Ha le seguenti colonne:

• loan_id - Un ID univoco per ogni prestito e la chiave primaria della tabella.
• book_id - L'ID del libro prestato.
• patron_id - L'ID del cliente che ha preso in prestito il libro.
• loan_date - La data di emissione del prestito.
• due_date - La data di restituzione del libro.
• return_date - La data effettiva di restituzione del libro.

Ecco alcuni dati della tabella:

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

Tornate a questa sezione se dimenticate i nomi delle tabelle o delle colonne durante la risoluzione degli esercizi. Se avete bisogno di suggerimenti per la sintassi, tenete a portata di mano il nostro foglio informativo SQL per l'analisi dei dati. Copre tutti gli strumenti che possono aiutarvi a risolvere questi esercizi. Potete anche scaricarlo in formato PDF e stamparlo, in modo che vi aiuti con gli esercizi futuri!

Esercitatevi con le funzioni aggregate SQL

Risolvete gli esercizi da soli, poi date un'occhiata alle soluzioni sotto ogni esercizio. Per ogni soluzione ci sono anche ulteriori spiegazioni se vi siete bloccati.

Esercizio 1: Libri nel sistema

Esercizio: Contare il numero di libri registrati nel database.

Soluzione:

SELECT COUNT(book_id)
FROM book;

Spiegazione: Utilizziamo la funzione COUNT() per ottenere il numero di righe nella tabella. book tabella.

Si notino due cose. Innanzitutto, la query non ha la clausola WHERE, quindi non vengono filtrate le righe; vengono contate tutte le righe della tabella. In secondo luogo, la query non ha la clausola GROUP BY. Quando si utilizza una funzione aggregata senza GROUP BY, tutte le righe vengono inserite in un gruppo e la funzione viene applicata a tutte le righe di questo gruppo. La query conta quindi tutte le righe della tabella. book tabella.

Esercizio 2: Libri non restituiti

Esercizio: Contare quanti libri non sono ancora stati restituiti (cioè i libri che non hanno una data di restituzione).

Soluzione:

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

Spiegazione: I libri che non sono stati restituiti non hanno un return_date nella tabella. book_loan tabella; il loro return_date è NULL. Si utilizza questa condizione nella clausola WHERE per selezionare solo i libri che non sono ancora stati restituiti.

Utilizziamo la funzione COUNT() per contare il numero di righe nel set di dati risultante. Tenere presente che l'argomento utilizzato con la funzione COUNT() è importante. In questo caso abbiamo inserito come argomento loan_id, che indica al database di contare tutti i valori delle colonne loan_id.

In alternativa, avremmo potuto usare COUNT(*) e contare semplicemente tutte le righe nel set di risultati, ottenendo lo stesso risultato. Tuttavia, COUNT(return_date) non sarebbe appropriato: il risultato sarebbe 0. Tutti i return_dates presenti nel risultato sono NULL a causa della condizione return_date IS NULL.

Anche in questo caso, non c'è GROUP BY in questa query, quindi la funzione COUNT() conterà tutti i prestiti in cui la data di ritorno è vuota.

Esercizio 3: Libri per genere

Esercizio: Per ogni genere, indicare il nome del genere e il numero di libri di quel genere.

Soluzione:

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

Spiegazione: Questo è l'esercizio più elementare di GROUP BY. Dalla tabella bookselezioniamo il genere.

Per assicurarci che la funzione COUNT() restituisca un risultato separato per ogni genere, dividiamo il set di dati in gruppi utilizzando GROUP BY genere. In questo modo verranno creati dei gruppi in base ai valori della colonna genre; i libri con lo stesso valore di genere andranno nello stesso gruppo.

La funzione COUNT() lavorerà su ogni gruppo separatamente, contando il numero di libri in ogni gruppo.

Se avete bisogno di un ripasso su come lavorare con GROUP BY e le funzioni aggregate, consultate la nostra Panoramica completa delle funzioni GROUP BY e aggregate.

Esercizio 4: Autori per Paese

Esercizio: Per ogni paese, indicare il nome e il numero di autori ad esso associati.

Soluzione:

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

Spiegazione: Questo è un altro esercizio di base di GROUP BY. Selezioniamo i dati dalla tabella autore e li raggruppiamo in base ai valori della colonna country. Poi applichiamo COUNT(author_id) a ciascun gruppo per contare gli autori provenienti da questo Paese.

Esercizio 5: Gamme di pagine per genere

Esercizio: Per ogni genere, mostrare quattro colonne: il nome del genere, il numero minimo e massimo di pagine per i libri di quel genere e difference tra il numero maggiore e minore di pagine di ogni libro.

Soluzione:

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

Spiegazione: Si tratta di un altro esercizio di GROUP BY di base. Selezioniamo i dati dalla tabella autore e li raggruppiamo in base ai valori della colonna paese. Quindi applichiamo COUNT(author_id) a ciascun gruppo per contare gli autori provenienti da questo paese.

Esercizio 5: Gamme di pagine per genere

Esercizio: Per ogni genere, mostrare quattro colonne: il nome del genere, il numero minimo e massimo di pagine per i libri di quel genere e la differenza tra il numero maggiore e minore di pagine di ogni libro.

Soluzione:

Spiegazione: Per ottenere statistiche per ogni genere, raggruppare i dati della tabella in base alla colonna . book dalla tabella in base alla colonna genre.

Utilizzare le funzioni aggregate MIN(pages) e MAX(pages) per calcolare il numero minimo e massimo di pagine. Nella terza colonna, utilizzare MIN(pages) - MAX(pages) per calcolare la differenza per ogni gruppo. Infine, rinominate l'ultima colonna in difference utilizzando AS.

Esercizio 6: Generi di grandi libri

Esercizio: Per ogni genere, mostrare il numero medio di pagine per tutti i libri di quel genere. Mostrare solo i generi in cui il libro medio ha più di 250 pagine. Nominare la colonna delle pagine medie avg_pages.

Soluzione:

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

Spiegazione: Questo esercizio è simile al precedente: si raggruppano i libri per genere e si calcola il numero medio di libri in ciascun genere utilizzando AVG(). Tuttavia, c'è un nuovo elemento: HAVING. HAVING viene utilizzato per filtrare i gruppi e trovare i gruppi per i quali una funzione aggregata soddisfa una certa condizione. Nel nostro caso, cerchiamo i gruppi (generi) in cui il numero medio di pagine è superiore o uguale a 250.

Ricordate che HAVING funziona in modo diverso da WHERE. WHERE è usato per filtrare le singole righe prima del raggruppamento, mentre HAVING è usato per filtrare le righe dopo il raggruppamento. Potete leggere la differenza tra HAVING e WHERE nel nostro articolo HAVING vs. WHERE in SQL: Cosa si deve sapere.

Esercizio 7: Generi moderni

Esercizio: Mostrare l'anno medio di pubblicazione per ogni genere di libri. Arrotondare l'anno a un numero intero. Mostrare solo i generi in cui l'anno medio di pubblicazione è successivo al 1940.

Soluzione:

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

Spiegazione: Questo esercizio è simile al precedente: si raggruppano i libri per genre e si calcola l'anno medio di pubblicazione usando AVG(publication_year). Poi si filtrano i generi con un anno medio di aggregazione superiore al 1940 usando HAVING.

Esercizio 8: Libri con più autori

Esercizio: Per i libri scritti da più di un autore, indicare il titolo e il numero di autori di ciascun libro.

Soluzione:

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;

Spiegazione: Per prima cosa, dobbiamo trovare gli autori di ciascun libro. A tal fine, uniamo le tabelle book e book_author sulla base del loro comune book_id. In questo modo si uniscono i dati di ogni libro con quelli dei suoi autori: una riga per ogni combinazione libro-autore.

Raggruppiamo poi le righe in base a book_id: tutte le righe relative allo stesso libro sono nello stesso gruppo. Pertanto, tutti gli autori di ogni libro saranno nello stesso gruppo. Applichiamo quindi la funzione COUNT(author_id) per contare gli autori in ciascun gruppo.

Infine, filtriamo i libri con un solo autore utilizzando HAVING COUNT(author_id) > 1.

Esercizio 9: Ultimo prestito di ogni libro

Esercizio: Per ogni libro, mostrare il suo title e la data più recente in cui è stato prestato. Nominare la seconda colonna last_loaned. Mostrare NULL nella seconda colonna per tutti i libri che non sono mai stati prestati.

Soluzione:

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;

Spiegazione: Utilizzare un LEFT JOIN per unire le colonne book e book_loan per assicurarsi che anche i libri che non sono mai stati prestati siano inclusi nel set di risultati. Raggruppare i risultati per book_id e book_title.

Si noti che non è sufficiente raggruppare i risultati in base al titolo; se due libri hanno lo stesso titolo, verrebbero erroneamente inseriti nello stesso gruppo. Raggruppate per book_id (perché identifica in modo univoco ogni libro) e title (perché SQL lancia un errore se una colonna non aggregata in SELECT non viene inserita in GROUP BY). Potete leggere il nostro articolo 7 Common GROUP BY Errors.

Per ottenere l'ultimo loan_date, utilizzare MAX(loan_date). Le date successive vengono trattate come "più grandi". Se non ci sono prestiti per questo libro, tutte le date dei prestiti saranno NULL e la funzione MAX() restituirà NULL per questo libro.

Esercizio 10: Prestiti di libri per mese

Esercizio: Mostrare quanti prestiti sono stati effettuati ogni mese di ogni anno. Mostrare tre colonne:

• L'anno e il mese di loan_date come numeri nelle prime due colonne. Nominarle loan_year e loan_month
• Una colonna che conta quanti libri sono stati prestati in quel mese.

Ordinare il risultato in base all'anno e poi al mese, mostrando prima le date più vecchie.

Soluzione:

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;

Spiegazione: Utilizziamo EXTRACT(YEAR FROM loan_date) e EXTRACT(MONTH FROM loan_date) per ottenere le parti dell'anno e del mese da loan_date. Utilizziamo ancora EXTRACT() in GROUP BY per raggruppare i prestiti degli stessi mesi.

Utilizziamo la funzione COUNT() per calcolare il numero di prestiti effettuati in ciascun mese.

Infine, ordiniamo i risultati in base a loan_year e loan_month. Si noti che è possibile utilizzare gli alias delle colonne nell'istruzione ORDER BY. Nella clausola GROUP BY, invece, è ancora necessario utilizzare le funzioni; quando questa clausola viene elaborata, la funzione EXTRACT() (e quindi le nuove colonne) non sono ancora state definite. Per ulteriori informazioni su questo argomento, consultare l'articolo SQL Order of Operations.

Esercizio 11: Libri popolari

Esercizio: Per ogni libro, mostrate il suo title, il numero di volte che è stato prestato e il numero di clienti diversi che hanno preso in prestito il libro. Nominare le ultime due colonne times_loaned e different_patrons.

Soluzione:

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;

Spiegazione: Per trovare i prestiti per ogni libro, è necessario unire le tabelle book e book_loan. Utilizzare un LEFT JOIN per assicurarsi che anche i libri che non sono mai stati prestati compaiano nel risultato.

Vogliamo raggruppare i prestiti per ogni libro, quindi dobbiamo raggruppare sia per book_id che per il libro title (per lo stesso motivo discusso nell'Esercizio 9).

Vogliamo contare il numero di volte in cui il libro è stato prestato e il numero di clienti diversi che hanno preso in prestito il libro. Per farlo, dobbiamo usare la funzione COUNT() due volte.

Per prima cosa, utilizziamo COUNT(loan_id) per contare il numero di prestiti del libro.

Il secondo uso di COUNT() è più interessante: vogliamo contare i diversi clienti che hanno preso in prestito il libro. Se qualcuno ha preso in prestito lo stesso libro più volte, vogliamo contarlo solo una volta. Per questo usiamo COUNT(DISTINCT patron_id). L'uso di DISTINCT assicura che anche se un cliente ha preso in prestito lo stesso libro più volte, il suo ID sarà contato solo una volta.

Per saperne di più, leggere Qual è la differenza tra COUNT(*), COUNT(1), COUNT(column) e COUNT(DISTINCT)?

Esercizio 12: Libri in ritardo

Esercizio: Per ogni cliente, indicare il nome e la quantità di libri scaduti (cioè con una data di restituzione successiva alla data di scadenza).

Soluzione:

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;

Spiegazione: Unire patron e book_loan utilizzando un LEFT JOIN per assicurarsi che anche i clienti che non hanno prestiti scaduti siano inclusi nel risultato.

Per selezionare solo i prestiti la cui data di restituzione è successiva alla data di scadenza, utilizzare una condizione di unione combinata: ON p.patron_id = bl.patron_id AND return_date > due_date. La prima parte unisce solo le righe che sono effettivamente correlate. La seconda parte viene utilizzata come filtro aggiuntivo per unire solo i punti in cui return_date > due_date.

Si noti che questo è diverso dall'uso di una clausola WHERE più avanti nella query. La clausola WHERE scarterà tutte le righe in cui loan_id IS NULL. Tuttavia, vogliamo mantenere queste righe per includere gli utenti che non hanno libri in ritardo.

Raggruppiamo le righe in base a patron_id e patron_name (per gli stessi motivi dell'Esercizio 9). Infine, utilizziamo COUNT(book_id) per contare i libri scaduti per ogni cliente. COUNT() restituirà 0 per gli utenti che non hanno mai effettuato prestiti e per quelli che hanno sempre restituito i libri in tempo.

Esercizio 13: Autori medi per genere

Esercizio: Per ogni genere, indicare il nome e il numero medio di autori dei libri di quel genere. Dare un nome alla seconda colonna average_authors_per_book

Soluzione:

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;

Spiegazione: In questo caso si utilizza una costruzione chiamata espressione di tabella comune (CTE). Per saperne di più sulle CTE, consultate la nostra Guida alle espressioni di tabella comuni.

In breve, una CTE consente di creare un insieme di risultati temporanei con nome da utilizzare nella query. Per creare una CTE si utilizza la seguente sintassi:

WITH <cte_name> AS (query)

Qualsiasi query all'interno della parentesi agirà come una tabella virtuale denominata cte_name e sarà accessibile alla query principale (l'istruzione SELECT dopo la parentesi di chiusura della CTE).

Nella CTE, calcoliamo il numero di autori per ogni libro. Selezioniamo book_id e il numero di autori. Si tratta di una query simile a quella dell'Esercizio 8.

Nella query esterna, uniamo la nostra CTE number_of_authors con la tabella book per visualizzare il genere di ogni libro. Quindi si utilizzano i generi AVG(author_count) e GROUP BY per ottenere il risultato finale.

Se non si vuole usare una CTE, si può ottenere lo stesso risultato usando una sottoquery:

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;

Esercizio 14: Numero di pagine lette dai clienti

Esercizio: Per ogni cliente, indicare il suo nome e il numero totale di pagine lette (cioè il numero di pagine di tutti i libri che ha preso in prestito). Si presume che abbiano letto ogni libro per intero. Includere tutti i libri, anche quelli non ancora restituiti.

Mostrare i risultati solo per gli utenti che hanno letto più di 1.000 pagine.

Soluzione:

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;

Spiegazione: Unire tre tabelle utilizzando un normale JOIN: patron, book_loan, e book. Utilizzare SUM(pages) per sommare il numero di pagine di tutti i libri presi in prestito dal cliente. Filtrare con HAVING SUM(pages) > 1000 per mostrare solo gli utenti che hanno letto più di 1000 pagine.

Nota: poiché si vogliono mostrare gli avventori che hanno letto più di 1.000 pagine, non è necessario usare LEFT JOIN o FULL JOIN in questo caso. Gli utenti che hanno letto 0 pagine saranno comunque filtrati dalla condizione HAVING.

Esercizio 15: Utenti senza libri in prestito

Esercizio: Mostrare il numero totale di utenti che non hanno mai preso in prestito libri.

Soluzione:

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

Spiegazione: Per mostrare solo gli avventori che non hanno mai preso in prestito libri, filtrare il risultato con la clausola WHERE NOT EXISTS. Per ogni cliente che non ha mai preso in prestito libri, non esisterà una voce con il nome di quel cliente. book_loan con il suo id. Usare una sottoquery per trovare una serie di prestiti di libri per ogni utente, quindi usare il risultato di questa sottoquery nella clausola WHERE NOT EXISTS. In questo modo ci si assicurerà che tutti i patroni dell'insieme risultante non abbiano prestiti_libraricorrispondenti.

Infine, utilizzare la funzione COUNT() per contare i patron_ids selezionati.

Esercizio 16: Autori e pubblico

Esercizio: Per ogni autore, mostrare quanti mecenati diversi hanno preso in prestito il suo libro.

Soluzione:

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;

Spiegazione: Unire quattro tabelle: author, book_author, book, e book_loan. Utilizzare un JOIN regolare con le prime tre tabelle e un LEFT JOIN tra libro e libro_prestito. Il LEFT JOIN assicura che anche se il libro non è mai stato prestato, sarà comunque mostrato nel risultato.

SELECT Il nome dell'autore e l'utilizzo di COUNT(DISTINCT patron_id) per contare tutti i diversi clienti che hanno preso in prestito i libri. Se i libri dell'autore non sono mai stati presi in prestito, COUNT() restituirà 0.

Raggruppare i risultati in base all'ID e al nome dell'autore per evitare gli errori di cui abbiamo parlato in precedenza.

Esercizio 17: I libri più vecchi

Esercizio: Trovare i libri più vecchi nel database (cioè i libri con il più vecchio publication_year). Mostrare solo due colonne: title e publication_year.

Ricordate che può esserci più di un libro con l'anno di pubblicazione più vecchio.

Soluzione:

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

Spiegazione: Utilizzare una sottoquery per trovare i libri più vecchi. Selezionare solo title e l'anno di pubblicazione dei libri che hanno publication_year uguale all'anno di pubblicazione più basso del sistema. È possibile trovare i primi publication_year con MIN(publication_year). Utilizzare questa espressione all'interno di una sottoquery, quindi confrontare publication_year di ciascun libro con il risultato della sottoquery.

Esercizio 18: Utenti più attivi

Esercizio: Trovare i nomi di tutti gli utenti che hanno preso in prestito un numero di libri superiore alla media. Mostrare il numero di libri presi in prestito insieme al loro nome.

Soluzione:

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

Spiegazione: Unire i numeri patron e book_loan e raggruppare i risultati in base al nome e all'ID dell'utente. Per mostrare solo gli utenti che hanno preso in prestito un numero di libri superiore alla media, utilizzare la clausola HAVING che confronta il numero di prestiti dell'utente corrente con il numero medio di libri presi in prestito per utente. La media si ottiene dividendo il numero totale di prestiti per il numero totale di utenti.

Poiché / in SQL è una divisione intera (il che significa che il resto viene scartato), utilizzate > (maggiore di) e non >= (maggiore o uguale) per confrontare i valori nella clausola HAVING.

Esercizio 19: L'autore più produttivo

Esercizio: Trovare l'autore che ha scritto il maggior numero di libri.

Soluzione:

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

Spiegazione: Nella CTE authors_books_count, si trova il numero di libri che ogni autore ha scritto utilizzando il sito id.

Nella query esterna, selezioniamo gli autori il cui numero di libri è uguale al numero massimo di libri. Utilizziamo una sottoquery e la funzione MAX() per selezionare il numero massimo di libri dal CTE e confrontarlo con book_count per ogni autore.

Volete fare più pratica con le funzioni aggregate SQL?

Con questo si conclude questa serie di esercitazioni sulle funzioni aggregate SQL. Ora le vostre competenze sulle funzioni aggregate sono aggiornate! Potete verificare le vostre conoscenze teoriche con queste domande di intervista su GROUP BY.

Abbiamo trattato diverse funzioni aggregate, GROUP BY, HAVING e molto altro ancora! Abbiamo fatto pratica con i diversi tipi di JOINs, le subquery e il lavoro con i NULL. Volete saperne di più? Date un'occhiata agli esercizi della nostra enorme traccia Pratica su SQL , che contiene più di 100 ore di esercitazioni su SQL! Oppure provate il nostro Pratica Mensile su SQLche offre un nuovo corso pratico di SQL ogni mese!