Students to Business – 2011/1
Fase 2
Área de Banco de Dados
Transact 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 conjuntos
de 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
Exemplo 1
USE northwind
SELECT TOP 5 orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
GO
Exemplo 2
USE northwind
SELECT TOP 5 WITH TIES orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
GO
Usando funções agregadas
Funções agregadas
Descrição
AVG
Média de valores em uma expressão numérica
COUNT
Número de valores em uma expressão
COUNT (*)
Número de registros selecionados
MAX
Maior valor da expressão
MIN
Menor valor da expressão
SUM
Valores totais em uma expressão numérica
STDEV
Desvio padrão de todos os valores
STDEVP
Desvio padrão da população
VAR
Variância estatística de todos os valores
VARP
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 northwind
SELECT COUNT (*)
FROM employees
GO
Exemplo 1
USE northwind
SELECT COUNT(reportsto)
FROM employees
GO
Exemplo 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 northwind
SELECT productid, orderid
,quantity
FROM orderhist
GO
productid
orderid
USE northwind
SELECT productid
,SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid
GO
quantity
1
1
5
1
1
10
2
1
10
2
2
25
3
1
15
3
2
30
productid
Apenas os
registros que
satisfazem às
condições da
cláusula WHERE
serão agrupados
total_quantity
1
15
2
35
3
45
productid
2
total_quantity
35
USE northwind
SELECT productid
,SUM(quantity) AS total_quantity
FROM orderhist
WHERE productid = 2
GROUP BY productid
GO
Usando a cláusula GROUP BY
com a cláusula HAVING
USE northwind
SELECT productid, orderid
,quantity
FROM orderhist
GO
productid
orderid
quantity
USE northwind
SELECT productid, SUM(quantity)
AS total_quantity
FROM orderhist
GROUP BY productid
HAVING SUM(quantity)>=30
GO
1
1
5
1
1
10
2
1
10
2
35
2
2
25
3
45
3
1
15
3
2
30
productid
total_quantity
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 northwind
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH ROLLUP
ORDER BY productid, orderid
GO
productid
orderid total_quantity
Descrição
NULL
NULL
95
1
NULL
15
1
1
5
1
2
10
2
NULL
35
2
1
10
2
2
25
Valores de detalhe de productid 2, orderid 1
3
NULL
45
Resume apenas os registros de productid 3
3
1
15
Valores de detalhe de productid 3, orderid 1
3
2
30
Valores de detalhe de productid 3, orderid 2
Total geral
Resume apenas os registros de productid 1
Valores de detalhe de productid 1, orderid 1
Valores de detalhe de productid 1, orderid 2
Resume apenas os registros de productid 2
Usando a cláusula GROUP BY
com o operador CUBE
USE northwind
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH CUBE
ORDER BY productid, orderid
GO
productid
orderid total_quantity
Descrição
NULL
NULL
95
Total geral
NULL
1
30
Resume todos os registros de orderid 1
O operador CUBE NULL
produz dois
1
valores de
1
resumo a mais
que o operador
1
ROLLUP
2
2
65
Resume todos os registros de orderid 2
NULL
15
Resume apenas os registros de productid 1
1
5
Valores de detalhe de productid 1, orderid 1
2
10
Valores de detalhe de productid 1, orderid 2
NULL
35
Resume apenas os registros de productid 2
2
1
10
Valores de detalhe de productid 2, orderid 1
2
2
25
Valores de detalhe de productid 2, orderid 2
3
NULL
45
Resume apenas os registros de productid 3
3
1
15
Valores de detalhe de productid 3, orderid 1
3
2
30
Valores de detalhe de productid 3, orderid 2
Usando a função GROUPING
USE northwind
SELECT productid, GROUPING (productid)
,orderid, GROUPING (orderid)
,SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH CUBE
ORDER BY productid, orderid
GO
productid
1 representa os valores de resumo
na coluna anterior
0 representa os valores de detalhe na
na coluna anterior
NULL
NULL
NULL
1
1
1
2
2
2
3
3
3
orderid
1
1
1
0
0
0
0
0
0
0
0
0
NULL
1
2
NULL
1
2
NULL
1
2
NULL
1
2
total_quantity
1
0
0
1
0
0
1
0
0
1
0
0
95
30
65
15
5
10
35
10
25
45
15
30
Usando as cláusulas COMPUTE e COMPUTE BY
COMPUTE
COMPUTE BY
USE northwind
SELECT productid, orderid
,quantity
FROM orderhist
ORDER BY productid, orderid
COMPUTE SUM(quantity)
GO
USE northwind
SELECT productid, orderid, quantity
FROM orderhist
ORDER BY productid, orderid
COMPUTE SUM(quantity) BY productid
COMPUTE SUM(quantity)
GO
productid
orderid
quantity
productid
orderid
quantity
1
1
5
1
1
5
1
2
10
1
2
10
2
1
10
2
1
10
2
2
25
2
2
25
3
1
15
3
2
30
3
1
15
95
3
2
30
sum
sum
sum
15
35
sum
45
sum
95
Usando operadores lógicos
Exemplo 1
USE northwind
SELECT productid, productname, supplierid, unitprice
FROM products
WHERE (productname LIKE 'T%' OR productid = 46)
AND (unitprice > 16.00)
GO
productid
14
29
62
productname
Tofu
Thüringer Rostbratwurst
Tarte au sucre
supplierid
6
12
29
unitprice
23.25
123.79
49.3
Recuperando um intervalo de valores
USE northwind
SELECT productname, unitprice
FROM products
WHERE unitprice BETWEEN 10 AND 20
GO
productname
Chai
Chang
Aniseed Syrup
Genen Shouyu
Pavlova
Sir Rodney’s Scones
…
unitprice
18
19
10
15.5
17.45
10
…
Exemplo 1
Usando uma lista de valores como critérios de pesquisa
Exemplo 1
USE northwind
SELECT companyname, country
FROM suppliers
WHERE country IN ('Japan', 'Italy')
GO
companyname
Tokyo Traders
Mayumi’s
Formaggi Fortini s.r.l.
Pasta Buttini s.r.l.
country
Japan
Japan
Italy
Italy
Recuperando valores Nulos
USE northwind
SELECT companyname, fax
FROM suppliers
WHERE fax IS NULL
GO
companyname
Exotic Liquids
New Orleans Cajun Delights
Tokyo Traders
Cooperativa de Quesos ‘Las Cabras’
…
fax
NULL
NULL
NULL
NULL
…
Formatando conjuntos de resultados
•
•
•
•
Classificando dados
Ocultando registros duplicados
Alterando nomes de colunas
Usando literais
Classificando dados
Exemplo 1
USE northwind
SELECT productid, productname, categoryid, unitprice
FROM products
ORDER BY categoryid, unitprice DESC
GO
productid
38
43
2
…
63
8
61
…
productname
Cote de Blaye
Ipoh Coffee
Chang
…
Vegie-spread
Northwoods Cranberry Sauce
Sirop d'érable
…
categoryid
1
1
1
…
2
2
2
…
unitprice
263.5000
46.0000
19.0000
…
43.9000
40.0000
28.5000
…
Ocultando registros duplicados
Exemplo 1
USE northwind
SELECT DISTINCT country
FROM suppliers
ORDER BY country
GO
country
Australia
Brazil
Canada
Denmark
Finland
France
Germany
Italy
Japan
Netherlands
Norway
Singapore
Spain
Sweden
UK
USA
Alterando nomes de colunas
USE northwind
SELECT firstname AS First, lastname AS Last
,employeeid AS 'Employee ID:'
FROM employees
GO
First
Nancy
Andrew
Janet
Margaret
Steven
Michael
Robert
Laura
Anne
Last
Davolio
Fuller
Leverling
Peacock
Buchanan
Suyama
King
Callahan
Dodsworth
Employee ID:
1
2
3
4
5
6
7
8
9
Usando literais
USE northwind
SELECT firstname, lastname,
'Identification number:', employeeid
FROM employees
GO
firstname
Nancy
Andrew
Janet
Margaret
Steven
Michael
Robert
Laura
Anne
lastname
Davolio
Fuller
Leverling
Peacock
Buchanan
Suyama
King
Callahan
Dodsworth
employeeid
Identification Number: 1
Identification Number: 2
Identification Number: 3
Identification Number: 4
Identification Number: 5
Identification Number: 6
Identification Number: 7
Identification Number: 8
Identification Number: 9
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)
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
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
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
...
Associando mais de duas tabelas
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
Associando uma tabela a si mesma
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
Combinando vários conjuntos
de resultados
• Usar o operador UNION para criar um único
conjunto
de 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 northwind
SELECT (firstname + ' ' + lastname) AS name
,city, postalcode
FROM employees
UNION
SELECT companyname, city, postalcode
FROM customers
GO
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.