1 / 26

SQL – Comandos de Agregação

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) Número de valores da coluna A SUM ([DISTINCT] A) Soma dos valores da coluna A AVG ([DISTINCT] A)

tarala
Download Presentation

SQL – Comandos de Agregação

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SQL – Comandos de Agregação Profa. Sandra de Amo Capitulo 5 – Livro Texto Database Management Systems Ramakrishnan - Gehrke

  2. OPERADORES DE AGREGAÇÃO • COUNT([DISTINCT] A) • Número de valores da coluna A • SUM ([DISTINCT] A) • Soma dos valores da coluna A • AVG ([DISTINCT] A) • Média dos valores da coluna A • MAX(A) • Maior valor da coluna A • MIN(A) • Menor valor da coluna A

  3. 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

  4. 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

  5. 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

  6. 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

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

  8. 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)

  9. 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)

  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’)

  11. 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>

  12. Exemplo • Para cada nível de status, dê a idade do marinheiro mais jovem neste status Sailors Resposta Sailors Snome Idade Status Sid Snome Idade Sid Status M-Age Status 22 N1 7 45 22 N1 7 45 7 35 N6 35 33 7 29 N2 64 1 8 25,5 8 31 55,5 N3 8 31 55,5 N3 N4 25,5 8 32 N4 25,5 8 32 10 16 35 N5 10 58 N5 10 58 35 35 64 N6 7 N7 10 71 16 9 35 10 N7 71 16 35 74 N6 9 N6 9 29 N2 33 74 35 1 33 1 N8 3 85 25,5 N8 3 85 25,5 3 25,5 95 3 N9 63,5 95 3 N9 63,5 SELECT S.Status, MIN(S.Idade) AS M-Age FROM Sailors S GROUP BY S.Status

  13. 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

  14. 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

  15. Resposta Status 35 7 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. SELECT S.Status, Min(S.Idade) AS M-Age FROM Sailors S WHERE S.Idade >= 18 GROUP BY S.Status HAVING COUNT(*) > 1 Sailors Snome Idade Sid Status M-Age 22 N1 7 45 N6 35 7 64 8 31 55,5 N3 N4 25,5 8 32 58 N5 10 35 71 N7 10 16 N6 9 35 74 29 N2 33 1 N8 25,5 3 85 95 3 N9 63,5

  16. 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 35 7 25,5 8

  17. 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. 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) Resposta Sailors Snome Idade Sid Status Status Min-A 22 N1 7 45 35 7 7 64 N6 35 8 55,5 N3 31 8 25,5 N4 32 25,5 8 58 N5 10 35 71 N7 10 16 N6 9 74 35 29 N2 1 33 N8 3 25,5 85 63,5 N9 3 95 97 24 N10 3

  18. 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

  19. Resposta : Não são equivalentes ! Resposta 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 Sailors Sid Status Snome Idade Status Min-A 22 N1 7 45 35 7 7 N6 64 35 8 55,5 N3 31 8 25,5 32 25,5 N4 8 3 24 58 N5 10 35 16 71 N7 10 9 74 35 N6 1 33 29 N2 N8 3 25,5 85 N9 95 3 63,5 97 3 24 N10 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

  20. 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)

  21. 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

  22. 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

  23. 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)

  24. 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 nalista-atributos-de-agrupamento. 3. Atributos que aparecem emcondição-sobre-os-gruposdevem aparecer em “operador(atributo)”ou emlista-atributos-de-agrupamento.

  25. Exemplo SELECT ,Min( ) AS Min-Age FROM Sailors S WHERE S.Idade >= 18 GROUP BY HAVING > 5 AND EVERY ( <= 60) S.Idade S.Status S.Status S.Status S.Idade

  26. Particularidade de SQL 1999 Se a lista-atributos-de-agrupamento contém a chave primária de uma tabela dalista-tabelasentão cada coluna desta tabela só tem um único valor em cada grupo. Em SQL1999, tais colunas podem aparecer nalista de atributos da lista-seleção. SELECT S.Status, S.Snome, AVG(S.Idade) AS Min-AGE FROM Sailors S GROUP BY S.Sid HAVING COUNT(*) > 1 Chave de Sailors Logo: os grupos têm um único elemento

More Related