Banco de Dados Exercício 4

drop table r1_nota_fiscal cascade constraints;
drop table r1_produto cascade constraints;
drop table r1_item_nf cascade constraints;
drop table r1_cliente cascade constraints;

create table r1_cliente(
cgc varchar2(14) not null,
razao varchar2(20) not null,
endereco varchar2(50),
cidade varchar2(20),
uf varchar2(2),
constraint pkcliente primary key (cgc));

create table r1_produto(
codigo varchar2(6) not null,
descricao varchar2(50) not null,
estoque integer,
preco number(8,2),
desconto number(8,2),
constraint pkproduto primary key (codigo));

create table r1_nota_fiscal(
serie varchar2(1) not null,
numero varchar2(10) not null,
cgc varchar2(14),
data date,
condicao varchar2(10),
constraint pknf primary key (serie,numero),
constraint fkcliente_nf foreign key (cgc) references r1_cliente(cgc));

create table r1_item_nf(
serie varchar2(1) not null,
numero varchar2(10) not null,
produto varchar2(6) not null,
preco number(8,2) not null,
quantidade integer,
constraint pkitem_nf primary key (serie,numero,produto),
constraint fknf_item foreign key (serie,numero) references r1_nota_fiscal(serie,numero),
constraint fkproduto_item foreign key (produto) references r1_produto(codigo));

insert into r1_cliente values ('11111111111111','BH Hardsoft','rua das Flores, 50','Belo Horizonte','MG');
insert into r1_cliente values ('22222222222222','Popular PC','rua da República, 51/201','São Paulo','SP');
insert into r1_cliente values ('33333333333333','Infostyle','rua da Candelária, 1250','Rio de Janeiro','RJ');
insert into r1_cliente values ('44444444444444','Amazonas Micro','rua Bela Vista, 25','Manaus','AM');
insert into r1_cliente values ('55555555555555','Riogrande Info','rua três Gaúchos, 200','Porto Alegre','RS');

insert into r1_nota_fiscal values ('C','100','11111111111111','20/09/2002','À vista');
insert into r1_nota_fiscal values ('C','101','22222222222222','27/09/2002','30 d');
insert into r1_nota_fiscal values ('C','102','33333333333333','28/09/2002','À vista');
insert into r1_nota_fiscal values ('C','103','44444444444444','29/09/2002','60 d');
insert into r1_nota_fiscal values ('C','104','55555555555555','29/09/2002','À vista');
insert into r1_nota_fiscal values ('C','105','22222222222222','01/10/2002','30 d');

insert into r1_produto values ('MIC001','Microcomputador 1 Ghz, 64 MB','10',1790.00,50.00);
insert into r1_produto values ('MIC002','Microcomputador 1,3 Ghz, 128MB','6',2100.00,100.00);
insert into r1_produto values ('IMP001','Impressora Epson C20sx','4',250.00,10.00);
insert into r1_produto values ('IMP002','Impressora Epson C60sx','5',360.00,20.00);
insert into r1_produto values ('IMP003','Impressora Lexmark Z53','3',399.00,0);
insert into r1_produto values ('MON001','AOC Spectrum 4vn (14")','6',300.00,25.00);
insert into r1_produto values ('MON002','Samsung Syncmaster 15"','4',499.00,30.00);
insert into r1_produto values ('EST001','Estabilizador','20',35.00,0);

insert into r1_item_nf values ('C','100','IMP001',240.00,'2');
insert into r1_item_nf values ('C','100','MIC002',2000.00,'1');
insert into r1_item_nf values ('C','101','IMP002',360.00,'3');
insert into r1_item_nf values ('C','101','MON001',300.00,'4');
insert into r1_item_nf values ('C','101','MON002',499.00,'1');
insert into r1_item_nf values ('C','101','EST001',35.00,'2');
insert into r1_item_nf values ('C','102','MIC001',1740.00,'1');
insert into r1_item_nf values ('C','102','IMP002',340.00,'1');
insert into r1_item_nf values ('C','102','MON001',275.00,'2');
insert into r1_item_nf values ('C','102','MON002',469.00,'1');
insert into r1_item_nf values ('C','103','MIC001',1790.00,'10');
insert into r1_item_nf values ('C','103','MIC002',2100.00,'5');
insert into r1_item_nf values ('C','103','IMP001',250.00,'6');
insert into r1_item_nf values ('C','103','IMP002',360.00,'2');
insert into r1_item_nf values ('C','103','MON001',300.00,'3');
insert into r1_item_nf values ('C','103','EST001',35.00,'20');
insert into r1_item_nf values ('C','104','EST001',35.00,'10');
insert into r1_item_nf values ('C','105','MIC001',1790.00,'2');
insert into r1_item_nf values ('C','105','MON001',300.00,'2');

commit;

Baseado no esquema acima, responda àsperguntas:

1. Quantas unidades de microcomputadores (produtos MIC001 e MIC002) foram vendidas nototal?

SELECT SUM(I.QUANTIDADE) TOTAL
FROM r1_item_nf I
WHERE I.r1_produtoO IN ('MIC001', 'MIC002');

2. Quanto o valor total dos descontos dados nas vendas do item MIC001?

SELECT SUM(P.DESCONTO * I.QUANTIDADE) TOTAL_DESCONTO
FROM r1_produto P, r1_item_nf I
WHERE P.CODIGO=I.r1_produtoAND P.CODIGO='MIC001';

3. Quais produtos venderam mais unidades em setembro?

SELECT item.produto, SUM(item.quantidade) as qtd
FROM r1_item_nf item
GROUP BY item.produto);

4. Quais produtos não tiveram venda em setembro?

SELECT p.descricao
FROM r1_item_nf item, r1_produto p, r7_soma
WHERE p.codigo = r7_soma.produto ANDr7_soma.qtd = (select max(qtd) from r7_soma)GROUP BY p.descricao;

5. Qual foi o valor total das vendas em setembro, excluindo microcomputadores?

SELECT SUM(I.PRECO * I.QUANTIDADE) T_VENDAS
FROM r1_item_nf I, r1_nota_fiscal N
WHERE I.NUMERO=N.NUMERO AND TO_CHAR(N.DATA,'MM') = '09' AND I.r1_produto NOT IN ('MIC001', 'MIC002');

6. Quais produtos têm preço maior que 300,00 e possuem mais de 4 unidades em estoque?

SELECT P.DESCRICAO, P.PRECO, P.ESTOQUE
FROM r1_produto P
WHERE P.PRECO > 300 AND P.ESTOQUE > 4;

7. Quantos produtos foram vendidos para fora do estado de Minas Gerais em setembro?

SELECT SUM(I.QUANTIDADE) QTD_PROD
FROM r1_item_nf I, r1_nota_fiscal N, r1_cliente C
WHERE I.NUMERO=N.NUMERO AND N.CGC=C.CGCAND TO_CHAR(N.DATA,'MM') = '09' AND C.UF <> 'MG';

8. Que cliente comprou mais unidades do produto MIC001?

SELECT C.RAZAO
FROM r1_cliente C, r1_item_nf I, r1_nota_fiscal N
WHERE I.NUMERO=N.NUMERO AND N.CGC=C.CGCAND I.r1_produto = 'MIC001'AND I.QUANTIDADE = (SELECT MAX(I.QUANTIDADE) FROM r1_item_nf I WHERE I.r1_produto = 'MIC001');

9. Que cliente comprou mais unidades do produto IMP002?

SELECT C.RAZAO
FROM r1_cliente C, r1_item_nf I, r1_nota_fiscal N
WHERE I.NUMERO=N.NUMEROAND N.CGC=C.CGC AND I.r1_produto = 'IMP002' AND I.QUANTIDADE = (SELECT MAX(I.QUANTIDADE) FROM r1_item_nf I WHERE I.r1_produto = 'IMP002');