Pratica SQL: 10 esercizi pratici di GROUP BY con soluzioni dettagliate

Avete bisogno di esercitarvi in SQL? Questi 10 esercizi di GROUP BY, con spiegazioni e soluzioni, sono un ottimo inizio!

GROUP BY è una potente clausola SQL che consente di creare gruppi di record e di calcolare metriche di riepilogo (come le medie) per tali gruppi. Tuttavia, GROUP BY è spesso difficile da padroneggiare per gli studenti di SQL. Tuttavia, fare pratica con GROUP BY è molto importante se si intende utilizzare SQL.

In questo articolo abbiamo raccolto diversi esercizi pratici su GROUP BY per aiutarvi a comprendere meglio il funzionamento di questa clausola.

Indice dei contenuti

• Introduzione
• Rassegna della clausola GROUP BY
• Esercizi
  • Esercizio 1: Fatturato totale per ogni azienda
  • Esercizio 2: Giochi prodotti all'anno con ricavi e costi medi
  • Esercizio 3: Numero di giochi redditizi per ogni tipo di gioco
  • Esercizio 4: Fatturato totale per tipo di gioco nei sistemi PS2 e PS3
  • Esercizio 5: Profitto lordo totale per azienda
  • Esercizio 6: Profitto lordo annuale per azienda
  • Esercizio 7: Differenza tra i profitti lordi annuali
  • Esercizio 8: Aziende che producono più di un gioco
  • Esercizio 9: Aziende che producono giochi "buoni" con ricavi superiori a 4 milioni di euro
  • Esercizio 10: Aziende leader per tipo di gioco
  • Esercizio 11: Continuare a praticare le abilità SQL

Esercizio GROUP BY

Questo articolo contiene 10 esercizi di GROUP BY per aiutarvi a fare pratica. Imparare SQL facendo esercizi è uno dei modi migliori per affinare le proprie capacità.

Gli esercizi di questo articolo provengono dal nostro Esercizi SQL. Contiene più di 80 esercizi SQL interattivi che coprono argomenti come query semplici, JOIN, subquery e, naturalmente, GROUP BY. Offriamo anche altri set di esercizi, tra cui:

• Basic SQL Practice: Un negozio
• Basic SQL Practice: Università
• Basic SQL Practice: Blog e dati sul traffico

Ora che sapete quali sono le risorse per esercitarsi con SQL disponibili, analizziamo la clausola GROUP BY. Se non sapete nulla di GROUP BY, iniziate leggendo GROUP BY in SQL spiegato e 5 esempi di GROUP BY.

La clausola GROUP BY

In questi esercizi pratici di GROUP BY, utilizzeremo una tabella chiamata games che contiene informazioni sui videogiochi. Dobbiamo sottolineare il fatto che, sebbene i nomi dei giochi siano reali, gli altri campi della tabella contengono dati completamente inventati. Di seguito è riportata una vista parziale della tabella:

Rivediamo brevemente la clausola GROUP BY. Questa clausola ci permette di creare gruppi di record e di calcolare diverse metriche per ogni gruppo (come la media, il minimo o il massimo di ogni gruppo). Di seguito è riportata una semplice query che utilizza GROUP BY per calcolare il numero di giochi prodotti da ciascuna azienda:

SELECT 
  company, 
  COUNT(*)
FROM games
GROUP BY company;

Questa query indica al database di creare gruppi di righe dalla tabella games che hanno lo stesso valore nella colonna company. (Nell'immagine sottostante, le righe con lo stesso colore fanno parte dello stesso gruppo, cioè sono prodotte dalla stessa azienda). Quindi si utilizza la funzione COUNT(*) per contare la quantità di righe in ciascun gruppo; questo restituisce il numero di giochi prodotti da ciascuna azienda.

Ecco la tabella con le righe codificate per colore. Ogni riga con lo stesso colore appartiene allo stesso gruppo:

Ed ecco il risultato:

Esercizio 1: ricavi totali per ogni azienda

Esercizio:

Ottenere il nome e il fatturato totale di ogni azienda.

Soluzione:

SELECT
  company, 
  SUM(revenue)
FROM games
GROUP BY company;

Spiegazione:

Nella query, si utilizza la clausola GROUP BY company per creare gruppi di righe con lo stesso valore nella colonna company. Quindi, la funzione SUM(revenue) viene eseguita per ogni gruppo di righe e il risultato viene mostrato insieme al nome dell'azienda.

Ogni riga del risultato è associata a un gruppo di righe. Questo è un punto importante quando si utilizza GROUP BY: i dettagli delle singole righe vengono compressi in una riga per gruppo e i dati restituiti riguardano il gruppo di righe.

Prima di passare al prossimo esercizio, vorrei raccomandare il corso Pratica Mensile su SQL, dove è possibile trovare un'ulteriore fonte di esercizi di pratica SQL. Ogni mese pubblichiamo un nuovo corso di esercitazioni SQL.

Esercizio 2: Giochi prodotti all'anno con ricavi e costi medi

Esercizio:

Generare un report con l'anno di produzione e il numero di giochi rilasciati quest'anno (denominato count), la media del costo di produzione per tutti i giochi prodotti in questo anno (denominato avg_cost) e il ricavo medio per quell'anno (denominato avg_revenue).

Soluzione:

SELECT   
  production_year,
  COUNT(*) AS count,
  AVG(production_cost) AS avg_cost,
  AVG(revenue) AS avg_revenue
FROM games
GROUP BY production_year;

Spiegazione:

Questo esercizio è simile al precedente. Qui si utilizzano le funzioni aggregate COUNT() e AVG() per calcolare le metriche. Inoltre, rinominiamo le colonne del report per descrivere in modo appropriato il loro contenuto (come count, avg_cost e avg_revenue). Per rinominare una colonna, si utilizza la clausola AS seguita dal nome da assegnare alla colonna, ad esempio:

AVG(production_cost) AS avg_cost

Esercizio 3: Numero di partite redditizie di ogni tipo di gioco

Esercizio:

Contare quanti giochi di un determinato tipo sono redditizi (cioè le entrate sono superiori ai costi di produzione). Mostrare il tipo di gioco e il numero di giochi redditizi (denominato number_of_games) per ciascun tipo.

Soluzione:

SELECT    
  type,
  COUNT(*) AS number_of_games
FROM games
WHERE revenue > production_cost
GROUP BY type;

Spiegazione:

In questo esercizio, la condizione WHERE viene eseguita per prima. Essa determina quali giochi sono redditizi (hanno entrate maggiori del costo di produzione). Successivamente, GROUP BY raggruppa le righe (giochi) dello stesso type. Infine, la funzione COUNT(*) viene applicata a ciascun gruppo di righe per ottenere il numero di giochi redditizi di ciascun tipo.

Esercizio 4: Ricavi totali per tipo di gioco nei sistemi PS2 e PS3

Esercizio:

Ottenere il tipo di gioco e il ricavo totale generato per i giochi con production_year dopo il 2010 e con PS2 o PS3 system. Ordinare i risultati in modo che i tipi con i ricavi più elevati siano i primi.

Soluzione:

SELECT
  type,
  SUM(revenue) AS total_revenue
FROM games
WHERE production_year > 2010 AND systems IN ('PS2','PS3')
GROUP BY type;

Spiegazione:

Come nell'esercizio precedente, per prima cosa filtriamo le righe; questa volta le condizioni sono production_year > 2010 e system IN (‘PS2’, ‘PS3’). Una volta filtrate le righe, si procede a creare gruppi di righe utilizzando la clausola GROUP BY. Infine, si applica la funzione SUM(revenue) a ciascun gruppo di righe per ottenere il fatturato totale del gruppo.

Esercizio 5: Profitto lordo totale per azienda

Esercizio:

Per tutte le aziende presenti nella tabella, ottenere i loro nomi e la somma degli utili lordi per tutti gli anni. (Si supponga che l'utile lordo = ricavi - costi di produzione). Nominare questa colonna gross_profit_sum. Ordinare i risultati in base all'utile lordo, in ordine decrescente.

Soluzione:

SELECT
  company,
  SUM(revenue - production_cost) AS gross_profit_sum
FROM games
GROUP BY company
ORDER BY gross_profit_sum DESC;

Spiegazione:

Nella query, utilizziamo l'espressione revenue - production_cost per calcolare il profitto lordo per ogni gioco. Se creiamo dei gruppi utilizzando la colonna azienda e poi sommiamo i profitti lordi di tutti i giochi di quell'azienda, otteniamo il profitto lordo globale dell'azienda.

Notate la clausola ORDER BY gross_profit_sum DESC. La parola chiave DESC indica l'ordine decrescente; in altre parole, il valore più alto di gross_profit_sum appare per primo nel risultato.

Esercizio 6: Profitto lordo annuale per azienda

Esercizio:

Ottenere il profitto lordo annuale di ogni azienda. In altre parole, vogliamo un report con il nome dell'azienda, l'anno e il profitto lordo di quell'anno. Ordinare il report per nome dell'azienda e anno.

Soluzione:

SELECT
  company,
  production_year,
  SUM(revenue - production_cost) AS gross_profit_sum
FROM  games
GROUP BY company, production_year 
ORDER BY company, production_year;

Spiegazione:

Poiché si vuole ottenere l'utile lordo suddiviso per anno, è necessario utilizzare il comando GROUP BY company, production_year clause. In questo modo si crea un gruppo di righe per ogni coppia di valori company e production_year. Per ogni gruppo si calcola l'espressione SUM(revenue - production_cost), che rappresenta l'utile lordo.

Esercizio 7: Differenza tra i profitti lordi annuali

Esercizio:

Generare un report che mostri l'utile lordo annuale di ogni azienda, l'utile lordo dell'anno precedente e la differenza tra i due anni. Suggerisco di utilizzare la query precedente come punto di partenza.e

Soluzione:

WITH company_gross_profit AS (
  SELECT
    company,
    production_year AS year,
    SUM(revenue - production_cost) AS gross_profit
  FROM games
  GROUP BY company, production_year 
  ORDER BY company, production_year 
)
SELECT 
  cur.company,
  cur.year,
  cur.gross_profit,
  prev.gross_profit,
  cur.gross_profit - prev.gross_profit AS profit_delta
FROM company_gross_profit AS cur, 
LEFT JOIN company_gross_profit AS prev 
ON cur.company = prev.company AND cur.year = prev.year + 1;

Spiegazione:

Questa query si basa su una CTE chiamata company_gross_profit, che viene creata utilizzando la clausola WITH nella prima parte della query. Successivamente, la query principale fa riferimento alla tabella CTE company_gross_profit due volte in un self-join; la tabella "sinistra" o "prima" è denominata cur (corrente) e l'altra è denominata prev (precedente). Poi uniamo due righe della stessa azienda ma di anni contigui. La condizione per farlo è:

cur.company = prev.company AND cur.year = prev.year + 1

Infine, la colonna profit_delta è un campo calcolato. Si ottiene utilizzando la differenza tra l'utile lordo dell'anno in corso e l'utile lordo dell'anno precedente:

cur.gross_profit - prev.gross_profit AS profit_delta

Le CTE, o espressioni comuni di tabella, sono una funzione avanzata di SQL. Se volete saperne di più sulle CTE, vi consiglio di leggere gli articoli Una guida alle espressioni di tabella comuni di SQL, Le CTE di SQL spiegate con esempi e Come imparare le espressioni di tabella comuni di SQL (CTE).

Esercizio 8: Aziende che producono più di un gioco

Esercizio:

Per ogni azienda, selezionare il nome, il numero di giochi prodotti (colonna number_of_games ) e il costo medio di produzione (colonna avg_cost ). Mostrare solo le aziende che producono più di un gioco.

Soluzione:

SELECT 
  company,
  COUNT(company) AS number_of_games,
  AVG(production_cost) AS avg_cost
FROM games
GROUP BY company
HAVING COUNT(*) > 1;

Spiegazione:

In questa query, si utilizza la clausola HAVING invece della clausola WHERE. Mentre WHERE filtra i singoli record, HAVING si usa per applicare filtri a gruppi di record.

In questa query, vogliamo un report che mostri solo le aziende che hanno prodotto più di un gioco. Dopo aver inserito la società GROUP BY, è possibile utilizzare la condizione COUNT(*) > 1 per scartare le società che hanno prodotto un solo gioco.

In SQL, è possibile utilizzare direttamente le condizioni che coinvolgono le funzioni aggregate solo nella clausola HAVING; nella clausola WHERE, è necessario utilizzare una sottoquery. Questo perché WHERE viene elaborato prima delle funzioni aggregate nell'ordine delle operazioni di SQL.

Se avete dubbi sulla clausola HAVING, vi consiglio di leggere il nostro articolo La clausola HAVING di SQL spiegata, in cui discutiamo in dettaglio come usare questa clausola.

Esercizio 9: Aziende che producono giochi "buoni" con ricavi superiori a 4 milioni di euro

Esercizio:

Siamo interessati ai giochi di qualità prodotti tra il 2000 e il 2009. Un buon gioco è un gioco redditizio con una valutazione superiore a 6. Per ogni azienda, indicare il nome della società, i ricavi totali dei giochi buoni prodotti tra il 2000 e il 2009 (colonna revenue_sum ) e il numero di giochi buoni prodotti in questo periodo (colonna number_of_games ). Mostrare solo le aziende con ricavi da giochi validi superiori a 4 000 000.

Soluzione:

SELECT 
  company,
  COUNT(company) AS number_of_games,
  SUM(revenue) AS revenue_sum
FROM games
WHERE production_year BETWEEN 2000 AND 2009
  AND rating > 6
  AND revenue - production_cost > 0
GROUP BY company
HAVING SUM(revenue) > 4000000;

Spiegazione:

Nella query, utilizziamo la società GROUP BY perché le metriche che vogliamo ottenere (number_of_games e revenue_sum) sono a livello di società. Il punto interessante di questa query sono i filtri che applichiamo ai dati. Alcuni di essi sono a livello di riga e devono essere presenti nella clausola WHERE:

• production_year BETWEEN 2000 AND 2009
• rating > 6
• revenue - production_cost > 0

Tuttavia, c'è un altro filtro a livello di gruppo, che deve essere inserito nella colonna HAVING:

• SUM(revenue) > 4000000

Esercizio 10: Aziende leader per tipo di gioco

Esercizio:

Restituire un elenco di aziende e tipi di giochi in cui l'azienda è leader di mercato. Un leader di mercato per un tipo di gioco è un'azienda che ha un fatturato totale per quel tipo di gioco superiore al fatturato totale di tutte le altre aziende per quel tipo di gioco.

Mostrate il nome dell'azienda, il tipo di gioco e il fatturato totale dell'azienda per quel tipo di gioco.

Soluzione:

SELECT 
  company, 
  type, 
  SUM(revenue) as total_revenue
FROM games g1
GROUP BY company, type
HAVING SUM(revenue) > ( SELECT SUM(revenue) 
                        FROM games g2
                        WHERE g2.type = g1.type
                        AND g2.company <> g1.company );

Spiegazione:

Innanzitutto, si noti la clausola GROUP BY company, type. La usiamo perché vogliamo i ricavi dell'azienda per tutti i giochi dello stesso tipo.

Tuttavia, il punto più interessante è l'uso di una sottoquery nella clausola HAVING. Vogliamo aziende con una somma di ricavi maggiore del totale dei ricavi delle altre aziende; utilizziamo una subquery per ottenere il totale dei ricavi delle altre aziende.

La subquery fa riferimento alle colonne g1.company e g1.type, che sono le colonne azienda e tipo della query esterna. Questi riferimenti nella subquery ci permettono di ottenere i ricavi totali delle altre aziende per lo stesso tipo di gioco.

Esercizio 11: Continuare a fare pratica con l'SQL

Abbiamo svolto dieci esercizi su GROUP BY con diversi livelli di complessità, dai semplici aggregati alle CTE e alle subquery. Imparare il GROUP BY utilizzando esercizi pratici è uno degli approcci migliori per padroneggiare questa clausola. L'articolo TOP 9 SQL GROUP BY Interview Questions illustra le domande più comuni che vengono poste durante un colloquio di lavoro in SQL; è anche un ottimo modo per esercitarsi se non si ha un colloquio imminente.

Il nostro pacchetto Tutto per Sempre è un altro modo per esercitarsi con l'SQL, ed è molto completo! Questo pacchetto contiene tutti gli oltre 70 corsi SQL attuali in quattro diversi dialetti SQL (e l'accesso ai corsi futuri che creeremo). Tutti i corsi della nostra piattaforma sono pratici e interattivi. Quindi, scegliete il vostro percorso di pratica e iniziate a essere fantastici nell'uso di GROUP BY!