-- ============================================================================
-- WWW:  http://www.dis.uniroma1.it/~calvanese/didattica/02-03-basididati/
--         esercitazioni/eser-2002-10-17/soluzioni/punto-3-sol.sql
-- ============================================================================
-- Seconda esercitazione di laboratorio
-- Soluzione dell'esercizio 3
-- ============================================================================

-- 1) Trovare il numero di luoghi di residenza distinti.

select count(distinct Residenza)
from PERSONE;


-- 2) Trovare il minimo, il massimo, la somma e la media dei redditi di
--    persone raggruppate per residenza.

select Residenza, min(Reddito) as Min_Reddito, max(Reddito) as Max_Reddito,
                  sum(Reddito) as Sum_Reddito, avg(Reddito) as Avg_Reddito
from PERSONE
group by Residenza;


-- 3) Trovare il reddito medio dei padri per citta` di residenza.

select Residenza, avg(Reddito) as Avg_Reddito
from PERSONE, GENITORI
where Nome = Genitore and Sesso = 'M'
group by Residenza;


-- 4) Trovare le persone che sono genitori di almeno 2 figli.

select Genitore
from GENITORI
group by Genitore
having count(Figlio) >= 2;


-- 5) Definire una vista EtaMediaFigli che restituisce per ogni persona
--    l'eta` media dei suoi figli.

create view EtaMediaFigli (Genitore, Eta) as
  select Genitore, avg(Eta)
  from GENITORI, PERSONE
  where Figlio = Nome
  group by Genitore;

-- Visualizzare il contenuto della vista.

select * from EtaMediaFigli;


- 6) Trovare lo elenco ordinato dei genitori i cui figli guadagnano tutti
--   piu` di 20 milioni.

select distinct Genitore
from GENITORI
  minus
select distinct Genitore
from GENITORI, PERSONE
where Nome = Figlio and Reddito <= 20
order by Genitore;

-- Senza operatore di differenza:

select distinct Genitore
from GENITORI
where Genitore not in (select Genitore
                       from GENITORI, PERSONE
                       where Nome = Figlio and Reddito <= 20)
order by Genitore;

-- Senza operatore di differenza usando una vista:

create view GenitoriFigliRedditoM20 (Genitore) as
select Genitore
from GENITORI, PERSONE
where Nome = Figlio and Reddito <= 20;

select distinct Genitore
from GENITORI
where Genitore not in (select * from GenitoriFigliRedditoM20)
order by Genitore;


-- 7) Utilizzare la vista EtaMediaFigli per trovare tra tutti i genitori quello
--    i cui figli hanno la massima eta` media.

select Genitore
from EtaMediaFigli
where Eta = (select max(Eta)
             from EtaMediaFigli);

-- oppure

select Genitore
from EtaMediaFigli
where Eta >= all (select Eta
                  from EtaMediaFigli);
