ARA7129 UNIDADE 5 SQL A Solução dos Exercícios

Prévia do material em texto

Exercício 5-1-1
drop table venda;
drop table nota_produto;
drop table nota;
drop table cliente;
drop table produto;
CREATE TABLE cliente (
cod_cliente integer PRIMARY KEY,
nome varchar(50),
cidade varchar(50),
uf varchar(2),
endereco varchar(50)
)
CREATE TABLE nota (
num_nota integer PRIMARY KEY,
data timestamp,
num_parcela integer,
cod_cliente integer NOT NULL,
FOREIGN KEY(cod_cliente) REFERENCES cliente (cod_cliente)
)
CREATE TABLE produto (
cod_produto integer PRIMARY KEY,
qtde_estoque integer,
descricao varchar(50),
valor numeric
)
CREATE TABLE venda (
num_nota integer,
data_vencto date,
data_pagto date,
multa_juro numeric,
valor numeric,
desconto numeric,
PRIMARY KEY(num_nota,data_vencto),
FOREIGN KEY(num_nota) REFERENCES nota (num_nota)
)
CREATE TABLE nota_produto (
num_nota integer,
cod_produto integer,
valor numeric,
qtde integer,
PRIMARY KEY(num_nota,cod_produto),
FOREIGN KEY(num_nota) REFERENCES nota (num_nota),
FOREIGN KEY(cod_produto) REFERENCES produto (cod_produto)
)
Exercício 5-1-2
delete from venda;
delete from nota_produto;
delete from nota;
delete from produto;
delete from cliente;
commit;
insert into cliente values (1, 'Cliente 1', 'Araranguá', 'SC', 'Endereco 1');
insert into cliente values (2, 'Cliente 2', 'Arroio do Silva', 'SC', 'Endereco 2');
insert into produto values (1, 10, 'Produto 1', 12.00);
insert into produto values (2, 21, 'Produto 2', 33.00);
insert into nota values (1, '2015-09-02', 0, 1);
insert into nota_produto values (1, 1, 12.00, 3);
insert into nota_produto values (1, 2, 33.00, 8);
insert into venda values (1, '2015-09-02', '2015-09-02', null, 300, null);
insert into nota values (2, '2015-10-02', 2, 1);
insert into nota_produto values (2, 1, 12.00, 3);
insert into venda values (2, '2015-11-02', null, null, 18, null);
insert into venda values (2, '2015-12-03', null, null, 18, null);
insert into nota values (3, '2015-10-02', 2, 2);
insert into nota_produto values (3, 1, 12.00, 1);
insert into nota_produto values (3, 2, 33.00, 1);
insert into venda values (3, '2015-11-02', null, null, 18, null);
insert into venda values (3, '2015-12-03', null, null, 17, null);
commit;
Exercício 5-1-3
a)
select cliente.cod_cliente, extract(month from data_vencto) as mes, 
 extract(year from data_vencto) as ano, sum(valor) as soma
 from venda, nota, cliente
 where venda.num_nota = nota.num_nota and
 nota.cod_cliente = cliente.cod_cliente
group by cliente.cod_cliente , mes, ano
order by ano, mes, cliente.cod_cliente;
b)
select uf, sum(valor) as soma
 from venda, nota, cliente
 where venda.num_nota = nota.num_nota and
 nota.cod_cliente = cliente.cod_cliente 
group by uf;
c) 
select produto.* from produto,
 (select max(qtde_total) as qtde_total
 from (select np.cod_produto, sum(np.qtde) as qtde_total
 from nota_produto np, nota nt
 where np.num_nota = nt.num_nota and
 nt.data between '2015-10-01' and '2015-12-31'
 group by np.cod_produto
 )as prod_sum) as prod_max,
 (select np.cod_produto, sum(np.qtde) as qtde_total
 from nota_produto np, nota nt
 where np.num_nota = nt.num_nota and
 nt.data between '2015-10-01' and '2015-12-31'
 group by np.cod_produto
 ) as prod_sum
where produto.cod_produto = prod_sum.cod_produto and
 prod_sum.qtde_total = prod_max.qtde_total
-- Solução possível, mas somente retorna um dos produtos que tenham vendido mais.
select produto.* from produto,
 (select np.cod_produto, sum(np.qtde) as qtde_total
 from nota_produto np, nota nt
 where np.num_nota = nt.num_nota and
 nt.data between '2015-10-01' and '2015-12-31'
 group by np.cod_produto 
 order by qtde_total desc limit 1
 ) as prod_max
where produto.cod_produto = prod_max.cod_produto
d)
select cliente.cod_cliente, cliente.nome, sum(valor+coalesce(multa_juro,0)-coalesce(desconto,0)) as total
 from venda, nota, cliente
 where venda.num_nota = nota.num_nota and
 nota.cod_cliente = cliente.cod_cliente and
 data_pagto between '2015-08-01' and '2015-10-05'
group by cliente.cod_cliente, cliente.nome;
e)
select cliente.cod_cliente, cliente.nome, sum(valor) as total
 from venda, nota, cliente
 where venda.num_nota = nota.num_nota and
 nota.cod_cliente = cliente.cod_cliente and
 data_pagto is null and
 data_vencto between '2015-10-05' and '2016-01-05'
group by cliente.cod_cliente, cliente.nome;