SQL Exercício de Funções Banco de Dados Pedidos

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

Criar Banco de Dados Imobiliária Aluguel

Vamos Criar um “Bando de Dados” para uma imobiliária fazer o controle dos Imóveis para Aluguel. 
Será necessário:

•Guardar informações do Proprietário :

Nome, endereço, telefone e e-mail.

•Guardar informações do Corretor responsável :

Nome, endereço, telefone e e-mail.

•Guardar informações do inquilino:

Nome, endereço, telefone e e-mail.

•Guardar informações do imóvel:

Descrição do Imóvel, valor do aluguel, endereço, bairro, condição se alugado ou disponível.

No  Modelo Entidade-Relacionamento (MER) anterior temos as seguintes entidades e relacionamentos:

•Proprietário ,”contata”, Corretor (um proprietário pode contatar vários corretores e um corretor pode ser contatado por vários proprietários).

•Corretor, ”atende”, Inquilino (um corretor pode atender vários inquilinos e um inquilino pode ser atendido por vários corretores).

•Inquilino, ”aluga”, Imóvel (um inquilino aluga um imóvel e um imóvel pode ser alugado por vários inquilinos).

•Proprietário, ”possui”, Imóvel (um proprietário possui vários imóveis e um imóvel pertence a apenas um proprietário).

Modelo Relacional Aluguel

•tabela proprietário(codprop, nome, ender,tel,email), codprop é chave primária.

•tabela corretor(codcor, nome, ender,tel,email), codcor é chave primária.

•tabela inquilino(codinq, nome, ender,tel,email), onde codinq é chave primária.

•tabela imovel(codimov, descricao, valor, ender, bairo, condicao), ond codimov é chave primária

•tabela aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim), ond codalug é chave primaria e;

     codinc é chave estrangeira da tabela inquilino, 

     codimov é chave estrangeira da tabela imovel,

     codprop é chave estrangeira da tabela proprietario,

     codcor é chave estrangeira da tabela corretor.

/* Criar Banco de Dados Imobiliária Aluguel*/
create database DB_Aluguel

/* Altera o contexto de banco de dados, selcionar para ser utilizado  DB_Imobiliaria_Aluguél*/
use DB_Aluguel

/* criar tabela proprietário(codprop, nome, ender,tel,email), onde codprop é chave primária*/
create table proprietario(
codprop char(3) primary key,
nome varchar(50) not null,
ender varchar(130)not null,
tel char(14),
email varchar(100))

drop table proprietario

/* criar tabela corretor(codcor, nome, ender,tel,email), onde codcor é chave primária*/
create table corretor(
codcor char(3) primary key,
nome varchar(50) not null,
ender varchar(130)not null,
tel char(14),
email varchar(100))

drop table corretor

/* criar tabela inquilino(codinq, nome, ender,tel,email), onde codinq é chave primária*/
create table inquilino(
codinq char(3) primary key,
nome varchar(50) not null,
ender varchar(130)not null,
tel char(14),
email varchar(100))

drop table inquilino

/* criar tabela imovel(codimov,descricao,valor,ender,bairo,condicao), ond codimov é chave primária*/
create table imovel(
codimov char(3) primary key,
descricao varchar(100),
valor money,
ender varchar(120),
bairo varchar(50),
condicao varchar(30))

drop table imovel

/* criar tabela aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim), ond codalug é chave primaria e
codinc é chave estrangeira da tabela inquilino,
codimov é chave estrangeira da tabela imovel,
codprop é chave estrangeira da tabela proprietario,
codcor é chave estrangeira da tabela corretor. */
create table aluguel(
codalug varchar(4) primary key,
codinc char(3)FOREIGN KEY REFERENCES inquilino(codinq),
codimov char(3)FOREIGN KEY REFERENCES imovel(codimov),
codprop char(3)FOREIGN KEY REFERENCES proprietario(codprop),
codcor char(3)FOREIGN KEY REFERENCES corretor(codcor),
dataini date,
datafim date)

drop table aluguel

/* inserir dados na tabela proprietário(codprop, nome, ender,tel,email), onde codprop é chave primária*/
insert into proprietario(codprop, nome, ender,tel,email)
values
('P01','Antonio Mauel','Rua Maria Alzira Leal 123','1999345245','antonio@gmail.com'),
('P02','Milena Rafaela Nunes','AV Paulista 100','1993543765','milnunes@uol.com.br'),
('P03','Neide Alpino','Rua das Amelias 32','194433345','neidealpino@ig.com.br'),
('P04','Patricia Rufino','Rua Jose Bonifacio 1050','1997899923','patruf@rh.com.br'),
('P05','Jose Pascoal','Rua Curitiba 503','1997845637','jp@all.com'),
('P06','Felipe Maia','Rua 10 23','1997895534','fmaia@ig.com.br'),
('P07','Joao Santo','Rua 9 de julho 34','1999345245','jsanto@gmail.com'),
('P08','Walter Lopes','Rua das Rosas','1993543765','wlopes@uol.com.br'),
('P09','Cristiano Sulato','Rua Primavera 121','194433345','crissulato@ig.com.br'),
('P10','Maria Silveira','Rua Maracana 64','1997899923','msilveira@rh.com.br'),
('P11','Plino Milano','Rua Constantino 234','1997845637','plinim@all.com'),
('P12','Rosangela Bagers','Av Brasil 1000','1997895534','robarges@ig.com.br')


/* inserir dados na tabela corretor(codcor, nome, ender,tel,email), onde codcor é chave primária*/
insert into corretor(codcor, nome, ender,tel,email)
values
('100', 'Almir Lopez', 'Rua das Rosas 123','199399242','amlopez@hotmail.com'),
('200', 'Sebastiao Goncalo', 'Rua Pernambuco 344','1996453399','gonsalo@imobiliaria.com'),
('300', 'Beatriz Maria', 'AV Sao Joao 1232','9833823342','biam@imobiliaria.com')


/* inserir dados na tabela inquilino(codinq, nome, ender,tel,email), onde codinq é chave primária*/
insert into inquilino(codinq, nome, ender,tel,email)
values
('I01', 'Julio Simao', 'Rua Pitanga 34','199938456','julioS@fds.com'),
('I02', 'Milton Soares', 'Av Laguna 543','198790534','milsoares@ere.com'),
('I03', 'Marcia Rosatto', 'Rua Jose de Alencar 45','198876433','marciarosato@gmail.com'),
('I04', 'Paulo Gomes', 'Rua Joao Lima 894','195453332','paulog@all.com.br'),
('I05', 'Josefa Fernandes', 'Rua Araguari 76','198890432','josefaf@outlook.com'),
('I06', 'Gilmar Mendes', 'Rua Monte Videl 89','1988845321','gilmendes@gmailcom'),
('I07', 'Cristina Faustino', 'Rua Cantinga 122','1978363221','cris@gmail.com'),
('I08', 'Sivio Castilho', 'Rua Guanabara 110','198893421','silvioc@sp.com.br'),
('I09', 'Carlos Justino', 'Rua Marinara','19889789','carlosjus@ig.com.br'),
('I10', 'Samuel Souza', 'Rua Araguari 114','1978363221','samuels@gmail.com'),
('I11', 'Otavio Brandino', 'Rua Sao Pedro 20','198893421','otbr@sp.com.br'),
('I12', 'Naiara Lima', 'Av 15 de Novembro 10','19889789','nailima@ig.com.br')

/* inserir dados na tabela imovel(codimov,descricao,valor,ender,bairo,condicao, codprop), ond codimov é chave primária */
insert into imovel(codimov,descricao,valor,ender,bairo,condicao)
values
('C01','Apartamento 2 quartos','780','Rua Jurunas 455','Vl Maria','Disponivel'),
('C02','Casa 2 quartos 1 suite','1250','Rua Silveira Bueno 53','Jd Das Rosas','Disponivel'),
('C03','Casa 1 quartos 1 suite','950','AV Brasil 44','Centro','Disponivel'),
('C04','Edicula 1 quarto sem garagem','500','Rua Bahia 21','Jd Das Rosas','Disponivel'),
('C05','Apartamento 1 suite','780','Rua Constantino 231','Vila Sao Judas','Disponivel'),
('C06','Casa 3 quartos','890','Rua Santa Cruz 331','Condomino Azul','Disponivel'),
('C07','Casa 2 quartos','690','Av Guimaraes','Jd Brasilia','Disponivel'),
('C08','Casa 3 quartos 1 suite','1300','Rua Lindor Silveira 544','Vila Cascata','Disponivel'),
('C09','Casa 1 quartos 1 suite','780','Rua 22 15','Jd Paulista','Disponivel'),
('C10','Edicula 2 quarto ','750','Rua Laranjeiras 98','Pq Sao Luis','Disponivel'),
('C11','Apartamento 2 quartos 1 suite','1100','Rua Constantino 231','Vila Sao Judas','Disponivel'),
('C12','Casa 2 quartos','700','Rua Palmital 44','Vila Boa Esperanca','Disponivel')

/* inserir dados na tabela aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim), ond codalug é chave primaria e
codinc é chave estrangeira da tabela inquilino,
codimov é chave estrangeira da tabela imovel,
codprop é chave estrangeira da tabela proprietario,
codcor é chave estrangeira da tabela corretor. */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
/*('AA01',’I01',’C01', ’P01','100','20150812','20170810'),
('AA02',’I02',’C02', ’P02',’200','20161025','20171025'),
('AA03',’I03',’C03', ’P03',’300','20161204','20171204'),
('AA04',’I04',’C04', ’P04',’100','20160722','20170722'),
('AA05',’I05',’C05', ’P05','200','20160915','20170915') */


/* Criar uma View onde contenha Descrição do imovel, bairro do imovel, valor do aluguél dos imóveis
Disponivel para Alugar */
create view vw_Disponivel as
select  I.descricao, I.bairo, I.valor, I.condicao
from imovel I
where condicao = 'Disponivel'

select * from vw_Disponivel
drop view vw_Disponivel

/* Criar uma View com nomes e telefones de contatos do Corretor, do Inquilino
e do Proprietário, o bairro onde fica o Imoveis que estão alugados */
create view vw_Alugado as
select C.nome as [Nome Corretor], C.tel as [Tel Corretor],
I.nome as [Nome Inquilino], I.tel as [Tel Inquilino], P.nome as [Nome Propietario], P.tel as [Tel Proprietario],
M.bairo, M.condicao
from aluguel A
inner join corretor C
on C.codcor = A.codcor
inner join proprietario P
on P.codprop = A.codprop
inner join  inquilino I
on I.codinq = A.codinc
inner join  imovel M
on M.codimov = A.codimov

select * from vw_Alugado
drop view vw_Alugado

/* Criar uma View que calcula quantidade e valor dos Imoveis Alugados
por nome do Corretor e calcular 10% da comissao */
create view vw_CorrAluga as
select C.nome as[Corretor],
count(I.valor) as [Quantidade de Aluguel] ,
sum(I.valor) as [Soma dos Alugueis R$],
str(sum(I.valor * 0.10),6,2) as [Comissao R$]
from corretor C
inner join aluguel A
on C.codcor = A.codcor
inner join imovel I
on I.codimov = A.codimov
group by C.nome

select * from vw_CorrAluga
drop view vw_CorrAluga

/* Trigger de Insert que dispara a condição do Imovel "Disponível" para "Alugado" */
create trigger trg_Aluga
on aluguel
for insert as
begin
declare
 @condicao varchar(30),
 @codimov  char(3);
    select @codimov = codimov from inserted
select @condicao = condicao from  imovel
update imovel
    set condicao = 'Alugado'
where  @codimov = codimov;

print 'Imovel Alugado !!!'
end;

drop trigger trg_Aluga

/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA01','I01','C01', 'P01','100','20150812','20170810')

/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA02','I02','C02', 'P02','200','20161025','20171025')

select * from vw_Disponivel
select * from vw_Alugado
select * from vw_CorrAluga


/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA03','I03','C03', 'P03','300','20161204','20171204')

/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA04','I04','C04', 'P04','100','20160722','20170722')

/* inserir imovel na tabela aluguel */
insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA05','I05','C05', 'P05','200','20160915','20170915')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA06','I06','C06', 'P06','100','20150812','20170810')

select * from vw_Disponivel
select * from vw_Alugado
select * from vw_CorrAluga

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA07','I07','C07', 'P07','200','20161025','20171025')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA08','I08','C08', 'P08','300','20161204','20171204')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA09','I09','C09', 'P09','100','20160722','20170722')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA10','I10','C10', 'P10','200','20160915','20170915')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA11','I11','C11', 'P11','100','20160722','20170722')

insert into aluguel(codalug, codinc, codimov, codprop, codcor, dataini, datafim)
values
('AA12','I12','C12', 'P12','200','20160915','20170915')

select * from vw_Disponivel
select * from vw_Alugado
select * from vw_CorrAluga

Exercício Banco de Dados Agencia de Turismo

Construir o modelo entidade-relacionamento e descrever o mapeamento para o modelo relacional.

Agência de Turismo

 Deseja-se criar um BD para uma agência de turismo, contendo informações sobre recursos oferecidos pelas cidades que fazem parte da programação de turismo da agência. As informações a serem mantidas sobre cada cidade referem-se a hotéis, restaurantes e pontos turísticos. 

  Sobre os hotéis que a cidade possui deseja-se guardar o código, o nome,  o endereço,  a categoria, os tipos de quartos que os formam, o número dos quartos e o valor da diária de acordo com o tipo do quarto. 

 Sobre cada cidade deve-se armazenar seu nome, seu estado e a população. Além disso, quando uma nova cidade é cadastrada no banco de dados da agência, um código é a ela oferecido. 

 Cada restaurante da cidade possui um código que o identifica, um nome, um endereço e o tipo de sua categoria. Além disso, um restaurante pode pertencer a um hotel e um hotel somente pode ser associado a um restaurante. 

  Diferentes pontos turísticos da cidade estão cadastrados no sistema: igrejas, casas de show e museus. A agência de turismo somente trabalha com estes três tipos de pontos turísticos.  Nenhum outro é possível. Além da descrição e do endereço, igrejas devem possuir como característica a data e o estilo de construção. Já casas de show devem armazenar o horário de abertura e o dia de fechamento, além da descrição e do seu endereço. Finalmente, os museus devem armazenar o seu endereço, descrição, data de fundação e número de salas. Um museu pode ter sido fundado por vários fundadores.  Para estes, deve-se armazenar o seu nome, a data de nascimento e a data da morte (se houver), a nacionalidade e a atividade profissional que desenvolvia.  Além disso, um mesmo fundador pode ter fundado vários museus. Quando qualquer ponto turístico é cadastrado no sistema, ele também recebe um código que o identifica. O mesmo é válido para fundadores.  

 Finalmente, casas de show podem possuir restaurante. Quando o cliente da agência reserva um passeio para uma casa de show, ele já sabe se essa possui restaurante e qual o preço médio da refeição, além da especialidade.  Dentro de uma casa de show, apenas um único restaurante pode existir.     

create database DB_Agencia_Turismo

use DB_Agencia_Turismo

create table cidade(
nome_cidade varchar(50),
estado varchar (20),
populacao int,
cod_cidade int primary key)

create table hotel(
cod_hotel int primary key,
nome_hotel varchar(50),
endereco varchar(100),
categoria varchar(10))

create table tipo_quarto(
cod_hotel int foreign key references hotel(cod_hotel),
cod_tipo int,
primary key(cod_hotel,cod_tipo),
num_quarto int,
valor_diaria money,
decricao varchar(50))

create table restaurante(
cod_restaurante int primary key,
cod_cidade int foreign key references cidade(cod_cidade),
cod_hotel int foreign key references hotel(cod_hotel),
nome varchar(50),
endereco varchar(100),
categoria varchar(30))

create table ponto_turistico(
cod_ponto_turistico int,
cod_cidade int foreign key references cidade(cod_cidade),
primary key(cod_ponto_turistico),
descricao varchar(50),
endereco varchar (100))

create table igreja(
cod_igreja int foreign key references ponto_turistico(cod_ponto_turistico),
data_fundacao date,
estilo varchar(30)
primary key (cod_igreja))

create table museu(
cod_museu int foreign key references ponto_turistico(cod_ponto_turistico),
data_fund date,
numero_salas int,
primary key (cod_museu))

create table casa_show(
cod_casa_show int foreign key references ponto_turistico(cod_ponto_turistico),
horario time,
dias_fechado date,
cod_restaurante int foreign key references restaurante(cod_restaurante),
primary key (cod_casa_show))

create table fundador(
cod_fundador int,
nome varchar(50),
data_nasc date,
data_obto date,
nacionalidade varchar(30),
atividade varchar(30),
primary key (cod_fundador))

create table fundacao(
cod_museu int foreign key references museu(cod_museu),
cod_fundador int foreign key references fundador(cod_fundador),
primary key (cod_museu, cod_fundador))

insert into cidade(nome_cidade,estado,populacao,cod_cidade)
values
('Sao Paulo','SP','124000000','01'),
('Rio de Janeiro','RJ','6320000','02'),
('Belo Horizonte','MG','1433000','03')

select *from cidade

alter table hotel
add cod_cidade int

alter table hotel
add constraint fk_cod_cidade foreign key(cod_cidade)
references cidade(cod_cidade)

insert into hotel(cod_hotel, nome_hotel,endereco,categoria,cod_cidade)
values
('001','Nova Era','Rua Libero Badarol 22','3 estrelas','01'),
('002','Jasmine','Rua do Tijuco 430','2 estrelas','02'),
('003','Miraje','Rua Sertao 120','4 estrelas','03')

select *from hotel

insert into tipo_quarto(cod_hotel,cod_tipo,num_quarto,valor_diaria,decricao)
values
('001','2','10','89.50','Quarto solteiro'),
('001','3','10','100','Quarto Casal'),
('001','4','5','120.50','1 Casal 2 solteiro'),
('002','2','10','70','Quarto 2 solteiro'),
('002','3','4','85','Quarto Casal'),
('002','4','5','100','1 Casal 2 solteiro'),
('003','2','20','90.50','Quarto solteiro'),
('003','3','10','110.50','Quarto Casal'),
('003','4','15','120.50','1 Casal 2 solteiro')

select *from tipo_quarto

insert into restaurante(cod_restaurante,cod_cidade,cod_hotel,nome,endereco,categoria)
values
('10','1','1','Restaurnate Nova Era','Rua Libero Badarol 22','Restaurnate por Kilo'),
('20','2','2','Churrascaria','Rua do Tijuco 500','Churrascaria'),
('30','3','3','Pizzaria','Rua Sertao 150','Pizzaria'),
('40','1',null,'Restaurante X','Rua Xavantes 1330','Self Service')

select *from restaurante

insert into ponto_turistico(cod_ponto_turistico, cod_cidade, descricao,endereco)
values
('11','3','Igreja Menino Jesus','Rua Mojor Olimpo 1000'),
('15','1','Igreja NS da Luz','Rua Bom Fim 18'),
('20','1','Museu','Rua Braz Cubas 401'),
('30','1','Boate','Av Coimbra 100'),
('25','2','Igreja Sao Judas','Rua XV Novembro SN'),
('40','2','Museu','AV Copa Cabana 40'),
('50','2','Discotec','Rua da Ladeira 200'),
('5','3','Igreja NS da Luz','Rua Bom Fim 18'),
('2','3','Museu','Rua Braz Cubas 401'),
('3','3','Boate','Av Coimbra 100')

select *from ponto_turistico

insert into igreja(cod_igreja,data_fundacao,estilo)
values
('11','19000522','null'),
('15','18500204','barroco'),
('25','19100816','gotico'),
('5','17900601','bizantina')

select *from igreja

insert into museu(cod_museu,data_fund,numero_salas)
values
('20','19000705','7'),
('40','19881101','3'),
('2','20051010','5')

select *from museu

alter table casa_show
alter column dias_fechado varchar(50)

insert into casa_show(cod_casa_show, horario, dias_fechado,cod_restaurante)
values
('30','23:00','Segunda a Quinta',null),
('50','21:00','Seunda a Sexta',null),
('3','22:00','Domingo a Quinta',null)

select *from casa_show

insert into fundador(cod_fundador,nome,data_nasc,data_obto,nacionalidade,atividade)
values
('31','Jose Bonifacio','18450210','19020502','Portuguesa','Professor'),
('23','Plinio Alcnatara','19010307','19590511','Brasileira','Medico'),
('13','Antonio Villas Boas','19320101','19940507','Brasileira','Historiador')

select *from fundador

insert into fundacao(cod_museu,cod_fundador)
values
('20','31'),
('40','23'),
('2','13')

select *from fundacao

/* 1-Listar todos os pontos turisticos com codigo, descrição e endereço */

select  cod_ponto_turistico, descricao, endereco
from ponto_turistico

/* 2-Listar as igrejas com código, data fundção e estilo */

select cod_igreja, data_fundacao, estilo
from igreja

/* 3-Listar o conteudo completo de cada tabela do Banco de Dados da Agencia de Turismo */

select *from casa_show
select *from cidade
select *from fundacao
select *from fundador
select *from hotel
select *from igreja
select *from museu
select *from ponto_turistico
select *from restaurante
select *from tipo_quarto

/* 4-Recuperar as informções das cidades que tem populção superior a 1 milhão
     de habitantes */

select *from cidade
where populacao > 10000000


/* 5-Quais os hoteis que tem categoria "3 estrelas"  */

select *from hotel
where categoria = '2 estrelas'

/* 6-Adicionar o campo Especialidades em Restaurante */

alter table restaurante
add especialidade varchar(20)

update restaurante
set especialidade = 'Comida Mineira'
where nome = 'Restaurnate Nova Era'

update restaurante
set especialidade = 'Churrascaria'
where nome = 'Churrascaria'

update restaurante
set especialidade = 'Comida Italiana'
where nome = 'Pizzaria'

update restaurante
set especialidade = 'Comida Mineira'
where nome = 'Restaurante X'

-- Alterando Categoria
update restaurante
set categoria = '3 estrelas'
where nome = 'Restaurnate Nova Era'

update restaurante
set categoria = '2 estrelas'
where nome = 'Churrascaria'

update restaurante
set categoria = '3 estrelas'
where nome = 'Pizzaria'

update restaurante
set categoria = '2 estrelas'
where nome = 'Restaurante X'

select *from restaurante

/* 7-Quais são os restaurantes "3 estrlas" especializado
   em comida italiana */

select *from restaurante
where (categoria = '3 estrelas')and(especialidade = 'Comida Italiana')

/* 8-Recupere os Restaurantes que não são especiaizados em comida italiana
   e nem comida mineira */

select *from restaurante
where (especialidade != 'Comida Italiana') and (especialidade != 'Comida Mineira')


/* 9-Recupere quartos que tem valor entre 50 e 100 */

select *from tipo_quarto
where (valor_diaria > 50) and (valor_diaria < 100)


/*10-Recupere todos hoteis cujos nomes não comecem com "H" */


select *from hotel
where nome_hotel not LIKE'M%'

/*11-Recupere todos hoteis com categoria " 1 estrela "," 2 estrela "e" 3 estrelas "*/

select *from hotel
where categoria in ( '1 estrelas','2 estrelas','3 estrelas')

/*12-Recupere as informações fundadores que tem data de falecimento não preenchidas */

select *from fundador
where data_obto = null

/*13-Recupere Igrejas que tem estilos Cadastrados */

select *from igreja
where estilo != 'null'

/*14-Recupere as Igrejas ordenadas por data de Fundção */
--crescente
select *from igreja order by data_fundacao ASC
--decrecente
select *from igreja order by data_fundacao DESC

/*15-Ordenar por data de fundção(da mais nova para a mais antiga) as igrejas recuperadas exercicio 13 */

select *from igreja
where estilo is null
order by data_fundacao desc

/*16-Ordene os Museus por numeros de salas de forma decrescente */

select *from museu order by numero_salas desc


/*
Considere o projeto de banco de dados da agência de turismo contendo as seguintes tabelas:

• CIDADE (COD_CIDADE, NOME_CIDADE, ESTADO, POPULACAO)
• PONTO_TURISTICO (COD_PT, DESCRICAO_PT, ENDERECO_PT, COD_CIDADE), onde COD_CIDADE é chave estrangeira referente a CIDADE.
• IGREJA (COD_IGREJA,DATA, ESTILO), onde COD_IGREJA é chave estrangeira referente a PONTO_TURISTICO.
• MUSEU (COD_MUSEU, DATA_FUNCACAO, N_SALAS), onde COD_MUSEU é chave estrangeira referente a PONTO_TURISTICO.
• CASA_SHOW (COD_CASA_SHOW, HORARIO_INICIO, DIA_FECHAMENTO, COD_REST),
onde COD_CASA_SHOW é chave estrangeira referente a PONTO_TURISTICO E COD_REST é chave estrangeira referente a RESTAURANTE.
• RESTAURANTE (COD_REST, NOME_REST, ENDERECO_REST, CATEGORIA, ESPECIALIDADE, PRECO, COD_HOTEL),
onde COD_CIDADE é chave estrangeira referente a CIDADE e
onde COD_CASA_SHOW é chave estrangeira referente a CASA_SHOW e
onde COD_HOTEL é chave estrangeira referente a HOTEL.
• HOTEL (COD_HOTEL, NOME_HOTEL, ENDERECO_HOTEL, CATEGORIA, COD_CIDADE, COD_REST),
onde COD_CIDADE é chave estrangeira referente a CIDADE e onde COD_REST é chave estrangeira referente a RESTAURANTE.
• QUARTO (COD_HOTEL, QUARTO, VALOR, TIPO)
• FUNDADOR (COD_FUND, NOME_FUNDADOR, NASC, OBITO, NACIONALIDADE, ATIVIDADE)
• FUNDACAO (COD_MUSEU, COD_FUND), onde COD_MUSEU é chave estrangeira referente a MUSEU e onde COD_FUND é chave estrangeira referente a FUNDADOR.

*/

/*  1. Recupere os quartos que têm valor entre 50 e 100 contendo código e nome do hotel, o número do quarto e o tipo.*/
 
  select Q.decricao, Q.valor_diaria, Q.num_quarto, Q.cod_hotel,H.nome_hotel
  from tipo_quarto Q
  inner join hotel H
  on Q.cod_hotel = H.cod_hotel
  where (valor_diaria > 50) and (valor_diaria < 100)


/*  2. Apesente as seguintes informações dos museus: código, descrição, endereço, data e número de salas. */

select P.cod_ponto_turistico,P.descricao,P.endereco, M.data_fund, M.numero_salas
from ponto_turistico P
inner join museu M
on P.cod_ponto_turistico = M.cod_museu

select *from museu

update ponto_turistico
set endereco = 'Rua Chico Alencar 100'
where cod_ponto_turistico = 20

/*  3. Recupere as igrejas localizadas em São Paulo.  */

select  P.descricao, I.data_fundacao, I.estilo, P.endereco, C.nome_cidade, C.estado
from igreja I
inner join ponto_turistico P
on I.cod_igreja = P.cod_ponto_turistico
inner join cidade C
on C.cod_cidade = P.cod_cidade
where C.nome_cidade = 'Sao Paulo'


/*4. Recupere as igrejas de São Paulo que não têm estilo preenchido. */

select  P.descricao, I.data_fundacao, P.endereco, C.nome_cidade, C.estado
from igreja I
inner join ponto_turistico P
on I.cod_igreja = P.cod_ponto_turistico
inner join cidade C
on C.cod_cidade = P.cod_cidade
where I.estilo = 'null'


/*5. Recupere museus com código, descrição, endereço e número de salas, localizados em São Paulo e que tenha sido fundados por Jose Bonifacio. */

select M.cod_museu, P.descricao,C.nome_cidade, P.endereco, M.numero_salas, X.nome
from ponto_turistico P
inner join museu M
on P.cod_ponto_turistico = M.cod_museu
inner join cidade C
on C.cod_cidade = P.cod_cidade
inner join fundacao F
on M.cod_museu = F.cod_museu
inner join fundador X
on F.cod_fundador = X.cod_fundador
where (C.nome_cidade = 'Sao Paulo') and (X.nome = 'Jose Bonifacio')

DB_Trem_Estacoes

Trens e Estacoes
Modele um Banco de Dados pra atender as seguintes especificacoes:
-Trens podem ser dois tipos: Trem Local ou Trem Expresso. OBS: nunca ambos;
-Cada trem tem um numero unico e um Engenheiro responssavel;
-Estacoes são paradas Expressas ou paradas Local, OBS: nunca ambas;
-Estacao tem um nome Unico e um Endereco;
-Todos os trens Locais param em todas estacoes;
-Trens Expresso param apenas em estacoes Expressa;
-Para cada estacao cada trem tem hora de Ida e Retorno.

create database DB_Trem_Estacoes

use DB_Trem_Estacoes

create table trem(
numero_trem int primary key,
engenheiro varchar(30) not null)

create table estacao(
nome_estacao varchar(50) primary key,
endereco varchar(100))

create table trem_expresso(
numero_trem_expresso int foreign key references trem(numero_trem), primary key(numero_trem_expresso))

create table trem_local(
numero_trem_local int foreign key references trem(numero_trem), primary key(numero_trem_local))

create table estacao_expressa(
nome_estacao_expressa varchar(50) foreign key references estacao(nome_estacao), primary key(nome_estacao_expressa))

create table estacao_local(
nome_estacao_local varchar(50) foreign key references estacao(nome_estacao), primary key(nome_estacao_local))

create table parada_expressa(
nome_estacao_expressa varchar(50) foreign key references estacao_expressa(nome_estacao_expressa),
numero_trem_expresso int foreign key references trem_expresso(numero_trem_expresso), primary key(nome_estacao_expressa,numero_trem_expresso),
hora_ida time,
hora_retorno time)

create table parada_local(
nome_estacao_local varchar(50) foreign key references estacao_local(nome_estacao_local),
numero_trem_local int foreign key references trem_local(numero_trem_local), primary key(nome_estacao_local,numero_trem_local),
hora_ida time,
hora_retorno time)

DB_contabancaria2017 - Exercicio - Pratico

/* 1. Construir um banco de dados para tratar contas bancárias */

CREATE DATABASE DB_contabancaria2017;

USE DB_contabancaria2017

/*2. Criar as tabelas agência, cliente, conta e empréstimo com os atributos
mostrados a seguir: */


/*• agencia (cod_ag, nome, cidade), onde cod_ag é chave primária */
CREATE TABLE agencia (
cod_ag CHAR(3) PRIMARY KEY,
nome VARCHAR(20) NOT NULL,
cidade VARCHAR(50))

/*• cliente (cod_cli, nome, endereco, cidade), onde cod_cli é chave primária */
CREATE TABLE cliente (
cod_cli CHAR(4) PRIMARY KEY,
nome VARCHAR (80) NOT NULL,
endereco VARCHAR (120),
cidade VARCHAR(50))


/*• conta (cod_ag, cod_cli, nro_conta, saldo), onde cod_ag e cod_cli são chaves
estrangeiras e a chave primária é composta por cod_ag, cod_cli e nro_conta. */
CREATE TABLE conta (
cod_ag CHAR(3) FOREIGN KEY REFERENCES AGENCIA(COD_AG),
cod_cli CHAR(4) FOREIGN KEY REFERENCES CLIENTE(COD_CLI),
nro_conta CHAR(4) NOT NULL,
saldo MONEY,
PRIMARY KEY(COD_AG, COD_CLI, NRO_CONTA))


/*• emprestimo (cod_ag, cod_cli, nro_empr, valor), onde cod_ag e cod_cli são chaves estrangeiras
e a chave primária é composta por cod_ag, cod_cli e nro_empr. */
CREATE TABLE emprestimo (
cod_ag CHAR(3) FOREIGN KEY REFERENCES AGENCIA(COD_AG),
cod_cli CHAR(4) FOREIGN KEY REFERENCES CLIENTE(COD_CLI),
nro_empr INT NOT NULL,
valor MONEY
PRIMARY KEY(COD_AG, COD_CLI, NRO_EMPR))


/*3. Popular as tabelas conforme descrito a seguir*/

/*3.1. Incluir duas agências:
• ('001', 'MGU', 'Mogi Guaçu')
• ('010', 'MMI', 'Mogi Mirim') */

INSERT INTO AGENCIA VALUES
('001', 'MGU', 'Mogi Guaçu'),
('010', 'MMI', 'Mogi Mirim')

SELECT * FROM AGENCIA


/*3.2 Incluir seis clientes e, para cada cliente, incluir uma conta e um empréstimo na agência 010. */

INSERT INTO CLIENTE (COD_CLI, NOME, ENDERECO, CIDADE)
VALUES
('1001', 'JOAO PEREIRA', 'RUA XV DE NOVEMBRO, 15', 'MOGI MIRIM'),
('1002', 'ANTONIO CARLOS', 'RUA XV DE NOVEMBRO, 115', 'MOGI MIRIM'),
('1003', 'CARLOS SILVA', 'RUA XV DE NOVEMBRO, 215', 'MOGI MIRIM'),
('2001', 'ANDRE FERREIRA', 'RUA 7 DE SETEMBRO, 7', 'MOGI GUAÇU'),
('2002', 'MANOEL SANTOS', 'RUA 7 DE SETEMBRO, 17', 'MOGI GUAÇU'),
('2003', 'JOSÉ ANDRADE', 'RUA 7 DE SETEMBRO, 117', 'MOGI GUAÇU')

SELECT * FROM CLIENTE

/*4. Incluir seis clientes e, para cada cliente,
incluir uma conta e um empréstimo na agência 010.*/

 /* 3.3 Para cada cliente, incluir uma conta */

INSERT INTO CONTA (COD_AG, COD_CLI, NRO_CONTA, SALDO)
VALUES
('010', '1001', '9901', 1000),
('010', '1002', '9902', 1000),
('010', '1003', '9903', 1000),
('001', '2001', '9911', 1000),
('001', '2002', '9912', 1000),
('001', '2003', '9913', 1000)

SELECT * FROM CONTA

/* 3.4  um empréstimo na agência 0010.*/

INSERT INTO EMPRESTIMO VALUES
('010', '1001', '8801', 1000),
('010', '1002', '8802', 1000),
('010', '1003', '8803', 1000),
('001', '2001', '8811', 1000),
('001', '2002', '8812', 1000),
('001', '2003', '8813', 1000)

SELECT * FROM EMPRESTIMO

/*5. Criar uma view com código, nome e saldo dos clientes da agência
010 (para ser cliente de uma agência, é necessário ter uma conta). */


create view vwAg10 As

select N.cod_cli,N.nome, C.saldo
from cliente N
inner join conta C
on N.cod_cli = C.cod_cli
inner join agencia A
on C.cod_ag = A.cod_ag
where A.cod_ag = '010'



/* 6. Recuperar o nome e o endereço dos clientes da agência 010 que tem
saldo menor que 1500, usando a view criada no exercício anterior.*/

select C.nome, S.saldo,C.endereco
from vwAg10 S
inner join cliente C
on C.cod_cli = S.cod_cli
where saldo <= '1500'

/* 7. Incluir mais alguns empréstimos para os cliente da agência 010 e
criar uma view que apresente o código do cliente com empréstimos
na agência 0010, a quantidade de empréstimos e o valor total emprestado
(count e sum). */


INSERT INTO EMPRESTIMO VALUES
('010', '1001', '8841', 1800),
('010', '1002', '8842', 2300),
('010', '1003', '8843', 1500),
('001', '2001', '8841', 100),
('001', '2002', '8842', 800),
('001', '2003', '8843', 2500)


create view vwEmperstimoAg10 as
    select cod_cli ,
count(nro_empr) as [quantidade de clientes],
sum(valor) as [valor total]
from emprestimo
where cod_ag ='010'
group by cod_cli

select * from vwEmperstimoAg10

/* 8. Utilizando a view criada no exercício anterior, apresente os nomes dos clientes que
tem mais que dois empréstimos na agência 010 e o total emprestado.*/

select v.cod_cli,nome, [quantidade de clientes],[valor total]
from vwEmperstimoAg10 v, cliente c
where  [quantidade de clientes] > 2

/* 9. Crie uma visão com número da conta, saldo, código do cliente,
nome e endereço para os clientes com saldo maior que 1000.
Tente inserir um registro nessa view considerando uma conta e um cliente
existentes e saldo = 2000
(ex: cod_cliente = '1234', nome = 'JOAQUIM', 'rua xv de novembro, 400', conta = '444', saldo = 2000).
Foi possível realizar a inclusão?
Como ficam os dados nas tabelas conta e cliente? */

create view vwMaiorEmprestimo as
select c.nro_conta, saldo, cl.cod_cli, nome, endereco
from conta c, cliente cl
where saldo >= 1000 and c.cod_cli = cl.cod_cli

select * from vwMaiorEmprestimo

create view vwAlgunsClientes as
select cod_cli, nome, cidade
from cliente
where cidade in ('Mogi Mirim','Mogi Guacu')

insert into vwAlgunsClientes
values('4322','Silvio','Mogi Mirim');

insert into vwAlgunsClientes
values('4321','Pedro','Mogi Guacu');

create function calcula (@percentual int)
returns decimal(10,2)
begin
declare @total dec(10,2),
@valor_calc dec(10,2);
select @total = sum(saldo) from conta;
set @valor_calc = @total *@percentual /100;
return @valor_calc;
end

select dbo.calcula(50)

drop function calcula

create function clientes_empr_ag (@ag numeric(4) = 0010)
returns table
as
return (select C.cod_cli, nome
from cliente C, emprestimo E
where C.cod_cli = E.cod_cli and
E.cod_ag = @ag)

select * from clientes_empr_ag (0001)