CREATE TABLE Bagnante (
       nome VARCHAR PRIMARY KEY NOT NULL, 
       eta INTEGER
);

CREATE TABLE Pattino (
       codice VARCHAR PRIMARY KEY NOT NULL, 
       colore VARCHAR
);

CREATE TABLE Prenotazione (
       bagnante VARCHAR NOT NULL, 
       pattino VARCHAR NOT NULL, 
       PRIMARY KEY (bagnante,pattino),
       FOREIGN KEY (bagnante) REFERENCES Bagnante(nome),
       FOREIGN KEY (pattino) REFERENCES Pattino(codice)           
);


insert into Bagnante values ('Pippo',30);
insert into Bagnante values ('Topolino',30);
insert into Bagnante values ('Clarabella',25);
insert into Bagnante values ('Qui',10);
insert into Bagnante values ('Quo',10);
insert into Bagnante values ('Qua',10);

insert into Pattino values ('100', 'Verde');
insert into Pattino values ('110', 'Verde');
insert into Pattino values ('200', 'Rosso');
insert into Pattino values ('300', 'Blu');
insert into Pattino values ('310', 'Blu');
insert into Pattino values ('320', 'Blu');

insert into Prenotazione values ('Pippo', '200');
insert into Prenotazione values ('Topolino', '100');
insert into Prenotazione values ('Topolino', '110');
insert into Prenotazione values ('Clarabella', '100');
insert into Prenotazione values ('Clarabella', '200');
insert into Prenotazione values ('Qui', '300');
insert into Prenotazione values ('Quo', '310');
insert into Prenotazione values ('Qua', '320');


--1. Restituire i bagnanti che hanno prenotato almeno due pattini di colori.

select p1.bagnante as bagnante
from Prenotazione p1, Prenotazione p2, Pattino pp1, Pattino pp2
where p1.bagnante = p2.bagnante and p1.pattino = pp1.codice and 
        p2.pattino = pp2.codice 
      and pp1.colore <> pp2.colore
;


--2. Restituire i bagnanti che hanno prenotato solo pattini di uno stesso colore. 

select bagnante
from Prenotazione
except
select p1.bagnante as bagnante
from Prenotazione p1, Prenotazione p2, Pattino pp1, Pattino pp2
where p1.bagnante = p2.bagnante and p1.pattino = pp1.codice and 
        p2.pattino = pp2.codice 
      and pp1.colore <> pp2.colore
;


--3. Per ogni colore X, restituire l’età media dei bagnanti che hanno prenotato almeno un pattino di colore X, ma solo se tale età media e' minore di 30.

select p.colore as colore, avg(b.eta) as etaMedia
from Prenotazione pr, Bagnante b, Pattino p
where pr.bagnante = b.nome and  pr.pattino = p.codice
group by colore
having etaMedia < 30
;

