BD2_3

Prévia do material em texto

BANCO DE DADOS 
Prof. Higor
Contato:
E-mail: higor.delsoto@docente.unip.br
Material
Marquês: 
https://drive.google.com/drive/folders/1NMGv1-l7Xno1P8t_ikW0yNXelny_FlsK?usp=sharing
Norte:
https://drive.google.com/drive/folders/1Oltdz2Nq3V9_Dr7mit2wLTTTlIMKjpnc?usp=sharing
Motivação
Script SQLResposta
Motivação
Script SQL
Ações internas
SQL Embutida
• Script sql utilizado em uma linguagem de programação(C,
C#, Java, Python, PHP, etc);
• O script SQL é o mesmo, ja que é destinado ao SGBD;
• Capacidade de alterar, inserir, apagar e concultar.
Script SQLResposta
SQL Embutida
Exercício de revisão
• Criar o banco de dados;
• Inserir 5 produtos;
• Inserir 2 equipes;
• Inserir 3 vendedores;
• Inserir 3 vendas;
Exercício de revisão
• Buscar as vendas que
possuem o produto x(produto
inserido anteriormente) e o
vendedor y.
• Buscar equipes dos
vendedores com mais de uma
venda.
Exercício de revisão
• Buscar as vendas que possuem o produto x(produto inserido
anteriormente) e o vendedor y.
SELECT * FROM venda where venda.id_produto=0 and
venda.id_vendedor=1;
• Buscar equipes dos vendedores com mais de uma venda.
select equipe.nome, vendedor.nome from equipe, 
vendedor, venda where 
equipe.id=vendedor.id_equipe and 
vendedor.id=venda.id_vendedor group by 
(venda.id_vendedor) having count(venda.id) > 1;
Consultas encadeadas
Consultas encadeadas
• O resultado de uma consulta é utilizado por outra consulta,
de forma encadeada e no mesmo comando SQL.
• O resultado do comando SELECT mais interno (subselect)
é usado por outro SELECT mais externo para obter o
resultado final.
• O SELECT mais interno (subconsulta ou consulta aninhada)
pode ser usado principalmente nas cláusulas WHERE e
HAVING do comando mais externo.
Consultas encadeadas
Tipos de subconsulta:
• ESCALAR: Retornam um único valor.
• ÚNICA COLUNA: Retornam várias linhas,mas apenas uma 
única coluna é obtida.
• TABELA: Retornam uma ou mais colunas e múltiplas linhas.
Consultas encadeadas
Exemplo de utilização de uma subconsulta:
SELECT * FROM vendedor WHERE setor =
(SELECT nome FROM Departamento WHERE
Nome = ‘eletrodomesticos’ );
Listar os vendedores que trabalham no setor de
eletrodomésticos.
Exercício
Listar as vendas que o vendedor possui o nome com a
lerta R.
Exercício
Alterar a tabela vendedor e inserir o campo salário
(Preenche-lo também).
Exercício
Listar os empregados cujos salários são maiores
do que o salário médio, mostrando o quanto são
maiores.
Exercício
Listar os empregados cujos salários são maiores
do que o salário médio, mostrando o quanto são
maiores.
SELECT *,(SELECT AVG (Salario) FROM vendedor) 
AS media, Salario - ( SELECT AVG (Salario) 
FROM vendedor) AS DifSal FROM vendedor WHERE 
Salario > ( SELECT AVG ( Salario) FROM 
vendedor );
Consultas encadeadas – ANY / SOME
São usadas com subconsultas que produzem
uma única coluna de números.
Consultas encadeadas – ANY / SOME
Exemplo: Listar os empregados cujos salários são
maiores do que o salário de pelo menos um
funcionário do departamento de vendas
SELECT Nome, Sexo, Salario
FROM Empregado
WHERE Salario > SOME (SELECT Salario 
FROM Empregado WHERE Dep = “vendas”) ;
Exercício
Listar os vendedores cujos salários são
maiores do que o salário de pelo menos um
funcionário da equipe x (mais adequada para seu BD).
Consultas encadeadas – ALL
São usadas com subconsultas que produzem
uma única coluna de números.
Consultas encadeadas – ANY / SOME
Exemplo: Listar os empregados cujos salários são
maiores do que o salário de pelo menos um
funcionário do departamento de vendas
SELECT Nome, Sexo, Salario
FROM Empregado
WHERE Salario > SOME (SELECT Salario 
FROM Empregado WHERE Dep = “TI”) ;
Exercício
Listar os vendedores cujos salários são
maiores do que o salário do que cada funcionário da
equipe x (mais adequada para seu BD).
Consultas encadeadas – REGRAS
• A cláusula ORDER BY não pode ser usada em uma
subconsulta.
• Nomes de atributos especificados na subconsulta estão
associados às tabelas listadas na cláusula FROM.
• Quando a subconsulta é um dos operandos
envolvidos em uma comparação, ela deve
aparecer no lado direito da comparação.
Consultas encadeadas – EXISTS E NOT EXISTS
• Criada para ser utilizada somente em subconsultas;
• Retorna TRUE ou FALSE quando há alguma tupla no
que atende a clausula colocada na subconsulta;
SELECT *
FROM E
WHERE EXISTS
( SELECT *
FROM D
WHERE E.Num-Dep = D.Numero AND
D.Nome = ‘Informática’) ; 
Exercício
Liste todos os vendedores que possuem
resgistro de venda.
Exemplo:
SELECT *
FROM E
WHERE EXISTS
( SELECT *
FROM D
WHERE E.Num-Dep = D.Numero AND
D.Nome = ‘Informática’) ; 
Consultas encadeadas
Conjuntos
Consultas encadeadas – UNION
• Linhas duplicadas são removidas da tabela
resultante.
• A mesma quantidade de colunas devem ser
requisitadas em ambas as querys
Exemplo
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Exercício
Liste todos os vendedores, vendas e
equipes do BD.
Exemplo
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Procedure
Definições
• Procedimento armazenado, com uma 
coleção de comandos SQL
• Após a compilação o script é armazenado 
no servidor
Procedure
Vantagens:
• Devido ao encapsulamento de tarefas 
repetitivas, a execução torna-se mais 
eficiente.
• Redução no tráfego de rede
Para iniciarmos, crie o seguinte banco:
Procedure
Exemplo:
Consulta:
Criar procedure:
Executar:
SELECT codigop, nome FROM Projeto p, Alocacao a
WHERE p.codproj = a.codigop
DELIMITER $$
create procedure buscateste ()
BEGIN 
SELECT codigop, nome FROM Projeto p, Alocacao a
WHERE p.codproj = a.codigop
END $$
DELIMITER ;
call buscateste;
Procedure
Passagem de parâmetros
IN – O programa chamador deve passar um 
parâmetro. O valor do parâmetro fora da 
procedure permanece inalterado. 
Procedure
Passagem de parâmetros
OUT – Valor alterado dentro da procedure e 
retornado para o programa chamador.
OBS: Ao entrar no procedimento o parâmetro é 
‘limpo’.
Procedure
Passagem de parâmetros
INOUT – Combinação dos anteriores.
O programa chamador deve passar o
parâmetro, e dentro da procedure pode haver
mudança. O valor é retornado.
Procedure
Passagem de parâmetros
Sintaxe MODO nome_parâmetro tipo (tamanho)
Procedure
Passagem de parâmetros
Sintaxe MODO nome_parâmetro tipo (tamanho)
Procedure
Passagem de parâmetros – Qtd_livro
DELIMITER $$ 
CREATE PROCEDURE livro_qtd(IN qtd int)
BEGIN select titulo from livro where estoque > qtd;
END $$
DELIMITER ;
call livro_qtd(2);
Exercício 
Crie uma procedure que encontre o 
título dos livros da editora x.
Procedure
Passagem de parâmetros – Aumento venda
DELIMITER $$ 
CREATE PROCEDURE aumento_venda(IN taxa double, IN 
id int)
BEGIN 
update itens_da_venda
set subtotal= subtotal*((taxa/100) +1) 
where idvenda = id;
END $$
DELIMITER ;
call aumento_venda(20,1);
Exercício 
Crie uma procedure altere a
quantidade de livros e imprima o
resultado.
Procedure
Passagem de parâmetros – Número elevado² 
DELIMITER $$ 
CREATE PROCEDURE 
elev_quadrado(INOUT numero INT)
BEGIN 
SET numero = numero * numero;
END $$
DELIMITER ;
SET @num=4;
Call elev_quadrado(@num);
select @num;
Exercício 
Crie uma procedure que calcule a área de um 
triangulo retângulo.Formula: (b*h)/2
Procedure
Passagem de parâmetros – Área triangulo 2
DELIMITER $$ 
CREATE PROCEDURE a_tri2(IN b INT, IN h INT, out s int)
BEGIN 
SET s = (b * h)/2;
END $$
DELIMITER ;
SET @b=4;
SET @h=3;
SET @s=0;
call a_tri2(@b,@h,@s);
select @s;
Exercício 
Crie uma procedure que calcule a área de um 
retângulo e retorne o valor para uma variável.
Formula: (b*h)
Procedure
Declaração variáveis - exemplo 2
Procedure
Declaração variáveis
DECLARE nome_var tipo [DEFAULT valor padrão]
Procedure
Declaração variáveis
Exercício 
Crie uma procedure que calcule o salário 
líquido de um funcionário, recebendo o salário 
e o desconto por parâmetro e acrescentando 
um bônus através de uma variável interna.
Formula: (salário – desconto) + bônus
Índices 
Recurso de otimização de consultas, visando a 
velocidade na busca de dados.
Índices 
Índices 
Índices - Mostrar 
show indexes from nome_tabela;
Índices - Criar
create index nome_index on tabela(campo);
Índices - Excluir
drop index nome_index on tabela;