-- ============================================================================
-- WWW:  http://www.dis.uniroma1.it/~calvanese/didattica/02-03-basididati/
--         esercitazioni/eser-2002-10-17/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 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;
