Bancos de Dados

SQL Server 2012

SQL – Consultas

SQL - Consultas

• Elementos Básicos do SQL• Agrupamento e Sumarização• Junção de dados de múltiplas tabelas• Subqueries

Elementos Básicos do SQL

• Selecionando dados com o comando SELECT• Filtrando Dados• Formatando os resultados (Result Sets)

Selecionando dados com o comando SELECT

• Usando o comando SELECT• Especificando as colunas a serem retornadas• Usando a cláusula WHERE para especificar as

linhas a serem retornadas

Usando o comando SELECT

• A “Select List” especifica as colunas• A cláusula WHERE especifica as linhas• A cláusula FROM especifica a tabela

Sintaxe ParcialSELECT [ALL | DISTINCT] <select_list> FROM {<table_source>} [,…n] WHERE <search_condition>

SELECT [ALL | DISTINCT] <select_list> FROM {<table_source>} [,…n] WHERE <search_condition>

Especificando as colunas a serem retornadas

employeeidemployeeid lastnamelastname firstnamefirstname titletitle

11 DavolioDavolio NancyNancy Sales RepresentativeSales Representative

22 FullerFuller AndrewAndrew Vice President, SalesVice President, Sales

33 LeverlingLeverling JanetJanet Sales RepresentativeSales Representative

44 PeacockPeacock MargaretMargaret Sales RepresentativeSales Representative

55 BuchananBuchanan StevenSteven Sales ManagerSales Manager

66 SuyamaSuyama MichaelMichael Sales RepresentativeSales Representative

77 KingKing RobertRobert Sales RepresentativeSales Representative

88 CallahanCallahan LauraLaura Inside Sales CoordinatorInside Sales Coordinator

99 DodsworthDodsworth AnneAnne Sales RepresentativeSales Representative

USE northwindSELECT employeeid, lastname, firstname, titleFROM employeesGO

USE northwindSELECT employeeid, lastname, firstname, titleFROM employeesGO

Usando a cláusula Where para especificar as linhas retornadas

employeeidemployeeid lastnamelastname firstnamefirstname titletitle

55 BuchananBuchanan StevenSteven Sales ManagerSales Manager

USE northwindSELECT employeeid, lastname, firstname, titleFROM employeesWHERE employeeid = 5GO

USE northwindSELECT employeeid, lastname, firstname, titleFROM employeesWHERE employeeid = 5GO

Filtragem

• Usando Operadores de Comparação• Comparando Strings• Usando Operadores Lógicos• Selecionando Faixas de Valores• Usando uma Lista de Valores como critério

para busca• Selecionando Valores Desconhecidos (Null)

Usando Operadores de Comparação

USE northwindSELECT lastname, cityFROM employeesWHERE country = 'USA‘

GO

USE northwindSELECT lastname, cityFROM employeesWHERE country = 'USA‘

GO

lastnamelastname citycity

DavolioDavolio SeattleSeattle

FullerFuller TacomaTacoma

LeverlingLeverling KirklandKirkland

PeacockPeacock RedmondRedmond

CallahanCallahan SeattleSeattle

Exemplo 1Exemplo 1

Comparando Strings

USE northwindSELECT companynameFROM customersWHERE companyname LIKE '%Restaurant%‘GO

USE northwindSELECT companynameFROM customersWHERE companyname LIKE '%Restaurant%‘GO

companynamecompanyname

GROSELLA-RestauranteGROSELLA-Restaurante

Lonesome Pine RestaurantLonesome Pine Restaurant

Tortuga RestauranteTortuga Restaurante

Usando Operadores Lógicos

USE northwindSELECT productid, productname, supplierid, unitprice FROM products WHERE (productname LIKE 'T%' OR productid = 46) AND (unitprice > 16.00) GO

USE northwindSELECT productid, productname, supplierid, unitprice FROM products WHERE (productname LIKE 'T%' OR productid = 46) AND (unitprice > 16.00) GO

productidproductid productnameproductname supplieridsupplierid unitpriceunitprice

1414 TofuTofu 66 23.2523.25

2929 Thüringer RostbratwurstThüringer Rostbratwurst 1212 123.79123.79

6262 Tarte au sucreTarte au sucre 2929 49.349.3

Exemplo 1Exemplo 1

Selecionando uma Faixa de ValoresUSE northwindSELECT productname, unitpriceFROM productsWHERE unitprice BETWEEN 10 AND 20GO

USE northwindSELECT productname, unitpriceFROM productsWHERE unitprice BETWEEN 10 AND 20GO

productnameproductname unitpriceunitprice

ChaiChai 1818

ChangChang 1919

Aniseed SyrupAniseed Syrup 1010

Genen ShouyuGenen Shouyu 15.515.5

PavlovaPavlova 17.4517.45

Sir Rodney’s SconesSir Rodney’s Scones 1010

…… ……

Exemplo 1Exemplo 1

USE northwindSELECT companyname, countryFROM suppliersWHERE country IN ('Japan', 'Italy')

GO

USE northwindSELECT companyname, countryFROM suppliersWHERE country IN ('Japan', 'Italy')

GO

Usando uma Lista de Valores para busca

companynamecompanyname countrycountry

Tokyo TradersTokyo Traders JapanJapan

Mayumi’sMayumi’s JapanJapan

Formaggi Fortini s.r.l.Formaggi Fortini s.r.l. ItalyItaly

Pasta Buttini s.r.l.Pasta Buttini s.r.l. ItalyItaly

Exemplo 1Exemplo 1

Buscando Valores DesconhecidosUSE northwindSELECT companyname, faxFROM suppliersWHERE fax IS NULLGO

USE northwindSELECT companyname, faxFROM suppliersWHERE fax IS NULLGO

companynamecompanyname faxfax

Exotic LiquidsExotic Liquids NULLNULL

New Orleans Cajun DelightsNew Orleans Cajun Delights NULLNULL

Tokyo TradersTokyo Traders NULLNULL

Cooperativa de Quesos ‘Las Cabras’Cooperativa de Quesos ‘Las Cabras’ NULLNULL

…… ……

Formatando os Resultados

• Ordenando Dados (Sort)• Eliminando linhas duplicadas• Mudando nomes de colunas• Usando Literais

Ordenando DadosUSE northwindSELECT productid, productname, categoryid, unitprice FROM products ORDER BY categoryid, unitprice DESCGO

USE northwindSELECT productid, productname, categoryid, unitprice FROM products ORDER BY categoryid, unitprice DESCGO

productidproductid productnameproductname categoryidcategoryid unitpriceunitprice

3838 Cote de BlayeCote de Blaye 11 263.5000263.5000

4343 Ipoh CoffeeIpoh Coffee 11 46.000046.0000

22 ChangChang 11 19.000019.0000

…… …… …… ……

6363 Vegie-spreadVegie-spread 22 43.900043.9000

88 Northwoods Cranberry SauceNorthwoods Cranberry Sauce 22 40.000040.0000

6161 Sirop d'érableSirop d'érable 22 28.500028.5000

…… …… …… ……

Exemplo 1Exemplo 1

Eliminando linhas duplicadas

USE northwindSELECT DISTINCT country FROM suppliers ORDER BY countryGO

USE northwindSELECT DISTINCT country FROM suppliers ORDER BY countryGO

countrycountry

AustraliaAustralia

BrazilBrazil

CanadaCanada

DenmarkDenmark

FinlandFinland

FranceFrance

GermanyGermany

ItalyItaly

JapanJapan

NetherlandsNetherlands

NorwayNorway

SingaporeSingapore

SpainSpain

SwedenSweden

UKUK

USAUSA

Exemplo 1Exemplo 1

Mudando nomes de colunasUSE northwindSELECT firstname AS First, lastname AS Last

,employeeid AS 'Employee ID:' FROM employeesGO

USE northwindSELECT firstname AS First, lastname AS Last

,employeeid AS 'Employee ID:' FROM employeesGO

FirstFirst LastLast Employee ID:Employee ID:

NancyNancy DavolioDavolio 11

AndrewAndrew FullerFuller 22

JanetJanet LeverlingLeverling 33

MargaretMargaret PeacockPeacock 44

StevenSteven BuchananBuchanan 55

MichaelMichael SuyamaSuyama 66

RobertRobert KingKing 77

LauraLaura CallahanCallahan 88

AnneAnne DodsworthDodsworth 99

Usando LiteraisUSE northwindSELECT firstname, lastname

,'Identification number:', employeeidFROM employeesGO

USE northwindSELECT firstname, lastname

,'Identification number:', employeeidFROM employeesGO

FirstFirst LastLast Employee ID:Employee ID:

NancyNancy DavolioDavolio Identification Number: 1Identification Number: 1

AndrewAndrew FullerFuller

JanetJanet LeverlingLeverling

MargaretMargaret PeacockPeacock

StevenSteven BuchananBuchanan

MichaelMichael SuyamaSuyama

RobertRobert KingKing

LauraLaura CallahanCallahan

AnneAnne DodsworthDodsworth

Identification Number: 2Identification Number: 2

Identification Number: 3Identification Number: 3

Identification Number: 4Identification Number: 4

Identification Number: 5Identification Number: 5

Identification Number: 6Identification Number: 6

Identification Number: 7Identification Number: 7

Identification Number: 8Identification Number: 8

Identification Number: 9Identification Number: 9

Considerações de Performance

• Buscas envolvendo NOT podem reduzir o desempenho de consultas

• Algumas Buscas envolvendo LIKE podem reduzir o desempenho de consultas

• Buscas exatas ou por faixas podem aumentar o desempenho de consultas

• ORDER BY pode reduzir o desempenho de consultas

Agrupamento e Sumarização

• Selecionando os TOP n Valores• Usando Funções de Agregação• GROUP BY Fundamentals• Usando as cláusulas COMPUTE e

COMPUTE BY

Selecionando os TOP n Valores• Lista somente as primeiras n linhas do Result Set• Order By determina a faixa de valores• Retorna empates se WITH TIES é usado

USE northwindSELECT TOP 5 orderid, productid, quantity FROM [order details] ORDER BY quantity DESCGO

USE northwindSELECT TOP 5 orderid, productid, quantity FROM [order details] ORDER BY quantity DESCGO

USE northwindSELECT TOP 5 WITH TIES orderid, productid, quantity FROM [order details] ORDER BY quantity DESCGO

USE northwindSELECT TOP 5 WITH TIES orderid, productid, quantity FROM [order details] ORDER BY quantity DESCGO

Exemplo 1Exemplo 1

Exemplo 2Exemplo 2

Usando Funções de Agregação

Aggregate functionAggregate function DescriptionDescription

AVGAVG Average of values in a numeric expressionAverage of values in a numeric expression

COUNTCOUNT Number of values in an expressionNumber of values in an expression

COUNT (*)COUNT (*) Number of selected rowsNumber of selected rows

MAXMAX Highest value in the expressionHighest value in the expression

MINMIN Lowest value in the expressionLowest value in the expression

SUMSUM Total values in a numeric expressionTotal values in a numeric expression

STDEVSTDEV Statistical deviation of all valuesStatistical deviation of all values

STDEVPSTDEVP Statistical deviation for the populationStatistical deviation for the population

VARVAR Statistical variance of all valuesStatistical variance of all values

VARPVARP Statistical variance of all values for the populationStatistical variance of all values for the population

Usando Funções de Agregaçãocom valores NULL

• A maioria das funções de agregação ignora valores Null• Função COUNT(*) também conta as linhas com valores

Null

USE northwindSELECT COUNT (*) FROM employeesGO

USE northwindSELECT COUNT (*) FROM employeesGO

USE northwindSELECT COUNT(reportsto) FROM employeesGO

USE northwindSELECT COUNT(reportsto) FROM employeesGO

Exemplo 1Exemplo 1

Exemplo 2Exemplo 2

Fundamentos GROUP BY

• Usando a cláusula GROUP BY • Usando a cláusula GROUP BY com a cláusula

HAVING

Usando a cláusula GROUP BY

USE northwindSELECT productid, orderid ,quantity FROM orderhistGO

USE northwindSELECT productid, orderid ,quantity FROM orderhistGO

USE northwindSELECT productid ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productidGO

USE northwindSELECT productid ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productidGO

productidproductid total_quantitytotal_quantity

11 1515

22 3535

33 4545

productidproductid orderidorderid quantityquantity

11 11 55

11 11 1010

22 11 1010

22 22 2525

33 11 1515

33 22 3030

productidproductid total_quantitytotal_quantity

22 3535

Somente linhas que satisfazem a cláusula Where são agrupadas

USE northwindSELECT productid ,SUM(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productidGO

USE northwindSELECT productid ,SUM(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productidGO

Usando a cláusula GROUP BY com a cláusula HAVING

USE northwindSELECT productid, orderid ,quantity FROM orderhistGO

USE northwindSELECT productid, orderid ,quantity FROM orderhistGO

USE northwindSELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid HAVING SUM(quantity)>=30GO

USE northwindSELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid HAVING SUM(quantity)>=30GO

productidproductid total_quantitytotal_quantity

22 3535

33 4545

productidproductid orderidorderid quantityquantity

11 11 55

11 11 1010

22 11 1010

22 22 2525

33 11 1515

33 22 3030

Usando as cláusulas COMPUTE e COMPUTE BY

COMPUTE BYCOMPUTEUSE northwindSELECT productid, orderid, quantity FROM orderhist ORDER BY productid, orderid COMPUTE SUM(quantity) BY productid COMPUTE SUM(quantity)GO

USE northwindSELECT productid, orderid, quantity FROM orderhist ORDER BY productid, orderid COMPUTE SUM(quantity) BY productid COMPUTE SUM(quantity)GO

USE northwindSELECT productid, orderid ,quantity FROM orderhistORDER BY productid, orderidCOMPUTE SUM(quantity)GO

USE northwindSELECT productid, orderid ,quantity FROM orderhistORDER BY productid, orderidCOMPUTE SUM(quantity)GO

productidproductid orderidorderid quantityquantity

11 11 55

11 22 1010

22 11 1010

22 22 2525

33 11 1515

33 22 3030

sumsum 9595

productidproductid orderidorderid quantityquantity

11 11 55

11 22 1010

sumsum 1515

22 11 1010

22 22 2525

sumsum 3535

33 11 1515

33 22 3030

sumsum 4545

sumsum 9595

Junção de dados de múltiplas tabelas

• Usando Aliases (apelidos) para Tabelas• Combinando dados de múltiplas Tabelas• Combinando múltiplos Result Sets• Criando uma Tabela de um Result Set

Usando Aliases (apelidos) para Tabelas

• Exemplo 1 (sem alias)

• Exemplo 2 (com 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

Combinando dados de múltiplas Tabelas

• Fazendo Inner Joins• Fazendo Outer Joins• Fazendo Cross Joins• Fazendo Joins de mais de Duas Tabelas• Fazendo um Join de uma tabela com ela

mesma (Self-Join)

USE joindbSELECT buyer_name, sales.buyer_id, qtyFROM buyers INNER JOIN salesON buyers.buyer_id = sales.buyer_idGO

USE joindbSELECT buyer_name, sales.buyer_id, qtyFROM buyers INNER JOIN salesON buyers.buyer_id = sales.buyer_idGO

Fazendo Inner Joins

sales

buyer_idbuyer_id prod_idprod_id qtyqty

11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

buyers

buyer_namebuyer_name

Adam BarrAdam Barr

Sean ChaiSean Chai

Eva CoretsEva Corets

Erin O’MeliaErin O’Melia

buyer_idbuyer_id

11

22

33

44

Result

buyer_namebuyer_name

Adam BarrAdam Barr

Adam BarrAdam Barr

Erin O’MeliaErin O’Melia

Eva CoretsEva Corets

buyer_idbuyer_id qtyqty

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

Fazendo Outer Joins

sales

buyer_idbuyer_id prod_idprod_id qtyqty

11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

buyers

buyer_namebuyer_name

Adam BarrAdam Barr

Sean ChaiSean Chai

Eva CoretsEva Corets

Erin O’MeliaErin O’Melia

buyer_idbuyer_id

11

22

33

44 Result

buyer_namebuyer_name

Adam BarrAdam Barr

Adam BarrAdam Barr

Erin O’MeliaErin O’Melia

Eva CoretsEva Corets

buyer_idbuyer_id qtyqty

11

11

44

33

1515

55

3737

1111

Erin O’MeliaErin O’Melia 44 10031003

Sean ChaiSean Chai NULLNULL NULLNULL

Exemplo 1Exemplo 1

Fazendo Cross JoinsUSE joindbSELECT buyer_name, qty FROM buyers CROSS JOIN salesGO

USE joindbSELECT buyer_name, qty FROM buyers CROSS JOIN salesGO

Result

buyer_namebuyer_name

Adam BarrAdam Barr

Adam BarrAdam Barr

Adam BarrAdam Barr

Adam BarrAdam Barr

qtyqty

1515

55

3737

1111

Adam BarrAdam Barr 10031003

Sean ChaiSean Chai 1515

Sean ChaiSean Chai 55

Sean ChaiSean Chai 3737

Sean ChaiSean Chai 1111

Sean ChaiSean Chai 10031003

Eva CoretsEva Corets 1515

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

sales

buyer_idbuyer_id prod_idprod_id qtyqty

11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

buyers

buyer_idbuyer_id

11

22

33

44

buyer_namebuyer_name

Adam BarrAdam Barr

Sean ChaiSean Chai

Eva CoretsEva Corets

Erin O’MeliaErin O’Melia

Exemplo 1Exemplo 1

Fazendo Joins de mais de duas tabelasSELECT 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

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_idGO

produce

prod_idprod_id prod_nameprod_name

11

22

33

44

ApplesApples

PearsPears

OrangesOranges

BananasBananas

55 PeachesPeaches

buyers

buyer_idbuyer_id

11

22

33

44

buyer_namebuyer_name

Adam BarrAdam Barr

Sean ChaiSean Chai

Eva CoretsEva Corets

Erin O’MeliaErin O’Melia

sales

buyer_idbuyer_id

11

11

33

44

prod_idprod_id

22

33

11

55

22 22

qtyqty

1515

55

3737

1111

10031003Result

buyer_namebuyer_name

Erin O’MeliaErin O’Melia

Adam BarrAdam Barr

Erin O’MeliaErin O’Melia

Adam BarrAdam Barr

Eva CoretsEva Corets

prod_nameprod_name

ApplesApples

PearsPears

PearsPears

OrangesOranges

PeachesPeaches

qtyqty

3737

1515

10031003

55

1111

Exemplo 1Exemplo 1

Fazendo um Self-JoinUSE joindbSELECT a.buyer_id AS buyer1, a.prod_id ,b.buyer_id AS buyer2 FROM sales AS a 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 JOIN sales AS b ON a.prod_id = b.prod_idWHERE a.buyer_id > b.buyer_idGO

sales b

buyer_idbuyer_id prod_idprod_id qtyqty

11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

sales a

buyer_idbuyer_id prod_idprod_id qtyqty

11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

Result

buyer1buyer1

44prod_idprod_id buyer2buyer2

22 11

Exemplo 3Exemplo 3

Combinando múltiplos Result Sets• Use o Operador UNION para Criar um

único Result Set a partir de múltiplas consultas

• Cada consulta deve ter:– Tipos de dados similares– Mesmo número de colunas– Colunas na Lista do Select na mesma ordem

USE northwindSELECT (firstname + ' ' + lastname) AS name ,city, postalcode FROM employeesUNIONSELECT companyname, city, postalcode FROM customersGO

USE northwindSELECT (firstname + ' ' + lastname) AS name ,city, postalcode FROM employeesUNIONSELECT companyname, city, postalcode FROM customersGO

USE northwindSELECT productname AS products, unitprice AS price, (unitprice * 1.1) AS taxINTO #pricetableFROM products

USE northwindSELECT productname AS products, unitprice AS price, (unitprice * 1.1) AS taxINTO #pricetableFROM products

Criando uma Tabela de um Result Set

• Use o comando SELECT INTO– Requer um nome exclusivo para a tabela– Pode-se criar uma tabela temporária local (#)

ou global (##)

Subqueries

• Introdução• Usando uma Subquery como uma Tabela

Derivada• Usando uma Subquery como uma Expressão• Usando uma Subquery para correlacionar dados• Usando as cláusulas EXISTS e NOT EXISTS

Introdução as Subqueries• Para que usar Subqueries?

– Quebrar um problema complexo numa série de passos lógicos

– Para executar uma consulta que depende dos dados de uma outra consulta

• Porque usar Joins em vez de Subqueries– SQL Server tende a executar Joins mais rápido que

Subqueries• Como usar Subqueries

Usando uma Subquery como uma Tabela Derivada

• É um Recordset dentro de uma consulta que funciona como uma Tabela

• Toma o lugar de uma tabela na cláusula FROM• É otimizada normalmente junto com o resto

da consultaUSE northwindSELECT T.orderid, T.customerid FROM ( SELECT orderid, customerid FROM orders ) AS TGO

USE northwindSELECT T.orderid, T.customerid FROM ( SELECT orderid, customerid FROM orders ) AS TGO

Usando uma Subquery como uma Expressão

• É avaliada e tratada como uma expressão• É executada somente uma vez

USE pubsSELECT title, price ,( SELECT AVG(price) FROM titles) AS average ,price-(SELECT AVG(price) FROM titles) AS difference FROM titles WHERE type='popular_comp'GO

USE pubsSELECT title, price ,( SELECT AVG(price) FROM titles) AS average ,price-(SELECT AVG(price) FROM titles) AS difference FROM titles WHERE type='popular_comp'GO

Usando uma Subquery para correlacionar dados

• Executando a Subquery correlacionada• Simulando a cláusula JOIN• Simulando a cláusula HAVING

Executando a Subquery correlacionada

Back to Step 1Back to Step 1

USE northwindSELECT orderid, customerid FROM orders AS or1 WHERE 20 < (SELECT quantity FROM [order details] AS od WHERE or1.orderid = od.orderid AND od.productid = 23)GO

USE northwindSELECT orderid, customerid FROM orders AS or1 WHERE 20 < (SELECT quantity FROM [order details] AS od WHERE or1.orderid = od.orderid AND od.productid = 23)GO

Consulta de fora passa os valores para a de dentro

Consulta de fora passa os valores para a de dentro

Consulta de dentro é executada com valores passados pela de fora

Consulta de dentro é executada com valores passados pela de fora

Consulta de dentro retorna os valores para a consulta de fora

Consulta de dentro retorna os valores para a consulta de fora

O mesmo proceso é repetido para cada uma das linhas da consulta de fora

O mesmo proceso é repetido para cada uma das linhas da consulta de fora

Exemplo 1Exemplo 1

Simulando a cláusula JOIN

• Subqueries correlacionadas podem produzir o mesmo resultado que um JOIN

• Joins permitem que o Query Optimizer determine como correlacionar os dados mais eficientemente

USE pubsSELECT DISTINCT t1.type FROM titles AS t1 WHERE t1.type IN (SELECT t2.type FROM titles AS t2 WHERE t1.pub_id <> t2.pub_id)GO

USE pubsSELECT DISTINCT t1.type FROM titles AS t1 WHERE t1.type IN (SELECT t2.type FROM titles AS t2 WHERE t1.pub_id <> t2.pub_id)GO

Exemplo 1Exemplo 1

Simulando a cláusula HAVING• Subquery com o mesmo resultado de se usar HAVING

• Usando a cláusula HAVING sem a Subquery

USE pubsSELECT t1.type, t1.title, t1.price FROM titles AS t1 WHERE t1.price > ( SELECT AVG(t2.price) FROM titles AS t2 WHERE t1.type = t2.type )GO

USE pubsSELECT t1.type, t1.title, t1.price FROM titles AS t1 WHERE t1.price > ( SELECT AVG(t2.price) FROM titles AS t2 WHERE t1.type = t2.type )GO

USE pubsSELECT t1.type, t1.title, t1.price FROM titles AS t1 INNER JOIN titles AS t2 ON t1.type = t2.type GROUP BY t1.type, t1.title, t1.price HAVING t1.price > AVG(t2.price)GO

USE pubsSELECT t1.type, t1.title, t1.price FROM titles AS t1 INNER JOIN titles AS t2 ON t1.type = t2.type GROUP BY t1.type, t1.title, t1.price HAVING t1.price > AVG(t2.price)GO

Exemplo 1Exemplo 1

Exemplo 2Exemplo 2

Usando uma Subquery correlacionada na cláusula HAVING

• Use a Subquery correlacionada na cláusula HAVING da consulta externa (Outer Query)

USE pubsSELECT t1.typeFROM titles t1GROUP BY t1.typeHAVING MAX(t1.advance) >= ALL (SELECT 2 * AVG(t2.advance) FROM titles t2 WHERE t1.type = t2.type)

USE pubsSELECT t1.typeFROM titles t1GROUP BY t1.typeHAVING MAX(t1.advance) >= ALL (SELECT 2 * AVG(t2.advance) FROM titles t2 WHERE t1.type = t2.type)

Usando as cláusulas EXISTS e NOT EXISTS• Use com consultas correlacionadas• Determine se o dados Existe ou não numa Lista

de Valores• Como o SQL Server processa a consulta

– Consulta externa testa se existem linhas a retornar– Consulta interna retorna TRUE ou FALSE– Nenhum dado é produzido

USE northwindSELECT lastname, employeeid FROM employees AS e WHERE EXISTS (SELECT * FROM orders AS o WHERE e.employeeid = o.employeeid AND o.orderdate = '9/5/97')GO

USE northwindSELECT lastname, employeeid FROM employees AS e WHERE EXISTS (SELECT * FROM orders AS o WHERE e.employeeid = o.employeeid AND o.orderdate = '9/5/97')GO

Exemplo 1Exemplo 1

Exercícios

1. Fazer download do script instnwnd.sql de ftp://ftp.cefetes.br/Cursos/Informatica/ELE-BancosdeDados/Scripts2. Abrir o SQL Server Management Studio e executar o script para criar o banco Northwind3. Verificar se o banco Northwind foi mesmo criado4. Criar um diagrama de todas as tabelas do banco, organizando as tabelas como no próximo slide

Exercícios

Exercícios Básicos5. Selecione as colunas FirstName e LastName da tabela Employees organizando a saída por Lastname 6. Selecione as colunas Title, FirstName e LastName da tabela Employees organizando a saída primeiro por Title e depois Lastname 7. Selecione as colunas Title, FirstName e LastName da tabela Employees organizando a saída primeiro pela coluna 1 e depois pela coluna 38. Selecione as colunas Title, FirstName e LastName da tabela Employees organizando a saída primeiro por Title em ordem ascendente e depois Lastname em ordem descendente9. Selecione as colunas Title, FirstName e LastName da tabela Employees listando apenas os funcionários que não tenham o título de ‘Sales Representative’

Exercícios Básicos10. Selecione as colunas FirstName e LastName da tabela Employees listando apenas os funcionários cujo Sobrenome comece com a letra "N"11. Selecione as colunas FirstName e LastName da tabela Employees listando apenas os funcionários cujo Sobrenome comece da letra "N" em diante (O,P,Q,R...)12. Selecione as colunas FirstName e LastName da tabela Employees listando apenas os funcionários cujo Nome comece da letra “M”, e tendo como segunda letra a letra “a” ou “i”13. Selecione as colunas FirstName , LastName e Region da tabela Employees listando apenas os funcionários em que se conhece a região em que os mesmos estão localizados

Exercícios Básicos14. Selecione as colunas FirstName e LastName da tabela Employees listando apenas os funcionários cujo Sobrenome comece com as letras de “J” a “M”15. Selecione as colunas TitleOfCourtesy, FirstName e LastName da tabela Employees listando apenas os funcionários cujo TitleOfCourtesy comece pela letra “M”16. Selecione as colunas TitleOfCourtesy, FirstName e LastName da tabela Employees listando apenas os funcionários cujo TitleOfCourtesy comece pela letra “M”, seguida por qualquer caracter e um sinal de ponto “.”17. Selecione as colunas TitleOfCourtesy, FirstName e LastName da tabela Employees listando apenas os funcionários cujo TitleOfCourtes não seja "Ms." ou "Mrs."

Exercícios Básicos18. Selecione as colunas FirstName e LastName da tabela Employees listando apenas os funcionários cujo TitleOfCourtesy comece pela letra “M” e cujo título seja de ‘Sales Representative’19. Selecione as colunas City, FirstName e LastName da tabela Employees listando apenas os funcionários que vivem nas cidades de Seattle ou Redmond20. Selecione as colunas FirstName e LastName da tabela Employees listando apenas os funcionários cujo título seja de ‘Sales Representative’ e vivam nas cidades de Seattle ou Redmond

Exercícios Avançados

1. Retorne os nomes dos produtos que começam com as letras a, d, e de m a z, ordenados por nome2. Retorne os nomes dos fornecedores que estão sem homepage informada 3. Mostre a lista de países dos clientes da empresa (tabela Customers), sem repetições4. Mostre os nomes dos clientes que não fizeram pedidos5. Mostre os nomes dos clientes que fizeram pedidos, informando a data em que os mesmos foram feitos6. Mostre os nomes de todos os clientes que fizeram ou não pedidos, informando a data em que os pedidos foram feitos ou a data com o valor NULL caso o cliente nunca tenha feito um pedido

Exercícios Avançados7. Mostre o nome de todos os clientes que nunca fizeram nenhum pedido, usando JOINS para resolver o problema8. Mostre o nome de todos os clientes que nunca fizeram nenhum pedido, usando SUBQUERIES para resolver o problema9. Gere uma lista de nomes de funcionários contendo a data dos pedidos feitos pelos mesmos e nome dos clientes que fizeram os pedidos10. Gere uma lista de pedidos feitos em 1996 e 1997, contendo a descrição de todos os produtos vendidos e a quantidade vendida dos mesmos por pedido11. Liste a quantidade total de itens de cada produto, vendidos até o presente momento12. Liste a quantidade total de itens de cada produto, vendidos em 1996

Exercícios Avançados13. Liste todos os produtos e quantidade totais vendidas, desde que tenham vendido mais que 3 unidades14. Gere uma listagem contendo nomes de clientes fictícios que consistirão da combinação de nomes e sobrenomes de cada um dos clientes existentes15. Mostrar quantos produtos existem em cada categoria, incluindo o nome o nome de cada categoria16. Listar o valor total em vendas realizadas por empregado no mês de Abril/199717. Listar o nome e a soma dos valores de pedidos feitos para cada produto no mês de Março/1998. Listar somente os 10 mais pedidos em função do valor total pedido18. Listar os 5 fornecedores que mais foram acionados no ano de 1997, por total de vendas. Mostrar o nome do fornecedor e o total da venda de cada um deles.

Exercícios Avançados19. Listar o total de vendas realizadas mês a mês. Mostrar o mês/ano e o total de vendas realizadas naquele mês/ano20. Gerar uma listagem de funcionários contendo o nome completo dos mesmos (Nome + Sobrenome) 21. Gerar uma listagem de funcionários contendo o nome completo do mesmos e o nome completo dos seus chefes imediatos22. Exibir um ranking dos três países que mais compraram produtos desde 199723. Exiba uma listagem contendo o valor de comissão pago a cada vendedor, mês a mês, durante o ano de 1997. Supor que a comissão paga seja de 1% das vendas