Aula 6 SQL Parte II

Prévia do material em texto

Banco de Dados II
INF8021
Aula 6 
SQL – Parte II
Professor: André Lucio
A linguagem SQL
Problemas com operandos nulos
• Q20: select * from jogadores where natp = NULL
– Não retorna nada, embora haja vários jogadores cujo natp 
tem o valor nulo
• Q21: select * from jogadores where natp = natp
– Retorna apenas os jogadores cujo natp não é nulo, em vez de 
retornar todos os jogadores, já que existem valores nulos para 
o atributo natp.
• Q22: select * from jogadores where natp IS NULL
– O operador IS é usado para testar se um valor é nulo.
– Retorna todos os jogadores aposentados.
A linguagem SQL
Problemas com operandos nulos
• Q23: select * from jogadores where natp IS 
NOT NULL
– Retorna os jogadores que participam de torneios 
oficiais da ATP
– Operadores de comparação envolvendo ao menos 
um NULL (?)  ? No resultado é = “desconhecido”
Expressão Resultado
? = ? ?
X = ? ?
X > ? ?
A linguagem SQL
Problemas com operandos nulos
• Q24: Dê o nome de todos os jogadores exceto 
daquele cujo natp é 1271
– select nome from jogadores where natp <> 1271
– OBS: a query acima não produz o resultado esperado, pois 
não inclui os jogadores com natp = NULL
• Q25: Corrigindo a consulta anterior
– select nome from jogadores where natp <> 1271 or 
natp IS NULL
A linguagem SQL
Problemas com operandos nulos
– A tabela a seguir nos dá o resultado das expressões 
booleanas onde pelo menos um dos operandos tem valor 
nulo (desconhecido - ?)
Expressão Resultado
? and ? ?
? or ? ?
not ? ?
X and ? ? ou F (caso X seja F)
X or ? ? ou T (caso X seja T)
A linguagem SQL
Problemas com operandos nulos
• Q26: Qual o resultado do comando:
– select numj, natp from jogadores where natp >= 
natp
– Resposta: numj e natp de todos os jogadores 
com natp diferente de NULL
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Q27: Obtenha o nome de cada jogador que venceu 
pelo menos um torneio.
– select nome from jogadores where numj in (select 
numj from torneios)
– OBS: A consulta aninhada (entre parênteses) gera um 
conjunto de valores contendo o número de cada jogador 
que venceu torneios, e esse conjunto é usado pelo 
operador in.
• Poderíamos fazer a mesma consulta usando uma 
junção:
– Q28: select distinct nome from jogadores, torneios 
where jogadores.numj = torneios.numj
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Q29: Obter os nomes dos jogadores que 
venceram uma ou mais vezes o torneio 1
– select nome from jogadores where numj in
(select numj from torneios where numt = 1)
• Q30: O exemplo acima poderia também ser feito 
com uma junção (Tente!!!)
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Há casos, porém, em que não podemos usar uma 
consulta aninhada no lugar de uma junção.
• Q31: Para cada jogador que venceu torneios, 
obtenha o seu nome, o número do torneio e o ano.
– select nome, numt, ano from jogadores, torneios 
where jogadores.numj = torneios.numj
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• O contrário também é verdadeiro: há casos em 
que uma consulta aninhada não pode ser 
substituída por uma junção.
• Q32: obtenha os nomes dos jogadores que não 
venceram torneios:
– select nome from jogadores where numj not in
(select numj from torneios)
• Q33: (PARA CASA) o que resultaria se 
usássemos para a consulta anterior a seguinte 
junção intuitiva?
– select nome from jogadores, torneios where 
jogadores.numj <> torneios.numj
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Subconsultas com operador de comparação
– Q34: Obtenha o pre-nome e o nome do jogador 
que ganhou o torneio 2 em 1997.
• select pnome, nome from jogadores where 
numj = (select numj from Torneios where 
numt=2 and ano= 1997
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Subconsultas com operador de comparação
– Q35: Obtenha o nome de cada jogador que é
mais velho que Kuerten.
• select nome from jogadores where ano_n <
(select ano_n from jogadores where nome = 
'Kuerten')
– OBS: como o operador de comparação compara 
dois valores escalares, é fundamental garantir que a 
subconsulta só retorne um valor (vai dar erro, caso 
contrário). 
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Operadores all e any em subconsultas
– Q36: Obtenha o(s) nome(s) do(s) 
jogador(es) mais velho(s) que todos os 
outros jogadores".
• select nome from jogadores where ano_n 
<= (select ano_n from jogadores 
(ERRADA)
• select nome from jogadores where ano_n 
<= all (select ano_n from jogadores) 
(CORRETA)
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Operadores all e any em subconsultas
– Q37: Obter o nome da cada jogador que 
não pertence ao grupo dos mais velhos
• select nome from jogadores where ano_n >
any (select ano_n from jogadores)
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Operadores exists e not exists em subconsultas
– Q38: Obtenha o nome de cada jogador que 
venceu algum torneio.
• select nome from jogadores where exists
(select * from torneios where torneios.numj = 
jogadores.numj)
– OBS: Este é o primeiro exemplo do importante conceito de 
subconsulta correlacionada. Até agora uma subconsulta era 
inteiramente avaliada e o seu resultado passado para as 
consultas mais externas; nesse caso, porém, para cada linha da 
tabela Jogadores a subconsulta é avaliada, utilizando o valor 
corrente de jogadores.numj. Por esta razão, subconsultas 
correlacionadas podem ser mais ineficientes do que consultas 
comuns, e devem ser evitadas quando possível.
A linguagem SQL
Consultas Aninhadas (Subconsultas)
• Operadores exists e not exists em subconsultas
– Q39: Obtenha o nome de todos os jogadores que 
não foram vencedores de torneio.
• select nome from jogadores where not exists
(select * from torneios where torneios.numj = 
jogadores.numj)
– Q40: Obtenha o nome de cada jogador que 
venceu o torneio de Wimbledon. (para CASA)
– Q41: Obtenha o nome de cada jogador que não 
foi vencedor de torneios vencidos pelo jogador 
Sampras (numj = 812). (para CASA)
A linguagem SQL
Operações de conjuntos
• União (union), interseção (intersect), diferença 
(except)
• Q42: Dê o número e nome de todos os jogadores
– select numj, nome from jogadores_aposentados 
union
select numj, nome from jogadores_ativos
• Restrições para este comandos:
– Várias uniões (ou diferença/interseção) são admissíveis
– Apenas o último select pode ter a cláusula order by
– Linhas duplicas são eliminadas, a menos que se use a 
palavra reservada all
– Colunas correspondentes devem ser do mesmo tipo
– Distinct não é permitido
A linguagem SQL
• Cláusula Order by
• Q43: especificando múltiplas colunas para 
ordenação
– select numj, ano from torneios order by numj, ano
– Nesse caso é feita a ordenação lexicográfica: por 
jogador, e para cada jogador pelo ano do(s) 
torneio(s) que venceu.
– É possível especificar em vez do nome, a posição 
na lista select da coluna desejada para ordenação. 
Ex: order by 1, 2
A linguagem SQL
• Cláusula Order by
• A ordenação é ascendente por default, mas poder 
ser descendente usando a palavra reservada desc
• Q44: select nome, cid_res from jogadores order by
cid_res asc, nome desc
• OBS: a ordenação de nulos depende muito do 
SGBD. Em alguns deles (SQL Server, MySQL), os 
nulos aparecem antes de todos.
• Q45: select natp from jogadores order by natp
A linguagem SQL
• Funções que produzem valores agregados
– count (nome_coluna): obtém o número de valores na 
consulta especificada.
– min(nome_coluna): obtém o valor mínimo da coluna.
– max(nome_coluna): obtém o valor máximo da coluna.
– sum(nome_coluna): soma os valores da coluna.
– avg(nome_coluna): obtém a médiaaritmética das colunas.
– Q46: Quantos torneios existem?".
• select count (*) from lista_torneios
A linguagem SQL
• Funções que produzem valores agregados
– Q47: Qual foi o prêmio de valor máximo?.
• select max(premio) from torneios
– Q48: Quantas cidades de residência distintas 
existem?.
• select count (distinct cid_res) from jogadores
– Q49: Quantos jogadores vivem em Monte Carlo?".
• select count (*) from jogadores where cid_res = 
'Monte Carlo'
A linguagem SQL
• Funções que produzem valores agregados
– Q50: Quantos prêmios são iguais ao mínimo 
prêmio?.
• select count (premio) from torneios where 
premio = (select min (premio) from torneios)
– Q51: Obtenha para cada jogador que venceu 
torneios, o valor do seu maior prêmio, o número do 
torneio e o ano em que foi ganho.
• select numj, premio, numt,ano from torneios t1 
where premio =(select max(premio) from 
torneios t2 where t2.numj = t1.numj)
Referências Bibliográficas
• [1] Guimarães C.C. – Fundamentos de Banco de 
Dados – Modelagem, Projeto e Linguagem SQL –
Editora Unicamp
• [2] Pressman R. S. 2006 – Engenharia de Software 
– Sexta Edição – Editora McGraw-Hill
• [3] Date, C. J 2004 – Introdução a Sistemas de 
Banco de Dados – Oitava Edição – Editora Campus
• [4] Navathe. S e Elmasri 2005, E. – Sistemas de 
Banco de Dados - Quarta Edição – Editora Addison-
Wesley