--Cittadino(nome, reddito)
--Albero(codice, costo)
--Adotta(cittadino,albero)


create table Cittadino (
	nome varchar(20) primary key,
	reddito int
	);
create table Albero (
	codice varchar(20) primary key,
	costo int
	);
create table Adotta (
	cittadino varchar(20) references Cittadino(nome),
	albero varchar(20) references Albero(codice)
	);
	
insert into Cittadino values ("Pippo", 10000);
insert into Cittadino values ("Topo", 15000);
	
insert into Albero values ("A100", 500);
insert into Albero values ("A200", 500);	
insert into Albero values ("A300", 1000);

insert into Adotta values("Pippo","A100");
insert into Adotta values("Pippo","A200");	
insert into Adotta values("Pippo","A300");	
insert into Adotta values("Topo","A300");				


--1) Restituire il codice degli alberi che sono stati adottati da piu'
--   di un cittadino.

select a1.albero
from Adotta a1, Adotta a2
where a1.albero=a2.albero and
      a1.cittadino<>a2.cittadino
;

--2) Restituire il nome di ogni cittadino X tale che tutti gli alberii di costo
--   pari a 500 sono stati adottati da X.

--  Restituire il nome di ogni cittadino X tranne quelli cittadini Y per
--  cui esiste un albero Z che costa 500, e Y NON HA ADOTTATO Z.


select nome
from Cittadino
except
select cittadino
from AUX
;

create view AUX as
select c.nome as cittadino, a.codice as albero 
from Cittadino c, Albero a
where a.costo = 500
except
select cittadino, albero
from Adotta 
;


--3) Per ogni valore che corrisponde al reddito di almeno un
--   cittadino, restituire il costo medio degli alberi adottati dai
--   cittadini con quel reddito.

select c.reddito, avg(c.reddito)
from Cittadino c left join Adotta ad on c.nome = ad.persona
               left join Albero a on ad.albero = a.codice 
group by c.reddito
;