/*<br />
Created		07/10/2016<br />
Modified		07/10/2016<br />
Project		<br />
Model			<br />
Company		<br />
Author		<br />
Version		<br />
Database		PostgreSQL 8.1 <br />
*/<br />
<br />
<br />
/* Create Tables */<br />
<br />
<br />
Create table cliente<br />
(<br />
	cod_cliente Integer NOT NULL,<br />
	nome Varchar(50),<br />
	data_nascimento Date,<br />
 primary key (cod_cliente)<br />
) Without Oids;<br />
<br />
<br />
Create table produto<br />
(<br />
	cod_produto Integer NOT NULL,<br />
	nome_produto Varchar(50),<br />
	preco Money,<br />
 primary key (cod_produto)<br />
) Without Oids;<br />
<br />
<br />
Create table pedido<br />
(<br />
	cod_pedido Integer NOT NULL,<br />
	data_pedido Date,<br />
	cod_cliente Integer NOT NULL,<br />
 primary key (cod_pedido)<br />
) Without Oids;<br />
<br />
<br />
Create table pedido_produto<br />
(<br />
	cod_pedido Integer NOT NULL,<br />
	cod_produto Integer NOT NULL,<br />
	quantidade Integer,<br />
 primary key (cod_pedido,cod_produto)<br />
) Without Oids;<br />
<br />
<br />
<br />
/* Create Foreign Keys */<br />
<br />
Alter table pedido add  foreign key (cod_cliente) references cliente (cod_cliente) on update restrict on delete restrict;<br />
<br />
Alter table pedido_produto add  foreign key (cod_produto) references produto (cod_produto) on update restrict on delete restrict;<br />
<br />
Alter table pedido_produto add  foreign key (cod_pedido) references pedido (cod_pedido) on update restrict on delete restrict;<br />
<br />
<br />
-------------------<br />
<br />
insert into cliente (cod_cliente, nome, data_nascimento) <br />
    values (1,'grinaldo','1971/10/28'), (2,'joao','1981/01/01'), (3,'alberto','1965/04/04');<br />
<br />
<br />
insert into produto (cod_produto, nome_produto, preco) <br />
      values (1,'coca-cola',10.0), (2, 'pepsi', 15.0), (3, 'fanta',12.5);<br />
<br />
insert into produto (cod_produto, nome_produto, preco) <br />
      values (4,'sukita',10.0);<br />
<br />
<br />
insert into pedido (cod_pedido, cod_cliente, data_pedido)<br />
            values (1,1,'2016/10/01'), (2,2,'2016/04/04'), (3,3,'2016/01/01');<br />
<br />
insert into pedido_produto (cod_pedido, cod_produto, quantidade)<br />
        values (1,1,2), (1,3,2), (2,2,4), (3,1,4), (3,2,1);<br />
<br />
<br />
---------------------<br />
select c.nome, sum (pp.quantidade * pr.preco) as total<br />
from cliente as c inner join pedido as p<br />
    on (c.cod_cliente = p.cod_cliente)<br />
     inner join pedido_produto as pp<br />
        on (p.cod_pedido = pp.cod_pedido)<br />
     inner join produto pr<br />
        on (pp.cod_produto = pr.cod_produto)<br />
group by c.nome  <br />
order by total desc<br />
<br />
select *<br />
from produto as p<br />
where p.cod_produto not in (select cod_produto from pedido_produto)<br />
<br />
<br />
select c.nome, sum (pp.quantidade * pr.preco) as total<br />
from cliente as c inner join pedido as p<br />
    on (c.cod_cliente = p.cod_cliente)<br />
     inner join pedido_produto as pp<br />
        on (p.cod_pedido = pp.cod_pedido)<br />
     inner join produto pr<br />
        on (pp.cod_produto = pr.cod_produto)<br />
group by c.nome  <br />
having sum (cast ((pp.quantidade * pr.preco)as numeric)) > (<br />
select  avg (cast ((pp.quantidade * pr.preco) as numeric) ) as total<br />
from pedido as p inner join pedido_produto as pp<br />
        on (p.cod_pedido = pp.cod_pedido)<br />
     inner join produto pr<br />
        on (pp.cod_produto = pr.cod_produto))<br />
<br />
<br />
<br />
<br />
delete <br />
from cliente<br />
where nome in (<br />
select c.nome<br />
from cliente as c inner join pedido as p<br />
    on (c.cod_cliente = p.cod_cliente)<br />
     inner join pedido_produto as pp<br />
        on (p.cod_pedido = pp.cod_pedido)<br />
     inner join produto pr<br />
        on (pp.cod_produto = pr.cod_produto)<br />
where extract(year from age(c.data_nascimento)) > 40<br />
group by c.nome <br />
having  cast (sum (pp.quantidade * pr.preco) as numeric)  > 50.0<br />
)<br />
<br />