/*
Created		15/10/2016
Modified		15/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,
	descricao Varchar(50),
	valor Double precision,
 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 lista
(
	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 lista add  foreign key (cod_produto) references produto (cod_produto) on update restrict on delete restrict;

Alter table lista 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,'roberto', '1981/12/01'),
(3,'pedro', '2011/07/01');

insert into produto (cod_produto, descricao, valor)
values 
(1, 'coca-cola', 10.0),
(2, 'fanta', 7.0),
(3, 'sukita', 5.0);

insert into pedido (cod_pedido, cod_cliente, data_pedido)
values
(1, 1, '2016/10/15'),
(2, 2, '2016/10/11'),
(3, 1, '2016/5/5'),
(4, 3, '2015/1/1');

insert into lista (cod_pedido, cod_produto, quantidade)
values
(1,1,2), (1,3,4), (2,3,5), (3,2,7), (3,3,3);

-------------------------
select c.nome, sum (l.quantidade*r.valor) as total
from cliente as c inner join pedido as p
    on (c.cod_cliente= p.cod_pedido)
    inner join lista as l
     on (p.cod_pedido = l.cod_pedido)
    inner join produto as r
      on (l.cod_produto = r.cod_produto)
group by c.nome
having sum (l.quantidade*r.valor) =
(
select max (total) 
from
(select c.nome, sum (l.quantidade*r.valor) as total
from cliente as c inner join pedido as p
    on (c.cod_cliente= p.cod_pedido)
    inner join lista as l
     on (p.cod_pedido = l.cod_pedido)
    inner join produto as r
      on (l.cod_produto = r.cod_produto)
group by c.nome) as tabela      
)

---
insert into produto (cod_produto, descricao, valor)
values (4, 'guarana', 7.0);

select p.descricao
from produto as p
where p.cod_produto not in (select cod_produto from lista)

------

select c.nome, p.cod_pedido, SUM (l.quantidade*r.valor) as total
from cliente as c inner join pedido as p
    on (c.cod_cliente= p.cod_pedido)
    inner join lista as l
     on (p.cod_pedido = l.cod_pedido)
    inner join produto as r
      on (l.cod_produto = r.cod_produto)
group by p.cod_pedido, c.nome
having SUM (l.quantidade*r.valor) >
(
select avg (total)
from
(select p.cod_pedido, SUM (l.quantidade*r.valor) as total
from cliente as c inner join pedido as p
    on (c.cod_cliente= p.cod_pedido)
    inner join lista as l
     on (p.cod_pedido = l.cod_pedido)
    inner join produto as r
      on (l.cod_produto = r.cod_produto)
group by p.cod_pedido) as tabela
)

-------------
delete from cliente
where cod_cliente in (

select c.cod_cliente
from cliente as c inner join pedido as p
    on (c.cod_cliente= p.cod_pedido)
    inner join lista as l
     on (p.cod_pedido = l.cod_pedido)
    inner join produto as r
      on (l.cod_produto = r.cod_produto)
where extract (year from age(c.data_nascimento)) >=5
group by p.cod_pedido, c.cod_cliente
having SUM (l.quantidade*r.valor) > 50)