/*
Created		07/10/2016
Modified		07/10/2016
Project		
Model			
Company		
Author		
Version		
Database		PostgreSQL 8.1 
*/


/* Create Tables */


Create table cliente
(
	cod_cliente Integer NOT NULL,
	nome Varchar(50),
	data_nascimento Date,
 primary key (cod_cliente)
) Without Oids;


Create table produto
(
	cod_produto Integer NOT NULL,
	nome_produto Varchar(50),
	preco Money,
 primary key (cod_produto)
) Without Oids;


Create table pedido
(
	cod_pedido Integer NOT NULL,
	data_pedido Date,
	cod_cliente Integer NOT NULL,
 primary key (cod_pedido)
) Without Oids;


Create table pedido_produto
(
	cod_pedido Integer NOT NULL,
	cod_produto Integer NOT NULL,
	quantidade Integer,
 primary key (cod_pedido,cod_produto)
) Without Oids;



/* Create Foreign Keys */

Alter table pedido add  foreign key (cod_cliente) references cliente (cod_cliente) on update restrict on delete restrict;

Alter table pedido_produto add  foreign key (cod_produto) references produto (cod_produto) on update restrict on delete restrict;

Alter table pedido_produto add  foreign key (cod_pedido) references pedido (cod_pedido) on update restrict on delete restrict;


-------------------

insert into cliente (cod_cliente, nome, data_nascimento) 
    values (1,'grinaldo','1971/10/28'), (2,'joao','1981/01/01'), (3,'alberto','1965/04/04');


insert into produto (cod_produto, nome_produto, preco) 
      values (1,'coca-cola',10.0), (2, 'pepsi', 15.0), (3, 'fanta',12.5);

insert into produto (cod_produto, nome_produto, preco) 
      values (4,'sukita',10.0);


insert into pedido (cod_pedido, cod_cliente, data_pedido)
            values (1,1,'2016/10/01'), (2,2,'2016/04/04'), (3,3,'2016/01/01');

insert into pedido_produto (cod_pedido, cod_produto, quantidade)
        values (1,1,2), (1,3,2), (2,2,4), (3,1,4), (3,2,1);


---------------------
select c.nome, sum (pp.quantidade * pr.preco) as total
from cliente as c inner join pedido as p
    on (c.cod_cliente = p.cod_cliente)
     inner join pedido_produto as pp
        on (p.cod_pedido = pp.cod_pedido)
     inner join produto pr
        on (pp.cod_produto = pr.cod_produto)
group by c.nome  
order by total desc

select *
from produto as p
where p.cod_produto not in (select cod_produto from pedido_produto)


select c.nome, sum (pp.quantidade * pr.preco) as total
from cliente as c inner join pedido as p
    on (c.cod_cliente = p.cod_cliente)
     inner join pedido_produto as pp
        on (p.cod_pedido = pp.cod_pedido)
     inner join produto pr
        on (pp.cod_produto = pr.cod_produto)
group by c.nome  
having sum (cast ((pp.quantidade * pr.preco)as numeric)) > (
select  avg (cast ((pp.quantidade * pr.preco) as numeric) ) as total
from pedido as p inner join pedido_produto as pp
        on (p.cod_pedido = pp.cod_pedido)
     inner join produto pr
        on (pp.cod_produto = pr.cod_produto))




delete 
from cliente
where nome in (
select c.nome
from cliente as c inner join pedido as p
    on (c.cod_cliente = p.cod_cliente)
     inner join pedido_produto as pp
        on (p.cod_pedido = pp.cod_pedido)
     inner join produto pr
        on (pp.cod_produto = pr.cod_produto)
where extract(year from age(c.data_nascimento)) > 40
group by c.nome 
having  cast (sum (pp.quantidade * pr.preco) as numeric)  > 50.0
)