-- ============================================================================
-- WWW:  http://www.dis.uniroma1.it/~calvanese/didattica/03-04-basididati/
--         esercitazioni/eser-2003-10-24/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) Trovare l'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;

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

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