SQL – Comandos de
Agregação
Profa. Sandra de Amo
Capitulo 5 – Livro Texto
Database Management Systems
Ramakrishnan - Gehrke
OPERADORES DE AGREGAÇÃO

COUNT ([DISTINCT] A)


SUM ([DISTINCT] A)


Média dos valores da coluna A
MAX(A)


Soma dos valores da coluna A
AVG ([DISTINCT] A)


Número de valores da coluna A
Maior valor da coluna A
MIN(A)

Menor valor da coluna A
Exemplos: AVG, Sum

Dê a média das idades dos marinheiros
SELECT AVG(S.Idade)
FROM Sailors S

Dê a soma das idades dos marinheiros com
status 10
SELECT Sum(S.Idade)
FROM Sailors S
WHERE S.rating = 10
Exemplos: MAX, MIN (ERRADO)

Dê o nome e idade do marinheiro mais velho
SELECT S.Snome, MAX(S.Idade)
FROM Sailors S
Agregado não pode aparecer junto com outro atributo
A MENOS QUE SE UTILIZE O OPERADOR
GROUP BY
Exemplos: MAX, MIN (CORRETO)

Dê o nome e idade do marinheiro mais velho
SELECT S.Snome, S.Idade
FROM Sailors S
WHERE S.Idade = (SELECT Max(S2.Idade)
FROM Sailors S2)
Resultado da consulta é uma tabela com um único
elemento (um número)
Tabela é transformada em um número
Exemplos (NEM SEMPRE ACEITO
POR ALGUNS SGBDs)

Dê o nome e idade do marinheiro mais velho
SELECT S.Snome, S.Idade
FROM Sailors S
WHERE (SELECT Max(S2.Idade)
FROM Sailors S2) = S.Idade
Exemplo: COUNT

Conte o número de marinheiros
SELECT COUNT (*)
FROM Sailors

Conte os nomes diferentes de marinheiros
SELECT COUNT( DISTINCT S.Snome)
FROM Sailors
Substituindo ALL por MAX

Encontre o nome dos marinheiros que são mais velhos do que
o marinheiro mais velho que tem status 10.
SELECT S.Snome
FROM Sailors S
WHERE S.Idade >= ALL (SELECT S2.Idade
FROM Sailors S2
WHERE S2.Status = 10)
SELECT S.Snome
FROM Sailors S
WHERE S.Idade >= (SELECT MAX(S2.Idade)
FROM Sailors S2
WHERE S2.Status = 10)
Substituindo ANY por MIN

Encontre o nome dos marinheiros que são mais velhos do que
algum marinheiro que tem status 10.
SELECT S.Snome
FROM Sailors S
WHERE S.Idade >= ANY (SELECT S2.Idade
FROM Sailors S2
WHERE S2.Status = 10)
SELECT S.Snome
FROM Sailors S
WHERE S.Idade >= (SELECT MIN(S2.Idade)
FROM Sailors S2
WHERE S2.Status = 10)
Exercicio 6

Dê a soma das idades e a média dos status
dos marinheiros que reservaram barcos
vermelhos.
SELECT Sum(S.Idade), AVG(S.Status)
FROM Sailors S
WHERE S.Sid IN (SELECT R.Sid
FROM Reservas R, Barcos B
WHERE B.Bid = R.Bid AND
B.Cor = ‘Vermelho’)
GROUP BY - HAVING
SELECT <lista-atributos>
FROM <lista-tabelas>
WHERE <condição sobre tuplas>
GROUP BY <lista-atributos-de-agrupamento>
HAVING <condição sobre os grupos>
Exemplo
Para cada nível de status, dê a idade do marinheiro mais jovem
neste status

Sailors
Sid
22
29
31
32
58
64
71
74
85
95
SnomeStatus Idade
N1
7
45
33
N2
1
8
55,5
N3
N4
25,5
8
35
N5
10
35
N6
7
10
N7
16
N6
9
35
N8
3
25,5
3
N9
63,5
Sailors
Sid Snome Status Idade
22 N1
7
45
35
7
64 N6
8
31 N3
55,5
25,5
8
32 N4
10
58 N5
35
10
71 N7
16
35
74 N6
9
29 N2
33
1
3
85 N8
25,5
95 N9
3
63,5
SELECT S.Status, MIN(S.Idade)
AS M-Age
FROM Sailors S
GROUP BY S.Status
Resposta
Status M-Age
7
35
8
25,5
10
16
9
35
1
33
3
25,5
Exemplo

Para cada nível de status, dê a idade do
marinheiro mais jovem neste status,
excluindo-se o status 1
SELECT S.Status, Min(S.Idade) AS M-Age
FROM Sailors S
GROUP BY S.Status
HAVING S.Status <> 1
Exemplo

Para cada nível de status diferente de 1, dê a idade
do marinheiro mais jovem neste status que reservou
o barco ‘102’.
SELECT S.Status, Min(S.Idade)
FROM Sailors S
WHERE S.Sid in (Select R.Sid
FROM Reservas R
WHERE R.Bid = ‘102’ )
GROUP BY S.Status
HAVING S.Status <> 1
Exemplo

Encontre a idade do marinheiro mais jovem mas
que possa votar (com pelo menos 18 anos) para
cada nivel de status com ao menos dois marinheiros
neste nível.
Sailors
SELECT S.Status, Min(S.Idade)
AS M-Age
FROM Sailors S
WHERE S.Idade >= 18
GROUP BY S.Status
HAVING COUNT(*) > 1
Sid Snome Status Idade
22 N1
7
45
35
7
64 N6
8
31 N3
55,5
25,5
8
32 N4
58 N5
10
35
71 N7
10
16
9
35
74 N6
29 N2
33
1
25,5
3
85 N8
95 N9
3
63,5
Resposta
Status
M-Age
7
35
8
25,5
3
25,5
Exemplo

Encontre a idade do marinheiro mais jovem mas
que possa votar (com pelo menos 18 anos) para
cada nivel de status com ao menos dois marinheiros
neste nível. Exiba a resposta ordenada por Status.
SELECT S.Status, Min(S.Idade) AS M-Age
FROM Sailors S
WHERE S.Idade >= 18
GROUP BY S.Status
HAVING COUNT(*) > 1
ORDER BY S.Status
Resposta
Status M-Age
3
25,5
7
35
8
25,5
Exemplo

Encontre a idade do marinheiro mais jovem mas
que possa votar (com pelo menos 18 anos) para
cada nivel de status com ao menos dois marinheiros
neste nível e onde todo marinheiro neste nível
tenha no máximo 60 anos.
Sailors
SELECT S.Status, Min(S.Idade)
AS Min-A
FROM Sailors S
WHERE S.Idade >= 18
GROUP BY S.Status
HAVING COUNT(*) > 1 AND
EVERY (S.Idade <= 60)
Sid Snome Status Idade
22 N1
7
45
7
64 N6
35
8
55,5
31 N3
32 N4
25,5
8
58 N5
10
35
71 N7
10
16
9
74 N6
35
29 N2
1
33
3
25,5
85 N8
63,5
3
95 N9
97 N10
24
3
Resposta
Status Min-A
35
7
8
25,5
A consulta a seguir é equivalente à
precedente ?
SELECT S.Status, Min(S.Idade)
FROM Sailors S
WHERE S.Idade >= 18 AND S.Idade <= 60
GROUP BY S.Status
HAVING COUNT(*) > 1
Resposta : Não são equivalentes !
SELECT S.Status, Min(S.Idade) AS
Min-AGE
FROM Sailors S
WHERE S.Idade >= 18 AND S.Idade
<= 60
GROUP BY S.Status
HAVING COUNT(*) > 1
Resposta
Sailors
SidSnome Status Idade
22 N1
7
45
7
64 N6
35
8
55,5
31 N3
32 N4
25,5
8
58 N5
10
35
16
71 N7
10
9
74 N6
35
1
33
29 N2
3
25,5
85 N8
95 N9
3
63,5
97 N10
3
24
Status Min-A
35
7
Encontre a idade e status dos marinheiros mais jovens que
possam votar (com pelo menos 18 anos) e que tenham no
máximo 60 anos, agrupados por status, onde cada grupo tem ao
menos dois marinheiros nestas condições
8
3
25,5
24
Exemplo

Encontre a idade do marinheiro mais jovem mas
que possa votar (com pelo menos 18 anos) para
cada nivel de status com ao menos dois marinheiros
neste nível e onde pelo menos um marinheiro no
nível tenha idade inferior a 60 anos.
SELECT S.Status, Min(S.Idade)
FROM Sailors S
WHERE S.Idade >= 18
GROUP BY S.Status
HAVING COUNT(*) > 1 AND ANY (S.Idade <= 60)
Exercício 7

Para cada barco vermelho, dê o número
de reservas que foram feitas para este
barco.
SELECT COUNT(*) AS ContadorReserva
FROM Barcos B, Reservas R
WHERE R.Bid = R. Bid and B.Cor = ‘Verm’
GROUP BY B.Bid
Exercicio 8

Encontre a média de idade dos
marinheiros com mais de 18 anos, para
cada nível de status que tem ao menos
dois marinheiros.
SELECT S.Status, AVG(S.Idade) AS Min-AGE
FROM Sailors S
WHERE S.Idade > 18
GROUP BY S.Status
HAVING COUNT(*) > 1
Consultas dentro do comando FROM

Dê os status para os quais a média de idades dos
marinheiros neste status é igual à menor das médias
de idades de cada status.
SELECT Temp.Status
FROM (SELECT S.Status, AVG(S.Idade)
FROM Sailors S
GROUP BY S.Status) AS Temp
WHERE Temp.AVG =
(SELECT MIN (Temp.AVG)
FROM Temp)
Resumo Geral do uso de Agregados
SELECT <lista-seleção>
FROM <lista-tabelas>
WHERE <condição-sobre-tuplas>
GROUP BY <lista-atributos-de-agrupamento>
HAVING <condição-sobre-os-grupos>
1. lista-seleção = lista de atributos + lista de termos do tipo
operador(atributo) AS novo-atributo
2. Todo atributo que aparece em “lista de atributos” deve aparecer na
lista-atributos-de-agrupamento.
3. Atributos que aparecem em condição-sobre-os-grupos devem
aparecer em “operador(atributo)” ou em lista-atributos-deagrupamento.
Exemplo
SELECT S.Status ,Min( S.Idade) AS Min-Age
FROM Sailors S
WHERE S.Idade >= 18
GROUP BY S.Status
HAVING S.Status > 5 AND
EVERY ( S.Idade <= 60)
Particularidade de SQL 1999
Se a lista-atributos-de-agrupamento contém a chave primária de
uma tabela da lista-tabelas então cada coluna desta tabela só tem
um único valor em cada grupo. Em SQL1999, tais colunas podem
aparecer na lista de atributos da lista-seleção.
SELECT S.Status, S.Snome, AVG(S.Idade) AS
Min-AGE
FROM Sailors S
Chave de Sailors
GROUP BY S.Sid
Logo: os grupos têm um
único elemento
HAVING COUNT(*) > 1