10 esercizi sulle subquery correlate con soluzioni

Le subquery correlate sono una potente funzione SQL essenziale per l'analisi avanzata dei dati. Questo articolo fornisce 10 esercizi pratici per aiutarvi a padroneggiare le subquery correlate.

L'SQL è un'abilità fondamentale per chiunque lavori con i dati, sia che si tratti di un analista di dati, di uno sviluppatore SQL, di un ingegnere dei dati o di qualsiasi altra professione correlata. La padronanza di SQL non si limita alla semplice comprensione delle nozioni di base. È necessario anche imparare funzioni SQL avanzate, come le subquery.

Una subquery è una query SQL annidata all'interno di una query più grande. Esistono diversi tipi di subquery. Una subquery correlata è un tipo di subquery che fa riferimento alla query esterna e non può essere eseguita in modo indipendente. Le subquery, e in particolare le subquery correlate, possono essere un argomento impegnativo per gli studenti di SQL.

In precedenza, abbiamo fornito una raccolta di esercizi sulle subquery nel nostro articolo: Esercitazioni sulle subquery SQL: 15 esercizi con soluzioni. In questo articolo vogliamo fornirvi una serie di esercizi pratici specifici sull'argomento delle subquery correlate. Gli esercizi di questo articolo sono tratti dai nostri corsi interattivi Basic SQL Practice: A Store e SQL Practice: University. Entrambi i corsi fanno parte del percorso "Pratica su SQL", che offre diversi corsi di pratica di SQL. Tutti i corsi della traccia si basano su scenari reali e sono organizzati per argomenti SQL, per aiutarvi a concentrarvi sulla vostra area di interesse.

Esercizi su un database Store

La prima serie di esercizi si basa sul database di un negozio online. Questi esercizi sono tratti dal nostro corso Basic SQL Practice: A Store. Inizieremo in piccolo e lavoreremo con due sole tabelle: product e category.

La tabella product contiene un elenco di prodotti disponibili nel negozio.

• product_id - l'ID del prodotto
• product_name - il nome del prodotto
• category_id - l'ID della categoria del prodotto. Aiuta a connettersi con la tabella delle categorie.
• quantity_per_unit - la quantità di articoli del prodotto in un'unità.
• unit_price - il prezzo del prodotto
• discontinued - l'informazione se il prodotto è ancora disponibile nel negozio (valore FALSO) o se è stato dismesso (VERO).

La tabella category contiene informazioni sulle categorie dei prodotti:

• category_id - l'ID della categoria
• name - il nome della categoria
• description - la descrizione opzionale della categoria

Esercizio 1: Prodotti costosi in ogni categoria

Esercizio: Trovare i prodotti che sono più costosi del prezzo medio dei prodotti della propria categoria. Includere nel risultato il nome della categoria, il nome del prodotto e il prezzo unitario.

Soluzione:

SELECT
  c.name,
  p.product_name,
  p.unit_price
FROM category AS c
JOIN product AS p
  ON c.category_id = p.category_id
WHERE p.unit_price > (
  SELECT AVG(unit_price)
  FROM product
  JOIN category
    ON product.category_id = category.category_id
  WHERE category.category_id = c.category_id
  );

Spiegazione della soluzione:

Nella soluzione, si utilizza una sottoquery correlata per calcolare il prezzo unitario medio dei prodotti appartenenti alla stessa categoria del prodotto elaborato dalla query esterna. Questa sottoquery fa riferimento alla categoria, indicata come c, dalla query esterna. Identifica i prodotti di questa categoria e calcola il loro prezzo medio. I prodotti che costano più della media della categoria vengono filtrati in base a questa media. La query esterna recupera e visualizza il nome della categoria, il nome del prodotto e il prezzo unitario di questi prodotti.

Esercizio 2: Il numero di prodotti costosi in ogni categoria

Esercizio: Mostrare i nomi delle categorie e il numero di prodotti di questa categoria con un prezzo unitario superiore al prezzo medio di un prodotto di questa categoria. Mostrare solo le categorie che hanno tali prodotti. Visualizzare due colonne: name (il nome della categoria) e expensive_products (il numero di prodotti che costano più del prodotto medio di questa categoria).

Soluzione:

SELECT
  c.name,
  COUNT(*) AS expensive_products
FROM category AS c
JOIN product AS p
  ON c.category_id = p.category_id
WHERE p.unit_price > (
  SELECT
    AVG(unit_price)
  FROM product
  JOIN category
    ON product.category_id = category.category_id
  WHERE category.category_id = c.category_id
  )
GROUP BY
  c.category_id,
  c.name;

Spiegazione della soluzione:

Questo esercizio è simile al precedente. Anche in questo caso dobbiamo trovare i prodotti con un prezzo unitario superiore al prezzo medio della categoria. Tuttavia, questa volta vogliamo contare i prodotti di ogni categoria.

Nella soluzione, utilizziamo la stessa sottoquery correlata di prima: calcoliamo il prezzo unitario medio dei prodotti della stessa categoria del prodotto elaborato dalla query esterna. Questa media viene poi utilizzata nella query principale per filtrare solo i prodotti il cui prezzo unitario è superiore alla media della categoria. La query principale conta questi prodotti utilizzando COUNT(*) e raggruppa i risultati per categoria con GROUP BY. Il risultato finale mostra il nome della categoria e il numero di prodotti.

Esercizio 3: Prodotti fuori produzione, continuati e tutti i prodotti di una categoria

Esercizio: Per ogni categoria, visualizzare il suo name, il numero di prodotti interrotti in questa categoria (discontinued_products), il numero di prodotti continuati in questa categoria (continued_products) e il numero di tutti i prodotti in questa categoria (all_products).

Soluzione:

SELECT
  c.name,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id AND discontinued IS TRUE) AS discontinued_products,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id AND discontinued IS FALSE) AS continued_products,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id) AS all_products
FROM category c;

Spiegazione della soluzione:

Qui si utilizzano tre sottoquery correlate nell'istruzione SELECT per ottenere il conteggio dei prodotti in base al loro stato di continuazione/discontinuazione per ogni categoria. Ogni sottoquery si correla con la query principale attraverso l'istruzione category_id in modo che i conteggi siano corretti per ogni categoria.

La prima sottoquery conta il numero di prodotti in una categoria in cui il flag discontinued è TRUE. In questo modo si ottiene un conteggio totale di tutti i prodotti non più disponibili per ogni categoria. La seconda subquery conta il numero di prodotti ancora attivi in ogni categoria, utilizzando un metodo simile. La terza sottoquery conta semplicemente il numero di tutti i prodotti di una categoria.

In alternativa, questo esercizio potrebbe essere risolto senza subquery, utilizzando una combinazione di CASE WHEN con SUM e GROUP BY. Come esercizio alternativo: potete risolvere questo esercizio in entrambi i modi?

Altre tabelle nel database del negozio: Tabelle Clienti e Acquisti

Nei prossimi esercizi lavoreremo con altre tabelle del database del negozio relative agli acquisti: customer, purchase, e purchase_item.

La tabella customer contiene le informazioni sui clienti. Ha le seguenti colonne:

• customer_id - l'ID del cliente.
• contact_name - il nome completo del cliente
• contact_email - l'e-mail del cliente.

La tabella purchase contiene le informazioni su ciascun ordine:

• purchase_id - l'ID dell'acquisto.
• customer_id - l'ID del cliente.
• total_price - il prezzo totale dell'ordine.
• purchase_date - il timestamp dell'acquisto.

La tabella purchase_item collega gli acquisti con i prodotti. La tabella contiene le seguenti colonne

• purchase_id - l'ID dell'acquisto.
• product_id - l'ID del prodotto acquistato.
• unit_price - il prezzo di un'unità di prodotto.
• quantity - il numero di unità acquistate di un prodotto.

Esercizio 4: Prodotti in categorie

Esercizio: Per ogni prodotto acquistato, visualizzare il nome del prodotto, la quantità massima in cui è stato acquistato e il numero di acquisti (di questo prodotto in questa quantità massima). Visualizzare tre colonne: product_name, quantity, e purchases_number.

Soluzione:

SELECT 
  product_name,
  quantity,
  COUNT(purchase_id) AS purchases_number
FROM purchase_item pi
JOIN product p
  ON pi.product_id = p.product_id 
WHERE quantity = (SELECT MAX(quantity) FROM purchase_item WHERE product_id = pi.product_id)
GROUP BY
  pi.product_id,
  product_name,
  quantity;

Spiegazione della soluzione:

Vogliamo trovare la quantità massima in cui ogni prodotto è stato acquistato e il numero di acquisti effettuati. Per ottenere questo risultato utilizziamo una subquery correlata e GROUP BY.

Per prima cosa, utilizziamo una sottoquery correlata in WHERE per determinare la quantità massima in cui ogni prodotto è stato acquistato. Poi, nella query esterna, utilizziamo questo valore per trovare gli acquisti in cui la quantità è uguale alla quantità massima. Infine, la query esterna raggruppa i risultati per product_id, product_name e quantity e utilizza la funzione aggregata COUNT(purchase_id) per calcolare il numero di acquisti per ciascun prodotto alla quantità massima.

Esercizio 5: Percentuale di denaro speso dal cliente per l'acquisto

Esercizio: Per ogni cliente che ha effettuato un qualsiasi acquisto, visualizzare l'ID di ogni acquisto effettuato da questo cliente, la percentuale del denaro speso per quell'acquisto rispetto a tutto il denaro speso da quel cliente. Arrotondare le percentuali a numeri interi. Mostrare tre colonne: contact_name, purchase_id e percentage.

Soluzione:

SELECT
  contact_name,
  purchase_id,
  ROUND(total_price * 100.0 / (SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id)) AS percentage
FROM purchase p
JOIN customer 
  ON p.customer_id = customer.customer_id;

Spiegazione della soluzione:

Nella query esterna elenchiamo ogni acquisto effettuato da ciascun cliente. Uniamo le tabelle purchase e customer per visualizzare il nome del contatto del cliente e l'ID dell'acquisto. Utilizziamo una sottoquery correlata per scoprire l'importo totale speso dal cliente corrente. Utilizziamo l'importo trovato dalla sottoquery per calcolare la percentuale della spesa totale che rappresenta l'acquisto corrente.

Esercizio 6: Clienti con acquisti superiori alla loro spesa media

Esercizio: Trovare i clienti il cui ultimo acquisto è stato superiore all'importo medio. Visualizzare il nome del cliente e il totale dell'ultimo acquisto.

Soluzione:

SELECT 
  c.contact_name, 
  p.total_price AS last_purchase_total
FROM purchase p
JOIN customer c 
ON p.customer_id = c.customer_id
WHERE p.purchase_date = (
    SELECT MAX(lp.purchase_date)
    FROM purchase lp
    WHERE lp.customer_id = p.customer_id
  )
AND p.total_price > (
    SELECT AVG(ap.total_price)
    FROM purchase ap
    WHERE ap.customer_id = p.customer_id
);

Spiegazione della soluzione:

Questo esercizio richiede due sottoquery. La prima subquery viene utilizzata per trovare la data dell'ultimo acquisto di ciascun cliente, in modo simile alla subquery dell'Esercizio 4, ma concentrandosi sulla data della transazione anziché sulle quantità. La seconda subquery calcola il prezzo totale medio degli acquisti del cliente e viene utilizzata per filtrare gli acquisti il cui prezzo è superiore alla media. Entrambe le sottoquery sono combinate nella clausola WHERE utilizzando l'operatore AND.

Esercizi sul modello di dati dell'università

La seconda serie di esercizi di questo articolo si basa sul database di un'università. Questi esercizi sono tratti dal nostro corso di SQL Practice: University. Il database dell'università ha 4 tabelle.

La tabella course contiene informazioni sui corsi offerti dall'università e sui percorsi di apprendimento a cui sono associati:

• id - Un ID univoco per ogni corso.
• title - Il nome del corso.
• lecture_hours - Numero totale di ore di lezione del corso.
• tutorial_hours - Il numero totale di ore di lezione del corso.

La tabella student contiene tutte le informazioni su ogni studente dell'università:

• id - Un ID univoco per ogni studente.
• first_name - Il nome dello studente.
• last_name - Il cognome dello studente.

La tabella course_edition contiene informazioni su quali docenti insegnano ogni corso in ogni semestre:

• id - L'ID dell'edizione del corso.
• course_id - L'ID del corso.
• academic_semester - L'anno solare e il trimestre (autunno o primavera) del semestre.
• lecturer - il nome del docente che tiene il corso.

La tabella course_enrollment contiene le informazioni relative agli studenti iscritti a un'edizione del corso:

• course_edition_id - L'ID dell'edizione del corso.
• student_id - l'ID dello studente.
• midterm_grade - Il voto intermedio ricevuto dallo studente.
• final_grade - Il voto finale ricevuto dallo studente.
• course_letter_grade - Il voto del corso, sotto forma di lettera (A+, A, A-, B+, B, B-, C+, C, C-, D+, D, D- o F).
• passed - Se lo studente ha superato (TRUE) o non superato (FALSE) il corso.

Se uno studente non ha ancora sostenuto l'esame, nella tabella possono essere presenti alcuni campi NULL.

Esercizio 7: Studenti con voti superiori alla media

Esercizio: Trovare gli studenti che hanno ottenuto un voto finale superiore alla media in una qualsiasi delle edizioni del corso che hanno frequentato. Mostrare: nome e cognome dello studente, titolo del corso e final_grade. Mostrare solo gli studenti il cui voto finale in questa edizione del corso è stato superiore alla media dei voti finali in questa edizione del corso.

Soluzione:

SELECT
  first_name,
  last_name,
  title,
  final_grade
FROM course_enrollment AS c_en
JOIN student AS s
  ON c_en.student_id = s.id
JOIN course_edition AS c_ed
  ON c_en.course_edition_id = c_ed.id
JOIN course AS c
  ON c_ed.course_id = c.id
WHERE final_grade > (
  SELECT AVG(final_grade)
  FROM course_enrollment AS c_e
  WHERE c_e.course_edition_id = c_en.course_edition_id
);

Spiegazione della soluzione:

La soluzione utilizza una singola query correlata. Nella sottoquery correlata si trova la media dei voti finali per l'edizione del corso elaborata nella query principale. Utilizziamo quindi la media trovata nella sottoquery per filtrare le iscrizioni ai corsi in cui il voto finale dello studente era superiore alla media. Nella query principale uniamo le tabelle course_enrollment, student, course_edition e course per combinare il nome e il cognome dello studente con il titolo del corso e il voto ricevuto.

Esercizio 8: Numero di studenti con lo stesso voto o superiore

Esercizio:Per ogni voto finale nella tabella course_enrollment, contare il numero di studenti che hanno ottenuto esattamente quel voto o un voto superiore. Mostrare due colonne: final_grade e students_number.

Soluzione:

SELECT DISTINCT
  final_grade,
  (SELECT COUNT (student_id)
   FROM course_enrollment
   WHERE final_grade >= c.final_grade) AS students_number
FROM course_enrollment c;

Spiegazione della soluzione:

La query principale seleziona ogni voto finale unico (distinto) dalla tabella course_enrollment. Per ognuno di questi voti, la sottoquery correlata conta il numero di studenti il cui voto finale è uguale o superiore al voto elaborato dalla query esterna. Il risultato viene visualizzato in due colonne: final_grade students_number , che indica il numero di studenti che hanno ottenuto quel voto o un voto superiore.

Esercizio 9: Studenti promossi e non promossi

Esercizio: Dividere gli studenti di ogni edizione del corso in due gruppi: quelli che hanno superato il corso e quelli che non l'hanno superato. Per ogni gruppo, visualizzare le seguenti colonne:

• course_edition_id
• passed
• average_final_grade - La media degli studenti di questo gruppo (promossi o non promossi), arrotondata al secondo decimale.
• average_edition_grade - La media complessiva degli studenti di questa edizione del corso, arrotondata al secondo decimale.

Soluzione:

SELECT
  course_edition_id,
  passed,
  ROUND(AVG(final_grade), 2) AS average_final_grade,
  (SELECT ROUND(AVG(final_grade), 2)
         FROM course_enrollment
         WHERE course_edition_id = c_e.course_edition_id) AS average_edition_grade
FROM course_enrollment c_e
GROUP BY 
  course_edition_id, 
  passed;

Spiegazione della soluzione:

La query esterna seleziona l'ID dell'edizione del corso, il valore di passed e la media dei voti finali per questo gruppo di studenti ("superato" o "non superato") usando la clausola regolare GROUP BY. Utilizza anche una sottoquery correlata per trovare il voto finale medio complessivo per l'edizione del corso elaborata nella query esterna e aggiunge questa media al risultato della query. In questo modo, è possibile confrontare il voto finale di ciascun gruppo con la media generale.

Esercizio 10: Ancora medie

Esercizio: Per ogni edizione del corso, mostrate i seguenti dati:

• title
• average_result - La media dei voti finali degli studenti con un voto finale superiore alla media dell'edizione del corso. Arrotondare il risultato a 0 decimali (cioè a un numero intero).
• results_better_than_average - Il numero di studenti il cui voto finale è superiore al voto finale medio dell'edizione del corso.

Soluzione:

SELECT
  c.title,
  ROUND(AVG(final_grade)) AS average_result,
  COUNT (student_id) AS results_better_than_average
FROM course_enrollment AS c_en
JOIN course_edition AS c_ed
  ON c_en.course_edition_id = c_ed.id
JOIN course AS c
  ON c_ed.course_id = c.id
WHERE final_grade > (
  SELECT AVG(final_grade)
  FROM course_enrollment AS c_en2
  WHERE c_en2.course_edition_id = c_en.course_edition_id
)
GROUP BY 
  c.id, 
  c.title;

Spiegazione della soluzione:

Nella sottoquery correlata calcoliamo il voto finale medio per l'edizione del corso elaborata dalla query esterna. Utilizziamo questo valore per filtrare gli studenti il cui voto finale in questa edizione del corso è superiore alla media. Nella query esterna visualizziamo il titolo del corso, il numero di studenti con voto finale superiore alla media e il voto finale medio per questo gruppo di studenti.

Conclusioni e passi successivi

In questo articolo abbiamo esplorato diversi esercizi di subquery correlate per migliorare la comprensione di questa importante funzione di SQL. Per coloro che desiderano sviluppare ulteriormente le proprie competenze in SQL, consigliamo il nostro percorso "Pratica su SQL". Include 10 diversi corsi pratici di SQL. Ogni corso è ricco di esercizi pratici basati su scenari reali ed è organizzato in base agli argomenti di SQL, per aiutarvi a concentrarvi sulla vostra area di interesse.

Inoltre, vi consigliamo di dare un'occhiata al nostro pacchetto Completo per sempre SQL - l'offerta definitiva per gli studenti di SQL. Questa offerta, che prevede un pagamento unico, fornisce l'accesso a vita a tutti i corsi attuali e futuri del nostro catalogo, compreso tutto ciò che fa parte del percorso "Pratica su SQL". È una risorsa inestimabile per chiunque voglia diventare un esperto di SQL.