-- ============================================================================
-- WWW:  http://www.dis.uniroma1.it/~calvanese/didattica/03-04-basididati/
--         esercitazioni/eser-2003-10-24/soluzioni/punto-4-sol.sql
-- ============================================================================
-- Seconda esercitazione di laboratorio
-- Soluzione dell'esercizio 4
-- ============================================================================

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

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

--2) 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='Anna' AND
      GM.Genitore='Luigi' AND GP.Figlio=P.Nome;


-- 3) Trovare il minimo, il massimo, la somma e la media dei redditi delle
--    donne residenti a Milano.

SELECT MIN(reddito), MAX(reddito), SUM(reddito), AVG(reddito)
FROM Persone
WHERE sesso='F' AND residenza='Milano';


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

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

-- 5) 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;

-- 6) Definire due viste PrimoFiglioOrd e PrimoFiglioEta che restituiscono
--     per ogni persona il primo figlio.
--     PrimoFiglioOrd deve usare il valore dell'attributo ordFiglio.
--     PrimoFiglioEta deve usare l'eta` dei figli.

CREATE VIEW PrimoFiglioOrd (genitore, figlio) AS
SELECT genitore, figlio
FROM Genitori
WHERE ordFiglio = 1;

CREATE VIEW PrimoFiglioEta (genitore, figlio) AS
SELECT genitore, nome
FROM Genitori G1, Persone F1
WHERE G1.figlio = F1.nome AND
      NOT EXISTS (SELECT nome
                  FROM Genitori G2, Persone F2
                  WHERE G1.genitore = G2.genitore AND
                        G2.figlio = F2.nome AND
                        F2.eta > F1.eta);

--    Visualizzare i contenuti delle due viste e confrontarli.
--    Motivare le differenze sulla base dei dati memorizzati nelle tabelle
--    Persone e Genitori.

SELECT * FROM PrimoFiglioOrd;
SELECT * FROM PrimoFiglioEta;
