1 / 34

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

Á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

clinton-pierce
Download Presentation

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

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. Área de Banco de Dados Transact SQL : Consultando o Banco de Dados

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

  3. Exemplo 1 USE northwind SELECT TOP 5 orderid, productid, quantity FROM [orderdetails] ORDER BY quantity DESCGO Exemplo 2 USE northwind SELECT TOP 5 WITH TIES orderid, productid, quantity FROM [order details] ORDER BY quantity DESCGO 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

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

  5. Usando funções agregadas com valores nulos Exemplo 1 USE northwind SELECT COUNT (*) FROM employeesGO Exemplo 2 USE northwind SELECT COUNT(reportsto) FROM employees GO A maioria das funções agregadas ignora valores nulos A função COUNT (*) conta todos os registros com valores nulos

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

  7. productid total_quantity 1 15 2 35 3 45 productid total_quantity 2 35 Usando a cláusula GROUP BY USE northwind SELECT productid, orderid ,quantity FROM orderhistGO USE northwind SELECT productid ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productidGO productid orderid quantity 1 1 5 Apenas osregistros que satisfazem às condições da cláusula WHERE serão agrupados 1 1 10 2 1 10 2 2 25 3 1 15 3 2 30 USE northwind SELECT productid ,SUM(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productidGO

  8. productid orderid quantity 1 1 5 1 1 10 2 1 10 2 2 25 3 1 15 3 2 30 Usando a cláusula GROUP BY com a cláusula HAVING USE northwind SELECT productid, orderid ,quantity FROM orderhistGO USE northwind SELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid HAVING SUM(quantity)>=30GO productid total_quantity 2 35 3 45

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

  10. productid orderid total_quantity NULL NULL 95 Total geral 1 NULL 15 Resume apenas os registros de productid 1 1 1 5 Valores de detalhe de productid 1, orderid 1 1 2 10 Valores de detalhe de productid 1, orderid 2 2 NULL 35 Resume apenas os registros de productid 2 2 1 10 Valores de detalhe de productid 2, orderid 1 2 2 25 Resume apenas os registros de productid 3 3 NULL 45 Valores de detalhe de productid 3, orderid 1 3 1 15 Valores de detalhe de productid 3, orderid 2 3 2 30 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, orderidGO Descrição

  11. Descrição productid orderid total_quantity Total geral NULL NULL 95 Resume todos os registros de orderid 1 NULL 1 30 O operador CUBE produz dois valores de resumo a mais que o operador ROLLUP Resume todos os registros de orderid 2 NULL 2 65 Resume apenas os registros de productid 1 1 NULL 15 Valores de detalhe de productid 1, orderid 1 1 1 5 Valores de detalhe de productid 1, orderid 2 1 2 10 Resume apenas os registros de productid 2 2 NULL 35 Valores de detalhe de productid 2, orderid 1 2 1 10 Valores de detalhe de productid 2, orderid 2 2 2 25 Resume apenas os registros de productid 3 3 NULL 45 Valores de detalhe de productid 3, orderid 1 3 1 15 Valores de detalhe de productid 3, orderid 2 3 2 30 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, orderidGO

  12. 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, orderidGO productid orderid total_quantity NULL 1 NULL 1 95 NULL 1 1 0 30 NULL 1 2 0 65 1 representa os valores de resumo na coluna anterior 0 representa os valores de detalhe na na coluna anterior 1 0 NULL 1 15 1 0 1 0 5 1 0 2 0 10 2 0 NULL 1 35 2 0 1 0 10 2 0 2 0 25 3 0 NULL 1 45 3 0 1 0 15 3 0 2 0 30

  13. productid orderid quantity 1 1 5 1 2 10 sum 15 2 1 10 2 2 25 sum 35 3 1 15 3 2 30 sum 45 sum 95 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 1 1 5 1 2 10 2 1 10 2 2 25 3 1 15 3 2 30 sum 95

  14. productid productname supplierid unitprice 14 Tofu 6 23.25 29 Thüringer Rostbratwurst 12 123.79 62 Tarte au sucre 29 49.3 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

  15. productname unitprice Chai 18 Chang 19 Aniseed Syrup 10 Genen Shouyu 15.5 Pavlova 17.45 Sir Rodney’s Scones 10 … … Recuperando um intervalo de valores Exemplo 1 USE northwind SELECT productname, unitprice FROM products WHERE unitprice BETWEEN 10 AND 20 GO

  16. companyname country Tokyo Traders Japan Mayumi’s Japan Formaggi Fortini s.r.l. Italy Pasta Buttini s.r.l. Italy 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

  17. companyname fax Exotic Liquids NULL New Orleans Cajun Delights NULL Tokyo Traders NULL Cooperativa de Quesos ‘Las Cabras’ NULL … … Recuperando valores Nulos USE northwind SELECT companyname, fax FROM suppliers WHERE fax IS NULL GO

  18. Formatando conjuntos de resultados Classificando dados Ocultando registros duplicados Alterando nomes de colunas Usando literais

  19. productid productname categoryid unitprice 38 Cote de Blaye 1 263.5000 43 Ipoh Coffee 1 46.0000 2 Chang 1 19.0000 … … … … 63 Vegie-spread 2 43.9000 8 Northwoods Cranberry Sauce 2 40.0000 61 Sirop d'érable 2 28.5000 … … … … Classificando dados Exemplo 1 USE northwind SELECT productid, productname, categoryid, unitprice FROM products ORDER BY categoryid, unitprice DESC GO

  20. country Australia Brazil Canada Denmark Finland France Germany Italy Japan Netherlands Norway Singapore Spain Sweden UK USA Ocultando registros duplicados Exemplo 1 USE northwind SELECT DISTINCT country FROM suppliers ORDER BY country GO

  21. First Last Employee ID: Nancy Davolio 1 Andrew Fuller 2 Janet Leverling 3 Margaret Peacock 4 Steven Buchanan 5 Michael Suyama 6 Robert King 7 Laura Callahan 8 Anne Dodsworth 9 Alterando nomes de colunas USE northwind SELECT firstname AS First, lastname AS Last ,employeeid AS 'Employee ID:' FROM employeesGO

  22. firstname lastname employeeid Nancy Davolio Identification Number: 1 Andrew Fuller Identification Number: 2 Janet Leverling Identification Number: 3 Margaret Peacock Identification Number: 4 Steven Buchanan Identification Number: 5 Michael Suyama Identification Number: 6 Robert King Identification Number: 7 Laura Callahan Identification Number: 8 Anne Dodsworth Identification Number: 9 Usando literais USE northwind SELECT firstname, lastname, 'Identification number:', employeeid FROM employeesGO

  23. Visão geral Usando aliases para nomes de tabelas Combinando dados de várias tabelas Combinando vários conjuntos de resultados

  24. Usando aliases para nomes de tabelas USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyersINNER JOIN sales ON buyers.buyer_id = sales.buyer_id GO USE joindb SELECT buyer_name, s.buyer_id, qty FROM buyers AS bINNER JOIN sales AS s ON b.buyer_id = s.buyer_id GO Exemplo 1 (sem nome de alias) Exemplo 2 (com nome de alias)

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

  26. 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çõesde associação • Usar colunas comuns às tabelas especificadas para associar tabelas

  27. buyer_name 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 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 Usando INNER JOIN USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyersINNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO Exemplo 1 sales buyers Resultado

  28. Usando OUTER JOIN buyer_name 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 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 Sean Chai NULL NULL USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyersLEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idGO Exemplo 1 buyers sales Resultado

  29. buyer_id buyer_name buyer_id prod_id qty buyer_name qty 1 Adam Barr 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 15 Sean Chai 5 Sean Chai 37 Sean Chai 11 Sean Chai 1003 Eva Corets 15 ... ... Usando CROSS JOIN USE joindb SELECT buyer_name, qty FROM buyers CROSS JOIN salesGO Exemplo 1 buyers sales Resultado

  30. buyer_id buyer_name buyer_id prod_id qty prod_id prod_name 1 Adam Barr 1 2 15 1 Apples 2 Sean Chai 1 3 5 2 Pears 3 1 37 3 Oranges 3 Eva Corets 4 Erin O’Melia 4 5 11 4 Bananas 2 2 1003 5 Peaches 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_idGO Exemplo 1 buyers sales produce 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

  31. 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 Exemplo 3 sales a sales b buyer_id prod_id qty buyer_id prod_id qty 1 2 15 1 2 15 1 3 5 1 3 5 4 1 37 4 1 37 3 5 11 3 5 11 4 2 1003 4 2 1003 Resultado buyer1 prod_id buyer2 4 2 1

  32. Combinando vários conjuntos de resultados USE northwind SELECT (firstname + ' ' + lastname) AS name ,city, postalcode FROM employees UNION SELECT companyname, city, postalcode FROM customers GO • 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

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

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

More Related