11 Esercizi sulle espressioni comuni di tabella SQL

In questo articolo vi proponiamo 11 esercizi pratici che mettono in pratica le vostre conoscenze sulle espressioni tabellari comuni (CTE). Ogni esercizio sulle CTE è accompagnato da una soluzione e da una spiegazione dettagliata.

Come dice il proverbio, "Un piccolo progresso ogni giorno porta a grandi risultati". E questo è indubbiamente vero per la conoscenza di SQL. Proprio come si dovrebbe andare regolarmente in palestra per mantenere i muscoli tonici, si dovrebbero fare spesso esercizi di espressioni di tabelle comuni per mantenere le proprie capacità di interrogazione toniche per il lavoro di analisi dei dati.

Le CTE, o espressioni comuni di tabella, sono potenti istruzioni di SQL. Consentono di definire temporaneamente una sottoquery all'interno di una query SQL e di assegnarle un nome. In questo modo, la CTE può essere citata con il suo nome all'interno della query, proprio come se fosse una tabella.

Il valore delle CTE

Oltre ai nomi, ci sono altre importanti differenze tra le CTE e le subquery. Le CTE sono utili per strutturare query complesse, facilitandone la lettura, la comprensione e il debug. Inoltre, le CTE possono essere utilizzate più volte all'interno della stessa query, rendendola più concisa. Queste qualità delle CTE le rendono ideali per il lavoro di analisi dei dati, poiché è comune che le query di analisi dei dati mescolino semplici SELECT di dati con SELECT raggruppate con totali, medie e altre funzioni aggregate. Senza le CTE, le query complesse potrebbero diventare praticamente impossibili da leggere o richiedere la creazione di tabelle o viste temporanee che popolano inutilmente il database con molti oggetti.

Le CTE sono molto utili anche per eseguire analisi di dati molto complesse senza ricorrere ad altri linguaggi di programmazione. L'uso della ricorsione per trovare percorsi critici o per attraversare tipi di dati astratti (come alberi e grafi) sono esempi che dimostrano l'utilità delle CTE. Così come la possibilità di creare query annidate, che riducono gradualmente la complessità di un problema fino a farlo diventare una semplice SELECT.

La capacità delle CTE di rendere le query più leggibili e concise è un aspetto che si apprezza se si deve rivedere una lunga query anni dopo averla scritta. Fortunatamente, la maggior parte dei moderni sistemi di gestione dei database relazionali (RDBMS), tra cui PostgreSQL, MySQL, SQL Server e Oracle, consentono l'uso delle CTE.

Tutti gli esercizi riportati in questo articolo sono stati presi dal nostro Query ricorsive corso. Si tratta di un tutorial approfondito sulle espressioni di tabella comuni nell'analisi dei dati. I 114 esercizi interattivi coprono le CTE semplici, le CTE annidate e le CTE ricorsive in un tempo totale stimato di 18 ore. Per saperne di più sulle CTE, potete anche leggere CTE spiegate con esempi.

Un altro argomento importante che ogni analista di dati dovrebbe conoscere è quello delle funzioni SQL a finestra. Per mettere alla prova le vostre capacità, potete consultare questa serie di esercizi pratici sulle funzioni SQL a finestra.

Ora iniziamo con i nostri esercizi sulle espressioni di tabelle comuni. Inizieremo con le CTE semplici per poi passare alle CTE annidate e ricorsive.

Esercizi CTE semplici

Per questi esercizi CTE utilizzeremo un database progettato per gestire progetti di crowdfunding. Questo schema è composto da tre tabelle:

1. supporter contiene informazioni sui sostenitori, che sono coloro che donano denaro per i progetti.
2. project contiene informazioni sui progetti che ricevono donazioni dai sostenitori.
3. donation registra le donazioni dei sostenitori ai progetti.

La tabella supporter memorizza i dati id, first_name e last_name di ogni sostenitore del sistema. Vediamo alcune delle sue righe:

La tabella project tabella memorizza id, category, author_id e minimal_amount necessari per avviare ogni progetto. Queste sono alcune delle sue righe:

I dati della colonna author_id collegano ogni progetto della tabella con una riga della tabella . project con una riga della tabella supporter tabella. Ogni sostenitore collegato a un progetto dalla colonna author_id è l'autore di quel progetto.

Infine, la donation tabella contiene id, supporter_id, l'importo della donazione e la colonna donated, che indica la data in cui è stata effettuata la donazione.

Esercizio 1: Utilizzo di una CTE per ottenere dati totalizzati

Esercizio: Ottenere l'ID del progetto, l'importo minimo e le donazioni totali per i progetti che hanno ricevuto donazioni superiori all'importo minimo.

Soluzione:

WITH project_revenue AS (
  SELECT
    project_id,
    SUM(amount) AS sum_amount
  FROM donation
  GROUP BY project_id
)
SELECT project.id, minimal_amount, sum_amount
FROM project_revenue
INNER JOIN project ON
project.id = project_revenue.project_id
WHERE sum_amount >= minimal_amount;

Spiegazione: Per risolvere questo esercizio, si utilizza una CTE chiamata project_revenue che totalizza le donazioni di ogni progetto. Questa CTE ha due colonne: id e sum_amount, quest'ultima è la somma calcolata delle donazioni per ogni project_id. Dopo la definizione della CTE, si utilizza un'istruzione SELECT che unisce la tabella con la CTE. project con la CTE. Per ogni progetto che ha ricevuto donazioni, la CTE restituisce i suoi id, minimal_amount, e il totale delle donazioni (sum_amount) che ha ricevuto.

La CTE project_revenue include solo le righe dei progetti che hanno ricevuto donazioni, perché ottiene i dati dalla tabella delle donazioni. Anche SELECT sotto la definizione della CTE mostra solo i progetti che hanno ricevuto donazioni, grazie al collegamento INNER JOIN tra la CTE e la tabella. project tabella. Inoltre, la condizione WHERE assicura che si otterranno solo i progetti per i quali l'importo donato supera l'importo minimo.

Se avete bisogno di esercitarvi a raggruppare i dati in SQL, date un'occhiata a questa serie di 10 esercizi GROUP BY. Provate questi esercizi avanzati di SQL per accelerare il vostro percorso verso la padronanza di SQL.

Esercizio 2: Utilizzo di più CTE nella stessa istruzione

Esercizio: Selezionare i sostenitori che hanno donato più di 200 dollari in totale o che hanno donato almeno due volte.

Soluzione:

WITH rich AS (
  SELECT
	s.id,
	first_name,
	last_name
  FROM supporter s
  JOIN donation d
	ON d.supporter_id = s.id
  GROUP BY s.id, first_name, last_name
  HAVING SUM(amount) > 200
),
frequent AS (
  SELECT
	s.id,
	first_name,
	last_name
  FROM supporter s
  JOIN donation d
	ON d.supporter_id = s.id
  GROUP BY s.id, first_name, last_name
  HAVING COUNT(d.id) > 1
)
SELECT
  id,
  first_name,
  last_name
FROM rich
UNION ALL
SELECT
  id,
  first_name,
  last_name
FROM frequent;

Spiegazione: Questo esercizio ci chiede di combinare due risultati diversi che dobbiamo ottenere recuperando le informazioni dai campi donation e supporter : i sostenitori le cui donazioni totali superano i 200 dollari e i sostenitori che hanno fatto più di una donazione. Questa situazione è ideale da risolvere scrivendo due CTE, una per ottenere il primo insieme di dati (rich) e l'altra per ottenere il secondo insieme (frequent).

La sintassi SQL consente di scrivere più CTE all'interno dello stesso comando, cosa che abbiamo sfruttato per risolvere questo esercizio. Collocando ogni subquery in una CTE diversa, la SELECT finale è semplicemente l'unione di due semplici SELECT, ognuna delle quali preleva i dati direttamente da una CTE.

CTE annidate

Sebbene nessun RDBMS consenta la creazione di una CTE all'interno di un'altra CTE, sono consentite le CTE annidate; ciò avviene quando una CTE fa riferimento a una CTE precedentemente definita come se fosse una tabella. In questo modo, le CTE creano diversi livelli di astrazione. Ciò rende la query finale semplice e concisa SELECT.

Per i nostri esercizi sulle CTE annidate, utilizzeremo uno schema di tabelle di un'azienda di vendita porta a porta. Questo schema ha tre tabelle: salesman, daily_sales, e city. La tabella salesman comprende i dati id, first_name, last_name e city_id per ogni venditore. Queste sono alcune delle sue righe:

La tabella daily_sales rappresenta le vendite totali per giorno e per venditore. Ha le colonne day, salesman_id, items_sold, amount_earned, distance e customers. Le ultime due colonne mostrano la distanza percorsa e il numero di clienti serviti da ciascun venditore ogni giorno. Queste sono alcune delle sue righe:

Infine, abbiamo la tabella city che memorizza i dati id, name, country e region di ogni città:

Esercizio 3: Utilizzo di CTE annidate per muoversi in modo incrementale verso un risultato

Esercizio: Ottenere la data, l'ID della città, il nome della città e l'importo totale di tutte le vendite giornaliere - raggruppate per data e città - che superano la media delle vendite giornaliere per tutte le città e tutti i giorni.

Soluzione:

WITH earnings_per_day_city AS (
  SELECT
	ds.day,
	c.id,
	c.name,
	SUM(amount_earned) AS total_earnings
  FROM salesman s
  JOIN daily_sales ds
	ON s.id = ds.salesman_id
  JOIN city c
	ON s.city_id = c.id
  GROUP BY ds.day, c.id, c.name
),
overall_day_city_avg AS (
  SELECT
	AVG(total_earnings) AS avg_earnings
  FROM earnings_per_day_city
)
SELECT
  day,
  id,
  name,
  total_earnings
FROM earnings_per_day_city, overall_day_city_avg
WHERE total_earnings > avg_earnings;

Spiegazione: Le CTE annidate consentono di suddividere un problema in parti e di avvicinarsi gradualmente alla soluzione. In questo esercizio, dobbiamo innanzitutto totalizzare le vendite per giorno e per città. Lo facciamo con la prima CTE, earnings_per_day_city.

Poi dobbiamo ottenere una media di tutte le vendite totalizzate per giorno e per città. Per ottenere questo risultato si utilizza la CTE overall_day_city_avg, che a sua volta utilizza i risultati precedentemente totalizzati dalla CTE earnings_per_day_city. Questa seconda CTE restituirà una singola riga con la media delle vendite per tutti i giorni e tutte le città.

Nella versione finale SELECT, si prendono semplicemente i dati delle due CTE (non è necessario combinarle con una CTE JOIN, poiché earnings_per_day_city restituisce un'unica riga) e si aggiunge la condizione WHERE secondo cui il totale delle vendite del giorno e della città deve essere maggiore della media complessiva.

Esercizio 4: Combinazione di query e sottoquery annidate

Esercizio: Ottenere la data in cui il numero medio di clienti serviti per regione è stato il più basso di tutti, visualizzando questa media insieme alla data.

Soluzione:

WITH sum_region AS (
  SELECT
	day,
	region,
	SUM(customers) AS sum_customers
  FROM salesman s
  JOIN daily_sales ds
	ON s.id = ds.salesman_id
  JOIN city c
	ON s.city_id = c.id
  GROUP BY day, region
),
avg_region AS (
  SELECT
	day,
	AVG(sum_customers) AS avg_region_customers
  FROM sum_region
  GROUP BY day
)
SELECT
  day,
  avg_region_customers
FROM avg_region
WHERE avg_region_customers = (SELECT
  MIN(avg_region_customers)
  FROM avg_region);

Spiegazione: Per risolvere questa domanda utilizziamo lo stesso approccio successivo alla soluzione dell'esercizio precedente, creando prima una CTE per ottenere il numero totale di clienti serviti al giorno e per regione e poi un'altra CTE basata sulla precedente per ottenere le medie giornaliere dei clienti serviti al giorno. Poi, nella versione finale SELECT, utilizziamo una sottoquery per ottenere il minimo del numero medio di clienti al giorno e lo usiamo nella clausola WHERE come valore di confronto, in modo che la query restituisca il giorno che corrisponde a quel minimo.

Se si volesse suddividere ulteriormente il risultato finale di SELECT, si potrebbe aggiungere una terza CTE invece di una subquery. In questo modo, il SELECT finale diventa ancora più semplice. Ecco la nuova (terza) CTE e l'esterno SELECT:

min_avg_region as (
  SELECT
    MIN(avg_region_customers) as min_avg_region_customers
  FROM avg_region
  )
SELECT
  day,
  avg_region_customers
FROM avg_region, min_avg_region
WHERE avg_region_customers = min_avg_region_customers;

Esercizio 5: Utilizzo di CTE annidate per calcolare statistiche complesse

Esercizio: Per ogni città, calcolare la distanza media totale percorsa da ciascun venditore. Calcolare anche la media complessiva di tutte le città.

Soluzione:

WITH distance_salesman_city AS (
  SELECT
	city_id,
	salesman_id,
	SUM(distance) AS sum_distance
  FROM daily_sales d
  JOIN salesman s
	ON d.salesman_id = s.id
  GROUP BY city_id, salesman_id
),
city_average AS (
  SELECT
	city_id,
	AVG(sum_distance) AS city_avg
  FROM distance_salesman_city
  GROUP BY city_id
)
SELECT AVG(city_avg)
FROM city_average;

Spiegazione: I vantaggi delle CTE annidate si notano quando è necessario eseguire calcoli statistici composti da più passaggi successivi.

In questo caso, il risultato finale è una media totale delle medie per città delle somme delle distanze per ogni città e venditore. Si tratta di una media di medie di somme. Le CTE ci permettono di fare un'approssimazione graduale al risultato, analogamente a quanto farebbe un data scientist con le formule statistiche.

Esercizio 6: Usare le CTE annidate per confrontare insiemi di elementi

Esercizio: Confrontare le vendite medie di tutti i venditori negli Stati Uniti con le vendite medie di tutti i venditori nel resto del mondo.

Soluzione:

WITH cities_categorized AS (
  SELECT
    id AS city_id,
    CASE WHEN country = 'USA' THEN country ELSE 'Rest of the World' END AS category
  FROM city
),
sales_category AS (
  SELECT
    category,
    salesman_id,
    SUM(items_sold) total_sales
  FROM daily_sales ds
  JOIN salesman s
    ON s.id = ds.salesman_id
  JOIN cities_categorized
    ON cities_categorized.city_id = s.city_id
  GROUP BY category, salesman_id
)

SELECT
  category,
  AVG(total_sales)
FROM sales_category
GROUP BY category;

Spiegazione: Nel primo CTE (cities_categorized), abbiamo separato le città in due gruppi: città degli Stati Uniti e città del resto del mondo.

Nella seconda CTE, si combinano le informazioni della CTE cities_categorized con quelle di daily_sales e del venditore per ottenere i totali delle vendite raggruppati per le due categorie di città e per il venditore.

Nell'ultimo CTE SELECT, si raggruppa semplicemente per categoria di città e si ottiene la media delle vendite per ciascuna delle due categorie (città statunitensi e città del resto del mondo).

CTE ricorsive

Nella programmazione SQL, le CTE ricorsive sono comuni espressioni di tabella che fanno riferimento a se stesse. Come le funzioni ricorsive utilizzate in altri linguaggi di programmazione, le CTE ricorsive si basano sul principio di prendere i dati risultanti da un'esecuzione precedente, aggiungerli o modificarli e passare i risultati all'esecuzione successiva. Continuiamo a fare la stessa cosa fino a quando non viene soddisfatta una condizione di stop, ovvero quando si ottiene il risultato finale.

I CTE ricorsivi devono avere la parola RECURSIVE dopo la parola WITH. Il modo migliore per comprendere il funzionamento delle CTE ricorsive è utilizzare un semplice esempio, come nel seguente esercizio.

Esercizio 7: Uso delle CTE ricorsive per generare sequenze

Esercizio: Utilizzare la ricorsione per elencare tutti i numeri interi da 1 a 10.

Soluzione:

WITH RECURSIVE ten_numbers(prev_number) AS (
  SELECT 1
  UNION ALL
  SELECT ten_numbers.prev_number + 1
  FROM ten_numbers
  WHERE prev_number < 10
)

SELECT *
FROM ten_numbers;

Spiegazione: Questa query adotta la notazione CTE ricorsiva di PostgreSQL, che ha quattro parti:

1. Membro di ancoraggio: Qui si definisce il punto di partenza della ricorsione. Questa parte della query deve poter essere risolta autonomamente, senza dover utilizzare i risultati di precedenti iterazioni della stessa CTE.
2. Membro ricorsivo: Questa parte viene ripetuta il numero di volte necessario, utilizzando come base i risultati dell'iterazione precedente.
3. Condizione di terminazione: Questa condizione viene valutata dopo ogni ripetizione del membro ricorsivo; quando è soddisfatta, il ciclo ricorsivo termina. Se questa condizione non fosse presente o desse sempre un risultato vero, la ricorsione continuerebbe all'infinito.
4. Invocazione: La differenza principale tra questa query SELECT e altre query principali CTE è che questa SELECT funge da innesco per il ciclo di esecuzioni ricorsive.

In questo esercizio, il membro di ancoraggio restituisce semplicemente una riga con il numero 1. Il membro ricorsivo prende la riga con il numero 1 e la riga con il numero 1. Il membro ricorsivo prende le righe dell'esecuzione precedente e aggiunge (tramite la clausola UNION) una nuova riga con il valore precedente incrementato di 1. La condizione di terminazione indica che la query continuerà a iterare fino a quando il valore ottenuto non sarà uguale a 10.

Esercizio 8: Utilizzo di una CTE ricorsiva per attraversare una struttura di dati gerarchica (albero)

Per questo esercizio si utilizzerà la tabella employee che ha le colonne id, first_name, last_name e superior_id. Le sue righe contengono i seguenti dati:

Esercizio: Mostrare tutti i dati di ciascun dipendente, più un testo che mostri il percorso nella gerarchia dell'organizzazione che separa ciascun dipendente dal capo (identificato dal valore letterale 'Boss)'.

Soluzione:

WITH RECURSIVE hierarchy AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    'Boss' AS path
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employee.id,
    employee.first_name,
    employee.last_name,
    employee.superior_id,
    hierarchy.path || '->' || employee.last_name
  FROM employee JOIN hierarchy
  ON employee.superior_id = hierarchy.id
)

SELECT *
FROM hierarchy;

Spiegazione: I dati della tabella employee rappresentano una struttura gerarchica o ad albero, in cui ogni riga ha una colonna che la mette in relazione con il suo superiore (un'altra riga della stessa tabella). La riga che corrisponde al capo dell'azienda (il nodo radice dell'albero) è quella che presenta un valore nullo nella colonna superior_id. Pertanto, questo è il nostro membro di ancoraggio per costruire questa CTE ricorsiva. Il percorso di questo membro di ancoraggio contiene semplicemente il valore letterale "Boss".

Quindi, il membro della query ricorsiva unisce l'iterazione precedente della gerarchia con employeeponendo la condizione che i capi dei dipendenti dell'iterazione corrente (superior_id) siano già presenti nella gerarchia. Ciò significa che, per ogni iterazione, aggiungiamo un altro livello alla gerarchia. Questo strato è formato dai subordinati dei dipendenti aggiunti nell'iterazione precedente. Quindi la condizione di unione è employee.superior_id = hierarchy.id.

Il percorso di ogni dipendente viene assemblato concatenando il percorso del suo capo (hierarchy.path, che arriva fino a 'Boss') con il cognome del dipendente dell'iterazione corrente, unito da una stringa che rappresenta una freccia (hierarchy.path || '->' || employee.last_name).

Esercizio 9: Mostrare il percorso di un albero a partire dal nodo radice

Esercizio: Visualizzare un elenco contenente il nome e il cognome di ogni dipendente (compreso il capo), insieme a un testo (il campo percorso) che mostra il percorso dell'albero tra ogni dipendente e il capo. Nel caso del capo, la colonna del percorso deve mostrare il sito last_name del capo.

Soluzione:

WITH RECURSIVE hierarchy AS (
  SELECT
	first_name,
	last_name,
	CAST(last_name AS text) AS path
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
	employee.first_name,
	employee.last_name,
	hierarchy.path || '->' || employee.last_name AS path
  FROM employee, hierarchy
  WHERE employee.superior_id = hierarchy.id
)

SELECT *
FROM hierarchy;

Spiegazione: La soluzione di questo esercizio è molto simile a quella dell'esercizio precedente, con l'unica eccezione che il valore del percorso per il nodo radice non è un valore letterale di tipo TEXT, ma è un valore last_name nella tabella. employee nella tabella. Questo costringe a eseguire una conversione dei dati per evitare di ricevere un errore quando si esegue la query.

Poiché il CTE effettua una UNION tra i dati restituiti dal componente di ancoraggio e quelli restituiti dal componente ricorsivo, è indispensabile che entrambi i set di risultati abbiano lo stesso numero di colonne e che i tipi di dati delle colonne corrispondano.

La colonna last_name della tabella employee (indicata come percorso nel membro di ancoraggio CTE) è di tipo VARCHAR, mentre la concatenazione hierarchy.path || '->' || employee.last_name (indicata come percorso nel membro ricorsivo) produce automaticamente una colonna TEXT. Affinché UNION non causi un errore di mancata corrispondenza dei tipi, è necessario che CAST(last_name AS text) sia presente nel membro di ancoraggio. In questo modo, le colonne path di entrambe le parti del CTE saranno TEXT.

Esercizio 10: Utilizzo di più colonne ricorsive

Esercizio: Elencare tutti i dati di ciascun dipendente e il percorso nella gerarchia fino a raggiungere il capo, Includere una colonna chiamata distanza che mostra il numero di persone nella gerarchia dal capo al dipendente. Per il capo superiore, la distanza è 0; per i suoi subordinati, è 1; per i subordinati dei suoi subordinati, è 2, e così via.

Soluzione:

WITH RECURSIVE hierarchy AS (
  SELECT
	id,
	first_name,
	last_name,
	superior_id,
	'Boss' AS path,
	0 AS distance
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
	employee.id,
	employee.first_name,
	employee.last_name,
	employee.superior_id,
	hierarchy.path || '->' || employee.last_name,
	hierarchy.distance + 1
 FROM employee, hierarchy
 WHERE employee.superior_id = hierarchy.id
)
SELECT *
FROM hierarchy;

Spiegazione: Poiché nel CTE sono presenti due colonne ricorsive, è necessario indicare un valore iniziale per ciascuna di esse nel membro di ancoraggio. In questo caso, la colonna percorso ha il valore iniziale 'Capo' (come nell'esercizio 8) e la colonna distanza ha il valore 0. Poi, nel membro ricorsivo, il valore della distanza risulta dall'aggiunta di 1 alla distanza dell'iterazione precedente.

Esercizio 11: Uso delle CTE ricorsive per attraversare strutture di dati di tipo rete

Per questo esercizio si utilizzano due tabelle: una tabella destination (composta dalle colonne id e name) e una tabella ticket (composta dalle colonne city_from, city_to e cost ). La tabella destination contiene gli ID e i nomi di un gruppo di città, mentre la tabella ticket tabella indica il costo dei biglietti tra le coppie di città presenti nella tabella (se esistono). destination tabella (se esistono collegamenti di questo tipo).

Questi sono i dati di esempio di entrambe le tabelle (prima destinatione poi ticket):

Esercizio: Trovare l'itinerario più economico per viaggiare tra tutte le città della tabella, partendo da Varsavia. destination partendo da Varsavia. La query deve mostrare le seguenti colonne:

• path - I nomi delle città del percorso, separati da '->'.
• last_id - L'id della città finale del viaggio.
• total_cost - La somma dei costi dei biglietti.
• count_places - Il numero di città visitate. Questo deve essere uguale al numero totale di città in destination, cioè 4.

Soluzione:

WITH RECURSIVE travel(path, last_id,
	total_cost, count_places) AS (
  SELECT
	CAST(name as text),
	Id,
	0,
	1
  FROM destination
  WHERE name = 'Warsaw'
  UNION ALL
  SELECT
	travel.path || '->' || c2.name,
	c2.id,
	travel.total_cost + t.cost,
	travel.count_places + 1
  FROM travel
  JOIN ticket t
	ON travel.last_id = t.city_from
  JOIN destination c1
	ON c1.id = t.city_from
  JOIN destination c2
	ON c2.id = t.city_to
  WHERE position(c2.name IN travel.path) = 0
)
SELECT *
FROM travel
WHERE count_places = 4
ORDER BY total_cost ASC;

Spiegazione: Il metodo di risoluzione di questo esercizio è simile a quello dell'esercizio precedente. In questo caso, però, non c'è un ordine diretto nella relazione tra gli elementi della stessa tabella. Invece, le relazioni tra gli elementi della tabella destination sono espresse nella tabella ticket tabella, collegando ogni coppia di città collegate.

La prima riga dei risultati dell'interrogazione precedente mostra il percorso più economico. Questo è possibile perché l'esterno SELECT della query ordina i risultati in ordine crescente per total_cost. A sua volta, il membro esterno SELECT si assicura che il numero di città percorse sia 4, ponendo la condizione che count_places sia uguale a 4.

Il membro ricorsivo assicura che ogni iterazione aggiunga una nuova città al percorso impostando la condizione che il nome della città non sia già presente nel percorso (position(c2.name IN travel.path) = 0).

Poiché il punto di partenza del viaggio è la città di Varsavia, il membro di ancoraggio della CTE ricorsiva è la riga della tabella destination in cui il nome è uguale a "Varsavia". Si noti che la colonna nome è stata convertita nel tipo di dati TEXT (come nell'Esercizio 9), in modo che il tipo di dati corrisponda alla colonna corrispondente nel membro CTE ricorsivo.

Avete bisogno di altri esercizi sulle espressioni di tabella comuni?

Se avete lavorato con gli esercizi SQL di questo articolo, ora sapete come usare le espressioni di tabella comuni. E sapete quanto possano essere utili le CTE. Questi esercizi provengono dal nostro Query ricorsive e ci sono ancora più esercizi come questi nel corso completo.

Per diventare davvero bravi nell'analisi dei dati con SQL, pensate di unirvi a LearnSQL.it. Vi consigliamo di dare un'occhiata al pacchetto Completo per sempre SQL. Vi dà accesso a vita a tutti i corsi SQL che offriamo ora, più tutti quelli che aggiungeremo in seguito. In questo modo, potrete continuare ad apprendere nuove competenze per sempre. Iscrivetevi oggi stesso a LearnSQL.it e iniziate a costruire un grande futuro nell'analisi dei dati!