19 esercizi pratici su PostgreSQL con soluzioni dettagliate

Probabilmente avete sentito il detto "la pratica rende perfetti". Scoprite se questa frase è vera provando la serie completa di esercizi pratici su PostgreSQL che troverete in questo articolo!

Per padroneggiare qualsiasi lingua, è essenziale esercitarsi regolarmente per non perdere l'abilità. Questo vale tanto per la comunicazione verbale quanto per la programmazione. E la programmazione SQL non fa eccezione. Anche il programmatore PostgreSQL più esperto ha bisogno di esercitarsi quotidianamente con PostgreSQL per perfezionare (e mantenere) le proprie capacità.

I principianti di PostgreSQL dovrebbero sviluppare le proprie capacità attraverso semplici esercizi che consentano loro di incorporare gradualmente le nuove conoscenze. In questo articolo troverete una serie di esercizi pratici su PostgreSQL che vi aiuteranno a imparare rapidamente a interrogare i database PostgreSQL. In questo articolo troverete altri esercizi pratici di SQL per principianti.

Per svolgere questi esercizi, è necessario disporre di un server PostgreSQL in cui creare un database, aggiungervi tabelle e popolarlo di dati. È inoltre necessaria un'applicazione client che consenta di eseguire comandi SQL sul server PostgreSQL. Con questi elementi, è possibile creare le tabelle di esempio che consentiranno di svolgere tutti gli esercizi pratici su PostgreSQL riportati in questo articolo. Se avete bisogno di aiuto, potete leggere questo articolo su come creare il vostro database SQL di esercitazione.

Esistono molti modi per esercitarsi con l'SQL online. Quello che raccomando specificamente agli utenti di PostgreSQL è il nostro sito interattivo Esercizi SQL in PostgreSQL. In esso troverete 88 esercizi che vanno da concetti semplici a concetti avanzati. Le tabelle di esempio utilizzate in questo articolo sono già create e popolate; potete scrivere ed eseguire tutte le query che volete e vedere come funzionano. Gli esercizi di questo articolo sono tratti direttamente dal corso: se li trovate utili, potete star certi che troverete utile anche il resto del corso. Non preoccupatevi: potete commettere qualsiasi tipo di errore, non c'è nulla che possiate rompere e nessuno si arrabbierà con voi per aver eseguito query strane.

Infine, tenete a portata di mano il nostro foglio informativo gratuito su PostgreSQL mentre leggete questo articolo. Questa guida rapida riassume i principali comandi e funzioni di PostgreSQL.

Ora iniziamo a fare pratica con PostgreSQL!

Set di dati PostgrePratica su SQL

La tabella principale che utilizzeremo per gli esercizi PostgreSQL di questo articolo contiene informazioni sui gatti: il loro nome, la razza, l'età, ecc. Il nome della tabella è semplicemente gatto e la sua struttura è la seguente:

• id: Un valore numerico che identifica ogni gatto rappresentato nella tabella.
• name: Il nome del gatto.
• breed
• coloration
• age
• Fav_toy: Il tipo di giocattolo preferito dal gatto.

Ecco un esempio di dati:

id	name	breed	coloration	age	fav_toy
1	Micky	Maine Coon	tortoiseshell	3	ball - red
2	Nine	Ragamuffin	cream	2	ball - green
3	Carmen	Persian	brown	2	toy mouse
4	Luna	Abyssinian	tortoiseshell	12	teaser
5	Bella	Siamese	red	15	teaser

Esercizio 1: Conoscere i dati

Esercizio: Ricavare tutte le informazioni dalla tabella cat.

Soluzione:

SELECT *
FROM cat;

Spiegazione: Il comando SELECT * FROM cat chiede a PostgreSQL di recuperare tutte le colonne e tutte le righe della tabella. cat tabella. La chiave per ottenere tutte le colonne è SELECT *. Il simbolo "*" funziona come un carattere jolly: se non si conoscono i nomi delle colonne, SELECT * le restituirà tutte.

Con la clausola FROM cat si dice a PostgreSQL che si vogliono ottenere informazioni dalla tabella cat dalla tabella. Non impostando alcuna condizione aggiuntiva, si otterranno tutte le righe. Il punto e virgola alla fine del comando indica semplicemente che il comando finisce lì.

Esercizio 2: Selezione di colonne specifiche di una tabella

Esercizio: Selezionare solo le colonne name ed età dalla tabella cat.

Soluzione:

SELECT name, age
FROM cat;

Spiegazione: Nell'esercizio precedente si è usato il carattere jolly "*" per selezionare tutte le colonne di una tabella. In questo esercizio, si vuole vedere solo il nome e l'età di ogni gatto. Invece di usare il carattere jolly "*", è sufficiente specificare i nomi delle colonne name e age dopo il comando SELECT. Separare le colonne con una virgola.

Esercizio 3: Raccolta di informazioni sui gatti siamesi

Esercizio: Ottenere dalla tabella dei gatti tutte le informazioni sui gatti siamesi.

Soluzione:

SELECT *
FROM cat
WHERE breed = 'Siamese';

Spiegazione: Per applicare un filtro alle righe selezionate, utilizzare la clausola WHERE. Questa consente di specificare una condizione che le righe devono soddisfare per essere incluse nel set di risultati. In questo esercizio, la condizione WHERE chiede al database di fornire informazioni solo sui gatti siamesi.

La condizione specificata nella clausola WHERE è (in generale) una condizione logica che può essere valutata come vera o falsa. Nella sua forma più semplice, indica il nome di una delle colonne della tabella (breed), quindi un operatore di confronto (=) e un valore da confrontare ('Siamese'). Si noti che tutti i letterali di stringa devono essere racchiusi tra virgolette.

Esercizio 4: Trovare i gatti più giovani

Esercizio: Ottenere dalla tabella le righe cat che corrispondono a individui di età inferiore a 8 anni.

Soluzione:

SELECT *
FROM cat
WHERE age < 8;

Spiegazione: Nell'esercizio precedente, abbiamo visto come filtrare i risultati della query confrontando una colonna di testo (breed) con un valore letterale ('Siamese'). In questo modo sono state incluse solo le righe in cui il valore della colonna corrisponde alla stringa letterale specificata.

Anche in questo esercizio è necessario specificare una condizione nella clausola WHERE. Tuttavia, ora si applica la condizione a un valore numerico e si utilizza l'operatore di confronto non di uguaglianza < (meno di).

Per ottenere un elenco di tutte le righe della tabella cat per i gatti di età inferiore a 8 anni, è necessario utilizzare la colonna età nella clausola WHERE. La si combina con l'operatore di confronto < e con il valore numerico 8.

Allo stesso modo, è possibile utilizzare criteri diversi per confrontare i valori numerici. Per vedere i gatti più vecchi di 8 anni, ad esempio, basta cambiare la condizione nella clausola WHERE in age > 8.

Esercizio 5: Ricerca di gatti siamesi giovani

Esercizio: Ottenere le righe corrispondenti ai gatti siamesi di età inferiore a 8 anni.

Soluzione:

SELECT *
FROM cat
WHERE breed = 'Siamese'
AND age < 8;

Spiegazione: Spesso è necessario ottenere righe che soddisfano più di una condizione. Per ottenere questo risultato, tutte le condizioni devono essere specificate nella clausola WHERE e unite dalla congiunzione AND. Come risultato di questa query, PostgreSQL restituirà un insieme di righe per i gatti siamesi che hanno meno di 8 anni.

Oltre alla congiunzione AND, esistono altri modi per combinare le condizioni in affermazioni logiche che danno come risultato vero o falso.

Esercizio 6: Uso di condizioni alternative (una o l'altra)

Esercizio: Prendete un elenco di gatti persiani o siamesi dalla tabella. cat tabella.

Soluzione:

SELECT id, name
FROM cat
WHERE breed = 'Siamese'
OR breed = 'Persian';

Spiegazione: Le condizioni combinate dall'operatore logico OR restituiscono Vero quando almeno una delle espressioni è True. In questo esercizio, abbiamo usato l'operatore OR per ottenere i dati dei gatti siamesi o persiani.

Esercizio 7: Combinazione di diverse condizioni logiche

Esercizio: Ottenere dalla tabella un elenco di gatti siamesi o persiani che hanno meno di 5 anni. cat che hanno meno di 5 anni o più di 10 anni.

Soluzione:

SELECT id, name
FROM cat
WHERE (breed = 'Siamese' OR breed = 'Persian')
AND (age < 5 OR age > 10);

Spiegazione: Per ottenere dati da una tabella che soddisfano una combinazione di condizioni alternative o obbligatorie, è necessario combinare gli operatori logici OR e AND. Questo viene fatto con le parentesi, in modo che PostgreSQL capisca correttamente la precedenza delle condizioni.

Occorre prestare attenzione quando gli operatori OR e AND sono inseriti nella stessa clausola WHERE. PostgreSQL combinerà le condizioni logiche nell'ordine in cui appaiono , a meno che non vengano utilizzate le parentesi. Se si utilizzano le parentesi (come nell'esempio precedente), il risultato di ciò che è tra le parentesi viene valutato per primo; quindi, questi risultati vengono combinati con l'operatore che li collega.

In questo esercizio, prima viene valutata la razza del gatto. Se soddisfa una delle due condizioni di WHERE, il database valuterà la serie successiva di condizioni per vedere se il gatto soddisfa i requisiti di età. Solo i gatti che soddisfano una condizione di razza e una di età sono inclusi nel risultato.

Esercizio 8: Elenco di gatti di razze diverse

Esercizio: Ottenere un elenco di gatti persiani, siamesi o ragdoll senza usare l'operatore OR.

Soluzione:

SELECT id, name
FROM cat
WHERE breed IN ('Persian', 'Siamese', 'Ragdoll');

Spiegazione: A volte è necessario filtrare i risultati di una query in base a un insieme di valori. Questo può essere ottenuto specificando molte condizioni collegate da operatori OR, come in questo caso:

SELECT name
FROM cat
WHERE breed = 'Persian' OR breed = 'Siamese' OR breed = 'Ragdoll';

Ma per semplificare una query con molte enumerazioni, è preferibile utilizzare l'operatore logico IN, che valuta l'appartenenza di un valore a un insieme. In questo caso, il valore sarebbe la colonna razza e l'insieme sarebbe un elenco di valori ('Persian', 'Siamese', 'Ragdoll').

L'operatore IN facilita la lettura e la comprensione di query complesse.

Esercizio 9: Ricerca di corrispondenze parziali

Esercizio: Ottenere tutte le righe della tabella cat corrispondenti a gatti il cui giocattolo preferito è una palla di qualsiasi colore.

Soluzione:

SELECT *
FROM cat
WHERE fav_toy LIKE '%ball%';

Spiegazione: Quando si cerca una corrispondenza in colonne contenenti descrizioni, spesso non si sa esattamente come sono memorizzate le descrizioni. A volte è necessario cercare una parola particolare che può comparire in una descrizione. In queste situazioni, l'operatore di confronto = non è utile, poiché restituisce solo le righe in cui la corrispondenza è esatta.

Quando è necessario trovare corrispondenze parziali, PostgreSQL consente di cercare frammenti di testo all'interno delle colonne varchar. Il trucco consiste nel sostituire l'operatore = con LIKE, aggiungendo dei caratteri jolly per indicare le parti di testo che non si conoscono.

Nella tabella cat la colonna fav_toy contiene "palla" e il suo colore, ad esempio "palla rossa" o "palla verde". Con l'operatore LIKE, si può chiedere a PostgreSQL di restituire le righe in cui fav_toy contiene la parola "palla".

Il simbolo % posto ai lati della parola "palla" funge da jolly, indicando che può esserci qualsiasi testo che circonda "palla". Le righe risultanti sono quelle in cui la parola "palla" compare come parte del testo nella colonna fav_toy, indipendentemente dal testo che contiene.

Esercizio 10: Trovare i valori NULL in alcune colonne

Esercizio: Ottenere tutte le righe della tabella cat corrispondenti ai gatti che non hanno un giocattolo preferito.

Soluzione:

SELECT name
FROM cat
WHERE fav_toy IS NULL;

Spiegazione: Ci sono situazioni in cui è necessario ottenere le righe di una tabella per le quali non ci sono informazioni in una determinata colonna. In altre parole, occorre dire al database di recuperare solo le righe in cui una determinata colonna ha un valore NULL. Non è possibile utilizzare gli operatori di confronto per questo scopo; in SQL, qualsiasi confronto che coinvolga valori NULL produce sempre il valore logico False.

L'unico modo per dire al database di recuperare tali righe è specificare che il valore della colonna IS NULL: fav_toy IS NULL.

Ricordate che la parola NULL in SQL si riferisce a un valore sconosciuto. Non è la stessa cosa del valore 0 in una colonna numerica o di una stringa di lunghezza nulla in una colonna varchar. Qualsiasi operazione numerica, di data o di stringa che coinvolga un valore NULL risulta in un valore NULL.

Esercizio 11: Evitare le righe senza dati in alcune colonne

Esercizio: ottenere tutte le righe della tabella cat corrispondenti ai gatti che hanno un giocattolo preferito.

Soluzione:

SELECT name
FROM cat
WHERE fav_toy IS NOT NULL;

Spiegazione: Sappiamo come ottenere le righe in cui una colonna ha un valore NULL. Ma cosa succede se si ha bisogno dell'opposto, cioè di ottenere tutte le righe con qualsiasi valore in quella colonna? La condizione IS NOT NULL fa esattamente il contrario di IS NULL: restituisce tutte le righe che hanno qualche dato (qualsiasi dato) nella colonna fav_toy.

Esercizio 12: Ottenere un elenco di gatti ordinati per età

Esercizio: selezionare nome ed età dalla tabella cat assicurandosi che i risultati siano ordinati per età.

Soluzione:

SELECT name, age
FROM cat
ORDER BY age;

Spiegazione: Quando si esegue una query SELECT in un database SQL, è impossibile prevedere in quale ordine appariranno i risultati, a meno che non si specifichi un criterio di ordinamento. Se si desidera ottenere i risultati di una query in un ordine particolare, è necessario specificare le colonne che determineranno l'ordinamento utilizzando una clausola ORDER BY.

Esercizio 13: Ordinamento dei gatti per razza ed età

Esercizio: Selezionare la razza, il nome e l'età di tutte le righe della tabella catottenendo i risultati ordinati prima per razza e poi per età.

Soluzione:

SELECT breed, name, age
FROM cat
ORDER BY breed, age;

Spiegazione: È possibile utilizzare la clausola ORDER BY per indicare una o più colonne come criterio di ordinamento. La query di questo esercizio ordina i risultati per razza; all'interno di ogni razza, le righe sono ordinate per età.

Esercizio 14: Uso dell'ordinamento ascendente e discendente

Esercizio: Selezionare la razza, il nome e l'età di tutti i gatti, quindi ordinare i risultati prima per razza in ordine alfabetico e poi per età, dal più vecchio al più giovane.

Soluzione:

SELECT breed, name, age
FROM cat
ORDER BY breed ASC, age DESC;

Spiegazione: La clausola ORDER BY consente anche di specificare l'ordine ascendente o discendente per ciascuna delle colonne dell'ordinamento. La parola ASC indica l'ordine ascendente (da A a Z, da 1 a 10); DESC indica l'ordine discendente (da Z ad A, da 10 a 1). Se l'ordine di ordinamento non è specificato, si assume l'ordine ascendente. Per i dati alfanumerici, l'ordine ascendente corrisponde all'ordine alfabetico.

La query di questo esercizio ordina le righe in ordine alfabetico per razza (in ordine crescente) e poi per età in ordine decrescente.

Esercizio 15: Raggruppare e aggregare i dati

Esercizio: Selezionare le razze di gatti dalla tabella cat e il numero di gatti di ciascuna razza.

Soluzione:

SELECT breed, COUNT(*)
FROM cat
GROUP BY breed;

Spiegazione: La clausola GROUP BY raggruppa le righe in base al valore di una colonna (GROUP BY breed); viene restituita una riga per ogni valore del gruppo (ad esempio, la tabella avrebbe una riga per il Siamese, una per il Ragdoll e così via). cats avrà una riga per il Siamese, una per il Ragdoll e così via).

Per ottenere il numero di gatti di ogni razza, si utilizza la funzione aggregata COUNT(*), che restituisce il numero di righe di ogni gruppo.

Quando si usa la clausola GROUP BY, è importante notare che le colonne dell'elenco SELECT che non sono usate in una funzione aggregata devono comparire anche nell'elenco delle colonne della clausola GROUP BY.

Se ritenete di aver bisogno di più pratica per comprendere appieno il raggruppamento in SQL, provate questi 10 esercizi di GROUP BY.

Esercizio 16: Ottenere l'età del gatto più giovane e più vecchio di ogni razza

Esercizio: Selezionate un elenco di razze di gatti dalla tabella cat insieme all'età massima e minima di ciascuna razza.

Soluzione:

SELECT breed, MIN(age), MAX(age)
FROM cat
GROUP BY breed;

Spiegazione: Il raggruppamento ci permette di ottenere dati sintetici. Oltre a ottenere il numero di elementi in ogni gruppo (come abbiamo fatto nell'esercizio precedente), possiamo usare le funzioni per calcolare totali, medie e valori massimi/minimi per ogni gruppo.

In questo esercizio, le funzioni MIN() e MAX() sono utilizzate per ottenere l'età massima e minima di ogni razza.

Esercizio 17: Raggruppamento per colonne multiple

Esercizio: Selezionare un elenco di razze di gatti e di diverse colorazioni dalla tabella. cat tabella, contando il numero di gatti di ogni combinazione di razza e colorazione.

Soluzione:

SELECT 
  breed, 
  coloration, 
  COUNT(*)
FROM cat
GROUP BY breed, coloration;

Spiegazione: È possibile che si vogliano raggruppare i dati in base a più di una colonna, creando una gerarchia di diversi criteri di raggruppamento annidati. Ciò può essere fatto dettagliando tutti i campi di raggruppamento nella clausola GROUP BY.

L'ordine in cui questi campi vengono scritti determinerà l'aspetto della gerarchia di raggruppamento. In questo esercizio, i dati vengono raggruppati per razza e poi per colorazione all'interno di ogni razza. La funzione COUNT(*) mostra quanti gatti sono presenti nella tabella per ogni combinazione di razza e colorazione.

Esercizio 18: Impostazione di condizioni su dati aggregati

Esercizio: Ottenere un elenco delle razze rappresentate da più di un record nella tabella. cat tabella.

Soluzione:

SELECT breed, COUNT(*)
FROM cat
GROUP BY breed
HAVING COUNT(*) > 1;

Spiegazione: Abbiamo visto che WHERE consente di impostare condizioni sulle righe restituite dalla query. Tuttavia, potrebbe essere necessario filtrare le righe raggruppate impostando condizioni sui risultati delle funzioni aggregate.

A tale scopo, è necessario impostare la condizione che il risultato della funzione COUNT(*) sia maggiore di 1. Ma non è possibile specificare questa condizione nella clausola WHERE; essa viene calcolata sulle singole righe prima che GROUP BY venga elaborata dal database. Pertanto, è necessario utilizzare la clausola HAVING, come abbiamo fatto in questo esercizio.

Esercizio 19: Ottenere dati correlati da tabelle diverse

Esercizio: Selezionare l'origine della razza di ogni gatto nella tabella cat tabella ottenendola dalla tabella correlata. breed_origin tabella correlata. Ecco un'istantanea dei dati della tabella breed_origin tabella:

breed	origin
Ragdoll	California
Persian	Unknown
Ragamuffin	California
Maine Coon	Maine
Siamese	Thailand
Abyssinian	Southeast Asia

Soluzione:

SELECT cat.name, cat.breed, breed_origin.origin
FROM cat JOIN breed_origin
ON cat.breed = breed_origin.breed;

Spiegazione: Se si ha bisogno di una query che restituisca l'origine della razza per ciascun gatto, è necessario unire la tabella con la tabella cat con la tabella breed_origin tabella. Per farlo, è necessario specificare entrambe le tabelle nella clausola FROM, separate dalla parola JOIN.

La condizione di unione viene specificata dopo i nomi delle tabelle e preceduta dalla parola ON. È necessario impostare questa condizione in modo che il database sappia come abbinare correttamente le righe. In questo caso, la condizione è che la colonna razza di cat sia uguale alla colonna razza di breed_origin.

Poiché c'è più di una tabella nella clausola FROM, è meglio mettere il nome della tabella prima del nome della colonna, separandoli con un punto (ad esempio cat.breed). Questo evita ambiguità sia per il database che per il lettore. Non è obbligatorio farlo per tutte le colonne, ma è essenziale per le colonne che condividono lo stesso nome in entrambe le tabelle.

Nell'esempio precedente, la colonna breed è presente sia in cat e breed_originquindi è obbligatorio anteporre il nome della tabella ogni volta che si fa riferimento a una delle due colonne.

Una volta appreso come unire le tabelle in SQL, è possibile rafforzare le proprie conoscenze completando questi 12 esercizi pratici di JOIN.

Pronti per gli esercizi avanzati di PostgrePratica su SQL?

Cosa ne pensate di questa serie di esercizi di PostgreSQL per principianti? Ricordate che potete continuare a esercitarvi con gli 88 esercizi del corso interattivo Esercizi SQL in PostgreSQL, che è solo uno dei 65 corsi pratici di SQL per principianti ed esperti che potete trovare su LearnSQL.it.

Se gli esercizi di PostgreSQL suggeriti vi sono sembrati molto facili, forse siete pronti per iniziare a imparare PostgreSQL avanzato. Tenete presente che ogni sforzo fatto per imparare PostgreSQL sarà stato ben investito, poiché questo dialetto del linguaggio SQL sta crescendo rapidamente e aprirà le porte a molte interessanti opportunità di lavoro. Continuate a esercitarvi e a imparare e manterrete fresche le vostre competenze in SQL.