-- ============================================================================
-- Esercitazione SQL
-- Soluzione dell'esercizio 4
-- ============================================================================


--1) Trovare le madri di persone che guadagnano piu` di 20 milioni.

SELECT DISTINCT Genitore AS Madre
FROM PERSONE PF, GENITORI, PERSONE PM
WHERE PF.Nome=Figlio AND PM.Nome=Genitore AND
      PM.Sesso='F' AND PF.Reddito>20;


--2)Trovare i Nonni di Aldo e di Andrea

SELECT DISTINCT N.Genitore AS Nonno
FROM GENITORI P, GENITORI N
WHERE (P.Figlio='Aldo' or P.Figlio='Andrea') and N.Figlio=P.Genitore;


--3) Trovare i figli (in comune) di Franco e Maria 

SELECT GP.figlio 
FROM GENITORI GP, GENITORI GM
WHERE GP.Figlio=GM.Figlio AND
      GP.Genitore='Franco' AND 
      GM.Genitore='Maria';


--4) Trovare i fratelli di Maria

SELECT distinct G2.Figlio AS Fratello_di_Maria
FROM GENITORI G1, GENITORI G2
WHERE G1.Genitore=G2.Genitore and G1.Figlio='Maria' and G1.Figlio<>G2.Figlio;


--5) Trovare il reddito complessivo dei figli di Anna e Luigi

SELECT sum(P.reddito) AS REDDITOCOMPL 
FROM PERSONE AS P, GENITORI GP, GENITORI GM
WHERE GP.Figlio=GM.Figlio AND
      GP.Genitore='Maria' AND
      GM.Genitore='Franco' AND GP.Figlio=P.Nome;


--6) Trovare la relazione che mostra le coppie fratello-sorella

SELECT distinct G1.Figlio AS Fratello, G2.Figlio AS Sorella
FROM GENITORI G1, GENITORI G2, PERSONE F, PERSONE S
WHERE G1.Genitore=G2.Genitore and G1.Figlio=F.nome and F.sesso='M' and 
G2.Figlio=S.nome and S.sesso='F';


--7) Trovare l'eta` media dei geniori di Olga 

SELECT avg(eta) AS EtaMedia 
FROM PERSONE P, GENITORI 
WHERE Figlio='Olga' AND
    Genitore=Nome;


--8) Trovare per ogni persona il numero dei suoi figli.

SELECT genitore AS persona, COUNT(*) AS numfigli
FROM Genitori
GROUP BY genitore;

--9) Trovare il reddito delle persone che sono genitori di almeno 2 figli.

-- Non utilizzando raggruppamento

SELECT DISTINCT nome, reddito
FROM Persone, Genitori G1, Genitori G2
WHERE nome = G1.genitore AND nome = G2.genitore AND
      G1.figlio <> G2.figlio;

-- Utilizzando raggruppamento

SELECT nome, reddito
FROM Persone, Genitori
WHERE nome = genitore
GROUP BY nome, reddito
HAVING COUNT(figlio) >= 2;
