/* 1 - Criar Banco de Dados*/
create database Exerciocio3
/* 2 - Acionar o Banco de Dados Criado*/
use Exerciocio3
/* 3 - Criar Tabela Cliente*/
create table cliente
(
cod_cli int not null,
nome_cli varchar(40) not null,
endereco varchar(40) null,
cidade varchar(20) null,
cep char(08) null,
uf char(02) null,
primary key (cod_cli));
/* 4 - Criar Tabela vendedor*/
create table vendedor
(
cod_vend int not null,
nome_vend varchar(40) not null,
sal_fixo money ,
faixa_comiss char(01) not null,
primary key (cod_vend));
/* 5 - Criar Tabela Produto */
create table produto
(
cod_prod int not null,
unid_prod char(03) not null,
desc_prod varchar(20) not null,
val_unit money not null,
primary key (cod_prod));
/* 6 - Criar tabela Pedido */
create table pedido
(
num_ped int not null,
prazo_entr int not null,
cd_cli int foreign key references cliente(cod_cli),
cd_vend int foreign key references vendedor(cod_vend),
primary key (num_ped));
/* 7 - Criar tabela Item Pedido*/
create table item_pedido
(
no_ped int foreign key references pedido(num_ped),
cd_prod int foreign key references produto(cod_prod),
qtd_ped float not null);
/* 8 - Inserir dados na tabela cliente */
insert into cliente (cod_cli,nome_cli,endereco,cidade,cep,uf)
values('1000','Supermercado Carrefur','Av. das Amelias','Rio de Janeiro','2000002','RJ')
insert into cliente (cod_cli,nome_cli,endereco,cidade,cep,uf)
values('2000','Supermercado Baratao','Rua Rolando Lero','Rio de Janeiro','2000001','RJ')
insert into cliente (cod_cli,nome_cli,endereco,cidade,cep,uf)
values('3000','Supermercado Arariboia','Rua Itaoca','Niteroi','2100000','RJ')
/* 9 - Inserir dados na tabela vendedor*/
insert into vendedor(cod_vend,nome_vend,sal_fixo,faixa_comiss)
values('11','Ana Cristina','1500','B')
insert into vendedor(cod_vend,nome_vend,sal_fixo,faixa_comiss)
values('13','Paulo Alberto','2100','A')
insert into vendedor(cod_vend,nome_vend,sal_fixo,faixa_comiss)
values('15','Cassia Andrade','900','C')
/* 10 - Inserir dados na tabela produto */
insert into produto(cod_prod,unid_prod,desc_prod,val_unit)
values('100','kg','Chapa de Aco','2.5')
insert into produto(cod_prod,unid_prod,desc_prod,val_unit)
values('200','kg','Cimento','4.5')
insert into produto(cod_prod,unid_prod,desc_prod,val_unit)
values('300','kg','Parafuso 3.0x10.5mm','2')
insert into produto(cod_prod,unid_prod,desc_prod,val_unit)
values('400','m','Fio Plastico','1')
/* 11 - Inserir dados na tabela pedido */
insert into pedido(num_ped,prazo_entr,cd_cli,cd_vend)
values('1111','10','1000','11')
insert into pedido(num_ped,prazo_entr,cd_cli,cd_vend)
values('1112','5','1000','11')
insert into pedido(num_ped,prazo_entr,cd_cli,cd_vend)
values('1113','30','1000','15')
/* 12 - Inserir dados na tabela Item Pedido */
insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1111','100','100')
insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1111','200','100')
insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1111','300','200')
insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1112','400','100')
insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1112','300','200')
insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1113','100','300')
/* 13 - Selecionar Tabela Cliente */
select * from cliente
/* 14 - Selecionar Tabela Vendedor */
select * from vendedor
/* 15 - Selecionar Tabela Produto */
select * from produto
/* 16 - Selcionar Tabela Pedido */
select * from pedido
/* 17 - Selecionar Tabela Item pedido */
select * from item_pedido
/* 18 - Selecionar na Tabela produto , descricao, unidade de medida e valor unitario */
select desc_prod, unid_prod,val_unit from produto
/* 19 - Selecionar na Tabela cliente, nome, cidade e uf dos clientes */
select nome_cli, cidade, uf from cliente
/* 20 - Selecionar na tabela pedido, numero do pedido, cogio do produto, quantidade pedido.*/
select no_ped, cd_prod, qtd_ped from item_pedido
/* 21 - Selecionar da tabela item pedido, numero do pedido, codigo doproduto e quantidade pedido = 100. */
select no_ped, cd_prod, qtd_ped from item_pedido
where qtd_ped = 100
/* 22 - Selecionar da tabela cliente os de Niteroi */
select * from cliente
where cidade = 'niteroi'
/* 23 - Selecionar produtos de unidade de medida kg e com valor de unidade maior que 2 */
select * from produto
where unid_prod = 'kg' and val_unit > 2
/* 24 - Selecionar cliente do Rio de Janeiro com cep entre 20000000 a 21000000. */
select * from cliente
where cidade = 'Rio de Janeiro' or cep between 20000000 and 21000000
/* 25 - Selecionar codigo do produto, descricao dos produtos com valor unitario entre 0,10 a 3. */
select cod_prod, desc_prod, val_unit from produto
where val_unit between 0.10 and 3
/* 26 - Selecionar codigo vendedor, nome do vendedor que comece coma a letra A.*/
select cod_vend, nome_vend from vendedor
where nome_vend like 'A%'
/* 27 - Selecionar codigo vendedor, nome do vendedor que não comece coma a letra A.*/
select cod_vend, nome_vend from vendedor
where nome_vend not like 'A%'
/* 28 - Substituir o nome do vendedor que tem o codigo 13, para Ana Cristina */
update vendedor set nome_vend = 'Ana Cristina'
where cod_vend = 13;
/* 29 - Selecionar codigo do vendedor e nome que estao na faixa de comicao A e B*/
select cod_vend, nome_vend from vendedor
where faixa_comiss in ('a','b')
/* 30 - Selecionar nome do cliente que nao tem endereco cadastrado*/
select nome_cli from cliente
where endereco is null
/* 31 - Selecionar nome e endereco dos Clentes que tem endereco cadastrado */
select nome_cli, endereco from cliente
where endereco is not null
/* 32 - Selecionar nome do Cliente, cidade e uf , ordenar lista cidade e uf decrescente */
select nome_cli, cidade,uf from cliente
order by uf desc, cidade desc
/* 33 - Substituir o cep da Av. das Amelias para 20000001 */
update cliente set cep = '20000001'
where endereco = 'Av. das Amelias';
/* 33 - Substituir no item pedido a quantidade para 200 no produto de codigo 300 */
update item_pedido set qtd_ped = '200'
where cd_prod = '300';
/* 34 - Recupere a lista de pedidos (numero do pedido) com o codigo e o nome do cliente */
select num_ped,cod_cli,nome_cli
from pedido,cliente
where pedido.cd_cli=cliente.cod_cli
/* 35 - Recupere os cliente (codigo e nome) que tem pedido com prazo de entrega menor que 15 dias.*/
select cod_cli,nome_cli,prazo_entr
from cliente, pedido
where cliente.cod_cli = pedido.cd_cli
and prazo_entr < 15;
/* 36 - Recupere os cliente (codigo e nome) que tem pedido com prazo de entrega menor que 15 dias e que sao
do estado de Sao Paulo(SP). Apresente o codigo e nome do cliente, prazo de entrega,cidade e UF.*/
select cod_cli,nome_cli,pedido.prazo_entr,cliente.cidade,cliente.uf
from cliente,pedido
where cliente.cod_cli = pedido.cd_cli
and prazo_entr < 15 and uf= 'SP';
/* 37 - Selecione os cliente(codigo e nome) de SP com prazo de entrega inferior a 15 dias que copraram cimento. */
select cod_cli,nome_cli,cliente.uf,pedido.prazo_entr,produto.desc_prod
from cliente,pedido,produto
where cliente.cod_cli = pedido.cd_cli and produto.desc_prod = 'Cimento'
and prazo_entr < 15 and cliente.uf = 'SP'
/* 38 - Selecionar nome dos vendedores que registraram pedido de parafuso com mais de 100 kg. */
select nome_vend, cod_vend
from vendedor V, pedido P, item_pedido IP, produto PR
where V.cod_vend = P.cd_vend
and PR.cod_prod = IP.cd_prod
and P.num_ped = IP.no_ped
and PR.desc_prod = 'Parafuso 3.0x10.5mm'
and IP.qtd_ped > 100
/* 39 - Recuperar a quantidade de clientes que fizeram pedido com vendedor " Ana Cristina".*/
select COUNT(distinct nome_cli) as 'Qtde Pedidio'
from cliente C, pedido P, vendedor V
where C.cod_cli = P.cd_cli
and P.cd_vend = V.cod_vend
and V.nome_vend = 'Ana Cristina'
/* 40 - Quais vendedores ganham salario fixo abaixo da media.*/
select nome_vend
from vendedor
where sal_fixo < (select AVG(sal_fixo) from vendedor);
/* 41 - Qual a media do salario fixo.*/
select AVG(sal_fixo)
from vendedor
/* 42 - Quais vendedores ganham salario fixo acima da media. */
select nome_vend, sal_fixo
from vendedor
where vendedor.sal_fixo > (select AVG(sal_fixo) from vendedor);
/* 43 - Nome do cliente que fez mais de um pedido.*/
select nome_cli
from cliente C
where exists (select COUNT(*) from pedido where cd_cli = C.cod_cli having COUNT(*)>1);
/* 44 - Nome do cliente que fez mais de um pedido.*/
select nome_cli
from cliente C
where exists (select COUNT(*)
from pedido P
where P.cd_cli = C.cod_cli
having COUNT(*)>=2)
Nenhum comentário:
Postar um comentário