Esercizi su SQL
Puoi utilizzare come ambiente di lavoro il sito SQLite online.
Le clausole SELECT e FROM
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Selezionare le colonne first_name e last_name dalla tabella patients
Esercizio 02
Selezionare tutte le colonne della tabella patients
Esercizio 03
Selezionare la colonna first_name dalla tabella patients, rimuovendo i duplicati
La clausola WHERE
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Selezionare i pazienti con first_name = Abe
Esercizio 02
Selezionare i pazienti con first_name = Abe e city = Hamilton
Esercizio 03
Selezionare i pazienti con first_name = Abe o city = Hamilton
Esercizio 04
Selezionare i pazienti con first_name = Abe e city = Hamilton, oppure city = Ottawa
Esercizio 05
Selezionare i pazienti con first_name = Abe. city = Hamilton oppure Ottawa
Esercizio 06
Selezionare i pazienti privi di allergie (ovvero quelli per i quali il campo allergies ha valore NULL)
Esercizio 07
Selezionare i pazienti con allergie (ovvero quelli per i quali il campo allergies non ha valore NULL)
Esercizio 08
Selezionare il nome dei pazienti cha abitano a Ottawa
Esercizio 09
Selezionare il nome dei pazienti che non abitano a Ottawa
Esercizio 10
Selezionare il nome dei pazienti di Ottawa che hanno allergie
La clausola WHERE su più tabelle
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Selezionare l’ID del paziente, le sue allergie e le sue diagnosi (si trovano nella tabella admissions)
Esercizio 02
Selezionare tutti pazienti del Quebec
Esercizio 03
Selezionare il nome e il cognome dei dottori che hanno visitato almeno un paziente. La coppia (nome, cognome) deve essere distinta dalle altre.
La clausola JOIN
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Selezionare l’ID del paziente, le sue allergie e le sue diagnosi (si trovano nella tabella admissions)
Esercizio 02
Selezionare tutti pazienti del Quebec
Esercizio 03
Selezionare il nome e il cognome dei dottori che hanno visitato almeno un paziente. La coppia (nome, cognome) deve essere distinta dalle altre.
Esercizio 04
Sia data la seguente tabella Impiegato:
| Matricola | Nome | DataAssunzione | Salario | MatricolaManager |
|---|---|---|---|---|
| 1 | Piero | 1-1-95 | 3 M | 2 |
| 2 | Giorgio | 1-1-97 | 2,5 M | NULL |
| 3 | Giovanni | 1-7-96 | 2 M | 2 |
Scrivi una query SQL che dia la risposta alla domanda: quali sono i dipendenti di Giorgio?
Esercizio 05
Selezionare il nome e il cognome dei dottori che hanno visitato almeno un paziente. La coppia (nome, cognome) deve essere distinta dalle altre.
Ordina i risultati in ordine crescente (alfabetico) rispetto al nome.
Esercizio 06
Selezionare il nome e il cognome dei dottori che hanno visitato almeno un paziente. La coppia (nome, cognome) deve essere distinta dalle altre.
Ordina i risultati in ordine crescente (alfabetico) rispetto al cognome, decrescente rispetto al nome.
Esercizio 07
Per ogni ricovero, mostra la diagnosi, la specialità del dottore che ha effettuato il ricovero e le allergie del paziente ricoverato.
Funzioni aggregate
Qui trovi un riassunto sulle funzioni aggregate disponibili in SQL
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Quanti ricoveri (admissions) sono stati gestiti dal dottore con ID 1?
Esercizio 02
Mostra first_name, last_name e height del paziente con heigth massima.
Esercizio 03
Mostra l’altezza media e la massa media dei pazienti.
Esercizio 04
Quanti sono i pazienti residenti in Nova Scotia?
La clausola GROUP BY
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Per ogni doctor_id, mostra l’altezza media dei clienti che ha ricoverato. Ordina i risultati in ordine decrescente rispetto all’altezza media.
Esercizio 02
Per ciascun dottore, indica il nome, il cognome e il numero di ricoveri effettuati.
Esercizio 03
Per ogni distinto valore di discharge_date (attributo della tabella admissions) diverso da “1971-01-05”, mostra la sua frequenza assoluta. Ordina i risultati in ordine decrescente rispetto alla frequenza assoluta.
Esercizio 04
Per ogni allergia (che non sia NULL), mostra la sua frequenza assoluta. Ordina le entries in ordine decrescente di frequenza assoluta.
La clausola HAVING
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Per ogni doctor_id, mostra l’altezza media dei clienti che ha ricoverato, solo se l’altezza media è maggiore di 160 cm. Ordina i risultati in ordine decrescente rispetto all’altezza media.
Risultato atteso:
| attending_doctor_id | Average height |
|---|---|
| “3” | “163.48214285714286” |
| “15” | “163.45959595959596” |
| “12” | “161.7577319587629” |
| “16” | “161.53968253968253” |
| “10” | “160.50785340314135” |
| “7” | “160.2864077669903” |
Esercizio 02
Per ogni doctor_id, mostra l’altezza media dei clienti che ha ricoverato e il numero di ricoveri effettuati, solo se il numero di ricoveri è maggiore o uguale a 200. Ordina i risultati in ordine decrescente rispetto al numero di ricoveri.
Risultato atteso:
| attending_doctor_id | Average height | Number of admissions |
|---|---|---|
| “1” | “154.05607476635515” | “214” |
| “13” | “151.7846889952153” | “209” |
| “7” | “160.2864077669903” | “206” |
| “27” | “157.85714285714286” | “203” |
| “11” | “157.681592039801” | “201” |
| “26” | “156.65” | “200” |
Esercizio 03
Seleziona le coppie doctor_id, patient_id di dottori che hanno ricoverato almeno due volte lo stesso paziente. In una colonna chiamata Number of admissions mostra quante volte il dottore ha ricoverato quello stesso paziente.
Nota bene: raggruppamento doppio!
Esercizio 04
Sia dato il seguente schema di un database (gli attributi in grassetto sono chiavi primarie):
| Tabella | Attrributi |
|---|---|
| AUTOBUS | TARGA, ANNOIMMATRIC, MODELLO, MARCA, NO_POSTI |
| CONDUCENTE | MATRCOND, NOMECONDUCENTE, TELEFONO |
| CORSA | IDCORSA, CITTÀPARTENZA, CITTÀARRIVO, ORAPARTENZA, TEMPOSTIMATO |
| TURNO | IDCORSA, DATA, MATRCOND, BUS |
Estrarre, per ogni autista che ha almeno 300 distinte giornate di servizio, il numero di autobus distinti che ha guidato.
Query binarie
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Selezionare gli id dei pazienti alti almeno 225 cm oppure che siano stati ricoverati il giorno “2019-06-02”.
Esercizio 02
Selezionare gli id dei pazienti alti almeno 200 cm, eccetto quelli che sono stati ricoverati dopo il “2019-06-02”.
Esercizio 03
Selezionare gli id dei pazienti che sono sempre stati ricoverati dal dottore con id 1.
Esercizio 04
Sia dato il seguente schema di un database (gli attributi in grassetto sono chiavi primarie):
| Tabella | Attributi |
|---|---|
| ORDINE | CodOrd, CodCli, Data, Importo |
| DETTAGLIO | CodOrd, CodProd, Qta |
| CLIENTE | CodCli, Nome, Cognome, Città |
Estrarre i codici degli ordini i cui importi superano 500 Euro oppure in cui qualche prodotto è presente con quantità superiore a 1000.
Esercizio 05
Considera lo schema di database dell’esercizio precedente.
Estrarre i codici degli ordini i cui importi superano 500 euro ma in cui nessun prodotto è presente con quantità superiore a 1000.
Esercizio 06
Considera lo schema di database dell’esercizio precedente.
Estrarre i codici degli ordini i cui importi superano 500 euro e in cui qualche prodotto è presente con quantità superiore a 1000.
Viste
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Qual è il nome del dottore che ha gestito più ricoveri di tutti?
Esercizio 02
Qual è il nome del dottore che ha gestito meno ricoveri di tutti?
Esercizio 03
Qual è la media dei ricoveri gestiti da un dottore?
Esercizio 04
Risolvi l’Esercizio 04 della sezione ‘La clausola JOIN’ creando anzitutto una vista chiamata “Manager”, la quale contiene i dati di tutti e soli i manager.
Query complesse
I seguenti esercizi sono riferiti al database hospital.db
Esercizio 01
Seleziona id, nome, cognome di pazienti che non sono mai stati ricoverati. Ordina i risultati in ordine crescente di id.
I pazienti che non sono mai stati ricoverati sono 1148. Ti risulta?
Esercizio 02
Selezionare id, nome, cognome del dottore che ha fatto il massimo numero di ricoveri.
Dovresti ottenere come unico risultato il dottor Claude Walls, con id 1.
Esercizio 03
Sia dato il seguente schema di un database (gli attributi in grassetto sono chiavi primarie):
| Tabella | Attrributi |
|---|---|
| AUTOBUS | TARGA, ANNOIMMATRIC, MODELLO, MARCA, NUMERO_POSTI |
| CONDUCENTE | MATRCOND, NOMECONDUCENTE, TELEFONO |
| CORSA | IDCORSA, CITTÀPARTENZA, CITTÀARRIVO, ORAPARTENZA, TEMPOSTIMATO |
| TURNO | IDCORSA, DATA, MATRCOND, TARGA_BUS |
Estrarre la targa dei bus con almeno 50 posti di capienza, ma che non sono mai partiti da Milano.
Esercizio 04
Seleziona il nome delle province nelle quali il peso medio dei pazienti dell’ospedale è maggiore della media di tutti i pesi.
Dovresti ottenere come risultati: British Columbia, Newfoundland and Labrador, Nova Scotia, Ontario, Saskatchewan
Esercizio 05
Seleziona il nome delle province nelle quali l’altezza media dei pazienti dell’ospedale è minore della media di tutte le altezze.
Dovresti ottenere come risultati: Alberta, British Columbia, Ontario.
Esercizio 06
Esistono pazienti omonimi di un dottore, ovvero pazienti che hanno lo stesso nome e lo stesso cognome di un dottore?
I seguenti esercizi sono riferiti al database chinook.db
Esercizio 07
Seleziona i titoli di tutti gli album contenenti esclusivamente tracce di genere Rock.
Dovresti ottenere 112 titoli, i cui primi dieci, in ordine alfabetico, sono:
| title |
|---|
| “20th Century Masters - The Millennium Collection: The Best of Scorpions” |
| “A Matter of Life and Death” |
| “A-Sides” |
| “Achtung Baby” |
| “All That You Can’t Leave Behind” |
| “Appetite for Destruction” |
| “Are You Experienced?” |
| “Audioslave” |
| “B-Sides 1980-1990” |
| “BBC Sessions [Disc 1] [Live]” |
Esercizio 08
Seleziona il nome e il cognome dei clienti (customer) che hanno acquistato il maggior numero di tracce.
Il massimo numero di tracce acquistate dai clienti dovrebbe risultare essere 38. Ben 58 clienti risultano aver acquistato 38 tracce.
Esercizio 09
Seleziona il nome e il cognome di clienti che hanno acquistato tracce di più di 20 artisti diversi.
Dovresti ottenere come risultato questo:
| FirstName | LastName |
|---|---|
| “François” | “Tremblay” |
| “Ellie” | “Sullivan” |
| “Marc” | “Dubois” |
I seguenti esercizi sono riferiti al database olympics.db
Esercizio 10
Per ogni nome di medaglia diverso da NA, mostra quante medaglie di quel tipo sono state vinte ai giochi olimpici. Dovresti ottenere questo risultato:
| medal_name | COUNT(*) |
|---|---|
| Bronze | 12947 |
| Gold | 13106 |
| Silver | 12765 |
Esercizio 11
In quali città si sono tenute almeno due edizioni delle olimpiadi estive? Mostra il loro nome e il numero di edizioni tenute in esse. Dovresti ottenere questo risultato:
| city_name | COUNT(*) |
|---|---|
| London | 3 |
| Athina | 3 |
| Paris | 2 |
| Los Angeles | 2 |
| Stockholm | 2 |
Esercizio 12
Trova l’atleta (o gli atleti) che ha vinto il maggior numero di medaglie di bronzo. Mostra il nome e il numero di bronzi vinti. Dovresti ottenere questo risultato:
| full_name | bronzi |
|---|---|
| Jrg Mller | 4 |
| Jana Salat | 4 |
Esercizi vari su SELECT, FROM, WHERE, JOIN, ORDER BY, LIKE, funzioni aggregate
I seguenti esercizi sono riferiti al database ottenuto da società.sql
SELECT, FROM, WHERE
- Seleziona nome e cognome di tutte le persone nate dopo il 1980.
- Mostra tutte le società fondate prima del 2000.
- Trova tutte le persone che hanno una cittadinanza “Statunitense”.
- Elenca i servizi di streaming di tipo
video. - Mostra le società che hanno sede attuale in California.
WHERE con operatori di confronto
- Trova le persone nate tra il 1970 e il 1985.
- Mostra i servizi di streaming con costo minimo maggiore di 5.
- Elenca le società fondate dopo il 2010.
- Trova le persone che non sono morte (Anno_di_Morte IS NULL).
- Mostra i servizi con costo massimo minore o uguale a 10.99.
ORDER BY
- Elenca tutte le persone ordinate per anno di nascita crescente.
- Mostra le società ordinate per anno di fondazione decrescente.
- Elenca i servizi di streaming ordinati per costo massimo (dal più alto al più basso).
- Mostra nome e cognome delle persone ordinate alfabeticamente per cognome.
LIKE
- Trova le persone il cui nome inizia con “S”.
- Trova le società il cui nome attuale contiene “Inc”.
- Mostra i servizi di streaming che contengono la parola “YouTube”.
- Trova le persone con cognome che termina con “son”.
JOIN
- Mostra nome della società e nome del CEO.
- Elenca tutte le società con i rispettivi fondatori (nome e cognome).
- Mostra i servizi di streaming con il nome della società controllante.
- Trova tutte le persone che sono sia CEO che fondatori (JOIN tra tabelle).
- Mostra le società controllate da altre società (con nome della controllante).
Funzioni aggregate (senza GROUP BY)
- Conta il numero totale di persone.
- Trova l’anno di nascita minimo tra le persone.
- Trova il costo massimo tra tutti i servizi di streaming.
- Calcola la media del costo minimo dei servizi.
- Conta quante società sono presenti.
JOIN + WHERE (più complessi)
- Trova i CEO delle società fondate dopo il 2000.
- Elenca i fondatori delle società con sede a “Menlo Park, California, USA”.
- Mostra i servizi di streaming controllati da società fondate prima del 2000.
- Trova i CEO con cittadinanza non statunitense.
Altri operatori (IN, NOT IN, BETWEEN)
- Trova le persone nate negli anni ’70 (usa BETWEEN).
- Mostra i servizi con costo minimo tra 0 e 10.
- Trova le società con ID tra 1 e 5.
- Elenca le persone con cittadinanza IN (‘Statunitense’, ‘Canadese’).
- Trova le persone che NON sono CEO (usa NOT IN).
Esercizi “misti” (livello più alto)
- Mostra i nomi delle società e il numero totale di fondatori (usa COUNT senza GROUP BY → ragiona con sottoquery).
- Trova il servizio di streaming più costoso (solo quello con costo massimo più alto).
- Mostra la persona più giovane presente nel database.
- Elenca le società che non controllano nessun servizio di streaming.
- Trova le persone che sono fondatori di più di una società (senza GROUP BY → suggerimento: sottoquery).
Esercizi vari
I seguenti esercizi sono riferiti al database ottenuto da società.sql
VIEW – Fondatori e società
Crea una VIEW che mostri i fondatori insieme al nome attuale della società da loro fondata.
VIEW – CEO e anno di nascita
Definisci una VIEW che riporti per ogni CEO il suo nome, cognome e l’anno di fondazione della società che dirige.
Query annidata – Società più antica
Scrivi una query che restituisca il nome attuale della società più antica tra quelle presenti.
Query annidata – Fondatori non CEO
Trova i nomi e cognomi delle persone che sono fondatori di almeno una società ma non compaiono mai come CEO.
JOIN + GROUP BY – Numero di fondatori per società
Elenca ogni società con il relativo numero di fondatori, ordinando il risultato in ordine decrescente di numero di fondatori.
HAVING – Società con più di un fondatore
Trova le società che hanno più di un fondatore, mostrando nome attuale e conteggio.
Aggregazione su servizi – Costo medio per tipo
Calcola il costo medio (tra minimo e massimo) dei servizi di streaming raggruppati per tipo.
HAVING + Aggregazione – Servizi costosi
Individua i tipi di servizi di streaming che hanno un costo medio superiore a 15€.
Subquery + JOIN – Controllate di una società
Trova i nomi delle società che sono controllate da una società fondata nello stesso anno della società “Netflix”.
VIEW complessa – Persone e ruoli
Crea una VIEW che per ogni persona riporti: se è CEO, se è Fondatore, se non ha alcun ruolo associato alle società.