Alcune premesse:
1) Ho svolto questi esercizi come applicazione di quanto ho potuto appendere dalla visione
delle video lezioni (anche qui) del corso di basi di dati tenuto dal Prof. Paolo Atzeni e dal Prof. Riccardo Torlone.
2) Ho usato SQLite 3.7 come dbms per svolgere e testare questi esercizi. Un’istanza del database a scopo di esercizio è qui
3) Sono riportate le soluzioni degli esercizi così come li ho svolti,
ciò non implica che gli esercizi non possano essere svolti trovando soluzioni alternative.
4) Per semplicità si assume che l’attributo “nome” nella tabella “persone” sia sufficiente ad
identificare univocamente una persona

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:

 

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 annidateSELECT 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, personeWHERE 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. Capito 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 opportunamente 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;oppureSELECT 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. Qual’è lo stipendio medio dei figli di ciascun padre?
   
   SELECT padre, AVG(reddito) AS redditoMedioFigli
   FROM paternita JOIN persone
   WHERE figlio = nome
   GROUP BY padre;
8. 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
9. Trovare tutte le persone che hanno un figlio.SELECT DISTINCT madre AS genitore FROM maternita
   UNION
   SELECT DISTINCT padre AS genitore FROM paternita;Oppure si può usare il quantificatore esistenziale EXISTSSELECT nome AS genitore FROM persone
   WHERE EXISTS (SELECT * FROM paternita WHERE nome = padre)
   OR EXISTS (SELECT * FROM maternita WHERE nome = madre);
10. 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.

.