Esercizi sulle query in SQL

Dato il seguente schema di database: 

CREATE TABLE maternita(
madre text references persone(nome),
figlio text unique references persone(nome)
);
CREATE TABLE paternita(
padre text references persone(nome),
figlio text unique references persone(nome)
);
CREATE TABLE persone(
nome text primary key,
eta numeric,
reddito numeric
);

Scrivere in SQL le query necessarie a:



Nota:
(per semplicità si assume che l'attributo "nome" nella tabella "persone" sia sufficiente ad identificare univocamente una persona)
(un'istanza del database a scopo di esercizio è qui)
(Sono presentate alcune soluzioni senza però che ne siano escluse altre)
(Le query SQL sono state testate con la versione 3.7 del SQLite database engine)

1. Trovare nome e reddito della madre di Filippo.
   
   SELECT nome, reddito
   FROM persone, maternita
   WHERE nome = madre AND figlio = 'Filippo';
   
   oppure tramite l'uso di query annidate
   
   SELECT nome, reddito
   FROM persone
   WHERE nome = (SELECT madre
           FROM maternita
           WHERE figlio = 'Filippo');
   
   
2. Trovare i padri delle persone che guadagnano più di 20.
   
   SELECT DISTINCT padre
   FROM paternita, persone
   WHERE figlio = nome AND reddito > 20;
   
   Osservazione: questo esercizio mostra l'uso della parola chiave DISTINCT e mette in evidenza le differenze che esistono tra ciò che in pratica è una tabella e quello che invece nella teoria dei database relazionali viene considerata una relazione.
   Una relazione è un insieme di tuple, mentre una tabella è una lista di ennuple. Qual'è la differenza?
   
   oppure tramite query annidate
   
   SELECT DISTINCT padre
   FROM paternita
   WHERE figlio IN (SELECT nome
             FROM persone
             WHERE reddito > 20);
   
   
3. Ricavare la relazione genitore-figlio.
   Questa relazione è già presente nel database, ma è divisa nelle relazioni di maternità e paternità. Per ottenere la relazione richiesta è sufficiente unire maternità e paternità insieme facendo attenzione a rinominare opportunamenti i loro attributi.
   
   SELECT padre AS genitore, figlio FROM paternita
   UNION
   SELECT madre AS genitore, figlio FROM maternita;
   
   
4. Trovare nome, padre e madre delle persone per cui sia noto il padre e la madre.
   Si ricercano i figli che sono comuni alle relazioni di paternità e maternità. E'necessario un equi-join sull'attributo figlio.
   
   SELECT P.figlio, padre, madre
   FROM paternita P, maternita M
   WHERE P.figlio=M.figlio;
   
   oppure
   
   SELECT figlio, padre, madre
   FROM paternita NATURAL JOIN maternita;
   
   
   
5. Per ogni persona trovare padre e madre. Rispetto al caso precedente sono da considerarsi informazioni da riportare nel risultato anche i casi in cui nella base di dati NON siano presenti padre e/o madre di un individuo.
   Nota: se nel database fossero presenti padre e madre di ogni persona, allora questo dovrebbe contenere l'intero albero genealogico fino ad Adamo ed Eva!
   
   SELECT nome,padre,madre
   FROM (persone LEFT JOIN paternita ON nome = figlio) AS P LEFT JOIN maternita AS M on nome = M.figlio;
   
   Per risolvere questa interrogazione è necessario l'uso dell'operatore di join esterno.
   SQLite ha a disposizione l'operatore di join a sinistra.
   
   
6. Quanti figli ha avuto ciascuna madre?
   
   SELECT madre, count(*) AS numFigli
   FROM maternita GROUP BY madre;
   
   Questa interrogazione mostra l'uso dell'operatore di aggregazione GROUP BY e delle funzione che operano sugli aggregati (nel nostro caso la funzione count) . L'operatore di aggregazione non è un operatore dell'algebra relazionale ma è specifico del linguaggio SQL.
   
   
   
7. Trovare le persone che guadagnano più dei rispettivi padri; mostrando nome della persona, il suo reddito ed il reddito del padre.
   
   SELECT bimbo.nome AS nome, bimbo.reddito AS reddito, papa.reddito AS padrereddito
   FROM persone papa, paternita relazione, persone bimbo
   WHERE papa.nome = relazione.padre AND bimbo.nome = relazione.figlio AND bimbo.reddito > papa.reddito
   
   
8. Trovare i padri che hanno figli che guadagnano TUTTI più di 20.
   Risolvere questa interrogazione richiederebbe l'uso di un quantificatore universale, che però non è presente in SQL ma può essere facilmente simulato con l'uso del quantificatore esistenziale EXISTS.
   Come noto dalla logica, l'interrogazione richiesta è equivalente a "trovare i padri che NON hanno ALCUN figlio che guadagna meno di 20".
   
   SELECT DISTINCT padre
   FROM paternita AS A
   WHERE NOT EXISTS (SELECT *
             FROM paternita B, persone
             WHERE A.padre = B.padre
             AND B.figlio = nome
             AND reddito <= 20);
   
   Si può anche risolvere osservando che l'insieme dei padri che hanno figli che guadagnano più di 20 può essere ottenuto sottraendo all'insieme di tutti i padri quelli  che hanno almeno un figlio che ha un reddito minore o uguale a 20.
   
   SELECT DISTINCT padre FROM paternita
   EXCEPT
   SELECT DISTINCT padre FROM paternita, persone
             WHERE figlio = nome AND reddito <= 20;
   
   quest'ultimo esempio mostra che pensare in termini d'insiemi può essere altrettanto efficace.
   

.