Banco de Dados
Prof.: Bruno Rafael de Oliveira Rodrigues
Para melhor compreensão dessa aula monte as tabelas abaixo
povoando-as:
Funcionario
Cod_Funcionario
Nome_Funcionario
Cidade
Cod_Setor
Cargo
Salario
Setor
Cod_Setor
Nome_Setor
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;
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;
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.
Group By e Order By podem ser
usados juntos
select cidade, avg(salario) from
funcionario group by cidade order by
cidade;
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;
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;
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.
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á.
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;
Verdadeiro ou falso
a) falso, a clausula HAVING pode ser
usada sem a cláusula GROUP BY.
Exemplo: => select avg(preco) from
produtos having count(*)>=5;
b) 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
Atividades Complementares
1) O que faz as clausulas group by? E o
que ela tem de diferente da order by?
2) Quando se usa clausula Having?
3) Como selecionar mais de duas tabelas
em uma mesma consulta?
4) O que faz a consulta: select
tipo_produto, avg(preco_unitario) from
produtos group by tipo_produto having
avg(preco_unitario)>=200?
Produto
CodProduto Produto
1
TV 21’’
LED
DVD
2
3
Sofa 3
lugares
Rack
4
5
Cama
Box
PrecoU
n
Eletrodomestic R$
o
599,00
Eletrodomestic R$
o
99,00
Movel
R$
400,00
Movel
R$
299,00
Movel
R$
999,00
CodClient NomeCliente
e
1
Rafael
Guimarães
2
Thiago Moreira
3
Pedido
Tipo_Produto
Samira Martins
Endereco
NumP CodCli CodPro DataP
edido ente
duto
edido
1
1
2
20/05/
2011
1
1
3
20/05/
2011
2
2
5
25/05/
2011
3
3
4
21/05/
2011
3
3
1
21/05/
2011
Rua A
nº14
Rua B nº15
Rua J
nº112
Cliente
QtdPro
duto
2
2
1
2
2
Prática
Usando as tabelas de Cliente, Produtos e
Pedidos*. Escreva as consultas para:
1) Agrupar a quantidade de produtos por tipo do
produto.
2) Fazer uma consulta para saber quem comprou
o produto de codigo 2 e qual produto é este.
3) Consulte o produto com sua media de preço
agrupada por tipo do produto tendo o preço do
produto maior que 100.
Junção de Tabelas
Uma junção combina duas ou mais
tabelas para recuperar dados de múltiplas
tabelas
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
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
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.
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.
Use as tabelas abaixo para o
próximo exemplo
Tabela Detalhes_Vendas
Tabela Produtos
Cod_Produto
Nome_Produto
Preco_Unt
101
Melão
1.50
Cod_Relatorio
Cod_Produto
Qtd
1101
101
1100
1101
102
300
1102
103
1700
1103
104
500
102
Morango
2.49
103
Maça
2.99
1104
101
2500
104
Limão
1.20
1105
103
2000
1105
104
700
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 where cod_produto
in (select cod_produto from
detalhes_vendas where qtd>=1000)
Subconsulta Correlata
É uma subconsulta que esteja contida
dentro de outra subconsulta. Ex.:
Select * from detalhes_vendas u where
qtd > (select avg(qtd) from
detalhes_vendas where
cod_produto=u.cod_produto)
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 from funcionario where func_id in (select func_id from
tabela_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.
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);