SEGUNDA FASE / S2B MIC PERNAMBUCO Banco de Dados Turma: Manhã / FIR Recife-PE

SE

GU

ND

A F

AS

E /

S2

B

MIC

PE

RN

AM

BU

CO

Banco de Dados

Turma: Manhã / FIRTurma: Manhã / FIRRecife-PERecife-PE

www.micpernambuco.com.br

Condições de pesquisa NOT atrasam a recuperaçãode dados

Condições de pesquisa LIKE atrasam a recuperaçãode dados

Correspondências exatas ou intervalos acelerama 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)

Exemplo 2 (com nome de alias)

USE joindbSELECT buyer_name, s.buyer_id, qty FROM buyers AS b INNER JOIN sales AS s ON b.buyer_id = s.buyer_idGO

USE joindbSELECT buyer_name, s.buyer_id, qty FROM buyers AS b INNER JOIN sales AS s ON b.buyer_id = s.buyer_idGO

USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO

USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO

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çõesde associação

Usar colunas comuns às tabelas especificadas para associar tabelas

USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO

USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO

buyer_namebuyer_namebuyer_namebuyer_name

Adam BarrAdam Barr

Sean ChaiSean Chai

Eva CoretsEva Corets

Erin O’MeliaErin O’Melia

buyer_idbuyer_idbuyer_idbuyer_id

11

22

33

44

sales

buyer_idbuyer_idbuyer_idbuyer_id prod_idprod_idprod_idprod_id qtyqtyqtyqty

11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

buyers

Resultado


Adam BarrAdam Barr

Adam BarrAdam Barr



buyer_idbuyer_idbuyer_idbuyer_id qtyqtyqtyqty

11

11

44

33

1515

55

3737

1111

Erin O’MeliaErin O’Melia 44 10031003

Exemplo 1Exemplo 1

USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idGO

USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idGO


11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003


Adam BarrAdam Barr

Sean ChaiSean Chai




11

22

33

44

salesbuyers

Resultado


Adam BarrAdam Barr

Adam BarrAdam Barr



buyer_idbuyer_idbuyer_idbuyer_id qtyqtyqtyqty

11

11

44

33

1515

55

3737

1111

Erin O’MeliaErin O’Melia 44 10031003

Sean ChaiSean Chai NULLNULL NULLNULL

Exemplo 1Exemplo 1

Resultado

USE joindbSELECT buyer_name, qty FROM buyers CROSS JOIN salesGO

USE joindbSELECT buyer_name, qty FROM buyers CROSS JOIN salesGO


Adam BarrAdam Barr

Adam BarrAdam Barr

Adam BarrAdam Barr

Adam BarrAdam Barr

qtyqtyqtyqty

1515

55

3737

1111

Adam BarrAdam Barr 10031003

Sean ChaiSean Chai 1515





Eva CoretsEva Corets 1515

...... ......

sales


11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

buyers


11

22

33

44


Adam BarrAdam Barr

Sean ChaiSean Chai



Exemplo 1Exemplo 1

USE joindbSELECT 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_idGO

USE joindbSELECT 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_idGO

producebuyers sales

Resultado

prod_idprod_idprod_idprod_id prod_nameprod_nameprod_nameprod_name

11

22

33

44

ApplesApples

PearsPears

OrangesOranges

BananasBananas

55 PeachesPeaches


11

22

33

44


Adam BarrAdam Barr

Sean ChaiSean Chai




11

11

33

44

prod_idprod_idprod_idprod_id

22

33

11

55

22 22

qtyqtyqtyqty

1515

55

3737

1111

10031003

Exemplo 1Exemplo 1



Adam BarrAdam Barr


Adam BarrAdam Barr


prod_nameprod_nameprod_nameprod_name

ApplesApples

PearsPears

PearsPears

OrangesOranges

PeachesPeaches

qtyqtyqtyqty

3737

1515

10031003

55

1111

USE joindbSELECT 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_idWHERE a.buyer_id > b.buyer_idGO

USE joindbSELECT 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_idWHERE a.buyer_id > b.buyer_idGO

sales b


11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

sales a


11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

Resultado

buyer1buyer1buyer1buyer1

44prod_idprod_idprod_idprod_id buyer2buyer2buyer2buyer2

22 11

Exemplo 3Exemplo 3

VIEWSVIEWS

Focalizar os dados para os usuários Focalizar somente em dados importantes ou

apropriados Limitar o acesso a dados confidenciais

Mascarar 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

VIEWSVIEWS

EmployeeViewEmployeeViewEmployeeViewEmployeeView

Lastname Lastname Firstname Firstname

Davolio Fuller Leverling

Davolio Fuller Leverling

Nancy Andrew Janet

Nancy Andrew Janet

EmployeesEmployeesEmployeesEmployees

EmployeeIDEmployeeID LastName LastName FirstnameFirstname TitleTitle

123

123

DavolioFullerLeverling

DavolioFullerLeverling

NancyAndrewJanet

NancyAndrewJanet

~~~~~~~~~

~~~~~~~~~

View de usuárioView de usuário

USE NorthwindGOCREATE VIEW dbo.EmployeeViewAS SELECT LastName, FirstnameFROM Employees

USE NorthwindGOCREATE VIEW dbo.EmployeeViewAS SELECT LastName, FirstnameFROM Employees

VIEWSVIEWS

OrderIDOrderIDOrderIDOrderID

1066310827104271045110515

1066310827104271045110515

CustomerIDCustomerIDCustomerIDCustomerID

BONAP BONAP PICCO QUICKQUICK

BONAP BONAP PICCO QUICKQUICK

~~~ ~~~ ~~~ ~~~ ~~~

~~~ ~~~ ~~~ ~~~ ~~~

RequiredDateRequiredDateRequiredDateRequiredDate

1997-09-241998-01-261997-02-241997-03-051997-05-07

1997-09-241998-01-261997-02-241997-03-051997-05-07

ShippedDateShippedDateShippedDateShippedDate

1997-10-031998-02-061997-03-031997-03-121997-05-23

1997-10-031998-02-061997-03-031997-03-121997-05-23

Orders Customers

ShipStatusViewUSE NorthwindGOCREATE VIEW dbo.ShipStatusViewASSELECT OrderID, ShippedDate, ContactNameFROM Customers c INNER JOIN Orders o ON c.CustomerID = O.CustomerIDWHERE RequiredDate < ShippedDate

USE NorthwindGOCREATE VIEW dbo.ShipStatusViewASSELECT OrderID, ShippedDate, ContactNameFROM Customers c INNER JOIN Orders o ON c.CustomerID = O.CustomerIDWHERE RequiredDate < ShippedDate

CustomerIDCustomerIDCustomerIDCustomerID

BONAPPICCOQUICK

BONAPPICCOQUICK

CompanyNameCompanyNameCompanyNameCompanyName

Bon app'Piccolo und mehrQUICK-Stop

Bon app'Piccolo und mehrQUICK-Stop

ContactNameContactNameContactNameContactName

Laurence LebihanGeorg PippsHorst Kloss

Laurence LebihanGeorg PippsHorst Kloss

OrderIDOrderIDOrderIDOrderID

102641027110280

102641027110280

1996-08-211996-08-291996-09-11

1996-08-211996-08-291996-09-11

ShippedDateShippedDateShippedDateShippedDate

1996-08-231996-08-301996-09-12

1996-08-231996-08-301996-09-12

ContactNameContactNameContactNameContactName

Maria LarssonArt BraunschweigerChristina Berglund

Maria LarssonArt BraunschweigerChristina Berglund

Alterando views

Descartando views

USE NorthwindGOALTER VIEW dbo.EmployeeViewAS SELECT LastName, FirstName, ExtensionFROM Employees

USE NorthwindGOALTER VIEW dbo.EmployeeViewAS SELECT LastName, FirstName, ExtensionFROM Employees

DROP VIEW dbo.ShipStatusViewDROP VIEW dbo.ShipStatusView

VIEWSVIEWS

PROCEDIMENTOS PROCEDIMENTOS ARMAZENADOS (STORED ARMAZENADOS (STORED PROCEDURES)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 LocadoraGOCREATE PROC dbo.FilmesNaoDevolvidosAS SELECT * FROM dbo.Pedidos WHERE DataDeEntregaPrevista < GETDATE() AND DataDeEntregaEfetiva IS NullGO

EXEC FilmesNaoDevolvidos

PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURES)

USE NorthwindGOALTER PROC dbo.OverdueOrdersASSELECT CONVERT(char(8), RequiredDate, 1) RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM OrdersWHERE RequiredDate < GETDATE() AND ShippedDate IS NullORDER BY RequiredDateGO

USE NorthwindGOALTER PROC dbo.OverdueOrdersASSELECT CONVERT(char(8), RequiredDate, 1) RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM OrdersWHERE RequiredDate < GETDATE() AND ShippedDate IS NullORDER BY RequiredDateGO


CREATE PROCEDURE dbo.[Locacao de Filmes] @DataInicio DateTime, @DataEntrega DateTime ASIF @DataInicio IS NULL OR @DataEntrega IS NULLBEGIN RAISERROR(‘Valores NULL não são permitidos', 14, 1) RETURNENDSELECT PED.DataEntregaPrevista, PRE.Subtotal, FROM PEDIDOS PED INNER JOIN PRECO PRE ON PED.IDPedido = PRE.IDPedidoWHERE PED.DataEntregaPrevista BETWEEN @DataInicioAND @DataEntregaGO

CREATE PROCEDURE dbo.[Locacao de Filmes] @DataInicio DateTime, @DataEntrega DateTime ASIF @DataInicio IS NULL OR @DataEntrega IS NULLBEGIN RAISERROR(‘Valores NULL não são permitidos', 14, 1) RETURNENDSELECT PED.DataEntregaPrevista, PRE.Subtotal, FROM PEDIDOS PED INNER JOIN PRECO PRE ON PED.IDPedido = PRE.IDPedidoWHERE PED.DataEntregaPrevista BETWEEN @DataInicioAND @DataEntregaGO

PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURES) Passando valores por nome de parâmetro

Passando valores por posição

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'

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'

EXEC AdicionarCliente 154, 'Maria Anders', 'Alfreds Futterkiste', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', '030-0074321'

EXEC AdicionarCliente 154, 'Maria Anders', 'Alfreds Futterkiste', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', '030-0074321'


CREATE PROCEDURE dbo.Multiplicador @n1 smallint, @n2 smallint, @resultado smallint OUTPUTAS SET @resultado = @n1 * @n2GO

DECLARE @resposta smallintEXECUTE Multiplicador 5,6, @resposta OUTPUTSELECT ‘O resultado é:', @resposta

O resultado é: 30

CREATE PROCEDURE dbo.Multiplicador @n1 smallint, @n2 smallint, @resultado smallint OUTPUTAS SET @resultado = @n1 * @n2GO

DECLARE @resposta smallintEXECUTE Multiplicador 5,6, @resposta OUTPUTSELECT ‘O resultado é:', @resposta

O resultado é: 30

Resultados do Resultados do procedimentoprocedimentoarmazenadoarmazenado

Resultados do Resultados do procedimentoprocedimentoarmazenadoarmazenado

Executando o Executando o procedimentoprocedimentoarmazenadoarmazenado

Executando o Executando o procedimentoprocedimentoarmazenadoarmazenado

Criando o Criando o procedimentoprocedimentoarmazenadoarmazenado

Criando o Criando o procedimentoprocedimentoarmazenadoarmazenado

Documents

SEGUNDA FASE / S2B MIC PERNAMBUCO Banco de Dados Turma: Manhã / FIR Recife-PE