create database db_vendas_2016
use db_vendas_2016
create table CLIENTE (
cod_cli int primary key,
nome_cli varchar(80) not null,
endereco varchar(120),
cidade varchar(50),
cep char(8),
uf char(2));
create table VENDEDOR (
cod_vend int primary key,
nome_vend varchar(80) not null,
sal_fixo money,
faixa_comiss char(1));
create table PEDIDO (
num_ped int primary key,
prazo_entr int,
cod_cli int foreign key references cliente(cod_cli),
cod_vend int foreign key references vendedor(cod_vend));
create table PRODUTO (
cod_prod int primary key,
unid_prod varchar(10) not null,
desc_prod varchar(120),
val_unit money not null);
select * from PRODUTO
create table ITEM_PEDIDO (
num_ped int foreign key references pedido(num_ped),
cod_prod int foreign key references produto(cod_prod),
qtd_ped int
primary key (num_ped, cod_prod));
insert into cliente values
(1000, 'Supermercado Carrefour', 'Av. das Americas', 'rio de janeiro', '20000001', 'rj'),
(2000, 'Supermercado Baratao', 'Rua 7 de setembro', 'rio de janeiro', '20000002', 'rj'),
(3000, 'Supermercado Arariboia', 'Rua Itaoca', 'niteroi', '20000003', 'rj'),
(4000, 'Mercado São João', 'Cidade Univers.', 'niteroi', '20000004', 'rj'),
(5000, 'CSN', 'Rua das Nações', 'volta redonda', '20000005', 'rj'),
(6000, 'Pegeout', 'Rodovia Pres. Dutra', 'resende', '20000006', 'rj'),
(7000, 'Lojas Pague Pouco', 'Rua Tuiuti', 'sao paulo', '11000001', 'sp'),
(8000, 'Ford Caminhoes', 'Rua Henry Ford', 'sao paulo', '11000002', 'sp'),
(9000, 'Célula Celulose', 'Rua Gen. Arouca', 'guaiba', '30000001', 'rs'),
(10000, 'Elevadores RioSul', 'Rua Planejada', 'guaiba', '30000001', 'rs');
insert into produto (cod_prod, unid_prod, desc_prod, val_unit) values
(100, 'kg', 'Chapa de Aco', 2.5),
(200, 'kg', 'Cimento', 4.5),
(300, 'kg', 'parafuso 3.0X10.5 mm', 2),
(400, 'm', 'Fio plastico', 2),
(500, 'l', 'Solvente PRW', 5);
insert into vendedor values
(11, 'Paulo Alberto', 1500, 'b'),
(12, 'Ana Cristina', 2100, 'a'),
(13, 'Cassia Andrade', 900, 'c'),
(14, 'João Roberto', 2500, 'a'),
(15, 'Maria Paula', 900, 'c');
insert into pedido values
(1111, 10, 1000, 11),
(1112, 5, 1000, 11),
(1113, 30, 1000, 15),
(2111, 15, 3000, 14),
(2112, 18, 3000, 15),
(2113, 3, 3000, 12),
(3111, 13, 4000, 12),
(3112, 7, 4000, 11),
(4111, 7, 6000, 11),
(4112, 7, 6000, 14),
(5111, 10, 8000, 14),
(6111, 30, 9000, 14),
(6112, 60, 9000, 12),
(7111, 20, 10000, 15);
insert into item_pedido values
(1111, 100, 100),
(1111, 200, 100),
(1111, 300, 200),
(1112, 400, 100),
(1112, 500, 1000),
(1113, 100, 300),
(2111, 100, 500),
(2111, 500, 400),
(2112, 200, 100),
(2112, 300, 200),
(2113, 500, 500),
(3111, 400, 300),
(3112, 100, 400),
(3112, 200, 600),
(4111, 300, 700),
(4112, 500, 1000),
(4112, 200, 500),
(5111, 200, 100),
(5111, 300, 500),
(6111, 400, 100),
(6112, 300, 400),
(6112, 400, 200),
(7111, 100, 500);
USE db_vendas_2016
CREATE VIEW vw_ProdutosCaros
as
select
cod_prod ,
unid_prod ,
desc_prod ,
val_unit
from PRODUTO
where val_unit > 100;
select* from vw_ProdutosCaros
select *from PRODUTO
where unid_prod = 'm';
create view vw_ProdutosPorMetro as
select*
from PRODUTO
where unid_prod = 'm';
select* from vw_ProdutosPorMetro
select
cod_prod as codigo,
unid_prod as unidade,
desc_prod as descricao,
val_unit as valor
from PRODUTO
where unid_prod = 'm';
create view vw_clientesNiteroi as
select cod_cli as codigo,
nome_cli as cliente,
endereco,
cidade,
cep
from cliente
where cidade = 'niteroi'
select* from vw_clientesNiteroi
select count(*) as [Qtde Cliente Niteroi]
from vw_clientesNiteroi;
select * from PEDIDO, CLIENTE
create view vw_pedidosniteroi as
select num_ped, prazo_entr, p.cod_cli
from vw_clientesNiteroi v, pedido p
where v.codigo = p.cod_cli;
select * from vw_pedidosniteroi
select cod_cli, count(*) as [Qtde de Pedidos Niteroi]
from vw_pedidosniteroi
group by cod_cli;
create view vw_PedidosNiteroiComCliente as
select num_ped, prazo_entr, cod_cli, cidade, cep
from vw_pedidosniteroi p, vw_clientesNiteroi c
where p.cod_cli = c.codigo;
select * from vw_PedidosNiteroiComCliente
create view vw_PedidoNietroiComIntens as
select v.num_ped, prazo_entr, cod_prod, qtd_ped
from vw_pedidosniteroi v, ITEM_PEDIDO i
where v.num_ped = i.num_ped
select * from vw_PedidoNietroiComIntens
select prazo_entr, sum(qtd_ped) as Total_Itens
from vw_PedidoNietroiComIntens
group by prazo_entr;
CREATE VIEW vw_produtosPorMetro AS
SELECT cod_prod,
desc_prod,
unid_prod,
val_unit
From PRODUTO
WHERE unid_prod = 'm';
SELECT * FROM vw_produtosPorMetro;
CREATE VIEW vw_ProdutosCaros AS
SELECT cod_prod,
desc_prod,
unid_prod,
val_unit
FROM PRODUTO
WHERE val_unit > 3;
CREATE VIEW vw_Clientesniteroi AS
SELECT cod_cli,
nome_Cli,
endereco,
cep
FROM CLIENTE
WHERE cidade = 'Niteroi';
SELECT COUNT(*) AS [Quantidade Clientes Niteroi]
FROM vw_ClientesNiteroi;
CREATE VIEW vw_pedidosniteroi as
select num_ped, prazo_entr, p.cod_cli
from vw_Clientesniteroi v, PEDIDO p
where v.cod_cli = p.cod_cli;
drop view vw_pedidosniteroi;
CREATE VIEW vw_pedidosniteroi as
select num_ped, prazo_entr, p.cod_cli
from vw_Clientesniteroi v, PEDIDO p
where v.cod_cli = p.cod_cli;
select * from vw_pedidosniteroi;
use db_vendas_2016
create view vw_produtoNiteroiComItens as
select v.num_ped, prazo_entr, cod_prod, qtd_ped
from vw_pedidosniteroi v, ITEM_PEDIDO I
where v.num_ped = I.num_ped
select prazo_entr, sum(qtd_ped) as total_itens
from vw_produtoNiteroiComItens
group by prazo_entr;
select * from vw_produtoNiteroiComItens
select * from vw_ProdutosCaros
--04 - Selecione os clientes(nome, cidade, cep) da cidade de são paulo ou que tem cep entre 20.000.005 e 20.000.010
select nome_cli, cidade, cep
from CLIENTE
where cidade = 'São Paulo' or
cep between 20000005 and 20000010;
--05 - Liste os vendedores(código e nome) com faixa de comissão a ou b. Use IN.
select cod_vend, nome_vend
from VENDEDOR
where faixa_comiss in ('a','b');
--06 - selecione os clientes com endereço não preenchido.
select *
from CLIENTE
where endereco is null;
--07 - Atualize o salário dos vendedores da faixa 'C' com aumento de de 15% e mais 120,00 de bonus.
select cod_vend,
nome_vend
from VENDEDOR
where faixa_comiss = 'C';
--08 - apresente os vendedores (código, nome, faixa, salário) que tem o menor e o maior salario
select *
from VENDEDOR
where sal_fixo IN ((select MIN(sal_fixo) from vendedor),
(select max(sal_fixo) from vendedor))
order by sal_fixo;
--09 - Recupere a media dos salarios dos vendedores
select avg (sal_fixo) as media from vendedor;
--10 - crie uma view vw_vendedorComAltoSalario com as informações dos vendedores que tem salário maior que a média.
create view vw_vendedorComAltoSalario as --errro
select *
from VENDEDOR
where sal_fixo > (select avg(sal_fixo) from vendedor);
select * from vw_vendedorComAltoSalario;
--11 - Quais clientes que tem pedidos maior que 10 dias e que são dos estados de são paulo e Rio de janeiro(nome,UF,num_ped e prazo)
select nome_cli, uf, num_ped, prazo_entr
from CLIENTE, PEDIDO
where prazo_entr > 10
and uf='sp' or uf='rj';
--12 - Apresentar os clientes com os pedidos ordenados por prazo de entrega(código, nome, num_ped e prazo)
--13 - Apresentar os vendedores que registraram pedidos com prazo maior de 10 dias e que tem salários maior que a média dos salários.
--trigger
--exercicio 1
create TRIGGER tr_UFinvalida
on CLIENTE
INSTEAD of insert
as begin
declare @UF VARCHAR(2);
select @UF=uf from inserted;
if(@UF = 'RJ' or @UF = 'ES')
print('Operação cancelada')
else
begin
insert into CLIENTE (cod_cli, nome_cli, endereco,cidade,cep,uf)
(select*from inserted);
Print('Sucesso')
end;
end ;
--trigger
--exercicio 2
create table tlogproduto(
codigo_produto int,
descricao_produto varchar(120),
unidade char(2),
dataalteracao datetime)
CREATE TRIGGER tg_naoaltera
ON produto
instead of INSERT
AS begin
-- variável utilizada no trigger
declare @cod_prod varchar(50)
declare @desc_prod varchar(50)
declare @unid_prod char(2)
-- define o valor da variável @produto
set @cod_prod = (select cod_prod from inserted)
set @desc_prod = (select desc_prod from inserted)
set @unid_prod = (select unid_prod from inserted)
insert into tlogproduto (codigo_produto, descricao_produto, unidade, dataalteracao)
values (@cod_prod, @desc_prod, @unid_prod, GETDATE())
print 'AS INCLUSÕES DE PRODUTOS ESTÃO SUSPENSAS!'
END
insert into PRODUTO (cod_prod, unid_prod, desc_prod, val_unit)
values (1234,'kg','Banana',10)
insert into PRODUTO (cod_prod, unid_prod, desc_prod, val_unit)
values (0022,'kg','arroz',10)
select * from produto
select * from tlogproduto
--trigger
--exercicio 3
create table tlogitem(
cod_usuario varchar(20),
data_atualizacao datetime,
num_pedido int,
cod_produto int,
tipo_atualizacao varchar(15)
)
CREATE TRIGGER tg_logitem
ON item_pedido
after INSERT, DELETE
AS begin
-- variável utilizada no trigger
declare @num_pedido int
declare @cod_prod int
declare @tipo_atualizacao varchar(15)
if((select count(*)from inserted)>0)
set @tipo_atualizacao = 'inclusão'
else if ((select count(*) from deleted)>0)
set @tipo_atualizacao = 'remoção'
set @num_pedido = (select num_ped from inserted)
set @cod_prod = (select cod_prod from inserted)
insert into tlogitem (cod_usuario, data_atualizacao, num_pedido, cod_produto, tipo_atualizacao)
values (CURRENT_USER, getdate(), @num_pedido, @cod_prod,@tipo_atualizacao)
END
insert into ITEM_PEDIDO (num_ped, cod_prod, qtd_ped)
values(1114, 100, 555)
delete from ITEM_PEDIDO
where num_ped = 1114;
select * from tlogitem
--trigger
--exercicio 4
create table tlogvendedor(
usuario char(30),
dataalteracao datetime,
cod_vendedor int,
salario_fixo money,
faixa_comissao char(1)
)
create trigger tr_atualiuzarvendedor
on vendedor
for update as
begin
insert into tlogvendedor (usuario, dataalteracao, cod_vendedor,
salario_fixo, faixa_comissao)
values (CURRENT_USER, GETDATE(),
(select cod_vend from inserted),
(select sal_fixo from deleted),
(select faixa_comiss from deleted));
end;
select * from VENDEDOR
select * from tlogvendedor
select *from CLIENTE
create procedure sp_exclui_clientes
@cod_cliente int,
@qtde_pedido int OUTPUT AS
Begin
select @qtde_pedido = count(*)
from PEDIDO where cod_cli = @cod_cliente;
delete from ITEM_PEDIDO
where num_ped in
(select num_ped from PEDIDO where cod_cli = @cod_cliente);
delete from PEDIDO
where cod_cli = @cod_cliente;
delete from CLIENTE
where cod_cli = @cod_cliente;
END
drop procedure sp_exclui_clientes
select * from CLIENTE
declare @cod_cliente int
declare @qtde_pedido int
declare @qt_pedido int
execute sp_exclui_clientes @cod_cliente = 6000, @qtde_pedido =
@qt_ped OUTPUT
print 'qtde_pedido'
print @qt_pedido
create PROCEDURE SP_ATUALIZA_SALARIO
@codigo int
as
begin
if(@codigo = 2)
update VENDEDOR set sal_fixo = sal_fixo * 1.2
where FAIXA_COMISS = 'B'
else
if(@codigo = 3)
update VENDEDOR set sal_fixo = sal_fixo * 1.3
where faixA_comiss = 'B'
else
if(@codigo=4)
update VENDEDOR set sal_fixo = sal_fixo * 1.4
where faixa_comiss = 'B'
end;
exec SP_ATUALIZA_SALARIO 3;
select *from VENDEDOR
where faixa_comiss = 'b'
create PROCEDURE sp_inserir_pedido
@num_pedido int,
@cod_cliente int,
@cod_vendedor int
As
Begin
declare @datacompra datetime
set @datacompra = getdate()
declare @dataentrega datetime
set @dataentrega = @datacompra + 14
if DATEPART(DW, @dataentrega)= 1
set @dataentrega = @dataentrega +1
insert into PEDIDO(num_ped,prazo_entr,data_entrega,cod_cli, cod_vend)
values(@num_pedido, 14,@datacompra,@cod_cliente,@cod_vendedor)
end
exec sp_inserir_pedido 1010,1000,11
drop PROCEDURE sp_inserir_pedido
select *from PEDIDO
alter table PEDIDO add data_entrega datetime
select * from PRODUTO
alter table PRODUTO add qtde_estoque int;
update PRODUTO
SET qtde_estoque = 200
/* Criar Nome Procedure */
create PROCEDURE sp_desconto(@qtde_estoque int output)
as
begin
/*variaveis locais e Condições if, While*/
set @qtde_estoque = (select count(cod_prod)
from PRODUTO
where @qtde_estoque >= 100)
update PRODUTO set val_unit = val_unit * 0.9
where qtde_estoque between 100 and 200
update PRODUTO set val_unit = val_unit * 0.8
where qtde_estoque > 200
/*final End*/
end;
/*executar Procedure*/
declare @qtde int
exec sp_desconto @qtde output
/* Excluir Procedure */
drop PROCEDURE sp_desconto
select * from PRODUTO
alter table ITEM_PEDIDO add valor_item money
select * from ITEM_PEDIDO
/* Cursor permite percorrer o resultado de uma consulta linha a linha
1- Declare nome_do_cursor Cursor
for consulta
2- OPEN nome_do_cursor
3- FETCH NEXT FROM nome_do_cursor int @codigo
4- CLOSE nome_do_cursor
5- DEALLOCATE nome_do_cursor*/
create procedure SP_INSERIRITEMPEDIDO(
@num_ped int,
@cod_prod int,
@quantidade int)
begin
declare @estoque int;
if (@quantidade <= 0)
print ('Quantidade Invalida')
else
insert into ITEM_PEDIDO (num_ped, cod_prod, qtd_ped,valor_item)
values (@num_ped
@cod_prod
@quantidade
(select @quantidade * val_unit
from PRODUTO
where cod_prod = @cod_prod));
update PRODUTO
set @qtde_estoque = @qtde_estoque - @quantidade
where cod_prod = @cod_prod;
/*1. Escreva o comando SQL para criação da stored procedure sp_desconto,
considerando que os produtos que têm estoque entre 100 e 200 devem receber um desconto de 10%
e aqueles que tem estoque superior a 200 devem receber um desconto de 20%.
A stored procedure deve, então, atualizar o valor do desconto para 10 ou 20 dependendo da quantidade em estoque.
A stored procedure deve retornar a quantidade de registros atualizados.*/
/* alteração da tabela produto para incluir o campo qtde_estoque */
alter table produto
add qtde_estoque int
/* remoção da procedure */
drop procedure sp_desconto
/* criação da procedure */
create procedure sp_desconto
(@qtde_atualiza int output) as
begin
set @qtde_atualiza = (select count (cod_prod)
from produto
where qtde_estoque >= 100)
update produto
set val_unit = val_unit * 0.9
where qtde_estoque between 100 and 200;
update produto
set val_unit = val_unit * 0.8
where qtde_estoque > 200;
end
/* execução da procedure */
declare @qtde int
exec sp_desconto @qtde output
print @qtde
--2. Altere a tabela item_pedido acrescentando o novo atributo valor_item do tipo Money.
-- Alteração da tabela item_pedido
alter table item_pedido
add valor_item money
--Atualize os itens de pedidos cadastrados: o valor do item deve receber o resultado da quantidade multiplicada pelo valor unitário do produto.
-- Atualização de valor_item para os itens de pedido existentes na tabela ITEM_PEDIDO.
-- Note o uso do CURSOR
-- Removendo a procedure
drop procedure sp_atualizavaloritem
-- Criando a procedure
create procedure sp_atualizavaloritem as
begin
-- declarando variáveis locais
declare @cod_produto int
-- declarando o CURSOR cursor_itens
DECLARE cursor_itens CURSOR
LOCAL
FOR SELECT cod_prod FROM item_pedido
-- abrindo o CURSOR cursor_itens para leitura
OPEN cursor_itens
-- Lendo a primeira linha
FETCH NEXT FROM cursor_itens INTO @cod_produto
-- Percorrendo linhas do cursor (enquanto existe linhas)
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE item_pedido
set valor_item = qtd_ped * (select val_unit
from produto
where cod_prod = @cod_produto)
WHERE CURRENT OF cursor_itens
-- Lendo a próxima linha do cursor
FETCH NEXT FROM cursor_itens INTO @cod_produto
END
-- Fechando Cursor para leitura
CLOSE cursor_itens
-- Desalocando o cursor
DEALLOCATE cursor_itens
END
-- Executando a procedure
EXEC sp_atualizavaloritem
/*3. Crie a stored procedure Proc_InsereItensPedido para inclusão de itens de pedido. Considere:
Parâmetros de entrada: número do pedido, código do produto e quantidade.
Se a quantidade fornecida for menor ou igual a zero então informar ‘Quantidade inválida. Pedido não registrado’ e sair da procedure.
Se o produto fornecido não estiver cadastrado então emitir mensagem de erro e sair da procedure.
Se o produto fornecido não estiver cadastrado então emitir mensagem de erro e sair da procedure.
Se o valor_unitario ou qtde_estoque do produto estiverem zerados, então emitir a mensagem ‘Produto incompleto. Pedido não registrado’ e sair da procedure.
Se o estoque do produto for menor que a quantidade fornecida então emitir a mensagem ‘Estoque insuficiente. Pedido não registrado’ e sair da procedure.
Incluir o item do pedido com número do pedido, código do produto e quantidade fornecidos. O valor_item deve ser calculado (quantidade fornecida X valor unitário do produto). Se ocorrer algum erro (@@error <> 0) emitir a mensagem ‘Erro ao incluir o item do pedido’ e retroceder a operação.
Atualizar o estoque do produto (qtde_estoque = qtde_estoque – quantidade fornecida). Se ocorrer algum erro (@@error <> 0) emitir a mensagem ‘Erro ao incluir o item do pedido’ e retroceder a operação.
Emitir a mensagem ‘Pedido registrado e estoque atualizado com sucesso’.
Se o estoque atualizado do produto for zero então emitir a mensagem ‘Produto esgotado’.*/
--- Proc_InsereItensPedido
-- Removendo a procedure
DROP PROCEDURE PROC_INSEREITEMPEDIDO
-- Criando a procedure
CREATE PROCEDURE PROC_INSEREITEMPEDIDO (
@NUM_PEDIDO INT,
@COD_PRODUTO INT,
@QUANTIDADE INT) AS
BEGIN
-- Declaração de variáveis locais
DECLARE @estoque int;
-- Verificando se a quantidade fornecida é válida
IF (@QUANTIDADE <= 0)
PRINT('QUANTIDADE INVALIDA. PEDIDO NÃO REGISTRADO');
ELSE
BEGIN
-- Verificando se o produto fornecido está cadastrado
IF ((SELECT COUNT(COD_PROD)
FROM PRODUTO
WHERE COD_PROD = @COD_PRODUTO) <= 0)
PRINT ('PRODUTO NÃO CADASTRADO')
ELSE
BEGIN
-- Verificando se há estoque suficiente do produto para atender o pedido.
SET @estoque = (SELECT QTDE_ESTOQUE
FROM PRODUTO
WHERE COD_PROD = @COD_PRODUTO)
IF (@QUANTIDADE > @estoque)
PRINT ('ESTOQUE INSUFICIENTE')
ELSE
BEGIN
-- Incluindo o novo registro em ITEM_PEDIDO
INSERT INTO ITEM_PEDIDO
(NUM_PED, COD_PROD, QTD_PED, VALOR_ITEM)
VALUES (@NUM_PEDIDO, @COD_PRODUTO,
@QUANTIDADE,
(SELECT @QUANTIDADE * val_unit
FROM PRODUTO WHERE COD_PROD = @COD_PRODUTO));
-- Atualizando o estoque em PRODUTO
UPDATE PRODUTO
SET QTDE_ESTOQUE = QTDE_ESTOQUE - @QUANTIDADE
WHERE COD_PROD = @COD_PRODUTO;
-- Verificando se o estoque está esgotado
IF ((SELECT QTDE_ESTOQUE
FROM PRODUTO
WHERE COD_PROD = @COD_PRODUTO) = 0)
PRINT ('PRODUTO ESGOTADO')
END
END
END
END
select * from ITEM_PEDIDO
create function ValorPedido (@num_ped int)
returns money
as
begin
declare @total money;
select @total = sum(valor_item) from ITEM_PEDIDO
where num_ped = @num_ped;
return @total;
end
select dbo.ValorPedido (1111);
create function PrimeiroNome (@nomeCompleto varchar(80))
returns varchar(80)
as
begin
declare @primeiroNome varchar(30) = ' ',
@achou bit = 0, /*false*/
@contador numeric(2) =1,
@caractere char(1);
while(@achou = 0 )
begin
set @caractere = SUBSTRING(@nomeCompleto, @contador, 1);
set @primeiroNome = CONCAT(@primeiroNome, @caractere);
if (@caractere = ' ') set @achou = 1; /*True*/
else set @contador = @contador + 1;
end
return @primeiroNome
end
select dbo.PrimeiroNome (nome_cli) from CLIENTE;
create function FormataCEP (@cep varchar(11))
returns varchar(14) as
begin
declare @i int ;
declare @formatado varchar(10);
set @formatado = "";
while (@i <= datalength(@cep) and (datalength (@formatado) <= 14))
if (substring(@cep,@i,1) in ('0','1','2','3','4','5','6','7','8','9'))
BEGIN
set @formatado = @formatado + substring (@cep,@i,1);
if (@i = 3) or (@i = 7) set @formatado = @formatado + '.';
else
if (@i = 11) set @formatado = @formatado + '-';
end
set @i = @i +1
if datalength(@formatado) < 14
set @formatado = 'erro';
return (@formatado)
end
ALTER TABLE CLIENTE ADD CPF VARCHAR(14);
select *from CLIENTE
/* Otmizacao de Banco de Dados */
/*Ao criar um simples filtro em uma tabela,
pode-se encontrar alguns pontos que prejudicam a
performance da consulta.
A consulta abaixo demonstra um dos exemplos: */
SELECT
* FROM
Cliente
/*Ao fazer algum filtro em uma tabela,
evite informar todos os campos de uma tabela
em uma consulta, a menos que isso for realmente
necessário. Muitas das vezes não será utilizado
todos os campos. A consulta acima pode ser executada
rapidamente se a tabela da base de dados contiver poucos
registros, mas para adquirir uma melhor performance da
consulta é recomendável filtrar apenas os campos que
serão utilizados, o exemplo abaixo demonstra um cenário
parecido: */
SELECT
nome_cli,
CPF,
cep,
endereco
FROM
Cliente
/*Outro ponto importante é a utilização da instrução
COUNT, esta instrução informa a quantidade de
registros retornados em uma consulta.*/
SELECT
COUNT(*)
FROM
Cliente
/*Porém não há a necessidade de utilização desta
consulta, porque ao executá-la, será contado um registro
de cada vez. Para isto existe as tabelas
‘sysobjects‘ e ‘sysindexes‘.
Com estas duas tabelas é possível obter muitas
informações de diversos todos objetos existentes na
base de dados. */
SELECT
sysobjects.name AS [Cliente],
sysindexes.rowcnt AS [Qtde. de Registros]
FROM
sysobjects
INNER JOIN
sysindexes ON
sysindexes.id = sysobjects.id
WHERE
sysobjects.name = 'Cliente'
AND
sysindexes.indid = 1
/* A consulta acima informa a quantidade de registros
existentes na tabela ‘Cliente’. Reparem que foi utilizado
uma nova instrução, o INNER JOIN. Esta instrução serve
para fazer a junção de duas ou mais tabelas, lembrando
que isto significa que DEVE possuir algum registro na
tabela secundária, ou seja na tabela que está recebendo
o INNER JOIN, sendo assim, é necessário fazer a
comparação de campos em comum que as duas possuem.
Certifique-se que o nome da tabela escrito na cláusula
WHERE está correto, caso contrário não será retornado
nenhum registro. Esta consulta é outro exemplo que poderá
trazer problemas de performance, porque ao utilizá-la,
a consulta toda pode acessar qualquer uma das informações
(campos) existentes nas tabelas.
Uma outra forma de consultar a quantidade de registros de
uma tabela é a seguinte: */
SELECT
sysindexes.rowcnt AS [Qtde. de Registros]
FROM
sysindexes
WHERE
sysindexes.indid = 1
AND
EXISTS(
SELECT
sysobjects.id
FROM
sysobjects
WHERE
sysobjects.id= sysindexes.id
AND
sysobjects.name= 'Cliente'
)
/*Ao fazer um filtro como o acima, pode-se obter
resultados muito eficazes em relação ao desempenho.
A instrução EXISTS é uma instrução extremamente rápida
porque utiliza pouco recurso de hardware. É como se
retornasse uma variável booleana.
Outra instrução que consome um maior processamento é a
instrução IN, que é utilizada quando é necessário fazer
o filtro de um vetor de dados. O exemplo abaixo informa
os todos clientes que possuir algum telefone. */
SELECT
* FROM
CLIENTE
SELECT
nome_cli
FROM
CLIENTE
WHERE
cod_cli IN(SELECT cod_cli FROM CLIENTE)
/*Outra maneira de informar estes registro seria utilizar
(novamente) a instrução EXISTS ao invés da instrução IN.*/
SELECT
Cliente.nome_cli
FROM
Cliente
WHERE
EXISTS(
SELECT
CLIENTE.cod_cli
FROM
CLIENTE
WHERE
CLIENTE.cod_cli= Cliente.cod_cli
)
/*São inúmeras as tarefas que se pode fazer para melhorar
a performance de uma consulta. Estas são apenas algumas
operações que devem ser feitas caso necessita de
resultados mais rápidos em consultas SQL.*/
use db_vendas_2016
create table CLIENTE (
cod_cli int primary key,
nome_cli varchar(80) not null,
endereco varchar(120),
cidade varchar(50),
cep char(8),
uf char(2));
create table VENDEDOR (
cod_vend int primary key,
nome_vend varchar(80) not null,
sal_fixo money,
faixa_comiss char(1));
create table PEDIDO (
num_ped int primary key,
prazo_entr int,
cod_cli int foreign key references cliente(cod_cli),
cod_vend int foreign key references vendedor(cod_vend));
create table PRODUTO (
cod_prod int primary key,
unid_prod varchar(10) not null,
desc_prod varchar(120),
val_unit money not null);
select * from PRODUTO
create table ITEM_PEDIDO (
num_ped int foreign key references pedido(num_ped),
cod_prod int foreign key references produto(cod_prod),
qtd_ped int
primary key (num_ped, cod_prod));
insert into cliente values
(1000, 'Supermercado Carrefour', 'Av. das Americas', 'rio de janeiro', '20000001', 'rj'),
(2000, 'Supermercado Baratao', 'Rua 7 de setembro', 'rio de janeiro', '20000002', 'rj'),
(3000, 'Supermercado Arariboia', 'Rua Itaoca', 'niteroi', '20000003', 'rj'),
(4000, 'Mercado São João', 'Cidade Univers.', 'niteroi', '20000004', 'rj'),
(5000, 'CSN', 'Rua das Nações', 'volta redonda', '20000005', 'rj'),
(6000, 'Pegeout', 'Rodovia Pres. Dutra', 'resende', '20000006', 'rj'),
(7000, 'Lojas Pague Pouco', 'Rua Tuiuti', 'sao paulo', '11000001', 'sp'),
(8000, 'Ford Caminhoes', 'Rua Henry Ford', 'sao paulo', '11000002', 'sp'),
(9000, 'Célula Celulose', 'Rua Gen. Arouca', 'guaiba', '30000001', 'rs'),
(10000, 'Elevadores RioSul', 'Rua Planejada', 'guaiba', '30000001', 'rs');
insert into produto (cod_prod, unid_prod, desc_prod, val_unit) values
(100, 'kg', 'Chapa de Aco', 2.5),
(200, 'kg', 'Cimento', 4.5),
(300, 'kg', 'parafuso 3.0X10.5 mm', 2),
(400, 'm', 'Fio plastico', 2),
(500, 'l', 'Solvente PRW', 5);
insert into vendedor values
(11, 'Paulo Alberto', 1500, 'b'),
(12, 'Ana Cristina', 2100, 'a'),
(13, 'Cassia Andrade', 900, 'c'),
(14, 'João Roberto', 2500, 'a'),
(15, 'Maria Paula', 900, 'c');
insert into pedido values
(1111, 10, 1000, 11),
(1112, 5, 1000, 11),
(1113, 30, 1000, 15),
(2111, 15, 3000, 14),
(2112, 18, 3000, 15),
(2113, 3, 3000, 12),
(3111, 13, 4000, 12),
(3112, 7, 4000, 11),
(4111, 7, 6000, 11),
(4112, 7, 6000, 14),
(5111, 10, 8000, 14),
(6111, 30, 9000, 14),
(6112, 60, 9000, 12),
(7111, 20, 10000, 15);
insert into item_pedido values
(1111, 100, 100),
(1111, 200, 100),
(1111, 300, 200),
(1112, 400, 100),
(1112, 500, 1000),
(1113, 100, 300),
(2111, 100, 500),
(2111, 500, 400),
(2112, 200, 100),
(2112, 300, 200),
(2113, 500, 500),
(3111, 400, 300),
(3112, 100, 400),
(3112, 200, 600),
(4111, 300, 700),
(4112, 500, 1000),
(4112, 200, 500),
(5111, 200, 100),
(5111, 300, 500),
(6111, 400, 100),
(6112, 300, 400),
(6112, 400, 200),
(7111, 100, 500);
USE db_vendas_2016
CREATE VIEW vw_ProdutosCaros
as
select
cod_prod ,
unid_prod ,
desc_prod ,
val_unit
from PRODUTO
where val_unit > 100;
select* from vw_ProdutosCaros
select *from PRODUTO
where unid_prod = 'm';
create view vw_ProdutosPorMetro as
select*
from PRODUTO
where unid_prod = 'm';
select* from vw_ProdutosPorMetro
select
cod_prod as codigo,
unid_prod as unidade,
desc_prod as descricao,
val_unit as valor
from PRODUTO
where unid_prod = 'm';
create view vw_clientesNiteroi as
select cod_cli as codigo,
nome_cli as cliente,
endereco,
cidade,
cep
from cliente
where cidade = 'niteroi'
select* from vw_clientesNiteroi
select count(*) as [Qtde Cliente Niteroi]
from vw_clientesNiteroi;
select * from PEDIDO, CLIENTE
create view vw_pedidosniteroi as
select num_ped, prazo_entr, p.cod_cli
from vw_clientesNiteroi v, pedido p
where v.codigo = p.cod_cli;
select * from vw_pedidosniteroi
select cod_cli, count(*) as [Qtde de Pedidos Niteroi]
from vw_pedidosniteroi
group by cod_cli;
create view vw_PedidosNiteroiComCliente as
select num_ped, prazo_entr, cod_cli, cidade, cep
from vw_pedidosniteroi p, vw_clientesNiteroi c
where p.cod_cli = c.codigo;
select * from vw_PedidosNiteroiComCliente
create view vw_PedidoNietroiComIntens as
select v.num_ped, prazo_entr, cod_prod, qtd_ped
from vw_pedidosniteroi v, ITEM_PEDIDO i
where v.num_ped = i.num_ped
select * from vw_PedidoNietroiComIntens
select prazo_entr, sum(qtd_ped) as Total_Itens
from vw_PedidoNietroiComIntens
group by prazo_entr;
CREATE VIEW vw_produtosPorMetro AS
SELECT cod_prod,
desc_prod,
unid_prod,
val_unit
From PRODUTO
WHERE unid_prod = 'm';
SELECT * FROM vw_produtosPorMetro;
CREATE VIEW vw_ProdutosCaros AS
SELECT cod_prod,
desc_prod,
unid_prod,
val_unit
FROM PRODUTO
WHERE val_unit > 3;
CREATE VIEW vw_Clientesniteroi AS
SELECT cod_cli,
nome_Cli,
endereco,
cep
FROM CLIENTE
WHERE cidade = 'Niteroi';
SELECT COUNT(*) AS [Quantidade Clientes Niteroi]
FROM vw_ClientesNiteroi;
CREATE VIEW vw_pedidosniteroi as
select num_ped, prazo_entr, p.cod_cli
from vw_Clientesniteroi v, PEDIDO p
where v.cod_cli = p.cod_cli;
drop view vw_pedidosniteroi;
CREATE VIEW vw_pedidosniteroi as
select num_ped, prazo_entr, p.cod_cli
from vw_Clientesniteroi v, PEDIDO p
where v.cod_cli = p.cod_cli;
select * from vw_pedidosniteroi;
use db_vendas_2016
create view vw_produtoNiteroiComItens as
select v.num_ped, prazo_entr, cod_prod, qtd_ped
from vw_pedidosniteroi v, ITEM_PEDIDO I
where v.num_ped = I.num_ped
select prazo_entr, sum(qtd_ped) as total_itens
from vw_produtoNiteroiComItens
group by prazo_entr;
select * from vw_produtoNiteroiComItens
select * from vw_ProdutosCaros
--04 - Selecione os clientes(nome, cidade, cep) da cidade de são paulo ou que tem cep entre 20.000.005 e 20.000.010
select nome_cli, cidade, cep
from CLIENTE
where cidade = 'São Paulo' or
cep between 20000005 and 20000010;
--05 - Liste os vendedores(código e nome) com faixa de comissão a ou b. Use IN.
select cod_vend, nome_vend
from VENDEDOR
where faixa_comiss in ('a','b');
--06 - selecione os clientes com endereço não preenchido.
select *
from CLIENTE
where endereco is null;
--07 - Atualize o salário dos vendedores da faixa 'C' com aumento de de 15% e mais 120,00 de bonus.
select cod_vend,
nome_vend
from VENDEDOR
where faixa_comiss = 'C';
--08 - apresente os vendedores (código, nome, faixa, salário) que tem o menor e o maior salario
select *
from VENDEDOR
where sal_fixo IN ((select MIN(sal_fixo) from vendedor),
(select max(sal_fixo) from vendedor))
order by sal_fixo;
--09 - Recupere a media dos salarios dos vendedores
select avg (sal_fixo) as media from vendedor;
--10 - crie uma view vw_vendedorComAltoSalario com as informações dos vendedores que tem salário maior que a média.
create view vw_vendedorComAltoSalario as --errro
select *
from VENDEDOR
where sal_fixo > (select avg(sal_fixo) from vendedor);
select * from vw_vendedorComAltoSalario;
--11 - Quais clientes que tem pedidos maior que 10 dias e que são dos estados de são paulo e Rio de janeiro(nome,UF,num_ped e prazo)
select nome_cli, uf, num_ped, prazo_entr
from CLIENTE, PEDIDO
where prazo_entr > 10
and uf='sp' or uf='rj';
--12 - Apresentar os clientes com os pedidos ordenados por prazo de entrega(código, nome, num_ped e prazo)
--13 - Apresentar os vendedores que registraram pedidos com prazo maior de 10 dias e que tem salários maior que a média dos salários.
--trigger
--exercicio 1
create TRIGGER tr_UFinvalida
on CLIENTE
INSTEAD of insert
as begin
declare @UF VARCHAR(2);
select @UF=uf from inserted;
if(@UF = 'RJ' or @UF = 'ES')
print('Operação cancelada')
else
begin
insert into CLIENTE (cod_cli, nome_cli, endereco,cidade,cep,uf)
(select*from inserted);
Print('Sucesso')
end;
end ;
--trigger
--exercicio 2
create table tlogproduto(
codigo_produto int,
descricao_produto varchar(120),
unidade char(2),
dataalteracao datetime)
CREATE TRIGGER tg_naoaltera
ON produto
instead of INSERT
AS begin
-- variável utilizada no trigger
declare @cod_prod varchar(50)
declare @desc_prod varchar(50)
declare @unid_prod char(2)
-- define o valor da variável @produto
set @cod_prod = (select cod_prod from inserted)
set @desc_prod = (select desc_prod from inserted)
set @unid_prod = (select unid_prod from inserted)
insert into tlogproduto (codigo_produto, descricao_produto, unidade, dataalteracao)
values (@cod_prod, @desc_prod, @unid_prod, GETDATE())
print 'AS INCLUSÕES DE PRODUTOS ESTÃO SUSPENSAS!'
END
insert into PRODUTO (cod_prod, unid_prod, desc_prod, val_unit)
values (1234,'kg','Banana',10)
insert into PRODUTO (cod_prod, unid_prod, desc_prod, val_unit)
values (0022,'kg','arroz',10)
select * from produto
select * from tlogproduto
--trigger
--exercicio 3
create table tlogitem(
cod_usuario varchar(20),
data_atualizacao datetime,
num_pedido int,
cod_produto int,
tipo_atualizacao varchar(15)
)
CREATE TRIGGER tg_logitem
ON item_pedido
after INSERT, DELETE
AS begin
-- variável utilizada no trigger
declare @num_pedido int
declare @cod_prod int
declare @tipo_atualizacao varchar(15)
if((select count(*)from inserted)>0)
set @tipo_atualizacao = 'inclusão'
else if ((select count(*) from deleted)>0)
set @tipo_atualizacao = 'remoção'
set @num_pedido = (select num_ped from inserted)
set @cod_prod = (select cod_prod from inserted)
insert into tlogitem (cod_usuario, data_atualizacao, num_pedido, cod_produto, tipo_atualizacao)
values (CURRENT_USER, getdate(), @num_pedido, @cod_prod,@tipo_atualizacao)
END
insert into ITEM_PEDIDO (num_ped, cod_prod, qtd_ped)
values(1114, 100, 555)
delete from ITEM_PEDIDO
where num_ped = 1114;
select * from tlogitem
--trigger
--exercicio 4
create table tlogvendedor(
usuario char(30),
dataalteracao datetime,
cod_vendedor int,
salario_fixo money,
faixa_comissao char(1)
)
create trigger tr_atualiuzarvendedor
on vendedor
for update as
begin
insert into tlogvendedor (usuario, dataalteracao, cod_vendedor,
salario_fixo, faixa_comissao)
values (CURRENT_USER, GETDATE(),
(select cod_vend from inserted),
(select sal_fixo from deleted),
(select faixa_comiss from deleted));
end;
select * from VENDEDOR
select * from tlogvendedor
select *from CLIENTE
create procedure sp_exclui_clientes
@cod_cliente int,
@qtde_pedido int OUTPUT AS
Begin
select @qtde_pedido = count(*)
from PEDIDO where cod_cli = @cod_cliente;
delete from ITEM_PEDIDO
where num_ped in
(select num_ped from PEDIDO where cod_cli = @cod_cliente);
delete from PEDIDO
where cod_cli = @cod_cliente;
delete from CLIENTE
where cod_cli = @cod_cliente;
END
drop procedure sp_exclui_clientes
select * from CLIENTE
declare @cod_cliente int
declare @qtde_pedido int
declare @qt_pedido int
execute sp_exclui_clientes @cod_cliente = 6000, @qtde_pedido =
@qt_ped OUTPUT
print 'qtde_pedido'
print @qt_pedido
create PROCEDURE SP_ATUALIZA_SALARIO
@codigo int
as
begin
if(@codigo = 2)
update VENDEDOR set sal_fixo = sal_fixo * 1.2
where FAIXA_COMISS = 'B'
else
if(@codigo = 3)
update VENDEDOR set sal_fixo = sal_fixo * 1.3
where faixA_comiss = 'B'
else
if(@codigo=4)
update VENDEDOR set sal_fixo = sal_fixo * 1.4
where faixa_comiss = 'B'
end;
exec SP_ATUALIZA_SALARIO 3;
select *from VENDEDOR
where faixa_comiss = 'b'
create PROCEDURE sp_inserir_pedido
@num_pedido int,
@cod_cliente int,
@cod_vendedor int
As
Begin
declare @datacompra datetime
set @datacompra = getdate()
declare @dataentrega datetime
set @dataentrega = @datacompra + 14
if DATEPART(DW, @dataentrega)= 1
set @dataentrega = @dataentrega +1
insert into PEDIDO(num_ped,prazo_entr,data_entrega,cod_cli, cod_vend)
values(@num_pedido, 14,@datacompra,@cod_cliente,@cod_vendedor)
end
exec sp_inserir_pedido 1010,1000,11
drop PROCEDURE sp_inserir_pedido
select *from PEDIDO
alter table PEDIDO add data_entrega datetime
select * from PRODUTO
alter table PRODUTO add qtde_estoque int;
update PRODUTO
SET qtde_estoque = 200
/* Criar Nome Procedure */
create PROCEDURE sp_desconto(@qtde_estoque int output)
as
begin
/*variaveis locais e Condições if, While*/
set @qtde_estoque = (select count(cod_prod)
from PRODUTO
where @qtde_estoque >= 100)
update PRODUTO set val_unit = val_unit * 0.9
where qtde_estoque between 100 and 200
update PRODUTO set val_unit = val_unit * 0.8
where qtde_estoque > 200
/*final End*/
end;
/*executar Procedure*/
declare @qtde int
exec sp_desconto @qtde output
/* Excluir Procedure */
drop PROCEDURE sp_desconto
select * from PRODUTO
alter table ITEM_PEDIDO add valor_item money
select * from ITEM_PEDIDO
/* Cursor permite percorrer o resultado de uma consulta linha a linha
1- Declare nome_do_cursor Cursor
for consulta
2- OPEN nome_do_cursor
3- FETCH NEXT FROM nome_do_cursor int @codigo
4- CLOSE nome_do_cursor
5- DEALLOCATE nome_do_cursor*/
create procedure SP_INSERIRITEMPEDIDO(
@num_ped int,
@cod_prod int,
@quantidade int)
begin
declare @estoque int;
if (@quantidade <= 0)
print ('Quantidade Invalida')
else
insert into ITEM_PEDIDO (num_ped, cod_prod, qtd_ped,valor_item)
values (@num_ped
@cod_prod
@quantidade
(select @quantidade * val_unit
from PRODUTO
where cod_prod = @cod_prod));
update PRODUTO
set @qtde_estoque = @qtde_estoque - @quantidade
where cod_prod = @cod_prod;
/*1. Escreva o comando SQL para criação da stored procedure sp_desconto,
considerando que os produtos que têm estoque entre 100 e 200 devem receber um desconto de 10%
e aqueles que tem estoque superior a 200 devem receber um desconto de 20%.
A stored procedure deve, então, atualizar o valor do desconto para 10 ou 20 dependendo da quantidade em estoque.
A stored procedure deve retornar a quantidade de registros atualizados.*/
/* alteração da tabela produto para incluir o campo qtde_estoque */
alter table produto
add qtde_estoque int
/* remoção da procedure */
drop procedure sp_desconto
/* criação da procedure */
create procedure sp_desconto
(@qtde_atualiza int output) as
begin
set @qtde_atualiza = (select count (cod_prod)
from produto
where qtde_estoque >= 100)
update produto
set val_unit = val_unit * 0.9
where qtde_estoque between 100 and 200;
update produto
set val_unit = val_unit * 0.8
where qtde_estoque > 200;
end
/* execução da procedure */
declare @qtde int
exec sp_desconto @qtde output
print @qtde
--2. Altere a tabela item_pedido acrescentando o novo atributo valor_item do tipo Money.
-- Alteração da tabela item_pedido
alter table item_pedido
add valor_item money
--Atualize os itens de pedidos cadastrados: o valor do item deve receber o resultado da quantidade multiplicada pelo valor unitário do produto.
-- Atualização de valor_item para os itens de pedido existentes na tabela ITEM_PEDIDO.
-- Note o uso do CURSOR
-- Removendo a procedure
drop procedure sp_atualizavaloritem
-- Criando a procedure
create procedure sp_atualizavaloritem as
begin
-- declarando variáveis locais
declare @cod_produto int
-- declarando o CURSOR cursor_itens
DECLARE cursor_itens CURSOR
LOCAL
FOR SELECT cod_prod FROM item_pedido
-- abrindo o CURSOR cursor_itens para leitura
OPEN cursor_itens
-- Lendo a primeira linha
FETCH NEXT FROM cursor_itens INTO @cod_produto
-- Percorrendo linhas do cursor (enquanto existe linhas)
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE item_pedido
set valor_item = qtd_ped * (select val_unit
from produto
where cod_prod = @cod_produto)
WHERE CURRENT OF cursor_itens
-- Lendo a próxima linha do cursor
FETCH NEXT FROM cursor_itens INTO @cod_produto
END
-- Fechando Cursor para leitura
CLOSE cursor_itens
-- Desalocando o cursor
DEALLOCATE cursor_itens
END
-- Executando a procedure
EXEC sp_atualizavaloritem
/*3. Crie a stored procedure Proc_InsereItensPedido para inclusão de itens de pedido. Considere:
Parâmetros de entrada: número do pedido, código do produto e quantidade.
Se a quantidade fornecida for menor ou igual a zero então informar ‘Quantidade inválida. Pedido não registrado’ e sair da procedure.
Se o produto fornecido não estiver cadastrado então emitir mensagem de erro e sair da procedure.
Se o produto fornecido não estiver cadastrado então emitir mensagem de erro e sair da procedure.
Se o valor_unitario ou qtde_estoque do produto estiverem zerados, então emitir a mensagem ‘Produto incompleto. Pedido não registrado’ e sair da procedure.
Se o estoque do produto for menor que a quantidade fornecida então emitir a mensagem ‘Estoque insuficiente. Pedido não registrado’ e sair da procedure.
Incluir o item do pedido com número do pedido, código do produto e quantidade fornecidos. O valor_item deve ser calculado (quantidade fornecida X valor unitário do produto). Se ocorrer algum erro (@@error <> 0) emitir a mensagem ‘Erro ao incluir o item do pedido’ e retroceder a operação.
Atualizar o estoque do produto (qtde_estoque = qtde_estoque – quantidade fornecida). Se ocorrer algum erro (@@error <> 0) emitir a mensagem ‘Erro ao incluir o item do pedido’ e retroceder a operação.
Emitir a mensagem ‘Pedido registrado e estoque atualizado com sucesso’.
Se o estoque atualizado do produto for zero então emitir a mensagem ‘Produto esgotado’.*/
--- Proc_InsereItensPedido
-- Removendo a procedure
DROP PROCEDURE PROC_INSEREITEMPEDIDO
-- Criando a procedure
CREATE PROCEDURE PROC_INSEREITEMPEDIDO (
@NUM_PEDIDO INT,
@COD_PRODUTO INT,
@QUANTIDADE INT) AS
BEGIN
-- Declaração de variáveis locais
DECLARE @estoque int;
-- Verificando se a quantidade fornecida é válida
IF (@QUANTIDADE <= 0)
PRINT('QUANTIDADE INVALIDA. PEDIDO NÃO REGISTRADO');
ELSE
BEGIN
-- Verificando se o produto fornecido está cadastrado
IF ((SELECT COUNT(COD_PROD)
FROM PRODUTO
WHERE COD_PROD = @COD_PRODUTO) <= 0)
PRINT ('PRODUTO NÃO CADASTRADO')
ELSE
BEGIN
-- Verificando se há estoque suficiente do produto para atender o pedido.
SET @estoque = (SELECT QTDE_ESTOQUE
FROM PRODUTO
WHERE COD_PROD = @COD_PRODUTO)
IF (@QUANTIDADE > @estoque)
PRINT ('ESTOQUE INSUFICIENTE')
ELSE
BEGIN
-- Incluindo o novo registro em ITEM_PEDIDO
INSERT INTO ITEM_PEDIDO
(NUM_PED, COD_PROD, QTD_PED, VALOR_ITEM)
VALUES (@NUM_PEDIDO, @COD_PRODUTO,
@QUANTIDADE,
(SELECT @QUANTIDADE * val_unit
FROM PRODUTO WHERE COD_PROD = @COD_PRODUTO));
-- Atualizando o estoque em PRODUTO
UPDATE PRODUTO
SET QTDE_ESTOQUE = QTDE_ESTOQUE - @QUANTIDADE
WHERE COD_PROD = @COD_PRODUTO;
-- Verificando se o estoque está esgotado
IF ((SELECT QTDE_ESTOQUE
FROM PRODUTO
WHERE COD_PROD = @COD_PRODUTO) = 0)
PRINT ('PRODUTO ESGOTADO')
END
END
END
END
select * from ITEM_PEDIDO
create function ValorPedido (@num_ped int)
returns money
as
begin
declare @total money;
select @total = sum(valor_item) from ITEM_PEDIDO
where num_ped = @num_ped;
return @total;
end
select dbo.ValorPedido (1111);
create function PrimeiroNome (@nomeCompleto varchar(80))
returns varchar(80)
as
begin
declare @primeiroNome varchar(30) = ' ',
@achou bit = 0, /*false*/
@contador numeric(2) =1,
@caractere char(1);
while(@achou = 0 )
begin
set @caractere = SUBSTRING(@nomeCompleto, @contador, 1);
set @primeiroNome = CONCAT(@primeiroNome, @caractere);
if (@caractere = ' ') set @achou = 1; /*True*/
else set @contador = @contador + 1;
end
return @primeiroNome
end
select dbo.PrimeiroNome (nome_cli) from CLIENTE;
create function FormataCEP (@cep varchar(11))
returns varchar(14) as
begin
declare @i int ;
declare @formatado varchar(10);
set @formatado = "";
while (@i <= datalength(@cep) and (datalength (@formatado) <= 14))
if (substring(@cep,@i,1) in ('0','1','2','3','4','5','6','7','8','9'))
BEGIN
set @formatado = @formatado + substring (@cep,@i,1);
if (@i = 3) or (@i = 7) set @formatado = @formatado + '.';
else
if (@i = 11) set @formatado = @formatado + '-';
end
set @i = @i +1
if datalength(@formatado) < 14
set @formatado = 'erro';
return (@formatado)
end
ALTER TABLE CLIENTE ADD CPF VARCHAR(14);
select *from CLIENTE
/* Otmizacao de Banco de Dados */
/*Ao criar um simples filtro em uma tabela,
pode-se encontrar alguns pontos que prejudicam a
performance da consulta.
A consulta abaixo demonstra um dos exemplos: */
SELECT
* FROM
Cliente
/*Ao fazer algum filtro em uma tabela,
evite informar todos os campos de uma tabela
em uma consulta, a menos que isso for realmente
necessário. Muitas das vezes não será utilizado
todos os campos. A consulta acima pode ser executada
rapidamente se a tabela da base de dados contiver poucos
registros, mas para adquirir uma melhor performance da
consulta é recomendável filtrar apenas os campos que
serão utilizados, o exemplo abaixo demonstra um cenário
parecido: */
SELECT
nome_cli,
CPF,
cep,
endereco
FROM
Cliente
/*Outro ponto importante é a utilização da instrução
COUNT, esta instrução informa a quantidade de
registros retornados em uma consulta.*/
SELECT
COUNT(*)
FROM
Cliente
/*Porém não há a necessidade de utilização desta
consulta, porque ao executá-la, será contado um registro
de cada vez. Para isto existe as tabelas
‘sysobjects‘ e ‘sysindexes‘.
Com estas duas tabelas é possível obter muitas
informações de diversos todos objetos existentes na
base de dados. */
SELECT
sysobjects.name AS [Cliente],
sysindexes.rowcnt AS [Qtde. de Registros]
FROM
sysobjects
INNER JOIN
sysindexes ON
sysindexes.id = sysobjects.id
WHERE
sysobjects.name = 'Cliente'
AND
sysindexes.indid = 1
/* A consulta acima informa a quantidade de registros
existentes na tabela ‘Cliente’. Reparem que foi utilizado
uma nova instrução, o INNER JOIN. Esta instrução serve
para fazer a junção de duas ou mais tabelas, lembrando
que isto significa que DEVE possuir algum registro na
tabela secundária, ou seja na tabela que está recebendo
o INNER JOIN, sendo assim, é necessário fazer a
comparação de campos em comum que as duas possuem.
Certifique-se que o nome da tabela escrito na cláusula
WHERE está correto, caso contrário não será retornado
nenhum registro. Esta consulta é outro exemplo que poderá
trazer problemas de performance, porque ao utilizá-la,
a consulta toda pode acessar qualquer uma das informações
(campos) existentes nas tabelas.
Uma outra forma de consultar a quantidade de registros de
uma tabela é a seguinte: */
SELECT
sysindexes.rowcnt AS [Qtde. de Registros]
FROM
sysindexes
WHERE
sysindexes.indid = 1
AND
EXISTS(
SELECT
sysobjects.id
FROM
sysobjects
WHERE
sysobjects.id= sysindexes.id
AND
sysobjects.name= 'Cliente'
)
/*Ao fazer um filtro como o acima, pode-se obter
resultados muito eficazes em relação ao desempenho.
A instrução EXISTS é uma instrução extremamente rápida
porque utiliza pouco recurso de hardware. É como se
retornasse uma variável booleana.
Outra instrução que consome um maior processamento é a
instrução IN, que é utilizada quando é necessário fazer
o filtro de um vetor de dados. O exemplo abaixo informa
os todos clientes que possuir algum telefone. */
SELECT
* FROM
CLIENTE
SELECT
nome_cli
FROM
CLIENTE
WHERE
cod_cli IN(SELECT cod_cli FROM CLIENTE)
/*Outra maneira de informar estes registro seria utilizar
(novamente) a instrução EXISTS ao invés da instrução IN.*/
SELECT
Cliente.nome_cli
FROM
Cliente
WHERE
EXISTS(
SELECT
CLIENTE.cod_cli
FROM
CLIENTE
WHERE
CLIENTE.cod_cli= Cliente.cod_cli
)
/*São inúmeras as tarefas que se pode fazer para melhorar
a performance de uma consulta. Estas são apenas algumas
operações que devem ser feitas caso necessita de
resultados mais rápidos em consultas SQL.*/
Nenhum comentário:
Postar um comentário