Banco de Dados

1. Prof.: Bruno Rafael de Oliveira Rodrigues Banco de Dados

2. Para melhor compreensão dessa aula monte as tabelas abaixo povoando-as:

3. Consulta com duas tabelas ou mais tabelas select Funcionario.Nome_Funcionario, Setor.Nome_Setor from Funcionario, Setor where Funcionario.Cod_Setor = 1 and Setor.Cod_Setor=1;

4. Group By A cláusula GROUP BY organiza dados em grupos, produzindo sumários. Exemplos: select cargo, count(*) from funcionario group by cargo; select Nome_Funcionario, Cidade from funcionario group by Cidade, Nome_Funcionario; select cidade, avg(salario) from funcionario group by cidade;

5. Group By x Order By ORDER BY é usado para ordenar um conjunto de informações sendo ela exposta crescente ou decrescente. O GROUP BY é usando para agrupar dados comuns, ou seja, informações repetidas.

6. Group By e Order By podem ser usados juntos select cidade, avg(salario) from funcionario group by cidade order by cidade;

7. Clausula Having A cláusula HAVING restringe os resultados do GROUP BY. É aplicada a cada grupo da tabela agrupada, de forma parecida como a cláusula WHERE é aplicada à lista de seleção. Exemplo: select cidade, avg(salario) from funcionario where cidade <> 'Pirapora' group by cidade having avg(salario) > 3000 order by cidade; select cargo, (*) from funcionario group by cargo having count(*) > 2;

8. Questionário 1- As instruções SQL a seguir funcionarão? a) SELECT SUM(SALARIO), FUNC_ID FROM FUNCIONARIO GROUP BY 1 AND 2; B)SELECT FUNC_ID, MAX(SALARIO) FROM FUNCIONARIO GROUP BY SALARIO, FUNC_ID; C) SELECT FUNC_ID, COUNT(SALARIO) FROM FUNCIONARIO ORDER BY FUNC_ID, GROUP BY SALARIO;

9. Verdadeiro ou Falso: Quando usa a clausula HAVING, você também tem que usar a cláusula Group By. A instrução SQL a seguir retorna um total de salarios por grupo: Select SUM(Salario) from funcionario; As colunas selecionadas devem aparecer na clausula Group by na mesma ordem A clausula HAVING informa à clausula GROUP BY quais grupos deverão ser incluidos.

10. Respostas: 1 – a) Não, essa instrução não funcionará. O operador AND na clausula Group By não está no local correto e não é possível usar um número inteiro na clausula Group By. A sintaxe correta é: SELECT SUM(SALARIO), FUNC_ID FROM FUNCIONARIO GROUP BY SALARIO, FUNC_ID; b) Sim, a instrução funcionará.

11. c) Não, essa instrução não funcionará. As clausulas ORDER BY e GROUP BY não estão corretas. Além disso, a coluna FUNC_ID é obrigatória na clausula GROUP BY . Forma correta: SELECT FUNC_ID, COUNT(SALARIO) FROM FUNCIONARIO GROUP BY FUNC_ID ORDER BY FUNC_ID;

12. Verdadeiro ou falso falso, a clausula HAVING pode ser usada sem a cláusula GROUP BY. Exemplo: => selectavg(preco) from produtos havingcount(*)>=5; b) Falso, a instrução não pode retornar um total de salário por grupo porque não foi incluída a clausula GROUP BY. c) Falso, a ordem das colunas na cláusula SELECT pode ser diferente da existente na cláusula GROUP BY d) Verdadeiro

13. Atividades Complementares O que faz as clausulas group by? E o que ela tem de diferente da order by? Quando se usa clausula Having? Como selecionar mais de duas tabelas em uma mesma consulta? O que faz a consulta: select tipo_produto, avg(preco_unitario) from produtos group by tipo_produto having avg(preco_unitario)>=200?

14. Produto Pedido Cliente

15. Prática Usando as tabelas de Cliente, Produtos e Pedidos*. Escreva as consultas para: Agrupar a quantidade de produtos por tipo do produto. Fazer uma consulta para saber quem comprou o produto de codigo 2 e qual produto é este. Consulte o produto com sua media de preço agrupada por tipo do produto tendo o preço do produto maior que 100.

16. Junção de Tabelas Uma junção combina duas ou mais tabelas para recuperar dados de múltiplas tabelas

17. Junção de Igualdade (Equijoins) Também conhecida como Inner Join, efetua junção de duas tabelas com uma coluna comum na qual cada uma é normalmente a chave primária. Ex.: Select funcionario.cod_setor, setor.nome_setor from funcionario, setor where funcionario.cod_setor=setor.cod_setor

18. Alias Nomes alternativos para as tabelas. Exs.: Select f.cod_setor, s.nome_setor from funcionario f, setor s where f.cod_setor=s.cod_setor Select f.*, s.nome_setor from funcionario f, setor s where f.cod_setor=s.cod_setor

19. Outras Junções Junções Naturais: idêntica a equijoin, a diferença é que ela elimina colunas repetidas nas colunas cujas junções são efetuadas. Junções de Desigualdade: efetua junção de duas ou mais tabelas com base no valor de uma coluna especificada que não seja igual ao valor de uma outra coluna especificada em uma outra tabela. Junções Externas: é usada para retornar todas as linhas que existem em uma tabela, embora não existam linhas correspondentes na tabela unida. Auto-junções: usada para efetuar junção de uma tabela a si mesma, como se a tabela fosse duas tabelas, renomeando temporariamente, pelo menos, uma tabela na instrução SQL.

20. Usando uma tabela Base table Usada para efetuar junção de uma ou mais tabelas que contenham colunas comuns ou para efetuar junção de tabelas que não possuam tabelas comuns.

21. Use as tabelas abaixo para o próximo exemplo Tabela Detalhes_Vendas Tabela Produtos

22. Subconsultas São consultas embutidas dentro de outras. Pode-se utilizar essas duas tabelas para pesquisar por nomes de produtos cujo volume de vendas seja maior ou igual a 1000. Ex.: Select * from produtos wherecod_produto in (selectcod_produtofromdetalhes_vendaswhere qtd>=1000)

23. Subconsulta Correlata É uma subconsulta que esteja contida dentro de outra subconsulta. Ex.: Select * fromdetalhes_vendas u where qtd > (selectavg(qtd) fromdetalhes_vendaswherecod_produto=u.cod_produto)

24. FAQ 1 - Quando tabelas apresentam junções, é necessário que as junções sejam efetuadas na mesma ordem. Por que? 2 – Quando usa-se uma base table para efetuar junção de tabelas não-relacionadas, tem-se que selecionar alguma coluna da tabela base. 3 – Pode-se efetuar junção com mais de uma coluna entre tabelas? 4 – Em que parte da instrução SQL as condições de junção estão localizadas? 5 – Que tipo de junção usa-se para avaliar a igualdade entre as linhas de tabelas relacionadas? 6 – Qual a função de uma subconsulta quando usada com uma instrução select? 7- O que aconteceria com ao executar a instrução: Delete fromfuncionariowherefunc_id in (selectfunc_idfromtabela_pagamento) 8 – Encontre o detalhe de venda para frutas com preços unitários maiores ou iguais a 300. 9 – Obtenha o volume médio de vendas por produto e encontre itens que tenham vendas abaixo da média.

25. Respostas: 1 – Não, elas não precisam necessariamente aparecer na mesma ordem. 2 – Não, a utilização de uma Base Table para efetuar junção de tabelas não-relacionadas não faz com que seja obrigatório selecionar colunas na tabela base. 3 – Sim, para algumas consultas pode ser que seja necessário efetuar junção de mais de uma coluna por tabela para criar um relacionamento completo entre linhas de dados de tabelas associadas. 4 – As condições de junção estão localizadas na cláusula where. 5 – Equijoin 6 – A principal função de uma subconsulta quando usada com uma instrução SELECT é retornar os dados que a consulta principal usar para resolver a consulta 7 – Todas as linhas que fossem recuperadas a partir da tabela tabela_pagamento seriam usadas por DELETE para removê-las da tabela funcionario. 8 – Select * from detelhes_vendas where cod_produto in (select cod_produto from produto where preco_unt >= 300) 9 – Select * from detalhes_vendas u where qtd < (select avg(qtd) from detalhes_vendas where cod_produto = u.cod_produto);