Área de Banco de Dados Transact SQL : Consultando o Banco de Dados

Students to Business – 2011/1Fase 2

Área de Banco de DadosTransact SQL : Consultando o Banco de Dados

Agenda

• Listando dados com TOP n

• Usando funções agregadas

• Noções básicas sobre a cláusula GROUP BY

• Gerando valores agregados em conjuntosde resultados

• Usando as cláusulas COMPUTE e COMPUTE BY

• Hands On: Comandos de DML Avançados

• Laboratório: Consultando o ambiente AERO

Listando os primeiros n valores com TOP n

• Lista apenas os n primeiros registros de um conjunto de resultados

• Especifica o intervalo de valores na cláusula ORDER BY

• Retorna correspondências se WITH TIES for usada

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 agregadas

Funções agregadasFunções agregadasFunções agregadasFunções agregadas DescriçãoDescriçãoDescriçãoDescrição

AVGAVG Média de valores em uma expressão numéricaMédia de valores em uma expressão numérica

COUNTCOUNT Número de valores em uma expressãoNúmero de valores em uma expressão

COUNT (*)COUNT (*) Número de registros selecionadosNúmero de registros selecionados

MAXMAX Maior valor da expressãoMaior valor da expressão

MINMIN Menor valor da expressãoMenor valor da expressão

SUMSUM Valores totais em uma expressão numéricaValores totais em uma expressão numérica

STDEVSTDEV Desvio padrão de todos os valoresDesvio padrão de todos os valores

STDEVPSTDEVP Desvio padrão da populaçãoDesvio padrão da população

VARVAR Variância estatística de todos os valoresVariância estatística de todos os valores

VARPVARP Variância estatística de todos os valores da população

Variância estatística de todos os valores da população

Usando funções agregadas com valores nulos

• A maioria das funções agregadas ignora valores nulos

• A função COUNT (*) conta todos os registros com valores nulos

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

Noções básicas sobre a cláusula 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 ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productidGO

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

productidproductidproductidproductid orderidorderidorderidorderid quantityquantityquantityquantity

11 11 55

11 11 1010

22 11 1010

22 22 2525

33 11 1515

33 22 3030

productidproductidproductidproductid total_quantitytotal_quantitytotal_quantitytotal_quantity

11 1515

22 3535

33 4545


22 3535

Apenas osregistros que satisfazem às condições da cláusula WHERE serão agrupados

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, 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


22 3535

33 4545


11 11 55

11 11 1010

22 11 1010

22 22 2525

33 11 1515

33 22 3030

Gerando valores agregados em conjuntos de resultados

• Usando a cláusula GROUP BY com o operador ROLLUP

• Usando a cláusula GROUP BY com o operador CUBE

• Usando a função GROUPING

Usando a cláusula GROUP BY com o operador ROLLUP

USE northwindSELECT productid, orderid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH ROLLUP ORDER BY productid, orderidGO

USE northwindSELECT productid, orderid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH ROLLUP ORDER BY productid, orderidGO

Descriçãoproductidproductidproductidproductid orderidorderidorderidorderid total_quantitytotal_quantitytotal_quantitytotal_quantity

NULLNULL NULLNULL 9595

11 NULLNULL 1515

11 11 55

11 22 1010

22 NULLNULL 3535

22 11 1010

22 22 2525

33 NULLNULL 4545

33 11 1515

33 22 3030

Total geralTotal geral

Resume apenas os registros de productid 1Resume apenas os registros de productid 1

Valores de detalhe de productid 1, orderid 1Valores de detalhe de productid 1, orderid 1







USE northwindSELECT productid, orderid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderidGO

USE northwindSELECT productid, orderid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderidGO

Usando a cláusula GROUP BY com o operador CUBE

O operador CUBEproduz doisvalores deresumo a maisque o operador ROLLUP

Descriçãoproductidproductidproductidproductid orderidorderidorderidorderid total_quantitytotal_quantitytotal_quantitytotal_quantity

NULLNULL NULLNULL 9595

NULLNULL 11 3030

NULLNULL 22 6565

11 NULLNULL 1515

11 11 55

11 22 1010

22 NULLNULL 3535

22 11 1010

22 22 2525

33 NULLNULL 4545

33 11 1515

33 22 3030

Total geralTotal geral

Resume todos os registros de orderid 1Resume todos os registros de orderid 1

Resume todos os registros de orderid 2Resume todos os registros de orderid 2










1 representa os valores de resumona coluna anterior

0 representa os valores de detalhe na na coluna anterior

Usando a função GROUPING

USE northwindSELECT productid, GROUPING (productid) ,orderid, GROUPING (orderid) ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderidGO

USE northwindSELECT productid, GROUPING (productid) ,orderid, GROUPING (orderid) ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderidGO

productidproductidNULL

NULL

NULL

1

1

1

2

2

2

3

3

3

1

1

1

0

0

0

0

0

0

0

0

0

orderidorderidNULL

1

2

NULL

1

2

NULL

1

2

NULL

1

2

1

0

0

1

0

0

1

0

0

1

0

0

total_quantitytotal_quantity95

30

65

15

5

10

35

10

25

45

15

30

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 orderhist ORDER BY productid, orderid COMPUTE SUM(quantity)GO

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


11 11 55

11 22 1010

22 11 1010

22 22 2525

33 11 1515

33 22 3030

sumsum 9595


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

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

productidproductidproductidproductid productnameproductnameproductnameproductname supplieridsupplieridsupplieridsupplierid unitpriceunitpriceunitpriceunitprice

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

Recuperando um intervalo de valores

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

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

productnameproductnameproductnameproductname unitpriceunitpriceunitpriceunitprice

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 como critérios de pesquisa

companynamecompanynamecompanynamecompanyname countrycountrycountrycountry

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

Recuperando valores Nulos

USE northwindSELECT companyname, faxFROM suppliersWHERE fax IS NULLGO

USE northwindSELECT companyname, faxFROM suppliersWHERE fax IS NULLGO

companynamecompanynamecompanynamecompanyname faxfaxfaxfax

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 conjuntos de resultados

• Classificando dados

• Ocultando registros duplicados

• Alterando nomes de colunas

• Usando literais

Classificando dados

USE 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

productidproductidproductidproductid productnameproductnameproductnameproductname categoryidcategoryidcategoryidcategoryid unitpriceunitpriceunitpriceunitprice

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

Ocultando registros duplicados

USE northwindSELECT DISTINCT country FROM suppliers ORDER BY countryGO

USE northwindSELECT DISTINCT country FROM suppliers ORDER BY countryGO

countrycountrycountrycountry

AustraliaAustralia

BrazilBrazil

CanadaCanada

DenmarkDenmark

FinlandFinland

FranceFrance

GermanyGermany

ItalyItaly

JapanJapan

NetherlandsNetherlands

NorwayNorway

SingaporeSingapore

SpainSpain

SwedenSweden

UKUK

USAUSA

Exemplo 1Exemplo 1

Alterando nomes de colunas

USE 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

FirstFirstFirstFirst LastLastLastLast Employee ID:Employee ID: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 literais

USE northwindSELECT firstname, lastname,

'Identification number:', employeeidFROM employeesGO

USE northwindSELECT firstname, lastname,

'Identification number:', employeeidFROM employeesGO

firstnamefirstname firstnamefirstname lastnamelastname lastnamelastname employeeidemployeeid employeeidemployeeid

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








Visão geral

• Usando aliases para nomes de tabelas

• Combinando dados de várias tabelas

• Combinando vários conjuntos de resultados

Usando aliases para nomes de tabelas

• 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

Combinando dados de várias tabelas

• 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

Introdução às associações

• 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 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

Associando mais de duas tabelas

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

Associando uma tabela a si mesma

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

Combinando vários conjuntos de resultados

• Usar o operador UNION para criar um único conjuntode resultados de várias consultas

• As consultas precisam de:– Tipos de dados semelhantes– Número de colunas idêntico– Ordem de colunas idêntica na lista de seleções

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

Hands on: Comandos de seleção Avançados

• Acompanhe junto com o seu instrutor os comandos nos detalhes deste slide. Execute o código e verifique alguns dos principais comandos de seleção do SQL Server.

• Você acompanhará consultas básicas e avanças.

• Após, nos demais slides você encontrará detalhes sobre cada comando.

Laboratório: Considerando a Base de Dados AERO, construa queries para responder as seguintes perguntas

1) O nome de todos os pilotos da varig.2) O nome de todos os pilotos, junto com seu salário e gratificação.3) O nome da cidade e país de destino do vôo RG230.4) A companhia dos vôos escalados para 1-Maio-2003.5) O código de todos os vôos, nome dos pilotos escalados para os mesmos, e respectivos tipos de avião e companhia.6) O código de todos os vôos que iniciam por RG ou VS, junto com a respectiva companhia.7) O código de todos os vôos para a Alemanha ou Itália, com as respectivas data e hora de saída.8) O nome de todos os aeroportos onde a varig opera.9) O salário do piloto mais bem pago da varig.10) O menor, maior, e média dos salários dos pilotos de companhias brasileiras.11) O número de aeroportos no Brasil.12) O número de diferentes vôos que a varig oferece para São Paulo.13) Para cada companhia, o menor, maior salário, bem como a média.14) Para cada cidade do Brasil, o número de aeroportos que possui.15) Para cada dia e hora, o número de vôos que partem pela manhã.16) As companhias que pagam como salário mais alto a seus pilotos menos que 2.000.17) As companhias brasileiras que possuem pelo menos 2 pilotos.18) As companhias que só usam um tipo de avião.19) O código dos pilotos cujos vôos escalados sempre tem a mesma destinação.20) O nome do país que é destino apenas de vôos para os quais sempre o mesmo piloto é escalado.21) O nome das companhias que voam de MD11.22) O nome das companhias que não voam de MD11.23) Nome dos pilotos que voam de MD11 ou de 737.24) Nome dos pilotos que voam de MD11 e de 737.25) Nome das companhias que empregam pelo menos dois pilotos.26) Nome dos pilotos que trabalham para companhias que empregam pelo menos dois pilotos.27) Nome dos pilotos que ganham menos que a média salarial.28) Selecione todas as companhia brasileiras onde a media salarial é maior que 3000.29) Selecione o piloto com maior salario da varig.30) Selecione o piloto com maior salario de cada companhia.

Documents

Área de Banco de Dados Transact SQL : Consultando o Banco de Dados