MIC PERNAMBUCO
SEGUNDA FASE / S2B
Banco de Dados
Turma: Manhã / FIR
Recife-PE
www.micpernambuco.com.br
 Condições
de pesquisa NOT atrasam a
recuperação
de dados
 Condições de pesquisa LIKE atrasam a
recuperação
de dados
 Correspondências exatas ou intervalos aceleram
a recuperação de dados
 A cláusula ORDER BY atrasa a recuperação de
dados
 Usando
aliases para nomes de tabelas
 Combinando dados de várias tabelas
 Combinando vários conjuntos de resultados
 Exemplo
1 (sem nome de alias)
USE joindb
SELECT buyer_name, sales.buyer_id, qty
FROM buyers
INNER JOIN sales
ON buyers.buyer_id = sales.buyer_id
GO
 Exemplo
2 (com nome de alias)
USE joindb
SELECT buyer_name, s.buyer_id, qty
FROM buyers AS b
INNER JOIN sales AS s
ON b.buyer_id = s.buyer_id
GO
 Introdução
às associações
 Usando associações internas
 Usando associações externas
 Usando associações cruzadas
 Associando mais de duas tabelas
 Associando uma tabela a si mesma
 Selecionar
colunas específicas a partir de
várias tabelas


A palavra-chave JOIN especifica quais tabelas
serão associadas e como associá-las
A palavra-chave ON especifica as colunas que as
tabelas têm em comum
 Consultar
duas ou mais tabelas para produzir
um conjunto de resultados


Usar chaves primárias e externas como
condições
de associação
Usar colunas comuns às tabelas especificadas
para associar tabelas
USE joindb
SELECT buyer_name, sales.buyer_id, qty
FROM buyers
INNER JOIN sales
Exemplo 1
ON buyers.buyer_id = sales.buyer_id
GO
sales
buyers
buyer_name
buyer_id prod_id
buyer_id
Adam Barr
Sean Chai
Eva Corets
1
2
3
Erin O’Melia
4
Resultado
buyer_name
buyer_id
qty
Adam Barr
1
15
Adam Barr
1
5
Erin O’Melia
4
37
Eva Corets
3
11
Erin O’Melia
4
1003
qty
1
1
4
3
2
3
1
5
15
5
37
11
4
2
1003
USE joindb
SELECT buyer_name, sales.buyer_id, qty
FROM buyers
Exemplo 1
LEFT OUTER JOIN sales
ON buyers.buyer_id = sales.buyer_id
GO
buyers
sales
buyer_id
buyer_id
prod_id
qty
Adam Barr
1
1
2
15
Sean Chai
2
1
3
5
Eva Corets
3
4
1
37
Erin O’Melia
4
3
5
11
4
2
1003
buyer_name
Resultado
buyer_name
buyer_id
qty
Adam Barr
1
15
Adam Barr
1
5
Erin O’Melia
4
37
Eva Corets
3
11
Erin O’Melia
4
1003
NULL
NULL
Sean Chai
USE joindb
SELECT buyer_name, qty
FROM buyers
CROSS JOIN sales
GO
buyers
buyer_id buyer_name
1
Adam Barr
Exemplo 1
sales
buyer_id prod_id
Resultado
qty
buyer_name
qty
1
2
15
Adam Barr
15
2
Sean Chai
1
3
5
Adam Barr
5
3
Eva Corets
4
1
37
Adam Barr
37
4
Erin O’Melia
3
5
11
Adam Barr
11
4
2
1003
Adam Barr
1003
Sean Chai
Sean Chai
15
5
Sean Chai
37
Sean Chai
11
Sean Chai
Eva Corets
...
1003
15
...
USE joindb
SELECT buyer_name, prod_name, qty
FROM buyers
INNER JOIN sales
ON buyers.buyer_id = sales.buyer_id
INNER JOIN produce
ON sales.prod_id = produce.prod_id
GO
buyers
sales
buyer_id prod_id qty
buyer_id buyer_name
1
2
15
1
Adam Barr
1
3
5
2
Sean Chai
3
1
37
3
Eva Corets
4
5
11
4
Erin O’Melia
2
2
1003
Resultado
buyer_name
prod_name qty
Erin O’Melia
Apples
37
Adam Barr
Pears
15
Erin O’Melia
Pears
1003
Adam Barr
Oranges
5
Eva Corets
Peaches
11
Exemplo 1
produce
prod_id prod_name
1
Apples
2
Pears
3
Oranges
4
Bananas
5
Peaches
USE joindb
SELECT a.buyer_id AS buyer1, a.prod_id
,b.buyer_id AS buyer2
FROM sales AS a
INNER JOIN sales AS b
ON a.prod_id = b.prod_id
WHERE a.buyer_id > b.buyer_id
GO
sales a
sales b
buyer_id
prod_id
1
1
4
3
2
3
1
4
Exemplo 3
5
2
qty
buyer_id
prod_id
15
5
37
11
1
1
4
3
2
3
1
1003
4
Resultado
buyer1
prod_id
buyer2
4
2
1
5
2
qty
15
5
37
11
1003
VIEWS
 Focalizar


Focalizar somente em dados importantes ou
apropriados
Limitar o acesso a dados confidenciais
 Mascarar


os dados para os usuários
a complexidade do banco de dados
Ocultar estruturas complexas de banco de dados
Simplificar consultas complexas, incluindo consultas
distribuídas para dados heterogêneos
 Simplificar
o gerenciamento de permissões de
usuários
 Melhorar o desempenho
 Organizar dados para serem exportados para
outros aplicativos
VIEWS
Employees
EmployeeID
LastName
Firstname
Title
1
2
3
Davolio
Fuller
Leverling
Nancy
Andrew
Janet
~~~
~~~
~~~
USE Northwind
GO
CREATE VIEW dbo.EmployeeView
AS
SELECT LastName, Firstname
FROM Employees
EmployeeView
Lastname
Firstname
Davolio
Fuller
Leverling
Nancy
Andrew
Janet
View de usuário
VIEWS
Orders
OrderID CustomerID RequiredDate ShippedDate
10663
BONAP
1997-09-24
~~~
1997-10-03
10827
BONAP
1998-01-26
~~~
1998-02-06
10427
PICCO
1997-02-24
~~~
1997-03-03
10451
QUICK
1997-03-05
~~~
1997-03-12
10515
QUICK
1997-05-07
~~~
1997-05-23
Customers
CustomerID CompanyName
BONAP
PICCO
QUICK
USE Northwind
GO
CREATE VIEW dbo.ShipStatusView
AS
SELECT OrderID, ShippedDate,
ContactName
FROM Customers c INNER JOIN Orders o
ON c.CustomerID = O.CustomerID
WHERE RequiredDate < ShippedDate
ContactName
Bon app'
Laurence Lebihan
Piccolo und mehr Georg Pipps
QUICK-Stop
Horst Kloss
ShipStatusView
OrderID ShippedDate
10264
10271
10280
ContactName
1996-08-23
1996-08-21 Maria Larsson
1996-08-30
1996-08-29 Art Braunschweiger
1996-09-12
1996-09-11 Christina Berglund
VIEWS
 Alterando
views
USE Northwind
GO
ALTER VIEW dbo.EmployeeView
AS
SELECT LastName, FirstName, Extension
FROM Employees
 Descartando
views
DROP VIEW dbo.ShipStatusView
PROCEDIMENTOS ARMAZENADOS
(STORED PROCEDURES)
 Compartilham
a lógica do aplicativo
 Protegem os detalhes das tabelas do banco de
dados
 Fornecem mecanismos de segurança
 Melhoram o desempenho
 Reduzem o tráfego de rede
PROCEDIMENTOS ARMAZENADOS
(STORED PROCEDURES)
USE Locadora
GO
CREATE PROC dbo.FilmesNaoDevolvidos
AS
SELECT *
FROM dbo.Pedidos
WHERE DataDeEntregaPrevista < GETDATE()
AND DataDeEntregaEfetiva IS Null
GO
EXEC FilmesNaoDevolvidos
PROCEDIMENTOS ARMAZENADOS
(STORED PROCEDURES)
USE Northwind
GO
ALTER PROC dbo.OverdueOrders
AS
SELECT CONVERT(char(8), RequiredDate, 1) RequiredDate,
CONVERT(char(8), OrderDate, 1) OrderDate,
OrderID, CustomerID, EmployeeID
FROM Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
ORDER BY RequiredDate
GO
PROCEDIMENTOS ARMAZENADOS
(STORED PROCEDURES)
CREATE PROCEDURE dbo.[Locacao de Filmes]
@DataInicio DateTime, @DataEntrega DateTime
AS
IF @DataInicio IS NULL OR @DataEntrega IS NULL
BEGIN
RAISERROR(‘Valores NULL não são permitidos', 14, 1)
RETURN
END
SELECT PED.DataEntregaPrevista,
PRE.Subtotal,
FROM PEDIDOS PED INNER JOIN PRECO PRE
ON PED.IDPedido = PRE.IDPedido
WHERE PED.DataEntregaPrevista BETWEEN @DataInicio
AND @DataEntrega
GO
PROCEDIMENTOS ARMAZENADOS
(STORED PROCEDURES)
 Passando
valores por nome de parâmetro
EXEC AdicionarCliente
@ClienteID = 154,
@NomeCliente = 'Maria Anders',
@NomeEmpresa = 'Alfreds Futterkiste',
@Cargo = 'Sales Representative',
@Endereco = 'Obere Str. 57',
@Cidade = 'Berlin',
@CodigoPostal = '12209',
@Pais = 'Germany',
@Telefone = '030-0074321'
 Passando
valores por posição
EXEC AdicionarCliente 154, 'Maria Anders',
'Alfreds Futterkiste', 'Sales
Representative', 'Obere Str. 57', 'Berlin',
NULL, '12209', 'Germany', '030-0074321'
PROCEDIMENTOS ARMAZENADOS
(STORED PROCEDURES)
Criando o
procedimento
armazenado
Executando o
procedimento
armazenado
Resultados do
procedimento
armazenado
CREATE PROCEDURE dbo.Multiplicador
@n1 smallint,
@n2 smallint,
@resultado smallint OUTPUT
AS
SET @resultado = @n1 * @n2
GO
DECLARE @resposta smallint
EXECUTE Multiplicador 5,6, @resposta
OUTPUT
SELECT ‘O resultado é:', @resposta
O resultado é: 30