set search_path to 20091117;
drop table prodotti;
create table prodotti(
   sigla integer primary key,
   nome character (2) not null,
   categoria character(20) not null
);
drop table vendite;
create table vendite(
   siglaprod integer, 
   data date,
   quantita integer not null,
   primary key(siglaprod,data)
);
insert into prodotti values (101,'A','Pasta');
insert into prodotti values (102,'B','Bevanda');
insert into vendite values (101,'13-nov-2009',10);
insert into vendite values (101,'14-nov-2009',30);
insert into vendite values (102,'13-nov-2009',25);
insert into vendite values (102,'14-nov-2009',35);

select Sigla
from Prodotti
where not exists (select *
                  from Vendite
                  where SiglaProd=Sigla
                  and Quantita < 20);

select Sigla
from Prodotti
where not exists (select *
                  from Vendite
                  where Quantita < 20);

select sigla, nome, avg(quantita)
from prodotti join vendite on sigla = siglaprod
group by sigla, nome

select data, siglaprod
from vendite v
where quantita < (select avg(quantita)
                  from vendite
                  where siglaprod = v.siglaprod) 

--- oppure equivalente
create view venditemedie as
	select siglaprod, avg(quantita) as quantitamedia
	from vendite 
	group by siglaprod
select data, v.siglaprod
from   vendite v join venditemedie m on v.siglaprod=m.SiglaProd
where  v.quantita < m.quantitamedia

create view datasiglaprod as 
	 select data, v.siglaprod
     from   vendite v join venditemedie m on v.siglaprod=m.SiglaProd
     where  v.quantita < m.quantitamedia

select data, count(*)
from datasiglaprod
group by data
