CREATE DATABASE PEDIDO --COLLATE Latin1_General_CI_AI
GO
USE PEDIDO
GO
--Verificar qual idioma está configurado
Select @@langid, @@language
GO
--Verificar quais idiomas o MSSQL dá suporte
Select * From sys.syslanguages
Exec sp_helplanguage
GO
--Verificar o Collate (Codificação de caracteres) e idioma do banco
EXEC sp_helpdb
GO
--Alterar o Collate depois de criado
ALTER DATABASE PEDIDO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE PEDIDO COLLATE Latin1_General_CI_AI
ALTER DATABASE PEDIDO SET MULTI_USER
GO
--Alterar o idioma para inglês, pois os inserts abaixo
--estão no padrão mdy
SET LANGUAGE 'us_english'
GO
create table cliente (
Cod_cliente int
CONSTRAINT PK_CODCLIENTE Primary Key,
Nome_cliente varchar(50) not null,
Endereco varchar (50),
Cidade varchar(20),
Cep char(8),
UF char(2),
CPF_CGC char(14),
IE char(12))
drop table cliente
select *from cliente
GO
create table vendedor (
cod_vendedor int,
nome_vendedor varchar (50) not null,
faixa_comissao char(1),
salario_fixo money,
CONSTRAINT PK_VENDEDOR PRIMARY KEY (COD_VENDEDOR))
drop table vendedor
select *from vendedor
GO
create table pedido (
Num_pedido int,
data_compra datetime,
data_entrega datetime,
cod_cliente int not null,
cod_vendedor int not null ,
FOREIGN KEY (COD_CLIENTE) REFERENCES CLIENTE (COD_CLIENTE),
CONSTRAINT FK_VENDEDOR FOREIGN KEY(COD_VENDEDOR) REFERENCES VENDEDOR,
CONSTRAINT PK_PEDIDO PRIMARY KEY (NUM_PEDIDO)
)
drop table pedido
select *from pedido
GO
create table produto (
cod_produto int
CONSTRAINT PK_CODPRODUTO Primary Key,
descricao varchar(20),
Unidade char(2),
Valor_unitario money)
drop table produto
select *from produto
GO
create table item_pedido (
num_pedido int references pedido,
cod_produto int references produto,
quantidade int
CONSTRAINT PK_PEDIDOPRODUTO primary key (num_pedido, cod_produto) )
drop table item_pedido
select * from item_pedido
GO
insert into cliente values (0001,'Marcelo Cruz' ,'Rua Brasil, 35' ,'Jundiaí' ,13124579,'SP','7464563762','53425364');
insert into cliente values (0002,'Jose da Silva' ,'Rua Argentina, 23' ,'Buenos Aires' ,45876548,'TY','4587412637','452145285');
insert into cliente values (0003,'Mia Bini' ,'Alameda cavalcante, 12' ,'Pindamonhangaba',45269887,'SP','1542845798','687286464');
insert into cliente values (0004,'Nelson Nerd' ,'Rua das Torres, 750' ,'Araraquara' ,25412563,'SP','654987985','984651698');
insert into cliente values (0005,'Rogério Silva' ,'Avenida Castro Alves, 5500' ,'Queluz' ,68416845,'ES','0654987878','984165494');
insert into cliente values (0006,'Vinicius Beto' ,'Rua Brasil, 42' ,'São Paulo' ,15478452,'SP','0640968109','981651988');
insert into cliente values (0007,'Cândido Tavares' ,'Rua da Abolição, 350' ,'Jundiaí' ,13215468,'SP','0065498987','954165214');
insert into cliente values (0008,'Uioswaldo Neto' ,'Rua Avestruz Limpa, 80' ,'Araraquara' ,16789487,'PR','987654613','774465546');
insert into cliente values (0009,'Ana Benedetti' ,'Rua Antônio Limones, 90' ,'Jundiaí' ,13215045,'SP','6549879495','665421634');
insert into cliente values (0010,'Bruno Mezenga' ,'Rua Major Mário Lacerda, 1340' ,'Pindamonhangaba',22400485,'SP','6549684851','968762461');
insert into cliente values (0011,'Silvia Popovic' ,'Alameda Castro, 48' ,'Jundiaí' ,13215064,'SP','6324161878','416516547');
insert into cliente values (0012,'Fausto Silva' ,'Avenida dos Ferroviários, 2000','Queluz' ,65498719,'SP','6547841352','654849514');
insert into cliente values (0013,'Renata Polire' ,'Rua do Retiro, 5400' ,'São Paulo' ,65491878,'PR','24165798787','165465787');
insert into cliente values (0014,'Adamastor Tio' ,'Rua Entre Postes, 366' ,'Jundiaí' ,13254687,'ES','99887958416','6321654657');
insert into cliente values (0015,'Luke Skywalker' ,'Rua Olivia Terceira, 240' ,'São Paulo' ,68423546,'SP','65495819518','3654621657');
insert into cliente values (0016,'Plínio Castro Mendes','Rua Padre Silveira, 750' ,'Taubaté' ,26549879,'SP','65498498425','6598798554');
insert into cliente values (0017,'Oswaldo Benegripe' ,'Rua Daniel Mantovani, 987' ,'São Paulo' ,32416579,'PR','65498798415','6546549846');
insert into cliente values (0018,'Adamastor Filho' ,'Avenida Antônio Ozanam, 5543' ,'Jundiaí' ,13245124,'SP','44564687951','3621654477');
insert into cliente values (0019,'Karina Bonamiga' ,'Rua dos Expedicionários, 750' ,'Jundiaí' ,13244457,'SP','44798946546','9854654445');
insert into cliente values (0020,'Vanderlei Luís Silva','Rua Brasil, 75' ,'São Paulo' ,11245788,'SP','65498495158','6549874988');
select * from cliente
insert into vendedor values (0001,'Sandra Bullock' ,'A', 200);
insert into vendedor values (0002,'Keanu Reeves' ,'A', 500);
insert into vendedor values (0003,'Matt Damon' ,'B',1000);
insert into vendedor values (0004,'Alicia Silverstone','C', 400);
insert into vendedor values (0005,'Marilyn Monroe' ,'B', 100);
insert into vendedor values (0006,'Ben Affleck' ,'A',4000);
insert into vendedor values (0007,'Anna Nicole Smith' ,'B',2200);
insert into vendedor values (0008,'Anna Kournikova' ,'B', 600);
insert into vendedor values (0009,'Paulo Zulu' ,'C', 800);
insert into vendedor values (0010,'Meg Ryan' ,'A',1500);
insert into vendedor values (0011,'Tom Cruise' ,'B',3200);
insert into vendedor values (0012,'Will Smith' ,'C', 30);
insert into vendedor values (0013,'Whitney Houston' ,'A',1000);
insert into vendedor values (0014,'Nicole Kidman' ,'B',2200);
insert into vendedor values (0015,'Nhá Barbina' ,'A', 200);
insert into vendedor values (0016,'Demi Moore' ,'C',2000);
insert into vendedor values (0017,'Sharon Stone' ,'A',7000);
insert into vendedor values (0018,'Carolina Dieckmann','D',1200);
insert into vendedor values (0019,'Giovana Antonelli' ,'A', 700);
insert into vendedor values (0020,'Kevin Costner' ,'B', 600);
select * from vendedor
insert into produto values (0001,'Manteiga diet','UN', 3.30);
insert into produto values (0002,'Toddynho' ,'UN', 1.23);
insert into produto values (0003,'Lapis de cor' ,'CX', 0.56);
insert into produto values (0004,'Barbeador' ,'UN', 10.50);
insert into produto values (0005,'Leite' ,'LT', 70.20);
insert into produto values (0006,'Danone' ,'LT', 50.43);
insert into produto values (0007,'Alcatra' ,'KG',140.98);
insert into produto values (0008,'Pinga' ,'UN',110.50);
insert into produto values (0009,'Alface' ,'UN',2.00);
insert into produto values (0010,'Caderno' ,'UN',3.00);
insert into produto values (0011,'Óculos' ,'UN',4.00);
insert into produto values (0012,'Cinto' ,'UN',81.50);
insert into produto values (0013,'Camisa' ,'UN', 0.50);
insert into produto values (0014,'Sapato' ,'UN', 10.50);
insert into produto values (0015,'Relógio' ,'UN', 50.00);
insert into produto values (0016,'Escada' ,'MT', 90.75);
insert into produto values (0017,'Porta' ,'UN',210.50);
insert into produto values (0018,'Impressora' ,'UN',610.50);
insert into produto values (0019,'Água' ,'LT', 80.50);
insert into produto values (0020,'Telefone' ,'UN', 60.00);
select * from produto
insert into pedido values (0001,'01/09/11', '01/19/11',0002,0003);
insert into pedido values (0002,'05/08/11','10/18/11',0002,0001);
insert into pedido values (0003,'02/06/11','02/16/11',0004,0003);
insert into pedido values (0004,'10/07/11','10/27/11',0010,0015);
insert into pedido values (0005,'08/05/11','08/25/11',0017,0013);
insert into pedido values (0006,'05/04/11','05/14/11',0001,0008);
insert into pedido values (0007,'09/09/11','09/12/11',0006,0009);
insert into pedido values (0008,'05/07/11','05/08/11',0018,0013);
insert into pedido values (0009,'06/02/11','06/12/11',0013,0017);
insert into pedido values (0010,'05/01/11','05/21/11',0010,0009);
insert into pedido values (0011,'05/03/05','05/05/11',0015,0014);
insert into pedido values (0012,'08/11/11','09/09/11',0007,0006);
insert into pedido values (0013,'02/11/11','05/03/11',0012,0018);
insert into pedido values (0014,'08/11/11','08/12/11',0009,0004);
insert into pedido values (0015,'08/09/11','08/11/11',0014,0013);
insert into pedido values (0016,'01/06/04','01/06/04',0007,0003);
insert into pedido values (0017,'01/04/11','01/14/11',0011,0014);
insert into pedido values (0018,'06/07/11','06/08/11',0007,0015);
insert into pedido values (0019,'02/06/11','02/09/11',0014,0016);
insert into pedido values (0020,'10/07/11','10/09/11',0018,0017);
select * from pedido
GO
insert into item_pedido values (0001,0002, 13);
insert into item_pedido values (0001,0003, 3);
insert into item_pedido values (0001,0001, 6);
insert into item_pedido values (0002,0002, 8);
insert into item_pedido values (0002,0001, 50);
insert into item_pedido values (0002,0004, 80);
insert into item_pedido values (0003,0001, 66);
insert into item_pedido values (0004,0003,200);
insert into item_pedido values (0004,0014, 40);
insert into item_pedido values (0004,0018, 50);
insert into item_pedido values (0004,0013, 40);
insert into item_pedido values (0004,0007, 3);
insert into item_pedido values (0005,0003, 6);
insert into item_pedido values (0005,0006, 8);
insert into item_pedido values (0005,0013, 6);
insert into item_pedido values (0006,0017, 20);
insert into item_pedido values (0006,0016, 50);
insert into item_pedido values (0006,0014,100);
insert into item_pedido values (0007,0006,150);
insert into item_pedido values (0008,0010,120);
insert into item_pedido values (0009,0001, 40);
insert into item_pedido values (0009,0003, 69);
insert into item_pedido values (0009,0016, 45);
insert into item_pedido values (0009,0019,120);
select * from item_pedido
SET LANGUAGE 'Português (Brasil)'
create trigger trg_NovoCliente
on cliente
for insert as
begin
declare
@Nome_cliente varchar(50),
@Endereco varchar (50),
@Cidade varchar(20),
@Cep char(8),
@UF char(2),
@CPF_CGC char(14),
@IE char(12)
select @Nome_cliente = Nome_cliente from inserted
select @Endereco = Endereco from inserted
select @Cidade = Cidade from inserted
select @Cep = Cep from inserted
select @UF = UF from inserted
select @CPF_CGC = CPF_CGC from inserted
select @IE = IE from inserted
if @UF = 'RJ' or @UF ='ES'
print 'Inclusão Cancelada !!!'
else
begin
print 'Inclusão Realizada com Sucesso !!!'
INSERT INTO cliente (Nome_cliente, Endereco, Cidade, Cep, UF,CPF_CGC, IE)
VALUES ( @Nome_cliente, @Endereco, @Cidade, @Cep, @UF, @CPF_CGC, @IE )
end;
end;
drop trigger trg_NovoCliente
create trigger trg_LogVendedor
on vendedor
for update as
begin
declare
@cod_vendedor int,
@salario_fixo money,
@faixa_comissao char(1)
set @cod_vendedor = select cod_vendedor from inserted
set @salario_fixo = select salario_fixo from deleted
set @faixa_comissao = select faixa_comissao from deleted
insert into trg_LogVendedor
(usuario, data_alteracao, cod_vendedor, salario_fixo, faixa_comissao)
values(current_user, getdate(),@cod_vendedor, @salario_fixo, @faixa_comissao)
end;
drop trigger trg_LogVendedor
select *from vendedor
create table historico_preco(
codigo int primary key identity,
cod_produto int,
valor_unitario_antigo money,
data_alteracao datetime)
drop table historico_preco
create trigger trg_HistoricoPreco
on Produto
for update as
begin
insert into historico_preco
(codigo, cod_produto, valor_unitario_antigo, data_alteracao)
values((
end;
drop trigger trg_HistoricoPreco
/*Exercício 1:
Faça um procedimento que exclua um cliente cujo código é passado como parâmetro e todos os seus pedidos,
retornando a quantidade de pedidos que o cliente tinha.*/
CREATE PROCEDURE sp_exclui_clientes
@codigo_cli INT,
@qtde_pedidos INT OUTPUT AS
BEGIN
SELECT @qtde_pedidos = COUNT(*) FROM pedido
WHERE cod_cliente = @codigo_cli;
DELETE FROM item_pedido
WHERE num_pedido IN (select num_pedido from pedido
WHERE cod_cliente = @codigo_cli);
DELETE FROM pedido
WHERE cod_cliente = @codigo_cli;
DELETE FROM cliente
WHERE cod_cliente = @codigo_cli;
END;
DECLARE @cod_cli INT
DECLARE @qt_ped INT
EXECUTE sp_exclui_clientes @codigo_cli=20,
@qtde_pedidos = @qt_ped OUTPUT
PRINT 'qtde pedidos '
PRINT @qt_ped
BEGIN
DECLARE @qt_p int
EXECUTE sp_exclui_clientes 1, @qt_p OUTPUT;
PRINT 'qtde pedidos '
PRINT @qt_p
END
drop procedure sp_exclui_clientes
/*Exercício 2:
Criar uma stored procedure que atualize os salários dos vendedores com faixa de comissão = ´B´ :
A procedure deverá receber como parâmetro de entrada um código que definirá a porcentagem de aumento:
Se Código = 2, aumentar salário_fixo em 20%
Se Código = 3, aumentar salário_fixo em 30%
Se Código = 4 aumentar salário_fixo em 40%
Se Código tiver um valor diferente de 2, 3 e 4, o salário deverá permanecer o mesmo*/
CREATE PROCEDURE SP_ATUALIZA_SALARIO
@CODIGO INT AS
BEGIN
IF (@CODIGO = 2)
UPDATE VENDEDOR SET SALARIO_FIXO = SALARIO_FIXO * 1.2
WHERE FAIXA_COMISSAO = 'B'
ELSE
IF (@CODIGO = 3) UPDATE VENDEDOR SET SALARIO_FIXO = SALARIO_FIXO * 1.3
WHERE FAIXA_COMISSAO = 'B'
ELSE
IF (@CODIGO = 4) UPDATE VENDEDOR SET SALARIO_FIXO = SALARIO_FIXO * 1.4
WHERE FAIXA_COMISSAO = 'B';
END;
EXEC SP_ATUALIZA_SALARIO 3
drop procedure SP_ATUALIZA_SALARIO
/*Para verificar o resultado na tabela Vendedor, selecionar os salários antes e depois da execução do procedimento.*/
select * from vendedor
where faixa_comissao = 'B'
/* 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. */
alter table produto add qtd_estoque int
update produto
set qtd_estoque =50
where cod_produto between 1 and 10
update produto
set qtd_estoque = 120
where cod_produto between 11 and 15
update produto
set qtd_estoque = 250
where cod_produto between 16 and 20
/* criação da procedure */
create procedure sp_desconto
(@qtd_alterada int output)
as
begin
set @qtd_alterada = (select count ( cod_produto)
from produto
where qtd_estoque >= 100)
update produto
set Valor_unitario = Valor_unitario *0.9
where qtd_estoque between 100 and 200;
update produto
set Valor_unitario = Valor_unitario *0.8
where qtd_estoque > 200;
end;
/* execução da procedure */
declare @qtd int
exec sp_desconto @qtd output
print @qtd
drop procedure sp_desconto
/*Altere a tabela item_pedido acrescentando o novo atributo valor_item do tipo
Money. Atualize os itens de pedidos cadastrados: o valor do item deve
receber o resultado da quantidade multiplicada pelo
valor unitário do produto. */
alter table item_pedido add valor_item money
update item_pedido
set item_pedido.valor_item = produto.Valor_unitario * produto.qtd_estoque
from produto , item_pedido
where (item_pedido.cod_produto = produto.cod_produto)
drop table item_pedido
select p.descricao, p.Valor_unitario, p.qtd_estoque, i.valor_item
from item_pedido i
inner join produto p
on i.cod_produto = p.cod_produto
/*Altere a tabela item_pedido acrescentando o novo atributo valor_item do tipo
Money. Atualize os itens de pedidos cadastrados: o valor do item deve
receber o resultado da quantidade multiplicada pelo
valor unitário do produto. */
/* Criando a procedure*/
create procedure sp_atualizavaloritem as
begin
declare @cod_produto int
declare cursor_itens cursor
local
for select cod_produto
from item_pedido
open cursor_itens
fetch next from cursor_itens
into @cod_produto
while @@FETCH_STATUS = 0
begin
update item_pedido
set valor_item = quantidade *(select Valor_unitario
from produto
where cod_produto = @cod_produto)
where current of cursor_itens
fetch next from cursor_itens into @cod_produto
end
close cursor_itens
deallocate cursor_itens
end
exec sp_atualizavaloritem
select *from item_pedido
drop procedure sp_atualizavaloritem
create procedure Proc_InsereItensPedido(
@num_pedido int,
@cod_produto int,
@quantidade int ) as
begin
declare @estoque int;
if (@quantidade = 0)
print ('Quantidade inválida. Pedido não registrado');
else
begin
if((select count(cod_produto)
from produto
where cod_produto = @cod_produto) <= 0)
print ('Produto não cadastrado!!!')
else
begin
set @estoque = (select qtd_estoque
from produto
where cod_produto = @cod_produto)
if(@quantidade > @estoque)
print('Estoque Insuficiente')
else
begin
insert into item_pedido
(num_pedido, cod_produto, quantidade, valor_item)
values
(@num_pedido, @cod_produto, @quantidade,
(select @quantidade * Valor_unitario
from produto
where cod_produto = @cod_produto));
update produto
set qtd_estoque = qtd_estoque - @quantidade
where cod_produto = @cod_produto;
if ((select qtd_estoque
from produto
where cod_produto = @cod_produto) = 0)
print ('Produto Esgotado!!!')
end
end
end
end
drop procedure Proc_InsereItensPedido
exec Proc_InsereItensPedido
@num_pedido = 20,
@cod_produto = 2 ,
@quantidade = 50
select *from produto
/*1. Crie a função ValorPedido para retornar o valor de um pedido.
A função tem um parâmetro de entrada que é o número do pedido e
retorna o valor do pedido. O valor do pedido é a soma dos valores
dos itens daquele pedido (usar a função sum()). */
create function ValorPedido (@num_pedido int)
returns money as
begin
return(select sum(valor_item)
from item_pedido
where num_pedido = @num_pedido)
end
select dbo.ValorPedido (1)
select *from item_pedido
drop function ValorPedido
/*2. Construa a função PrimeiroNome. A função deve retornar apenas
o primeiro nome de um nome completo fornecido como parâmetro
(considere que o separador do primeiro nome é ' ').
Usando a função PrimeiroNome, recupere os nomes dos
Clientes da base de dados Pedido. */
select nome_cliente from cliente
select * from pedido
create function PrimeiroNome (@nome varchar(50))
returns varchar(50) as
begin
declare @ind int,
@novo_nome varchar(50)
set @ind = 1
while (@ind <= DATALENGTH(@nome) and
(substring(@nome, @ind, 1) <> ' '))
begin
set @novo_nome = CONCAT(@novo_nome, (substring(@nome, @ind, 1)))
set @ind = @ind +1
end
return @novo_nome
end
select dbo.PrimeiroNome (nome_cliente) from cliente
-- Crie a função ValorPedido para retornar o valor de um pedido.
-- A função tem um parâmetro de entrada que é o número do pedido e
-- retorna o valor do pedido.
create function valor_pedido (@num_ped int)
returns money as
begin
return (select sum(valor_item)
from item_pedido
where num_ped = @num_ped)
end
select nome_cli from cliente
select concat(nome_cli, 'abc') from cliente
-- Construa a função PrimeiroNome.
drop function primeiro_nome
create function primeiro_nome (@nome varchar(120))
returns varchar(120) as
begin
declare @ind int,
@novo_nome varchar(120)
set @ind = 1
while (@ind <= datalength(@nome) and
(substring(@nome, @ind, 1) <> ' '))
begin
set @novo_nome = concat(@novo_nome, (substring(@nome, @ind, 1)))
set @ind = @ind+1
end
return @novo_nome
end
select dbo.primeiro_nome(nome_cli) from cliente
-- Construa a função FormataCEP.
drop function formatacep
create function formatacep (@cep varchar(10))
returns varchar(10) as
begin
declare @ind int,
@formatado varchar(10)
set @ind = 1
while ((@ind <= datalength(@cep)) and (@ind <= 10))
begin
if (substring(@cep, @ind, 1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'))
set @formatado = concat(@formatado, (substring(@cep, @ind, 1)))
if (@ind = 2)
set @formatado = concat(@formatado,'.')
else
begin
if (@ind = 5)
set @formatado = concat(@formatado,'-')
end
set @ind = @ind+1
end
if (datalength(@formatado) <> 10)
set @formatado = 'erro'
return @formatado
end
select dbo.FormataCEP(cep) from cliente
select * from cliente
-- Construa a função FormataCPF
drop function formatacpf
create function formatacpf (@cpf varchar(14))
returns varchar(14) as
begin
declare @ind int,
@formatado varchar(14)
set @ind = 1
while ((@ind <= datalength(@cpf)) and (@ind <= 14))
begin
if (substring(@cpf, @ind, 1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'))
set @formatado = concat(@formatado, (substring(@cpf, @ind, 1)))
if (@ind = 3)
set @formatado = concat(@formatado,'.')
else
begin
if (@ind = 6)
set @formatado = concat(@formatado,'.')
else
begin
if (@ind = 9)
set @formatado = concat(@formatado, '-')
end
end
set @ind = @ind+1
end
if (datalength(@formatado) <> 14)
set @formatado = 'erro'
return @formatado
end
select dbo.formatacpf(cpf) from cliente
GO
USE PEDIDO
GO
--Verificar qual idioma está configurado
Select @@langid, @@language
GO
--Verificar quais idiomas o MSSQL dá suporte
Select * From sys.syslanguages
Exec sp_helplanguage
GO
--Verificar o Collate (Codificação de caracteres) e idioma do banco
EXEC sp_helpdb
GO
--Alterar o Collate depois de criado
ALTER DATABASE PEDIDO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE PEDIDO COLLATE Latin1_General_CI_AI
ALTER DATABASE PEDIDO SET MULTI_USER
GO
--Alterar o idioma para inglês, pois os inserts abaixo
--estão no padrão mdy
SET LANGUAGE 'us_english'
GO
create table cliente (
Cod_cliente int
CONSTRAINT PK_CODCLIENTE Primary Key,
Nome_cliente varchar(50) not null,
Endereco varchar (50),
Cidade varchar(20),
Cep char(8),
UF char(2),
CPF_CGC char(14),
IE char(12))
drop table cliente
select *from cliente
GO
create table vendedor (
cod_vendedor int,
nome_vendedor varchar (50) not null,
faixa_comissao char(1),
salario_fixo money,
CONSTRAINT PK_VENDEDOR PRIMARY KEY (COD_VENDEDOR))
drop table vendedor
select *from vendedor
GO
create table pedido (
Num_pedido int,
data_compra datetime,
data_entrega datetime,
cod_cliente int not null,
cod_vendedor int not null ,
FOREIGN KEY (COD_CLIENTE) REFERENCES CLIENTE (COD_CLIENTE),
CONSTRAINT FK_VENDEDOR FOREIGN KEY(COD_VENDEDOR) REFERENCES VENDEDOR,
CONSTRAINT PK_PEDIDO PRIMARY KEY (NUM_PEDIDO)
)
drop table pedido
select *from pedido
GO
create table produto (
cod_produto int
CONSTRAINT PK_CODPRODUTO Primary Key,
descricao varchar(20),
Unidade char(2),
Valor_unitario money)
drop table produto
select *from produto
GO
create table item_pedido (
num_pedido int references pedido,
cod_produto int references produto,
quantidade int
CONSTRAINT PK_PEDIDOPRODUTO primary key (num_pedido, cod_produto) )
drop table item_pedido
select * from item_pedido
GO
insert into cliente values (0001,'Marcelo Cruz' ,'Rua Brasil, 35' ,'Jundiaí' ,13124579,'SP','7464563762','53425364');
insert into cliente values (0002,'Jose da Silva' ,'Rua Argentina, 23' ,'Buenos Aires' ,45876548,'TY','4587412637','452145285');
insert into cliente values (0003,'Mia Bini' ,'Alameda cavalcante, 12' ,'Pindamonhangaba',45269887,'SP','1542845798','687286464');
insert into cliente values (0004,'Nelson Nerd' ,'Rua das Torres, 750' ,'Araraquara' ,25412563,'SP','654987985','984651698');
insert into cliente values (0005,'Rogério Silva' ,'Avenida Castro Alves, 5500' ,'Queluz' ,68416845,'ES','0654987878','984165494');
insert into cliente values (0006,'Vinicius Beto' ,'Rua Brasil, 42' ,'São Paulo' ,15478452,'SP','0640968109','981651988');
insert into cliente values (0007,'Cândido Tavares' ,'Rua da Abolição, 350' ,'Jundiaí' ,13215468,'SP','0065498987','954165214');
insert into cliente values (0008,'Uioswaldo Neto' ,'Rua Avestruz Limpa, 80' ,'Araraquara' ,16789487,'PR','987654613','774465546');
insert into cliente values (0009,'Ana Benedetti' ,'Rua Antônio Limones, 90' ,'Jundiaí' ,13215045,'SP','6549879495','665421634');
insert into cliente values (0010,'Bruno Mezenga' ,'Rua Major Mário Lacerda, 1340' ,'Pindamonhangaba',22400485,'SP','6549684851','968762461');
insert into cliente values (0011,'Silvia Popovic' ,'Alameda Castro, 48' ,'Jundiaí' ,13215064,'SP','6324161878','416516547');
insert into cliente values (0012,'Fausto Silva' ,'Avenida dos Ferroviários, 2000','Queluz' ,65498719,'SP','6547841352','654849514');
insert into cliente values (0013,'Renata Polire' ,'Rua do Retiro, 5400' ,'São Paulo' ,65491878,'PR','24165798787','165465787');
insert into cliente values (0014,'Adamastor Tio' ,'Rua Entre Postes, 366' ,'Jundiaí' ,13254687,'ES','99887958416','6321654657');
insert into cliente values (0015,'Luke Skywalker' ,'Rua Olivia Terceira, 240' ,'São Paulo' ,68423546,'SP','65495819518','3654621657');
insert into cliente values (0016,'Plínio Castro Mendes','Rua Padre Silveira, 750' ,'Taubaté' ,26549879,'SP','65498498425','6598798554');
insert into cliente values (0017,'Oswaldo Benegripe' ,'Rua Daniel Mantovani, 987' ,'São Paulo' ,32416579,'PR','65498798415','6546549846');
insert into cliente values (0018,'Adamastor Filho' ,'Avenida Antônio Ozanam, 5543' ,'Jundiaí' ,13245124,'SP','44564687951','3621654477');
insert into cliente values (0019,'Karina Bonamiga' ,'Rua dos Expedicionários, 750' ,'Jundiaí' ,13244457,'SP','44798946546','9854654445');
insert into cliente values (0020,'Vanderlei Luís Silva','Rua Brasil, 75' ,'São Paulo' ,11245788,'SP','65498495158','6549874988');
select * from cliente
insert into vendedor values (0001,'Sandra Bullock' ,'A', 200);
insert into vendedor values (0002,'Keanu Reeves' ,'A', 500);
insert into vendedor values (0003,'Matt Damon' ,'B',1000);
insert into vendedor values (0004,'Alicia Silverstone','C', 400);
insert into vendedor values (0005,'Marilyn Monroe' ,'B', 100);
insert into vendedor values (0006,'Ben Affleck' ,'A',4000);
insert into vendedor values (0007,'Anna Nicole Smith' ,'B',2200);
insert into vendedor values (0008,'Anna Kournikova' ,'B', 600);
insert into vendedor values (0009,'Paulo Zulu' ,'C', 800);
insert into vendedor values (0010,'Meg Ryan' ,'A',1500);
insert into vendedor values (0011,'Tom Cruise' ,'B',3200);
insert into vendedor values (0012,'Will Smith' ,'C', 30);
insert into vendedor values (0013,'Whitney Houston' ,'A',1000);
insert into vendedor values (0014,'Nicole Kidman' ,'B',2200);
insert into vendedor values (0015,'Nhá Barbina' ,'A', 200);
insert into vendedor values (0016,'Demi Moore' ,'C',2000);
insert into vendedor values (0017,'Sharon Stone' ,'A',7000);
insert into vendedor values (0018,'Carolina Dieckmann','D',1200);
insert into vendedor values (0019,'Giovana Antonelli' ,'A', 700);
insert into vendedor values (0020,'Kevin Costner' ,'B', 600);
select * from vendedor
insert into produto values (0001,'Manteiga diet','UN', 3.30);
insert into produto values (0002,'Toddynho' ,'UN', 1.23);
insert into produto values (0003,'Lapis de cor' ,'CX', 0.56);
insert into produto values (0004,'Barbeador' ,'UN', 10.50);
insert into produto values (0005,'Leite' ,'LT', 70.20);
insert into produto values (0006,'Danone' ,'LT', 50.43);
insert into produto values (0007,'Alcatra' ,'KG',140.98);
insert into produto values (0008,'Pinga' ,'UN',110.50);
insert into produto values (0009,'Alface' ,'UN',2.00);
insert into produto values (0010,'Caderno' ,'UN',3.00);
insert into produto values (0011,'Óculos' ,'UN',4.00);
insert into produto values (0012,'Cinto' ,'UN',81.50);
insert into produto values (0013,'Camisa' ,'UN', 0.50);
insert into produto values (0014,'Sapato' ,'UN', 10.50);
insert into produto values (0015,'Relógio' ,'UN', 50.00);
insert into produto values (0016,'Escada' ,'MT', 90.75);
insert into produto values (0017,'Porta' ,'UN',210.50);
insert into produto values (0018,'Impressora' ,'UN',610.50);
insert into produto values (0019,'Água' ,'LT', 80.50);
insert into produto values (0020,'Telefone' ,'UN', 60.00);
select * from produto
insert into pedido values (0001,'01/09/11', '01/19/11',0002,0003);
insert into pedido values (0002,'05/08/11','10/18/11',0002,0001);
insert into pedido values (0003,'02/06/11','02/16/11',0004,0003);
insert into pedido values (0004,'10/07/11','10/27/11',0010,0015);
insert into pedido values (0005,'08/05/11','08/25/11',0017,0013);
insert into pedido values (0006,'05/04/11','05/14/11',0001,0008);
insert into pedido values (0007,'09/09/11','09/12/11',0006,0009);
insert into pedido values (0008,'05/07/11','05/08/11',0018,0013);
insert into pedido values (0009,'06/02/11','06/12/11',0013,0017);
insert into pedido values (0010,'05/01/11','05/21/11',0010,0009);
insert into pedido values (0011,'05/03/05','05/05/11',0015,0014);
insert into pedido values (0012,'08/11/11','09/09/11',0007,0006);
insert into pedido values (0013,'02/11/11','05/03/11',0012,0018);
insert into pedido values (0014,'08/11/11','08/12/11',0009,0004);
insert into pedido values (0015,'08/09/11','08/11/11',0014,0013);
insert into pedido values (0016,'01/06/04','01/06/04',0007,0003);
insert into pedido values (0017,'01/04/11','01/14/11',0011,0014);
insert into pedido values (0018,'06/07/11','06/08/11',0007,0015);
insert into pedido values (0019,'02/06/11','02/09/11',0014,0016);
insert into pedido values (0020,'10/07/11','10/09/11',0018,0017);
select * from pedido
GO
insert into item_pedido values (0001,0002, 13);
insert into item_pedido values (0001,0003, 3);
insert into item_pedido values (0001,0001, 6);
insert into item_pedido values (0002,0002, 8);
insert into item_pedido values (0002,0001, 50);
insert into item_pedido values (0002,0004, 80);
insert into item_pedido values (0003,0001, 66);
insert into item_pedido values (0004,0003,200);
insert into item_pedido values (0004,0014, 40);
insert into item_pedido values (0004,0018, 50);
insert into item_pedido values (0004,0013, 40);
insert into item_pedido values (0004,0007, 3);
insert into item_pedido values (0005,0003, 6);
insert into item_pedido values (0005,0006, 8);
insert into item_pedido values (0005,0013, 6);
insert into item_pedido values (0006,0017, 20);
insert into item_pedido values (0006,0016, 50);
insert into item_pedido values (0006,0014,100);
insert into item_pedido values (0007,0006,150);
insert into item_pedido values (0008,0010,120);
insert into item_pedido values (0009,0001, 40);
insert into item_pedido values (0009,0003, 69);
insert into item_pedido values (0009,0016, 45);
insert into item_pedido values (0009,0019,120);
select * from item_pedido
SET LANGUAGE 'Português (Brasil)'
create trigger trg_NovoCliente
on cliente
for insert as
begin
declare
@Nome_cliente varchar(50),
@Endereco varchar (50),
@Cidade varchar(20),
@Cep char(8),
@UF char(2),
@CPF_CGC char(14),
@IE char(12)
select @Nome_cliente = Nome_cliente from inserted
select @Endereco = Endereco from inserted
select @Cidade = Cidade from inserted
select @Cep = Cep from inserted
select @UF = UF from inserted
select @CPF_CGC = CPF_CGC from inserted
select @IE = IE from inserted
if @UF = 'RJ' or @UF ='ES'
print 'Inclusão Cancelada !!!'
else
begin
print 'Inclusão Realizada com Sucesso !!!'
INSERT INTO cliente (Nome_cliente, Endereco, Cidade, Cep, UF,CPF_CGC, IE)
VALUES ( @Nome_cliente, @Endereco, @Cidade, @Cep, @UF, @CPF_CGC, @IE )
end;
end;
drop trigger trg_NovoCliente
create trigger trg_LogVendedor
on vendedor
for update as
begin
declare
@cod_vendedor int,
@salario_fixo money,
@faixa_comissao char(1)
set @cod_vendedor = select cod_vendedor from inserted
set @salario_fixo = select salario_fixo from deleted
set @faixa_comissao = select faixa_comissao from deleted
insert into trg_LogVendedor
(usuario, data_alteracao, cod_vendedor, salario_fixo, faixa_comissao)
values(current_user, getdate(),@cod_vendedor, @salario_fixo, @faixa_comissao)
end;
drop trigger trg_LogVendedor
select *from vendedor
create table historico_preco(
codigo int primary key identity,
cod_produto int,
valor_unitario_antigo money,
data_alteracao datetime)
drop table historico_preco
create trigger trg_HistoricoPreco
on Produto
for update as
begin
insert into historico_preco
(codigo, cod_produto, valor_unitario_antigo, data_alteracao)
values((
end;
drop trigger trg_HistoricoPreco
/*Exercício 1:
Faça um procedimento que exclua um cliente cujo código é passado como parâmetro e todos os seus pedidos,
retornando a quantidade de pedidos que o cliente tinha.*/
CREATE PROCEDURE sp_exclui_clientes
@codigo_cli INT,
@qtde_pedidos INT OUTPUT AS
BEGIN
SELECT @qtde_pedidos = COUNT(*) FROM pedido
WHERE cod_cliente = @codigo_cli;
DELETE FROM item_pedido
WHERE num_pedido IN (select num_pedido from pedido
WHERE cod_cliente = @codigo_cli);
DELETE FROM pedido
WHERE cod_cliente = @codigo_cli;
DELETE FROM cliente
WHERE cod_cliente = @codigo_cli;
END;
DECLARE @cod_cli INT
DECLARE @qt_ped INT
EXECUTE sp_exclui_clientes @codigo_cli=20,
@qtde_pedidos = @qt_ped OUTPUT
PRINT 'qtde pedidos '
PRINT @qt_ped
BEGIN
DECLARE @qt_p int
EXECUTE sp_exclui_clientes 1, @qt_p OUTPUT;
PRINT 'qtde pedidos '
PRINT @qt_p
END
drop procedure sp_exclui_clientes
/*Exercício 2:
Criar uma stored procedure que atualize os salários dos vendedores com faixa de comissão = ´B´ :
A procedure deverá receber como parâmetro de entrada um código que definirá a porcentagem de aumento:
Se Código = 2, aumentar salário_fixo em 20%
Se Código = 3, aumentar salário_fixo em 30%
Se Código = 4 aumentar salário_fixo em 40%
Se Código tiver um valor diferente de 2, 3 e 4, o salário deverá permanecer o mesmo*/
CREATE PROCEDURE SP_ATUALIZA_SALARIO
@CODIGO INT AS
BEGIN
IF (@CODIGO = 2)
UPDATE VENDEDOR SET SALARIO_FIXO = SALARIO_FIXO * 1.2
WHERE FAIXA_COMISSAO = 'B'
ELSE
IF (@CODIGO = 3) UPDATE VENDEDOR SET SALARIO_FIXO = SALARIO_FIXO * 1.3
WHERE FAIXA_COMISSAO = 'B'
ELSE
IF (@CODIGO = 4) UPDATE VENDEDOR SET SALARIO_FIXO = SALARIO_FIXO * 1.4
WHERE FAIXA_COMISSAO = 'B';
END;
EXEC SP_ATUALIZA_SALARIO 3
drop procedure SP_ATUALIZA_SALARIO
/*Para verificar o resultado na tabela Vendedor, selecionar os salários antes e depois da execução do procedimento.*/
select * from vendedor
where faixa_comissao = 'B'
/* 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. */
alter table produto add qtd_estoque int
update produto
set qtd_estoque =50
where cod_produto between 1 and 10
update produto
set qtd_estoque = 120
where cod_produto between 11 and 15
update produto
set qtd_estoque = 250
where cod_produto between 16 and 20
/* criação da procedure */
create procedure sp_desconto
(@qtd_alterada int output)
as
begin
set @qtd_alterada = (select count ( cod_produto)
from produto
where qtd_estoque >= 100)
update produto
set Valor_unitario = Valor_unitario *0.9
where qtd_estoque between 100 and 200;
update produto
set Valor_unitario = Valor_unitario *0.8
where qtd_estoque > 200;
end;
/* execução da procedure */
declare @qtd int
exec sp_desconto @qtd output
print @qtd
drop procedure sp_desconto
/*Altere a tabela item_pedido acrescentando o novo atributo valor_item do tipo
Money. Atualize os itens de pedidos cadastrados: o valor do item deve
receber o resultado da quantidade multiplicada pelo
valor unitário do produto. */
alter table item_pedido add valor_item money
update item_pedido
set item_pedido.valor_item = produto.Valor_unitario * produto.qtd_estoque
from produto , item_pedido
where (item_pedido.cod_produto = produto.cod_produto)
drop table item_pedido
select p.descricao, p.Valor_unitario, p.qtd_estoque, i.valor_item
from item_pedido i
inner join produto p
on i.cod_produto = p.cod_produto
/*Altere a tabela item_pedido acrescentando o novo atributo valor_item do tipo
Money. Atualize os itens de pedidos cadastrados: o valor do item deve
receber o resultado da quantidade multiplicada pelo
valor unitário do produto. */
/* Criando a procedure*/
create procedure sp_atualizavaloritem as
begin
declare @cod_produto int
declare cursor_itens cursor
local
for select cod_produto
from item_pedido
open cursor_itens
fetch next from cursor_itens
into @cod_produto
while @@FETCH_STATUS = 0
begin
update item_pedido
set valor_item = quantidade *(select Valor_unitario
from produto
where cod_produto = @cod_produto)
where current of cursor_itens
fetch next from cursor_itens into @cod_produto
end
close cursor_itens
deallocate cursor_itens
end
exec sp_atualizavaloritem
select *from item_pedido
drop procedure sp_atualizavaloritem
create procedure Proc_InsereItensPedido(
@num_pedido int,
@cod_produto int,
@quantidade int ) as
begin
declare @estoque int;
if (@quantidade = 0)
print ('Quantidade inválida. Pedido não registrado');
else
begin
if((select count(cod_produto)
from produto
where cod_produto = @cod_produto) <= 0)
print ('Produto não cadastrado!!!')
else
begin
set @estoque = (select qtd_estoque
from produto
where cod_produto = @cod_produto)
if(@quantidade > @estoque)
print('Estoque Insuficiente')
else
begin
insert into item_pedido
(num_pedido, cod_produto, quantidade, valor_item)
values
(@num_pedido, @cod_produto, @quantidade,
(select @quantidade * Valor_unitario
from produto
where cod_produto = @cod_produto));
update produto
set qtd_estoque = qtd_estoque - @quantidade
where cod_produto = @cod_produto;
if ((select qtd_estoque
from produto
where cod_produto = @cod_produto) = 0)
print ('Produto Esgotado!!!')
end
end
end
end
drop procedure Proc_InsereItensPedido
exec Proc_InsereItensPedido
@num_pedido = 20,
@cod_produto = 2 ,
@quantidade = 50
select *from produto
/*1. Crie a função ValorPedido para retornar o valor de um pedido.
A função tem um parâmetro de entrada que é o número do pedido e
retorna o valor do pedido. O valor do pedido é a soma dos valores
dos itens daquele pedido (usar a função sum()). */
create function ValorPedido (@num_pedido int)
returns money as
begin
return(select sum(valor_item)
from item_pedido
where num_pedido = @num_pedido)
end
select dbo.ValorPedido (1)
select *from item_pedido
drop function ValorPedido
/*2. Construa a função PrimeiroNome. A função deve retornar apenas
o primeiro nome de um nome completo fornecido como parâmetro
(considere que o separador do primeiro nome é ' ').
Usando a função PrimeiroNome, recupere os nomes dos
Clientes da base de dados Pedido. */
select nome_cliente from cliente
select * from pedido
create function PrimeiroNome (@nome varchar(50))
returns varchar(50) as
begin
declare @ind int,
@novo_nome varchar(50)
set @ind = 1
while (@ind <= DATALENGTH(@nome) and
(substring(@nome, @ind, 1) <> ' '))
begin
set @novo_nome = CONCAT(@novo_nome, (substring(@nome, @ind, 1)))
set @ind = @ind +1
end
return @novo_nome
end
select dbo.PrimeiroNome (nome_cliente) from cliente
-- Crie a função ValorPedido para retornar o valor de um pedido.
-- A função tem um parâmetro de entrada que é o número do pedido e
-- retorna o valor do pedido.
create function valor_pedido (@num_ped int)
returns money as
begin
return (select sum(valor_item)
from item_pedido
where num_ped = @num_ped)
end
select nome_cli from cliente
select concat(nome_cli, 'abc') from cliente
-- Construa a função PrimeiroNome.
drop function primeiro_nome
create function primeiro_nome (@nome varchar(120))
returns varchar(120) as
begin
declare @ind int,
@novo_nome varchar(120)
set @ind = 1
while (@ind <= datalength(@nome) and
(substring(@nome, @ind, 1) <> ' '))
begin
set @novo_nome = concat(@novo_nome, (substring(@nome, @ind, 1)))
set @ind = @ind+1
end
return @novo_nome
end
select dbo.primeiro_nome(nome_cli) from cliente
-- Construa a função FormataCEP.
drop function formatacep
create function formatacep (@cep varchar(10))
returns varchar(10) as
begin
declare @ind int,
@formatado varchar(10)
set @ind = 1
while ((@ind <= datalength(@cep)) and (@ind <= 10))
begin
if (substring(@cep, @ind, 1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'))
set @formatado = concat(@formatado, (substring(@cep, @ind, 1)))
if (@ind = 2)
set @formatado = concat(@formatado,'.')
else
begin
if (@ind = 5)
set @formatado = concat(@formatado,'-')
end
set @ind = @ind+1
end
if (datalength(@formatado) <> 10)
set @formatado = 'erro'
return @formatado
end
select dbo.FormataCEP(cep) from cliente
select * from cliente
-- Construa a função FormataCPF
drop function formatacpf
create function formatacpf (@cpf varchar(14))
returns varchar(14) as
begin
declare @ind int,
@formatado varchar(14)
set @ind = 1
while ((@ind <= datalength(@cpf)) and (@ind <= 14))
begin
if (substring(@cpf, @ind, 1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'))
set @formatado = concat(@formatado, (substring(@cpf, @ind, 1)))
if (@ind = 3)
set @formatado = concat(@formatado,'.')
else
begin
if (@ind = 6)
set @formatado = concat(@formatado,'.')
else
begin
if (@ind = 9)
set @formatado = concat(@formatado, '-')
end
end
set @ind = @ind+1
end
if (datalength(@formatado) <> 14)
set @formatado = 'erro'
return @formatado
end
select dbo.formatacpf(cpf) from cliente
Nenhum comentário:
Postar um comentário