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


--1) Trovare nome e reddito dei maschi con meno di 30 anni.

SELECT Nome, Reddito
FROM PERSONE
WHERE Eta<30 AND Sesso='M';


--2) Trovare nome e reddito dei genitori di Ezechiele

SELECT Genitore, Reddito
FROM PERSONE, GENITORI 
WHERE Figlio='Ezechiele' AND
      Genitore=Nome; 


--3) Trovare la relazione che mostra per ogni persona i nonni

SELECT P.Figlio AS Nipote, N.Genitore AS Nonno
FROM GENITORI P, GENITORI N
WHERE P.Genitore=N.Figlio;


--4) Trovare il reddito medio dei padri per eta di residenza.

SELECT eta, AVG(reddito) AS AVG_REDDITO
FROM Persone, Genitori
WHERE nome = genitore AND sesso = 'M'
GROUP BY eta;


--5) Trovare la relazione che mostra le coppie di fratelli (due persone sono fratelli se hanno un genitore in comune)

SELECT distinct G1.Figlio AS Nome1, G2.Figlio AS Nome2
FROM GENITORI G1, GENITORI G2
WHERE G1.Genitore=G2.Genitore and G1.Figlio>G2.Figlio;


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

SELECT genitore
FROM Genitori
GROUP BY genitore
HAVING COUNT(figlio) >= 2;


-- 7) Trovare la relazione che mostra per ciascun figlio i rispettivi genitori
--    (Padre, Madre, Figlio). 

SELECT GP.Genitore AS Padre, GM.Genitore AS Madre, GP.Figlio
FROM GENITORI GP, GENITORI GM, PERSONE PP, PERSONE PM
WHERE GP.Figlio=GM.Figlio AND
      GP.Genitore=PP.Nome AND PP.Sesso='M' AND
      GM.Genitore=PM.Nome AND PM.Sesso='F';


-- 8) Trovare l'elenco ordinato dei genitori i cui figli guadagnano tutti
--    piu` di 20 milioni.

/*-- con operatore di differenza:

SELECT DISTINCT genitore
FROM Genitori
  EXCEPT
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;


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

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