Unioni SQL: 12 domande pratiche con risposte dettagliate

In questo articolo approfondiamo il nostro corso SQL JOINS e vi forniamo 12 esercizi di join da risolvere. Ma non preoccupatevi: tutti gli esercizi hanno soluzioni e spiegazioni. Se vi bloccate, l'aiuto c'è! Dopotutto, questo corso è fatto per esercitarsi e imparare.

Le join in SQL possono essere complicate. Non si tratta solo della sintassi, ma anche di sapere quali join utilizzare in quali scenari.

I join vengono utilizzati per combinare i dati di due o più tabelle in SQL. Le tabelle possono essere unite in diversi modi e, a seconda delle tabelle, ogni modo di unirle può portare a un risultato completamente diverso. Non c'è altro modo per impararlo che la pratica. Sì, è possibile leggere le spiegazioni e gli usi tipici delle unioni in SQL. Questo aiuta di sicuro! Ma la pratica si basa sulla risoluzione di problemi e sulla ripetizione, che rendono le conoscenze più solide. Più vi eserciterete, maggiore sarà la possibilità che i problemi di dati reali che dovrete risolvere siano simili o del tutto identici a quelli che avete già risolto!

E la pratica è ciò che faremo in questo articolo! Vi mostreremo esercizi per l'utilizzo di join SQL di base e più avanzati. Se vi piacciono, il nostro corso vi piacerà ancora di più. SQL JOINs perché tutti gli esercizi sono tratti da quel corso. In totale, il corso offre 93 esercizi sulle Join SQL. Gli argomenti trattati vanno dai tipi di join in SQL, al filtraggio dei dati, all'unione di più di due tabelle, all'auto-unione di una tabella e all'utilizzo di join non equi.

Bene, allora introduciamo i set di dati e iniziamo gli esercizi, che ne dite? Sentitevi liberi di aiutarvi con l'SQL JOIN Cheat Sheet mentre procedete.

Elenco degli esercizi

Ecco un elenco di tutti gli esercizi dell'articolo:

• Esercizio 1: Elenco di tutti i libri e dei loro autori
• Esercizio 2: Elencare gli autori e i libri pubblicati dopo il 2005
• Esercizio 3: Mostrare i libri adattati entro 4 anni e con una valutazione inferiore a quella dell'adattamento.
• Esercizio 4: Mostrare tutti i libri e i loro adattamenti (se presenti)
• Esercizio 5: Mostrare tutti i libri e i loro adattamenti cinematografici
• Esercizio 6: Mostra tutti i libri con le loro recensioni (se presenti)
• Esercizio 7: Elencare tutti i libri e tutti gli autori
• Esercizio 8: Mostra i prodotti sotto le 150 calorie e il loro reparto
• Esercizio 9: Elencare tutti i prodotti con i relativi produttori, reparti e carboidrati
• Esercizio 10: Mostrare tutti i prodotti, i prezzi, i produttori e i reparti.
• Esercizio 11: elencare tutti i lavoratori e i loro diretti supervisori
• Esercizio 12: mostrare le auto con un chilometraggio superiore a quello di un'auto specifica

GIUNTA INTERNA

INNER JOIN è un tipo di join SQL che restituisce solo le righe corrispondenti delle tabelle unite.

Per mostrarvi come funziona, utilizzeremo il Dataset 1 del corso.

Set di dati 1

Il dataset è composto da quattro tabelle: author, book, adaptation, e book_review.

La prima tabella mostra i dati dell'autore nelle seguenti colonne:

• id - L'ID univoco dell'autore all'interno del database.
• name - Il nome dell'autore.
• birth_year - L'anno di nascita dell'autore.
• death_year - L'anno in cui l'autore è morto (il campo è vuoto se è ancora vivo).

Ecco le prime righe della tabella:

id	name	birth_year	death_year
1	Marcella Cole	1983	NULL
2	Lisa Mullins	1891	1950
3	Dennis Stokes	1935	1994
4	Randolph Vasquez	1957	2004
5	Daniel Branson	1965	1990
…	…	…	…

La seconda tabella, bookmostra i dettagli sui libri. Le colonne sono:

• id - L'ID di un determinato libro.
• author_id - L'ID dell'autore che ha scritto quel libro.
• title - Il titolo del libro.
• publish_year - L'anno di pubblicazione del libro.
• publishing_house - Il nome della casa editrice che ha stampato il libro.
• rating - La valutazione media del libro.

Queste sono le prime cinque righe:

id	author_id	title	publish_year	publishing_house	rating
1	NULL	Soulless girl	2008	Golden Albatros	4.3
2	NULL	Weak Heart	1980	Diarmud Inc.	3.8
3	4	Faith Of Light	1995	White Cloud Press	4.3
4	NULL	Memory Of Hope	2000	Rutis Enterprises	2.7
5	6	Warrior Of Wind	2005	Maverick	4.6
…	…	…	…	…	…

La tabella adaptation ha le seguenti colonne:

• book_id - L'ID del libro adattato.
• type - Il tipo di adattamento (ad esempio, film, gioco, opera teatrale, musical).
• title - Il nome dell'adattamento.
• release_year - L'anno in cui è stato creato l'adattamento.
• rating - La valutazione media dell'adattamento.

Ecco un'istantanea dei dati di questa tabella:

book_id	type	title	release_year	rating
1	movie	Gone With The Wolves: The Beginning	2008	3
3	movie	Companions Of Tomorrow	2001	4.2
5	movie	Homeless Warrior	2008	4
2	movie	Blacksmith With Silver	2014	4.3
4	movie	Patrons And Bearers	2004	3.2
…	…	…	…	…

La tabella finale è book_review. È composta dalle seguenti colonne:

• book_id - L'ID del libro recensito.
• review - Il sommario della recensione.
• author - Il nome dell'autore della recensione.

Ecco i dati:

book_id	review	author
1	An incredible book	Sylvia Jones
1	Great, although it has some flaws	Jessica Parker
2	Dennis Stokes takes the reader for a ride full of emotions	Thomas Green
3	Incredible craftsmanship of the author	Martin Freeman
4	Not the best book by this author	Jude Falth
5	Claudia Johnson at her best!	Joe Marqiz
6	I cannot recall more captivating plot	Alexander Durham

Esercizio 1: Elenco di tutti i libri e dei loro autori

Esercizio: Mostrare il nome di ogni autore insieme al titolo del libro che ha scritto e all'anno in cui è stato pubblicato.

Soluzione:

SELECT
  name,
  title,
  publish_year
FROM author
JOIN book
  ON author.id = book.author_id;

Spiegazione della soluzione: La query seleziona il nome dell'autore, il titolo del libro e l'anno di pubblicazione. I dati provengono dalle due tabelle: author e book. Possiamo accedere a entrambe le tabelle utilizzando INNER JOIN. La query restituisce solo le righe con valori corrispondenti (valori che soddisfano la condizione di join) da entrambe le tabelle.

Per prima cosa facciamo riferimento alla tabella author nella clausola FROM. Poi aggiungiamo la clausola JOIN (che può essere scritta anche come INNER JOIN in SQL) e facciamo riferimento alla tabella book.

Le tabelle vengono unite sulla colonna comune. In questo caso, si tratta di id dalla tabella author e author_id dalla tabella book. Vogliamo unire le righe in cui queste colonne condividono lo stesso valore. Lo facciamo usando la clausola ON e specificando i nomi delle colonne. Mettiamo anche il nome della tabella prima di ogni colonna, in modo che il database sappia dove cercare. Questo principalmente perché c'è una colonna id in entrambe le tabelle, ma vogliamo la colonna id solo dalla tabella author tabella. Facendo riferimento al nome della tabella, il database saprà da quale tabella abbiamo bisogno di quella colonna.

L'output della soluzione:

Ecco l'istantanea di output. Abbiamo ottenuto tutti questi dati unendo due tabelle:

name	title	publish_year
Marcella Cole	Gone With The Wolves	2005
Lisa Mullins	Companions And Officers	1930
Dennis Stokes	Blacksmith With Silver	1984
Randolph Vasquez	Faith Of Light	1995
Michael Rostkovsky	Warrior Of Wind	2005
…	…	…

Esercizio 2: Elenco di autori e libri pubblicati dopo il 2005

Esercizio: Mostrare il nome di ogni autore insieme al titolo del libro che ha scritto e all'anno in cui è stato pubblicato. Mostrare solo i libri pubblicati dopo il 2005.

Soluzione:

SELECT
  name,
  title,
  publish_year
FROM author
JOIN book
  ON author.id = book.author_id
WHERE publish_year > 2005;

Spiegazione della soluzione: Questo esercizio e la sua soluzione sono quasi identici al precedente. Ciò si riflette nel fatto che la query seleziona le stesse colonne e unisce le tabelle nello stesso modo del precedente.

La differenza è che ora l'esercizio chiede di mostrare solo i libri pubblicati dopo il 2005. Per questo è necessario filtrare l'output; per farlo utilizziamo la clausola WHERE.

WHERE è una clausola che accetta le condizioni utilizzate per filtrare i dati. Viene scritta dopo aver unito le tabelle. Nel nostro esempio, filtriamo facendo riferimento alla colonna publish_year dopo WHERE e utilizzando l'operatore di confronto "maggiore di" (>) per trovare gli anni successivi al 2005.

Soluzione:

L'output mostra solo un libro pubblicato dopo il 2005.

name	title	publish_year
Darlene Lyons	Temptations In Nature	2007

Esercizio 3: Mostrare i libri adattati entro 4 anni e con una valutazione inferiore a quella dell'adattamento

Esercizio: Per ogni libro, mostrare il titolo, il titolo dell'adattamento, l'anno di adattamento e l'anno di pubblicazione.

Includere solo i libri con una valutazione inferiore a quella del corrispondente adattamento. Inoltre, mostrare solo i libri per i quali è stato pubblicato un adattamento entro quattro anni dalla pubblicazione del libro.

Rinominare la colonna title dalla tabella book a book_title e la colonna title della tabella a . adaptation tabella in adaptation_title.

Soluzione:

SELECT
  book.title AS book_title,
  adaptation.title AS adaptation_title,
  book.publish_year,
  adaptation.release_year
FROM book
JOIN adaptation
  ON book.id = adaptation.book_id
WHERE adaptation.release_year - book.publish_year <= 4
  AND book.rating < adaptation.rating;

Spiegazione della soluzione: Iniziamo a spiegare la soluzione dalle clausole FROM e JOIN. Le colonne da mostrare provengono dalle tabelle book e adaptation. Facciamo riferimento alla prima tabella in FROM e alla seconda in JOIN.

Nella clausola ON, equipariamo le due colonne ID libro e specifichiamo la tabella di ciascuna colonna. È la stessa cosa di prima, solo che i nomi delle tabelle e delle colonne sono diversi.

Ora dobbiamo selezionare le colonne richieste. Il problema è che c'è una colonna title in entrambe le tabelle. Per evitare ambiguità, la prassi migliore è quella di fare riferimento al nome della tabella prima di ogni colonna in SELECT.

Nota: quanto sopra è obbligatorio solo per le colonne ambigue. Tuttavia, è una buona idea farlo con tutte le colonne; migliora la leggibilità del codice e l'approccio rimane coerente.

Dopo aver selezionato le colonne, occorre rinominarne alcune. Lo facciamo usando la parola chiave AS e scrivendo poi un nuovo nome di colonna. In questo modo, una colonna title diventa book_title, l'altra adaptation_title. Dare degli alias ai nomi delle colonne aiuta anche a eliminare le ambiguità.

Ora dobbiamo filtrare l'output. La prima condizione è che l'adattamento sia uscito quattro anni o meno dopo il libro. Utilizziamo di nuovo WHERE e deduciamo semplicemente l'anno di pubblicazione del libro dall'anno di uscita dell'adattamento. Quindi diciamo che la differenza deve essere minore o uguale a (<=) 4.

Dobbiamo anche aggiungere la seconda condizione, in cui il libro ha una valutazione inferiore a quella dell'adattamento. È semplice! La domanda implica che sia la prima che la seconda condizione devono essere soddisfatte. L'indizio è in AND, un operatore logico che utilizziamo per aggiungere la seconda condizione. In questo caso, utilizza l'operatore "meno di" (<) per confrontare le due valutazioni.

Soluzione in uscita:

L'output mostra tre coppie libro-adattamento che soddisfano le condizioni.

book_title	adaptation_title	publish_year	release_year
Memory Of Hope	Patrons And Bearers	2000	2004
Music At The Lake	Music At The Lake	2004	2007
Companion Of Tomorrow	Lighting Faith	1949	1952

GIUNTA A SINISTRA

Ora che avete capito il senso di INNER JOIN, passiamo a LEFT JOIN. È un tipo di join esterno che restituisce tutte le colonne della tabella di sinistra (la prima) e solo le righe corrispondenti della tabella di destra (la seconda). Se ci sono dati non corrispondenti, vengono mostrati come NULL.

Per saperne di più, consultate il nostro articolo sul LEFT JOIN.

Esercizio 4: Mostrare tutti i libri e i loro adattamenti (se presenti)

Esercizio: Mostrare il titolo di ogni libro insieme al titolo del suo adattamento e alla data di pubblicazione. Mostrare tutti i libri, indipendentemente dal fatto che abbiano avuto un adattamento.

Soluzione:

SELECT
  book.title,
  adaptation.title,
  adaptation.release_year
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id;

Spiegazione della soluzione: Per prima cosa selezioniamo le colonne necessarie dalle due tabelle. Poi uniamo book (la tabella di sinistra) con adaptation (la tabella di destra) utilizzando LEFT JOIN. La sintassi del join SQL è la stessa per INNER JOIN. L'unica cosa che cambia è la parola chiave join.

Nota: SQL accetta sia LEFT JOIN che LEFT OUTER JOIN. Si tratta dello stesso comando.

Output della soluzione:

L'istantanea di output mostra i dati richiesti, con alcuni dei dati mostrati come NULL. Questi sono i libri senza l'adattamento.

title	title-2	release_year
Soulless girl	Gone With The Wolves: The Beginning	2008
Faith Of Light	Companions Of Tomorrow	2001
Warrior Of Wind	Homeless Warrior	2008
…	…	…
Guarding The Emperor	NULL	NULL
Blacksmith With Silver	NULL	NULL
…	…	…

Esercizio 5: Mostrare tutti i libri e i loro adattamenti cinematografici

Esercizio: Mostrare tutti i libri con i loro adattamenti cinematografici. Selezionare il titolo di ogni libro, il nome della casa editrice, il titolo dell'adattamento e il tipo di adattamento. Mantenere i libri senza adattamenti nel risultato.

Soluzione:

SELECT
  book.title,
  publishing_house,
  adaptation.title,
  adaptation.type
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id
WHERE type = 'movie'
  OR type IS NULL;

Spiegazione della soluzione:

La domanda chiede di mostrare tutte le righe, anche quelle senza adattamenti. È possibile che ci siano libri senza adattamenti, quindi utilizziamo LEFT JOIN.

Selezioniamo innanzitutto il titolo del libro, la casa editrice, il titolo dell'adattamento e il tipo.

Poi uniamo book (la tabella di sinistra) con adaptation (la tabella di destra) utilizzando LEFT JOIN. Uniamo le tabelle in base all'ID del libro. Tutti i libri che non soddisfano le condizioni avranno NULLs come titolo e tipo di adattamento.

Filtriamo i dati usando WHERE. La prima condizione è che il tipo di adattamento deve essere un film, quindi equipariamo la colonna tipo con un film usando il segno di uguale (=). Nota: quando si usano dati di testo nella condizione WHERE, questi devono essere racchiusi tra apici singoli ('').

La seconda condizione di filtraggio viene aggiunta utilizzando l'operatore logico OR. Essa dice che il tipo può anche essere NULL se non è un film. L'esercizio chiede di mantenere nei risultati i libri senza adattamenti.

Output della soluzione:

Ecco l'istantanea dell'output. Si può notare che mostra solo i libri adattati come film o non adattati affatto.

title	publishing_house	title-2	type
Soulless girl	Golden Albatros	Gone With The Wolves: The Beginning	movie
Faith Of Light	White Cloud Press	Companions Of Tomorrow	movie
Warrior Of Wind	Maverick	Homeless Warrior	movie
…	…	…	…
Guarding The Emperor	Flying Pen Media	NULL	NULL
Blacksmith With Silver	Diarmud Inc.	NULL	NULL

GIUNTA A DESTRA

Dove c'è LEFT JOIN, c'è anche RIGHT JOIN, giusto? Nonostante sia l'immagine speculare della LEFT JOIN, fa comunque parte della pratica delle join SQL.

È un tipo di join che restituisce tutte le colonne della tabella di destra (la seconda) e solo le righe corrispondenti della tabella di sinistra (la prima). Se ci sono dati non corrispondenti, vengono mostrati come NULL.

Esercizio 6: Mostrare tutti i libri con le loro recensioni (se presenti)

Esercizio: Unire le tabelle book_review e book utilizzando un RIGHT JOIN. Mostrare il titolo del libro, la recensione corrispondente e il nome dell'autore della recensione. Considerare tutti i libri, anche quelli che non sono stati recensiti.

Soluzione:

SELECT
  book.title,
  book_review.review,
  book_review.author
FROM book_review
RIGHT JOIN book
  ON book.id = book_review.book_id;

Spiegazione della soluzione:

Per prima cosa selezioniamo le colonne richieste. Poi facciamo come ci è stato detto: uniamo le tabelle usando RIGHT JOIN. Uniamo le tabelle in base all'ID del libro. La tabella book è la tabella giusta; vogliamo tutti i dati da essa, indipendentemente dalle recensioni.

Come si può vedere, la sintassi è la stessa di INNER JOIN e LEFT JOIN.

Nota: SQL accetta sia RIGHT JOIN che RIGHT OUTER JOIN.

Soluzione in uscita:

La query restituisce tutti i titoli dei libri, le loro recensioni e gli autori. Se non ci sono informazioni su recensioni o autori, viene mostrato un NULL.

title	review	author
Soulless girl	An incredible book	Sylvia Jones
Soulless girl	Great, although it has some flaws	Jessica Parker
…	…	…
Guarding The Emperor	NULL	NULL
Companions And Officers	NULL	NULL
Blacksmith With Silver	NULL	NULL
…	…	…

GIUNZIONE COMPLETA

Ecco un altro tipo di join utile in alcuni scenari: FULL JOIN. Si tratta di una LEFT JOIN e di una RIGHT JOIN messe insieme. Mostra le righe corrispondenti da entrambe le tabelle, le righe che non hanno corrispondenza dalla tabella di sinistra e le righe che non hanno corrispondenza dalla tabella di destra. In breve, mostra tutti i dati di entrambe le tabelle.

È possibile leggere ulteriori informazioni su come e quando utilizzare FULL JOIN.

Esercizio 7: Elenco di tutti i libri e di tutti gli autori

Esercizio: Visualizzare il titolo di ogni libro e il nome del suo autore. Mostrare tutti i libri, anche quelli senza autore. Mostrare tutti gli autori, anche quelli che non hanno ancora pubblicato un libro. Utilizzare un sito FULL JOIN.

Soluzione:

SELECT
  title,
  name
FROM book
FULL JOIN author
  ON book.author_id = author.id;

Spiegazione della soluzione: La domanda richiede di mostrare tutti i libri, ma anche tutti gli autori: FULL JOIN è perfetto per farlo in modo elegante.

Selezioniamo il titolo del libro e il nome dell'autore. Quindi, FULL JOIN la tabella book con la tabella author. La condizione di unione è che l'ID dell'autore deve essere lo stesso in entrambe le tabelle. Anche in questo caso, la sintassi è la stessa dei tipi di join precedenti.

Nota: SQL accetta sia FULL JOIN che FULL OUTER JOIN.

Soluzione:

L'output mostra tutti i libri e tutti gli autori, indipendentemente dal fatto che gli autori o i libri esistano o meno in entrambe le tabelle.

title	name
Gone With The Wolves	Marcella Cole
Companions And Officers	Lisa Mullins
…	…
NULL	Daniel Branson
…	…
Weep Of The West	NULL

Unire 3 o più tabelle

Sì, le Join SQL consentono di unire più di due tabelle. Vedremo come farlo in questa parte della pratica sulle unioni SQL. Una spiegazione più dettagliata delle unioni multiple è disponibile qui.

Abbiamo anche bisogno di un nuovo set di dati, quindi introduciamolo.

Set di dati 2

La prima tabella del dataset è department. Le sue colonne sono:

• id - L'ID univoco del reparto.
• name - Il nome del reparto, cioè dove viene venduto un particolare tipo di prodotto.

Ecco i dati della tabella.

id	name
1	fruits
2	vegetables
3	seafood
4	deli
5	bakery
6	meat
7	dairy

La seconda tabella è producted è composta dalle seguenti colonne:

• id - L'ID di un determinato prodotto.
• name - Il nome del prodotto.
• department_id - L'ID del reparto in cui si trova il prodotto.
• shelf_id - L'ID dello scaffale del reparto in cui si trova il prodotto.
• producer_id - L'ID dell'azienda che produce il prodotto.
• price - Il prezzo del prodotto.

Ecco l'istantanea dei dati:

id	name	department_id	shelf_id	producer_id	price
1	Apple	1	1	NULL	0.5
2	Avocado	1	1	7	1
3	Banana	1	1	7	0.5
4	Grapefruit	NULL	1	1	0.5
5	Grapes	1	1	4	2
…	…	…	…	…	…

La tabella successiva è nutrition_data. Le sue colonne e i suoi dati sono riportati di seguito:

• product_id - L'ID di un prodotto.
• calories - Il potere calorifico del prodotto.
• fat - La quantità di grassi contenuta nel prodotto.
• carbohydrate - La quantità di carboidrati del prodotto.
• protein - La quantità di proteine del prodotto.

product_id	calories	fat	carbohydrate	protein
1	130	0	5	1
2	50	4.5	3	1
3	110	0	30	1
4	60	0	15	1
NULL	90	0	23	0
…	…	…	…	…

La quarta tabella è denominata producer. Ha le seguenti colonne:

• id - L'ID di un determinato produttore alimentare.
• name - Il nome del produttore.

Di seguito sono riportati i dati di questa tabella:

id	name
1	BeHealthy
2	HealthyFood Inc.
3	SupremeFoods
4	Foodie
5	Gusto
6	Baker n Sons
7	GoodFoods
8	Tasty n Healthy

L'ultima tabella del set di dati è sales_history. Ha le seguenti colonne:

• date - La data di vendita.
• product_id - L'ID del prodotto venduto.
• amount - La quantità di quel prodotto venduto in un determinato giorno.

Ecco anche i dati:

date	product_id	amount
2015-01-14	1	14
2015-01-14	1	13
2015-01-15	2	2
2015-01-16	2	6
2015-01-17	3	8
…	…	…

Esercizio 8: Mostra i prodotti con meno di 150 calorie e il loro reparto

Esercizio: Elencare tutti i prodotti con meno di 150 calorie. Per ogni prodotto, indicare il nome (rinominare la colonna product) e il nome del reparto in cui si trova (nominare la colonna department).

Soluzione:

SELECT
  p.name AS product,
  d.name AS department
FROM department d
JOIN product p
  ON d.id = p.department_id
JOIN nutrition_data nd
  ON nd.product_id = p.id
WHERE nd.calories < 150;

Spiegazione della soluzione: Il principio generale di come si unisce la terza (quarta, quinta...) tabella è che si aggiunge semplicemente un'altra JOIN. Potete vedere come si fa in questo articolo che spiega le giunzioni multiple. In questo caso si procederà allo stesso modo.

Per prima cosa uniamo la tabella department con la tabella product con la tabella sull'ID del reparto usando JOIN. Ma abbiamo bisogno anche della terza tabella. Per ottenere i dati da quest'ultima, basta aggiungere un altro JOIN, che unirà la tabella product con la tabella nutrition_data tabella. La sintassi è la stessa del primo join. In questo caso, la query unisce le tabelle in base all'ID del prodotto.

Quindi si utilizza WHERE per trovare i prodotti con meno di 150 calorie. Infine, selezioniamo i nomi dei prodotti e dei reparti e rinominiamo le colonne secondo le istruzioni dell'esercizio.

Nota: probabilmente avrete notato che entrambe le colonne selezionate hanno lo stesso nome originale. E avete anche notato che abbiamo risolto questa ambiguità mettendo alcuni strani nomi di tabella abbreviati davanti a tutte le colonne della query. Questi nomi abbreviati sono gli alias delle tabelle, che si assegnano semplicemente scrivendoli dopo il nome della tabella in FROM o JOIN. Assegnando gli alias alle tabelle, si possono abbreviare i nomi delle tabelle. Pertanto, non è necessario scrivere i nomi completi (che a volte possono essere molto lunghi!), ma gli alias brevi. In questo modo si risparmia tempo e spazio.

Output della soluzione:

L'output mostra un elenco dei prodotti e del reparto a cui appartengono. Include solo i prodotti con meno di 150 calorie.

product	department
Apple	fruits
Avocado	fruits
Banana	fruits
Kiwi	fruits
Lemon	fruits
…	…

Esercizio 9: Elenco di tutti i prodotti con i rispettivi produttori, reparti e carboidrati

Esercizio: Per ogni prodotto, visualizzare il:

• Nome dell'azienda che lo ha prodotto (nominare la colonna producer_name).
• Nome del reparto in cui si trova il prodotto (nominarlo department_name).
• Nome del prodotto (nome product_name).
• Numero totale di carboidrati nel prodotto.

La query dovrebbe comunque considerare i prodotti senza informazioni su producer_id o department_id.

Soluzione:

SELECT
  prod.name AS producer_name,
  d.name AS department_name,
  p.name AS product_name,
  nd.carbohydrate
FROM product p
LEFT JOIN producer prod
  ON prod.id = p.producer_id
LEFT JOIN department d
  ON d.id = p.department_id
LEFT JOIN nutrition_data nd
  ON nd.product_id = p.id;

Spiegazione della soluzione: La query seleziona le colonne richieste. Poi unisce la tabella product con la tabella producer sull'ID del produttore utilizzando LEFT JOIN. Abbiamo scelto questo tipo di join perché dobbiamo includere i prodotti senza i dati del produttore.

Poi si aggiunge un'altra LEFT JOIN. Questo aggiunge la tabella department e la unisce alla tabella product tabella. Anche in questo caso, scegliamo LEFT JOIN perché dobbiamo mostrare i prodotti che non hanno un reparto.

C'è anche un terzo join! La aggiungiamo semplicemente alla catena di join precedenti. È di nuovo LEFT JOIN, in quanto aggiungiamo la tabella nutrition_data e la uniamo alla tabella product tabella.

Questo è un argomento interessante da esplorare, quindi ecco un articolo che spiega le JOIN LEFT multiple per aiutarvi.

Soluzione in uscita:

L'output mostra tutti i prodotti con i nomi dei produttori e dei reparti e le quantità di carboidrati:

producer_name	department_name	product_name	carbohydrate
BeHealthy	fruits	Kiwi	20
BeHealthy	vegetables	Broccoli	8
BeHealthy	meat	Chicken	NULL
BeHealthy	NULL	Grapefruit	15
HealthyFood Inc.	vegetables	Celery	4
…	…	…	…

Se avete bisogno di maggiori dettagli, leggete come effettuare LEFT JOIN tra più tabelle in SQL.

Esercizio 10: Mostrare tutti i prodotti, i prezzi, i produttori e i reparti

Esercizio: Per ogni prodotto, mostrare il nome, il prezzo, il nome del produttore e il nome del reparto.

Alias le colonne product_name, product_price, producer_name, e department_name, rispettivamente. Includere tutti i prodotti, anche quelli senza produttore o reparto. Includere anche i produttori e i reparti senza prodotto.

Soluzione:

SELECT
  p.name AS product_name,
  p.price AS product_price,
  prod.name AS producer_name,
  d.name AS department_name
FROM product p
FULL JOIN producer prod
  ON p.producer_id = prod.id
FULL JOIN department d
  ON d.id = p.department_id;

Spiegazione della soluzione: Questo esercizio richiede l'uso di FULL JOIN, poiché abbiamo bisogno di tutti i dati delle tabelle che utilizzeremo: product, producer, e department.

La sintassi è la stessa degli esempi precedenti. È sufficiente unire le diverse tabelle (product e producer) sull'ID del produttore e utilizziamo un tipo diverso di join: FULL JOIN.

Il secondo FULL JOIN unisce la tabella product con la tabella department tabella.

Dopo aver selezionato le colonne necessarie e averle rinominate, si ottiene il seguente output.

Output della soluzione:

La soluzione mostra tutti i dati delle tabelle e delle colonne selezionate:

product_name	product_price	producer_name	department_name
Chicken	5.5	BeHealthy	meat
Broccoli	2.5	BeHealthy	vegetables
Kiwi	0.3	BeHealthy	fruits
Grapefruit	0.5	BeHealthy	NULL
Cucumber	0.7	HealthyFood Inc.	vegetables
…	…	…	…

Autogiunzione

Un self-join non è un tipo distinto di SQL JOIN - qualsiasi join può essere usato per unire una tabella. È semplicemente una join utilizzata per unire la tabella con se stessa. Dando alias diversi alla stessa tabella, questa viene trattata come due tabelle diverse quando viene auto-unita.

Per maggiori dettagli, consultare la nostra guida illustrata all'auto-giunzione SQL.

Set di dati 3

Il dataset di questo esempio è costituito da una sola tabella: workshop_workers. Ha le seguenti colonne.

• id - L'ID del lavoratore.
• name - Nome e cognome del lavoratore.
• specialization - La specializzazione del lavoratore.
• master_id - L'ID del supervisore del lavoratore.
• experience - Gli anni di esperienza del lavoratore.
• project_id - L'ID del progetto a cui il lavoratore è attualmente assegnato.

Ecco i dati:

id	name	specialization	master_id	experience	project_id
1	Mathew Conn	woodworking	NULL	20	1
2	Kate Brown	woodworking	1	4	1
3	John Doe	incrusting	5	3	1
4	John Kowalsky	watchmaking	7	2	3
5	Suzan Gregowitch	incrusting	NULL	15	4

Esercizio 11: Elenco di tutti i lavoratori e dei loro supervisori diretti

Esercizio: Mostrare i nomi di tutti i lavoratori insieme ai nomi dei loro supervisori diretti. Rinominare le colonne apprentice_name e master_name, rispettivamente. Considerare solo i lavoratori che hanno un supervisore (cioè un padrone).

Soluzione:

SELECT
  apprentice.name AS apprentice_name,
  master.name AS master_name
FROM workshop_workers apprentice
JOIN workshop_workers master
  ON apprentice.master_id = master.id;

Spiegazione della soluzione: Cominciamo a spiegare l'auto-giunzione. Il principio generale è lo stesso delle join regolari. Facciamo riferimento alla tabella in FROM e le diamo un alias, apprentice. Poi si utilizza JOIN e si fa riferimento alla stessa tabella. Questa volta, diamo alla tabella l'alias master. In pratica, facciamo finta che una tabella contenga i dati degli apprendisti e l'altra quelli del master.

Le tabelle vengono unite in base all'ID master della tabella apprentice e l'ID della tabella master tabella.

Questo esempio è un uso tipico di una self-join: la tabella ha una colonna (master_id) che fa riferimento a un'altra colonna della stessa tabella (id). Entrambe le colonne mostrano l'ID del lavoratore. Quando c'è NULL in master_id, significa che il lavoratore non ha un master. In altre parole, è il master.

Dopo l'auto-giunzione, è sufficiente selezionare le colonne necessarie e rinominarle.

Output della soluzione:

L'output mostra tutti gli apprendisti e i loro diretti supervisori.

apprentice_name	master_name
Kate Brown	Mathew Conn
John Doe	Suzan Gregowitch
John Kowalsky	Joe Darrington
Peter Parker	Joe Darrington
Mary Smith	Mathew Conn
Carlos Bell	Suzan Gregowitch
Dennis Wright	Joe Darrington

Giunti non equi

L'ultimo argomento che affronteremo in questa esercitazione sulle unioni SQL sono le unioni non equi. Le giunzioni utilizzate finora sono chiamate giunzioni equi perché utilizzano il segno di uguaglianza (=) nella condizione di unione. Le giunzioni non equi sono tutte le altre giunzioni che utilizzano altri operatori - operatori di confronto (<, >, <=, >=, !=, <>), l'operatore BETWEEN o qualsiasi altra condizione logica - per unire le tabelle.

Set di dati 4

Utilizzeremo un set di dati composto da due tabelle. La prima tabella è car. Ecco le sue colonne:

• id - L'ID dell'auto nel database.
• model - Il modello dell'auto.
• brand - La marca dell'auto.
• original_price - Il prezzo originale dell'auto da nuova.
• mileage - Il chilometraggio totale dell'auto.
• prod_year - L'anno di produzione dell'auto.

I dati si presentano così:

id	model	brand	original_price	mileage	prod_year
1	Speedster	Teiko	80,000	150,000	1999
2	Roadmaster	Teiko	110,000	30,000	1980
3	Sundry	Teiko	40,000	25,000	1991
4	Furu	Domus	50,000	10,000	2002
5	Emperor	Domus	65,000	140,000	2005
6	King	Domus	200,000	6,000	1981
7	Empress	Domus	60,000	7,600	1997
8	Fury	Tatsu	150,000	13,000	1993

La seconda tabella è charity_auction con queste colonne:

• car_id - L'ID dell'auto.
• initial_price - Il prezzo iniziale (cioè di partenza) dell'auto.
• final_price - Il prezzo effettivo di vendita dell'auto.
• buyer_id - L'ID della persona che ha acquistato l'auto.

Ecco i dati:

car_id	initial_price	final_price	buyer_id
1	65,000	NULL	NULL
3	35,000	50,000	1
5	50,000	120,000	3
6	350,000	410,000	4
7	65,000	NULL	NULL

Esercizio 12: Mostrare le auto con un chilometraggio superiore a quello di un'auto specifica

Esercizio: Mostrare il modello, la marca e il prezzo finale di ogni auto venduta all'asta. Considerare solo le auto vendute che hanno un chilometraggio superiore a quello dell'auto con id = 4.

Soluzione:

SELECT
  car.model,
  car.brand,
  car.final_price
FROM car
JOIN charity_auction ca
  ON car.id = ca.car_id
JOIN car car2
  ON car.mileage > car2.mileage
WHERE car2.id = 4
  AND final_price IS NOT NULL;

Spiegazione della soluzione: Selezioniamo il modello di auto, la marca e il prezzo finale.

Nel primo JOIN, uniamo la tabella car con la tabella charity_auction tabella. Le tabelle vengono unite quando gli ID delle auto sono uguali. Questa è la nostra equi JOIN regolare.

Aggiungiamo il secondo JOIN, che è un self-join. Aggiunge la tabella car in modo da poter filtrare i dati utilizzando la condizione di join non equi. La condizione restituirà tutte le auto della tabella car e tutte le auto della tabella car2 con il chilometraggio più basso. Si tratta di una condizione non-equi, poiché utilizza l'operatore "maggiore di" ( > ). La sintassi è la stessa, ma questa volta c'è > invece di =.

Infine, dobbiamo filtrare i dati utilizzando WHERE. Non ci interessa confrontare il chilometraggio di tutte le auto. Vogliamo mostrare le auto che hanno un chilometraggio superiore a quello dell'auto con id = 4. Questo è ciò che fa la prima condizione di filtraggio.

Aggiungiamo un'altra condizione di filtraggio che dice che il prezzo finale non deve essere NULL, cioè che l'auto deve essere stata venduta all'asta.

Risultato della soluzione:

Il risultato mostra due auto:

model	brand	final_price
Sundry	Teiko	50,000
Emperor	Domus	120,000

SQL JOINs La pratica rende perfetti. Più pratica? Più perfetto!

Dodici esercizi di join SQL rappresentano una buona quantità di pratica. Grazie a questi esercizi, potrete imparare e mettere in pratica tutti gli argomenti più comuni relativi alle join che mettono in difficoltà gli utenti principianti e intermedi.

Ora non vi resta che continuare! Quando ci si esercita ancora di più, si diventa ancora più perfetti. Se vi sono piaciuti i nostri esercizi, potete trovarne altri nel nostro corso SQL JOINS o nell'articolo sulle domande del colloquio SQL JOIN.

Speriamo che riusciate a superare tutti gli esercizi che vi aspettano!