Torna all'elenco degli articoli Articoli
Tempo di lettura: 25 minuti

Esercitazioni online sulle query SQL di base: 20 esercizi per principianti

Author's photo
Tihomir Babic

Indice

Questi 20 esercizi sono proprio ciò che serve ai principianti per esercitarsi con le query SQL. Provate a risolvere ognuno di essi e poi guardate le soluzioni. Se c'è qualcosa che deve essere chiarito, ci sono spiegazioni per ogni soluzione.

In questo articolo si parlerà meno del solito. Scriverete invece le risposte alle query di esercitazione SQL. (Non preoccupatevi, abbiamo incluso le soluzioni se vi bloccate). L'obiettivo è quello di dare a voi, principianti, molte opportunità di esercitarvi con le query SQL.

Ho selezionato venti esempi dal nostro sito di base Pratica su SQL: Esegui le query! Se alla fine dell'articolo sentite il bisogno di esercitarvi di più - ve lo consiglio caldamente - in quel corso troverete quasi altri 100 esercizi interattivi di SQL. Si tratta di argomenti come l'interrogazione di una tabella, l'uso di JOINs, l'ordinamento dei dati con ORDER BY, l'aggregazione dei dati e l'uso di GROUP BY, la gestione di NULLs, l'esecuzione di operazioni matematiche e la scrittura di subquery.

Si tratta di argomenti che ogni principiante di SQL dovrebbe conoscere bene prima di passare agli argomenti più complessi. Il modo migliore per imparare l'SQL è scrivere costantemente il proprio codice. In questo modo, si impara a padroneggiare la sintassi SQL e le sue caratteristiche di base, oltre a comprendere la risoluzione dei problemi. Dopo tutto, lo scopo di conoscere l'SQL è saper usare i dati per risolvere i problemi.

E potreste andare anche oltre! Abbiamo il Pratica su SQL e il corso mensile Pratica su SQL per fare ancora più pratica con le query SQL.

Detto questo, tuffiamoci subito nella pratica dell'SQL, iniziando dal set di dati.

Esercitazioni di query SQL

Set di dati

Il dataset contiene i dati relativi alle finali delle gare di corsa su pista dei campionati di atletica: Olimpiadi di Rio de Janeiro nel 2016, Campionati mondiali IAAF di atletica leggera di Londra nel 2017 e Campionati mondiali IAAF di atletica leggera di Doha nel 2019.

I dati sono archiviati in sei tabelle: competition, event, discipline, final_result, athlete, e nationality. Lo schema è mostrato di seguito:

Esercitazioni di base sulle query SQL online

Le informazioni sulla competizione sono memorizzate nella tabella competition. Essa ha le seguenti colonne:

  • id - L'ID della competizione e la chiave primaria della tabella.
  • name - Il nome della competizione.
  • start_date - Il primo giorno della competizione.
  • end_date - L'ultimo giorno di gara.
  • year - L'anno in cui si è svolta la competizione.
  • location - Il luogo in cui si è svolta la competizione.

Ecco i dati della tabella.

idnamestart_dateend_dateyearlocation
7093747Rio de Janeiro Olympic Games2016-08-122016-08-212016Estádio Olímpico, Rio de Janeiro (BRA)
7093740London IAAF World Championships in Athletics2017-08-042017-08-132017Olympic Stadium, London (GBR)
7125365IAAF World Championships in Athletics2019-09-272019-10-062019Khalifa International Stadium, Doha (QAT)

La tabella discipline contiene informazioni per tutte le discipline di corsa. Ha queste colonne:

  • id - L'ID della disciplina e la chiave primaria della tabella.
  • name - Il nome della disciplina.
  • is_men - TRUE se si tratta di una disciplina maschile, FALSE se si tratta di una disciplina femminile.
  • distance - La distanza della disciplina, in metri.

Questa è un'istantanea delle prime cinque righe dei dati:

idnameis_mendistance
1Men's 100mTRUE100
2Men's 200mTRUE200
3Men's 400mTRUE400
4Men's 800mTRUE800
5Men's 1500mTRUE1,500

La tabella successiva è quella degli eventi, che contiene informazioni su ogni evento specifico:

  • id - L'ID dell'evento e la chiave primaria della tabella.
  • competition_id - Collega l'evento a una gara.
  • discipline_id - Collega l'evento a una disciplina.
  • final_date - Quando si è svolta la finale di questo evento.
  • wind - I punti di vento durante la finale.

Ecco le prime cinque righe di questa tabella:

idcompetition_iddiscipline_idfinal_datewind
1709374712016-08-140.2
2709374722016-08-18-0.5
3709374732016-08-140
4709374742016-08-150
5709374752016-08-200

I dati relativi a ciascun atleta sono contenuti nella tabella atleta:

  • id - L'ID dell'atleta e la chiave primaria della tabella.
  • first_name - Il nome dell'atleta.
  • last_name - Il cognome dell'atleta.
  • nationality_id - La nazionalità dell'atleta.
  • birth_date - La data di nascita dell'atleta.

Queste sono le prime cinque righe:

idfirst_namelast_namenationality_idbirth_date
14201847UsainBOLT11986-08-21
14238562JustinGATLIN21982-02-10
14535607AndréDE GRASSE31994-11-10
14201842YohanBLAKE11989-12-26

La tabella nationality contiene informazioni sul paese:

  • id - L'ID del paese e la chiave primaria della tabella.
  • country_name - Il nome del Paese.
  • country_abbr - L'abbreviazione di tre lettere del paese.

Ecco un'istantanea di cinque righe di questa tabella:

idcountry_namecountry_abbr
1JamaicaJAM
2United StatesUSA
3CanadaCAN
4South AfricaRSA
5Côte d’IvoireCIV

L'ultima tabella è final_result. Contiene informazioni sui partecipanti e sui loro risultati in un determinato evento:

  • event_id - L'ID dell'evento.
  • athlete_id - L'atleta
  • result - Il tempo/punteggio dell'atleta (può essere NULL).
  • place - Il piazzamento ottenuto dall'atleta (può essere NULL).
  • is_dsq - TRUE se si è verificata unasqualifica.
  • is_dnf - TRUE se l'atleta non ha terminatola gara.
  • is_dns - TRUE se l'atleta non ha iniziatola corsa.

Ecco l'istantanea:

event_idathlete_idresultplaceis_dsqis_dnsis_dnf
1142018470:00:101FALSEFALSEFALSE
1142385620:00:102FALSEFALSEFALSE
1145356070:00:103FALSEFALSEFALSE
1142018420:00:104FALSEFALSEFALSE
1144177630:00:105FALSEFALSEFALSE

Dopo aver dato un'occhiata al set di dati, iniziamo a esercitarci con le query SQL di base! Tutti gli esercizi richiederanno la conoscenza di SQL, quindi assicuratevi di conoscere tutti gli elementi di base di una query SQL.

Esercizio 1: mostrare le date finali di tutti gli eventi e i punti di vento

Esercizio: Trovare le date finali di tutti gli eventi e i punti di vento.

Soluzione:

SELECT
  final_date,
  wind
FROM event;

Spiegazione: I dati necessari si trovano nella tabella eventi. È necessario selezionare due colonne da essa: final_date e vento. Lo si fa scrivendo la prima colonna nell'istruzione SELECT. Quindi, si scrive il nome della seconda colonna e si separano i nomi delle colonne con una virgola.

Infine, si fa riferimento alla tabella nella clausola FROM.

Output:

final_datewind
2016-08-140.2
2016-08-18-0.5
2016-08-140
2016-08-150
2016-08-200

Esercizio 2: Mostra tutte le finali in cui il vento era superiore a .5 punti

Esercizio: Mostrare tutte le date delle finali con un vento superiore a 0,5 punti.

Soluzione:

SELECT final_date
FROM event
WHERE wind > 0.5;

Spiegazione: Per prima cosa, selezionare la colonna final_date dalla tabella event. In questo modo si ottiene un elenco di tutte le finali. Tuttavia, non si ha bisogno dell'intero elenco, ma solo delle finali in cui il vento è stato più forte di 0,5.

È quindi necessario filtrare i dati utilizzando la clausola WHERE. In essa si scrive il nome della colonna che si vuole filtrare; in questo caso, è la colonna wind. Per ottenere il vento superiore a 0,5, utilizzare l'operatore logico "maggiore di" (>).

Output:

final_date
2017-08-11
2019-09-28
2019-10-02

Esercizio #3: Mostra tutti i dati per tutte le maratone

Esercizio: Mostrare i dati della disciplina per tutte le maratone.

Soluzione: 

SELECT *
FROM discipline	
WHERE name LIKE '%Marathon%';

Spiegazione: Per selezionare tutte le colonne, non è necessario scrivere esplicitamente i loro nomi. Esiste un'abbreviazione per "tutte le colonne" chiamata asterisco (*). Al posto dei nomi delle colonne, basta inserire un asterisco in SELECT.

Poi, quando si vogliono i dati della tabella disciplinesi fa riferimento ad essa in FROM.

Infine, si devono filtrare i dati. Utilizzare WHERE e l' operatore LIKE. Questo operatore esamina i dati testuali della colonna e restituisce tutte le righe che contengono il testo nella condizione WHERE. In altre parole, la condizione cercherà la parola "Marathon". La parola deve essere inserita tra virgolette singole.

Tuttavia, non si conosce il nome esatto della disciplina; si sa solo che deve contenere quella parola. Può essere ovunque nel nome della disciplina: all'inizio, a metà o alla fine. Per cercare in qualsiasi punto della stringa, inserire l'operatore modulo (%) prima e dopo la parola da cercare.

Risultato:

idnameis_mendistance
8Men's MarathonTRUE42,195
16Women's MarathonFALSE42,195

Esercizio #4: Mostra tutti i risultati finali per i corridori che non corrono

Esercizio: Mostrare tutti i dati dei risultati finali dei corridori che non si sono piazzati.

Soluzione:

SELECT *
FROM final_result
WHERE place IS NULL;

Spiegazione: Sono necessarie tutte le colonne, quindi utilizzare un asterisco in SELECT e fare riferimento alla tabella in FROM. final_result in FROM.

È necessario mostrare solo i risultati dei corridori che hanno terminato la gara senza un piazzamento. Anche questa volta si utilizzerà WHERE e si filtrerà sulla colonna place. Se un corridore termina senza una posizione, la colonna place sarà vuota (cioè NULL). È necessario l'operatore IS NULL dopo il nome della colonna per restituire tutte queste righe.

Conoscere il significato di NULL in SQL sarebbe una buona idea prima di utilizzare l'operatore IS NULL.

Output:

event_idathlete_idresultplaceis_dsqis_dnsis_dnf
614464221NULLNULLTRUEFALSEFALSE
714530623NULLNULLFALSEFALSETRUE
714573513NULLNULLFALSEFALSETRUE
814167397NULLNULLFALSEFALSETRUE
814177784NULLNULLFALSEFALSETRUE

Esercizio #5: Mostrare tutti i dati dei risultati per i corridori non partiti

Esercizio: Mostrare tutti i dati dei risultati per i corridori che non hanno iniziato la corsa.

Soluzione: 

SELECT *
FROM final_result
WHERE is_dns IS TRUE;

Spiegazione: Selezionare tutte le colonne della tabella final_result utilizzando un asterisco e facendo riferimento alla tabella in FROM.

Poi, si vuole usare WHERE e filtrare la colonna per is_dns. Se il corridore non ha iniziato la gara, questa colonna avrà il valore TRUE. È quindi necessario utilizzare l'operatore IS TRUE dopo il nome della colonna.

Output: Ecco l'intero output:

event_idathlete_idresultplaceis_dsqis_dnsis_dnf
1414451797NULLNULLFALSETRUEFALSE
1614296979NULLNULLFALSETRUEFALSE
1914176330NULLNULLFALSETRUEFALSE
2214367867NULLNULLFALSETRUEFALSE
2414219653NULLNULLFALSETRUEFALSE
2414225279NULLNULLFALSETRUEFALSE
3214751813NULLNULLFALSETRUEFALSE
4114291986NULLNULLFALSETRUEFALSE

Esercizio n. 6: Mostra i nomi delle gare maschili di discipline inferiori a 500 metri

Esercizio: Mostrare solo i nomi delle discipline maschili in cui la distanza da percorrere è inferiore a 500 metri.

Soluzione:

SELECT name
FROM discipline
WHERE is_men IS TRUE
  AND distance < 500;

Spiegazione: Per prima cosa, selezionare il nome della colonna dalla tabella discipline.

È necessario filtrare nuovamente i dati, questa volta inserendo due condizioni in WHERE.

La prima condizione è che si tratti di una disciplina maschile. Quindi, si deve filtrare la colonna is_men utilizzando l'operatore IS TRUE. Poi si aggiunge la seconda condizione: i valori della colonna distance devono essere inferiori a 500. Questa condizione utilizza l'operatore less than ("meno di"). Questa condizione utilizza l'operatore meno di (<). Poiché entrambe le condizioni devono essere soddisfatte, separare le condizioni utilizzando l'operatore AND.

Risultato:

name
Men's 100m
Men's 200m
Men's 400m

Esercizio #7: Ordinamento dei nomi e delle abbreviazioni dei paesi

Esercizio: Mostrare i nomi e le abbreviazioni di tutti i Paesi. Ordinare l'output in ordine alfabetico per nome del paese.

Soluzione:

SELECT
  country_name,
  country_abbr
FROM nationality
ORDER BY country_name ASC;

Spiegazione: Selezionare il nome del paese e la sua abbreviazione dalla tabella nationality.

Per ordinare l'output, utilizzare la clausola ORDER BY. Si vuole ordinare per nome del Paese, quindi scrivere country_name in ORDER BY. L'output deve essere ordinato alfabeticamente, quindi utilizzare la parola chiave ASC (ascendente) dopo il nome della colonna.

L'output: Ecco le prime cinque righe dell'output:

country_namecountry_abbr
AfghanistanAFG
AlgeriaALG
American SamoaASA
AndorraAND
AngolaANG

Esercizio #8: Ordinare i nomi e i cognomi degli atleti

Esercizio: Mostrare il nome e il cognome di ogni atleta. Ordinare l'output in modo decrescente in base al nome dell'atleta. Se più atleti hanno lo stesso nome, mostrare i loro cognomi ordinati in modo decrescente.

Soluzione:

SELECT
  first_name,
  last_name
FROM athlete
ORDER BY first_name DESC, last_name DESC;

Spiegazione: Selezionare il nome e il cognome dalla tabella athlete.

Quindi, aggiungere la clausola ORDER BY. La prima condizione di ordinamento è in base al nome in senso decrescente, aggiungendo DESC dopo il nome della colonna. La seconda condizione di ordinamento ordina in base al cognome, anch'essa in modo decrescente. Anche in questo caso, scrivere il nome della colonna e aggiungere DESC. Le condizioni devono essere separate da una virgola.

Output: Ecco le prime cinque righe dell'output:

first_namelast_name
ZsófiaERDÉLYI
ZouhairAWAD
ZoeyCLARK
ZoeHOBBS
ZoeBUCKMAN

Esercizio #9: Ordinamento dei risultati finali su tre ore

Esercizio: Per tutti i risultati finali, mostrare gli orari che sono almeno di tre ore. Ordinare le righe in base al risultato in ordine decrescente.

Soluzione: 

SELECT result
FROM final_result
WHERE result >= INTERVAL '3 hours'
ORDER BY result DESC;

Spiegazione: Selezionare la colonna result dalla tabella final_result.

Quindi, utilizzare WHERE per trovare i risultati che sono inferiori a tre ore. Si possono usare gli operatori "maggiore o uguale" (>=) e INTERVAL.

I dati nella colonna dei risultati sono formattati come ora. Quindi, è necessario utilizzare INTERVAL per ottenere la parte specifica (intervallo) da quei dati. In questo caso, si tratta di tre ore. Scrivere semplicemente '3 ore' dopo INTERVAL.

Infine, ordinare l'output in modo decrescente in base al risultato.

Risultato: Ecco le prime cinque righe dell'output:

result
3:20:20
3:16:11
3:15:18
3:11:31
3:11:05

Esercizio #10: Mostrare i nomi e i luoghi dei 3 atleti più importanti

Esercizio: Per ogni atleta che è salito sul podio (cioè che si è classificato tra i primi 3), mostrare il cognome e il nome e la posizione.

Soluzione:

SELECT
  a.last_name,
  a.first_name,
  fin.place
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place <= 3;

Spiegazione: In questo esercizio è necessario utilizzare i dati di due tabelle: athlete e final_result. Quindi, iniziamo la spiegazione dalla clausola FROM.

Si fa riferimento alla tabella athlete e le si assegna l'alias "a", in modo da non dover scrivere il nome completo della tabella in altre parti della query. Per ottenere dati anche da un'altra tabella, è necessario unire le tabelle. In questo caso, utilizzare JOIN, che restituirà solo le righe corrispondenti di entrambe le tabelle. Per farlo, è sufficiente fare riferimento alla tabella final_result in JOIN e aggiungendo l'alias 'fin'.

Successivamente, occorre specificare la condizione di unione utilizzando la parola chiave ON. Le tabelle sono unite su colonne condivise: id da athlete e athlete_id da final_result. Si cercano righe in cui i valori di queste due colonne sono uguali, quindi si inserisce un segno di uguale (=) tra di esse. Davanti a ogni nome di colonna, inserire l'alias della tabella seguito da un punto, in modo che il database sappia in quale tabella si trova la colonna.

Dopo aver unito le tabelle, è possibile selezionare le colonne. Davanti al nome di ogni colonna, inserire l'alias della tabella per lo stesso motivo spiegato in precedenza. Ora si hanno i cognomi e i nomi degli atleti e le loro località.

Come ultimo passo, è sufficiente filtrare i dati utilizzando WHERE e la colonna place. Si cercano i piazzamenti sul podio, quindi i valori devono essere uguali o inferiori a tre. Utilizzate l'operatore "meno di o uguale" (<=).

Questa pratica di query SQL richiede la conoscenza di SQL JOIN. Se non siete ancora sicuri del loro funzionamento, date un'occhiata a queste SQL JOIN domande di esercitazione prima di passare agli altri esercizi.

Risultato: Ecco le prime cinque righe dell'output:

last_namefirst_nameplace
BOLTUsain3
BOLTUsain1
BOLTUsain1
GATLINJustin2
GATLINJustin1

Esercizio #11: Mostrare tutte le maratone con il loro nome di gara, l'anno di gara e il nome della disciplina

Esercizio: Mostrare tutte le maratone, il nome (rinominare questa colonna nome_gara) e l'anno della gara, nonché il nome della disciplina (rinominare questa colonna nome_disciplina).

Soluzione:

SELECT
  c.name AS competition_name,
  c.year,
  d.name AS discipline_name
FROM competition c
JOIN event e
  ON e.competition_id = c.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE d.name LIKE '%Marathon%';

Spiegazione: Questo esercizio mostra come unire più tabelle. Il principio è lo stesso di due tabelle. Si aggiungono solo altri JOINs e le condizioni di unione.

In questo caso, si uniscono le tabelle competition e event dove e.competition_id è uguale a c.id column.

Quindi, è necessario aggiungere la tabella discipline alla catena di unione. Scrivere di nuovo JOIN e fare riferimento alla tabella discipline. Aggiungere la condizione di unione: la colonna discipline_id della tabella event deve essere uguale alla colonna id della tabella discipline della tabella.

A questo punto, selezionate le colonne necessarie, ricordando di mettere l'alias della tabella davanti a ogni colonna. Alias competition_name e discipline_name usando la parola chiave AS per dare loro i nomi di colonna descritti nelle istruzioni.

Infine, filtrare i risultati per mostrare solo le discipline maratone.

Risultato:

competition_nameyeardiscipline_name
Rio de Janeiro Olympic Games2016Men's Marathon
Rio de Janeiro Olympic Games2016Women's Marathon
London IAAF World Championships in Athletics2017Men's Marathon
London IAAF World Championships in Athletics2017Women's Marathon
IAAF World Championships in Athletics2019Men's Marathon

Esercizio #12: Mostra i punteggi di Mo Farah per tutte le discipline

Esercizio: Mostrare i punteggi di Mo Farah (ID atleta di 14189197) per tutte le discipline. Mostrare NULL se non ha mai partecipato a una determinata disciplina. Mostrare i nomi, le date, i luoghi e i risultati di tutte le discipline maschili.

Soluzione:

SELECT
  d.name AS discipline_name,
  e.final_date,
  fin.place,
  fin.result
FROM discipline d
LEFT JOIN event e
  ON e.discipline_id = d.id
LEFT JOIN final_result fin
  ON fin.event_id = e.id
 AND athlete_id = 14189197
WHERE is_men IS TRUE;

Spiegazione: Unire le tabelle discipline e event sulle colonne discipline_id e id. È necessario utilizzare LEFT JOIN. Questo tipo di join restituirà tutte le righe della prima tabella (sinistra) e solo le righe corrispondenti della seconda tabella (destra). Se non ci sono righe corrispondenti, i valori saranno NULL. È l'ideale per questo esercizio, poiché è necessario mostrare tutte le discipline e utilizzare NULLs se Mo Farah non ha mai partecipato a una disciplina.

Anche il join successivo è un LEFT JOIN. Unisce la tabella event con la tabella final_result. La prima condizione di unione unisce le tabelle sulle colonne event_id e id. È necessario includere anche la seconda condizione, aggiungendo la parola chiave AND. Questa seconda condizione cercherà solo i dati di Mo Farah, cioè dell'atleta con l'ID 14189197.

Come ultimo passo, utilizzare WHERE per trovare solo le discipline maschili.

Output:

discipline_namefinal_dateplaceresult
Men's 5000m2016-08-2010:13:03
Men's 10,000m2016-08-1310:27:05
Men's 5000m2017-08-1220:13:33
Men's 10,000m2017-08-0410:26:50
Men's 800m2017-08-08NULLNULL
Men's Marathon2019-10-05NULLNULL
Men's 100m2017-08-05NULLNULL

Esercizio #13: Mostrare i nomi delle gare e il numero di eventi

Esercizio: Mostrare i nomi di tutte le competizioni e il numero di eventi per ogni competizione.

Soluzione: 

SELECT
  c.name AS competition_name,
  COUNT(*) AS events_held
FROM competition c
JOIN event e
  ON e.competition_id = c.id
GROUP BY c.name;

Spiegazione: Per prima cosa, mostrare il nome della colonna dalla tabella competition e rinominare la colonna in competition_name.

Quindi, utilizzare la funzione aggregata COUNT(*) per contare il numero di eventi che si sono svolti. La funzione COUNT() con l'asterisco conterà tutte le righe dell'output, compresi i NULL. Per una migliore leggibilità, la colonna risultante viene chiamata events_held.

Le tabelle che uniamo sono competition e event. Infine, per ottenere il numero di eventi per gara, è necessario GROUP BY il nome della gara.

Output:

competition_nameevents_held
IAAF World Championships in Athletics15
Rio de Janeiro Olympic Games16
London IAAF World Championships in Athletics16

Esercizio #14: Mostrare i nomi degli atleti più popolari

Esercizio: Mostrare i nomi degli atleti più popolari. I nomi sono popolari se almeno cinque atleti li condividono. Accanto al nome, mostrare anche il numero di atleti con quel nome. Ordinare i risultati in modo che i nomi più popolari siano i primi.

Soluzione:

SELECT
  first_name,
  COUNT(*) AS name_count
FROM athlete
GROUP BY first_name
HAVING COUNT(*) >= 5
ORDER BY COUNT(*) DESC;

Spiegazione: Per prima cosa, selezionare i primi nomi e contarli utilizzando COUNT(*). Quindi, raggruppare in base al nome dell'atleta. Ora si hanno tutti i nomi e il loro conteggio.

Ma è necessario mostrare solo i nomi con un conteggio superiore a cinque. Per ottenere questo risultato, utilizzare la clausola HAVING. Ha lo stesso utilizzo di WHERE, ma HAVING è usato per filtrare i dati aggregati.

Infine, ordinate il risultato in base al conteggio dei nomi, dal più alto al più basso. Non è possibile scrivere semplicemente il nome della colonna name_count in ORDER BY perché l'ordinamento viene fatto prima dell'aggregazione; SQL non riconoscerà il nome della colonna. Copiate invece COUNT(*) e ordinate in modo decrescente.

Questo esercizio mostra un tipico problema SQL che richiede di filtrare i dati con una funzione aggregata.

Risultato:

first_namename_count
David9
Daniel7
Michael7
Jessica6
Alex6
Sarah5
Diana5
Jonathan5
Emmanuel5
Isaac5
Julian5
Anna5

Esercizio #15: Mostrare ogni Paese e il numero di atleti che hanno terminato la gara senza un piazzamento.

Esercizio: Mostrare tutti i paesi con il numero di atleti che hanno terminato senza un posto. Mostrare 0 se non c'è nessuno. Ordinare l'output in ordine decrescente in base al numero di atleti e in ordine crescente in base al nome del Paese.

Soluzione:

SELECT
  n.country_name,
  COUNT(fin.athlete_id) AS athletes_no
FROM nationality n
LEFT JOIN athlete a
  ON n.id = a.nationality_id
LEFT JOIN final_result fin
  ON a.id = fin.athlete_id
 AND fin.place IS NULL
GROUP BY n.country_name
ORDER BY
  COUNT(fin.athlete_id) DESC,
  n.country_name ASC;

Spiegazione: È necessario mantenere tutte le righe della tabella nationality quindi è necessario LEFT JOIN con la tabella athlete tabella. Lo si fa dove id è uguale a nationality_id. Poi, LEFT JOIN un'altra tabella dove id della tabella è uguale a della tabella. athlete tabella è uguale a athlete_id dalla tabella. final_result tabella.

Poiché sono necessarie tutte le righe di nazionalità, non è possibile utilizzare la condizione IS NULL in WHERE. C'è una soluzione: spostatela nella clausola ON e otterrete tutti i valori in cui il luogo è NULL.

Ora è possibile selezionare la colonna country_name. Inoltre, utilizzate la funzione COUNT() sulla colonna athlete_id per ottenere il numero di atleti che si sono classificati senza un posto. Non è possibile utilizzare COUNT(*) in questo caso perché avrebbe contato f, mentre a voi serve il conteggio degli atleti concreti.

Per ottenere il valore del conteggio per paese, raggruppare l'output per nome del paese.

Infine, ordinare l'output in base al numero di atleti in senso decrescente e al nome del paese in senso crescente.

Output: Ecco le prime cinque righe dell'output:

country_nameathletes_no
Bahrain8
Ethiopia6
Turkey6
Kenya5
South Africa5

Esercizio #16: Calcolo del ritmo medio per ogni corsa

Esercizio: Calcolare il passo medio per ogni corsa e mostrarlo nella colonna average_pace.

Soluzione:

SELECT fin.result / (d.distance * 1.0 / 1000) AS average_pace
FROM event e
JOIN discipline d
  ON e.discipline_id = d.id
JOIN final_result fin
  ON fin.event_id = e.id;

Spiegazione: Per ottenere l'andatura media per corsa, è necessario dividere il risultato per la distanza. Questo è ciò che fa la query precedente, ma con due modifiche.

In primo luogo, è necessario moltiplicare la distanza per 1,0. Questo per convertire la distanza in un numero decimale. Senza questa operazione, la divisione potrebbe dare un risultato diverso, poiché il risultato sarà diviso per un numero intero. La seconda modifica consiste nel dividere la distanza per 1.000. In questo modo si converte la distanza da metri a chilometri.

Ora che si dispone del calcolo, assegnare a questa colonna l'alias average_pace.

Il resto della query è quello già visto negli esempi precedenti: si unisce la tabella event con la tabella discipline e poi con la tabella final_result.

Risultato: Ecco le prime cinque righe dell'output:

average_pace
0:01:38
0:01:39
0:01:39
0:01:39
0:01:39

Esempio #17: Trovare tutti i tempi più veloci della media per i 1.500 metri di corsa

Esercizio: Creare i tempi di tutte le corse di 1.500 metri. Mostrare solo i tempi che sono più veloci del tempo medio per quella corsa.

Soluzione:

SELECT fin.result
FROM final_result fin
JOIN event e
  ON fin.event_id = e.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE distance = 1500
  AND fin.result < (
    SELECT AVG(fin.result)
    FROM final_result fin
    JOIN event e
      ON fin.event_id = e.id
    JOIN discipline d
      ON e.discipline_id = d.id
    WHERE distance = 1500
  );

Spiegazione: Per risolvere questo esercizio è necessario conoscere le subquery di SQL. La loro definizione di base è che si tratta di query all'interno di una query principale. Vediamo come funziona!

Selezionare la colonna del risultato dalla tabella final_result. Quindi, JOIN la tabella con event e poi con la tabella discipline tabella.

Successivamente, è necessario impostare due condizioni in WHERE. La prima seleziona solo le distanze uguali a 1.500 metri.

La seconda cerca i dati il cui risultato è inferiore alla media totale delle corse di 1.500 metri. Per calcolare la media, utilizzare una sottoquery nel modo seguente.

Tra le parentesi dopo l'operatore di confronto, scrivere un'altra istruzione SELECT (cioè una subquery). In essa, utilizzare la funzione aggregata AVG() per calcolare il risultato medio. Il resto della query è identico alla query principale; si uniscono le stesse tabelle e si utilizza la stessa condizione di filtraggio in WHERE.

Risultato: Ecco le prime righe dell'output:

result
0:03:51
0:03:51
0:03:51
0:03:51
0:03:51
0:03:50
0:03:50
0:03:51

Esercizio #18: Trovare tutti gli atleti che hanno partecipato ad almeno due gare in una competizione.

Esercizio: Creare un elenco di atleti che hanno partecipato a due o più gare in una competizione. Mostrare solo il nome e il cognome.

Soluzione:

SELECT
  first_name,
  last_name
FROM athlete
WHERE id IN (
  SELECT fin.athlete_id
  FROM event e
  JOIN final_result fin
    ON fin.event_id = e.id
  GROUP BY e.competition_id, fin.athlete_id
  HAVING COUNT(*) >= 2
);

Spiegazione: Iniziare selezionando il nome e il cognome dalla tabella athlete.

Quindi, utilizzare WHERE per impostare una condizione. Utilizziamo nuovamente una sottoquery per restituire i dati da confrontare, questa volta con la colonna id. Tuttavia, nell'esempio precedente, abbiamo utilizzato l'operatore "meno di" (<) perché la subquery restituiva un solo valore. Questa volta, invece, utilizziamo l'operatore IN, che esaminerà tutti i valori restituiti dalla sottoquery e restituirà quelli che soddisfano la condizione.

La condizione è che gli atleti partecipino ad almeno due eventi di una competizione. Per trovare questi atleti, selezionare la colonna athlete_id e unire le tabelle event e final_result. Quindi, raggruppare i risultati in base agli ID della competizione e dell'atleta. Questo esempio mostra che è possibile raggruppare i risultati in base alla colonna che non è presente in SELECT. Tuttavia, tutte le colonne che compaiono in SELECT devono comparire anche in GROUP BY.

Infine, utilizzate HAVING per filtrare i dati. Contate il numero di righe usando COUNT(*). In questo modo, si conta quante volte appare ogni atleta. Impostare la condizione per restituire solo gli atleti con un conteggio uguale o superiore a due.

Output: Ecco l'istantanea dell'output.

first_namelast_name
UsainBOLT
AndréDE GRASSE
AaronBROWN
LaShawnMERRITT
WaydeVAN NIEKERK

Esercizio #19: Mostrare i corridori che hanno finito solo per primi

Esercizio: Mostrare tutti i corridori che non hanno mai terminato in un posto diverso dal primo; per loro non è mai mancato un posto. Mostrare tre colonne: id, first_name, e last_name.

Soluzione:

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1

EXCEPT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place != 1 OR fin.place IS NULL;

Spiegazione: Per questa soluzione è necessario utilizzare l'operatore set di EXCEPT. L'operatore set viene utilizzato per restituire i valori di due o più query. EXCEPT restituisce tutti i record unici della prima query tranne quelli restituiti dalla seconda query.

La prima query della soluzione cerca gli atleti che si sono classificati primi. Per ottenere questi valori, selezionare le colonne richieste dalla tabella athlete. Quindi, unire la tabella con la tabella final_result. Successivamente, impostare la condizione in WHERE per trovare solo i primi posti.

Ora, scrivere la parola chiave EXCEPT e seguirla con la seconda query.

La seconda query è quasi identica alla prima. L'unica differenza è rappresentata da due condizioni in WHERE.

La prima condizione restituisce tutti i piazzamenti che non sono i primi utilizzando l'operatore "non uguale" (!=). La seconda condizione cerca i piazzamenti nonNULL, cioè che non sono mai mancati per quell'atleta. Le condizioni sono collegate con l'operatore OR perché una di esse deve essere vera; l'atleta non può finire sotto il primo posto e non finire affatto.

Si noti che, affinché gli operatori di set funzionino, deve esserci lo stesso numero di colonne dello stesso tipo di dati in entrambe le query.

Risultato:

idfirst_namelast_name
14590785Elijah MotoneiMANANGOI
14208194EliudKIPCHOGE
14603138DonavanBRAZIER
14289014Jemima JelagatSUMGONG
14536762NoahLYLES
14377814LelisaDESISA
14209691DavidRUDISHA
14431159HalimahNAKAAYI

Esercizio #20: Trovare tutti gli atleti che non sono partiti e che hanno vinto almeno una volta

Esercizio: Trovare gli atleti che non hanno preso il via ad almeno una gara e che hanno vinto almeno una gara. Mostrare tre colonne: id, first_name, e last_name.

Soluzione:

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.is_dns IS TRUE

INTERSECT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1;

Spiegazione: Questo esercizio utilizza un altro operatore di insieme. Questa volta si tratta di INTERSECT, che restituisce tutti i valori che sono uguali in entrambe le query.

La prima query della soluzione elenca gli ID degli atleti e i nomi e cognomi. Le tabelle athlete e final_result sono unite sulle colonne id e athlete_id delle tabelle.

La condizione in WHERE cerca le righe con il valore VERO nella colonna is_dns, cioè la colonna che indica se l'atleta ha iniziato la gara.

Come nell'esempio precedente, scrivere l'operatore set e poi la seconda query.

La seconda query è uguale alla prima, tranne che per WHERE. La condizione di filtraggio troverà gli atleti che hanno terminato la gara per primi.

Insieme, queste due query producono gli atleti che non hanno iniziato la gara almeno una volta, ma che sono arrivati primi almeno una volta.

L'output:

idfirst_namelast_name
14291986DafneSCHIPPERS

Dalla pratica delle query SQL di base a diventare un maestro SQL

Bisogna iniziare da qualche parte. Queste 20 pratiche di query SQL di base sono ideali per costruire le fondamenta prima di imparare concetti più avanzati.

Avete imparato molto esercitandovi a scrivere query che utilizzano WHERE, ORDER BY, JOINs, GROUP BY e HAVING. Vi ho anche mostrato diversi esempi di gestione dei NULL, di esecuzione di calcoli, di scrittura di sottoquery e di utilizzo degli operatori di set. Le query contenute in questo articolo sono state tratte dal nostro sito Pratica SQL di base: una corsa tra le query! Qui troverete altri esercizi di SQL di base. E se volete fare più pratica, date un'occhiata al nostro Pratica su SQL che contiene 9 corsi di esercitazione SQL per principianti.

Se aggiungete 20 esempi di query SQL di base e 10 esercizi di pratica SQL per principianti, otterrete un livello intermedio di conoscenza di SQL.

Tags:

Ti potrebbe interessare anche