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)