Notas de aula e exercícios de Banco de Dados II

Tecnologia em Análise e Desenvolvimento de Sistemas
Disciplina: B1SGB - Sistemas Gerenciadores de Banco de Dados
Memória de aula – Aulas 11 e 12
1) Revisão
a. Cláusula join (inner e outer)
2) Cláusula GROUP BY
a. Usada para agrupar dados por valores de colunas
b. Ao usar group by será necessário usar cláusulas/funções de
filtro de dados, como a função COUNT.
i. Função MIN(x): retorna o menor valor de um atributo x.
ii. Função MAX(x): retorna o maior valor de um atributo x.
iii. Função SUM(x): retorna a soma dos valores do atributo x.
iv. Função AVG(x): retorna a média dos valores do atributo x.
v. Função COUNT(*): “conta o total de linhas” que
preenchem a qualificação de consulta.
vi. Exemplo:
select faixaef as Faixa, count(*) as
Qtd
from eficiencia_energ
group by faixaef
Faixa
Qtd
A
5
B
14
Página 1 de 7
C
2
D
18
E
4
c. Também pode ser utilizada a cláusula HAVING
i. Permite filtrar dados agrupados da mesma forma que a
cláusula WHERE permite filtrar dados brutos
ii. Exemplo:
select faixaef as Faixa, count(*) as
Qtd from eficiencia_energ
group by faixaef
having count(*) > 4
Faixa
Qtd
A
5
B
14
D
18
3) Subconsultas (subqueries): são consultas aninhadas (dentro de
outras consultas).
SELECT colunas
FROM tabela
WHERE expressão operador (SELECT colunas FROM tabela
WHERE ...)
a. O operador pode ser de comparação (linha - >,<,<=,>=,<>,!=,=)
ou de grupo (in, any, all). Os operadores de linha só
Página 2 de 7
funcionam se a subconsulta retornar apenas um valor (uma
linha e uma coluna).
Select nome,preco
From cd
Where preco > (select avg(preco) from cd)
b. Para realizar subconsultas:
i. Escreva-as entre parênteses
ii. Escreva-as à direita do operador
iii. Não coloque a cláusula ORDER BY em uma subquery
iv. Utilize operadores de linha apenas em buscas que
retornem uma única linha.
v. Utilize operadores de grupo apenas em buscas que
potencialmente retornem mais de uma linha.
c. Uma subconsulta também pode ser usada na cláusula
HAVING.
Select cd_gravadora,min(preco)
From cd
Group by cd_gravadora
Having min(preco) > (select preco from cd where codigo=6)
d. IN: verifica a pertinência em um conjunto
Select codigo, nome, preco
Where preco in (select min(preco) from cd
group by cd_gravadora)
e. ANY: permite comparar operadores de comparação de linha
com um grupo de linhas. Retorna verdadeiro se a comparação
com QUALQUER um dos valores da subconsulta for
verdadeira)
Select codigo,nome,preco
From cd
Página 3 de 7
Where
preco
<
ANY
(select
preco
from
cd
where
cd_gravadora = 2)
f. ALL: permite comparar operadores de comparação de linha
com um grupo de linhas. Retorna verdadeiro se a comparação
com TODOS os valores da subconsulta for verdadeira)
Select codigo,nome,preco
From cd
Where
preco
<
ALL
(select
preco
from
cd
where
cd_gravadora = 2)
4) Trabalhando com conjuntos – União, intersecção e diferença
a. União de duas consultas (lembre-se das regras da álgebra
relacional, continuam valendo)
i. Expressa pela cláusula UNION e UNION ALL
select 1 num, 'abc' string
union
select 2 num, 'xyz' stri
num
string
1
abc
2
xyz
ii. A diferença entre UNION e UNION ALL é que a primeira
remove as duplicatas, a segunda não.
iii. O número de linhas do conjunto resultado de um
UNION ALL é a soma do número de linhas dos
conjuntos que estão sendo combinados.
b. Para intersecção e diferença há no padrão ANSII a cláusula
INTERSECT, mas só funcionará a partir do SQL Server 2008.
Página 4 de 7
Todavia, a intersecção, assim como a diferença (EXCEPT
DISTINCT), podem ser realizadas por meio das cláusulas
EXISTS ou NOT EXISTS.
i. Exemplo de intersecção
SELECT DISTINCT cidade FROM autor
WHERE EXISTS (SELECT * FROM editora
WHERE
autor.cidade
=
editora.cidade)
ii. Exemplos de diferença
SELECT DISTINCT cidade FROM autor
WHERE NOT EXISTS(SELECT * FROM editora
WHERE
autor.cidade
=
editora.cidade)
5) View (Visão)
a. Consulta armazenada no banco de dados, parece com uma
tabela. Pode ser utilizada para fazer consultas como se fosse
uma tabela.
Ex.:
Create view v_empregados as
Select
e.cod_emp,
e.nome,
e.sobrenome,
YEAR(e.dt_contr) dt_contr
From empregados as e
Bibliografia
ELMASRI, R., NAVATHE, S.B. Sistemas de Banco de Dados. 4ª Ed. São Paulo: Pearson
Makron Books, 2005.
Página 5 de 7
EXERCÍCIO
Baseando-se no Modelo de Dados implementado em laboratório, efetue as consultas abaixo.
1) Antes de fazer os demais quesitos, inclua mais dez linhas na sua tabela de imóveis,
varie o bairro, a cidade e o vendedor, além dos demais campos.
2) Verifique a maior, a menor e o valor médio das ofertas da tabela.
3) Verifique a média do preço de venda dos imóveis localizados no bairro Flamengo, na
cidade do Rio de Janeiro.
4) Refaça o exercício anterior mostrando o resultado em formato decimal com apenas
duas casas decimais.
5) Mostre o maior, o menor, o total e a média de preço de venda dos imóveis.
6) Modifique o comando anterior para que sejam mostrados os mesmos índices por
bairro.
7) Faça uma busca que retorne o total de imóveis por vendedor. Apresente em ordem
total de imóveis.
8) Mostre o código do vendedor e o menor preço de imóvel dele no cadastro. Exclua da
busca os valores de imóveis inferiores a 10mil.
9) Faça o mesmo que no quesito anterior, mas ao invés de mostrar o código do vendedor,
mostre o seu nome.
10) Faça uma busca que retorne o total de imóveis à venda por bairro (mostre o nome do
bairro). Exclua da busca bairros cujos total de imóveis à venda seja igual a 1.
11) Mostre o código do comprador e a média do valor das ofertas e o número de ofertas
deste computador.
12) Faça uma busca que retorne o total de ofertas realizadas pelo comprador 1.
13) Faça uma lista de imóveis do mesmo bairro do imóvel 2. Exclua o imóvel 2 da sua
busca.
14) Faça uma lista que mostre todos os imóveis que custam mais que a média de preço
dos imóveis.
15) Faça uma lista com todos os compradores que tenham ofertas cadastradas com valor
superior a 70 mil.
16) Faça uma lista com todos os imóveis com oferta superior à média do valor das ofertas.
17) Faça uma lista com todos os imóveis com preço superior à média de preço dos imóveis
do mesmo bairro.
18) Faça uma lista dos imóveis com maior preço agrupado por bairro, cujo maior preço
seja superior à média de preços dos imóveis.
19) Faça uma lista com as ofertas menores que todas as ofertas do comprador 2, exceto as
ofertas do próprio comprador.
20) Faça uma lista com todos os códigos e nomes de compradores e códigos e nomes de
vendedores.
21) Faça uma lista com todos os códigos e nomes de compradores e códigos e nomes de
vendedores, e admita que não há repetição das linhas.
22) Faça uma lista com todos os compradores que não tenham ofertas cadastradas.
23) Faça uma lista de todos os vendedores que não tenham imóveis cadastrados.
24) Faça uma lista de todos os vendedores que tenham imóveis cadastrados.
Página 6 de 7
25) Faça uma lista com todos os compradores que tenham ofertas cadastradas.
Baseado em: OLIVEIRA, C. H. P. SQL curso prático. Novatec, 2002.
Página 7 de 7