MC536 - Consultas Avançadas em SQL Prof. Célio Guimarães     IC - Unicamp

A "consulta clássica" de SQL: fornecedores que fornecem todos os materiais

select f from F
where f not in
 (select f from F,M      -- subconsulta obtem fornecedores que não fornecem "algum" material
  where (f,m) not in 
  (select f,m from FM)   -- obtem fornecedores e materiais que eles fornecem
 )

Outra solução não tão compacta é:

select f from F
where f not in
(select f from
(select f,m from F,M
 where (f,m) not in 
   (select f,m from FM)) as foo)

Obs: Mysql e Postgres requerem inserir um alias (no caso, foo) para "tabelas derivadas"
     ou seja, tabelas obtidas através de uma subconsulta na cláusula from (mas não  nas outras cláusulas).

A consulta "super-clássica":

Exercícios

Dicas:
Onde apropriado use distinct.
Você pode usar order by para facilitar a visualização dos resultados.
Para facilitar a verificação da correção da sua consulta, coloque na última linha uma cláusula order by apropriada, e a mesma cláusula numa consulta mais simples sobre as tabelas envolvidas.
Exercícios em cuja descrição aparece a palavra não devem ser resolvidos com uma subconsulta do tipo not in (subconsulta) onde (subconsulta) corresponde à parte afirmativa da questão: ela é geralmente simples de formular, por isto você deve obtê-la (e testá-la) em primeiro lugar. Exemplo:
"Quais jogadores não venceram o torneio 1?"
obtenha primeiro os "jogadores que venceram o torneio 1".
Se a mesma tabela aparece 2 ou mais vezes na cláusula from elas devem ser renomeadas como a seguir:
from F as F1, F as F2, onde "as" é opcional

1. Escreva uma consulta SQL para "obter os materiais que não possuem fornecedores"

2. Reescreva a consulta clássica para obter os números dos jogadores que ganharam todos os torneios de Grand Slam (para isto utilize a tabela LT que só possui os torneios de Grand Slam em vez da tabela Lista_torneios ou utilize a sub-expressão where numt <= 4 )

3. Modifique a consulta acima para obter o nome em vez do número do jogador.

4. Considere a tabela FMP(f,m, preco) de fornecimentos de materiais, onde para cada par f,m é incluído o preço do material fornecido. Escreva uma consulta SQL com o seguinte objetivo: "para cada material m obtenha o fornecedor f que fornece m com o menor preço dentre todos os fornecedores que fornecem m".
   Sugestão: use o modificador all numa subconsulta correlacionada. Verifique a correção de sua solução executando:

   select f, m, preco
   from FMP
   order by m, preco, f
   

5. Considere a consulta q37: "obter os nomes dos jogadores que não pertencem ao grupo dos mais velhos". Suponha que o operador any esteja "quebrado".
   Re-escreva a consulta usando o operador all.
   É possível resolvê-la sem usar o operador not in?
6. Considere agora a consulta q36: "obter os nomes dos jogadores mais velhos que todos os outros". Suponha que o operador all esteja "quebrado".
   Re-escreva a consulta usando o operador any.

   ---

   Consultas sobre a tabela de "Pais e Filhos", D(p,f) :
   "Obtenha uma lista de pessoas cujos pais não estão presentes na tabela D"
   Solução: pelo requisito da consulta e pela definição da tabela D essas pessoas não podem aparecer como filhos; logo elas são pais e não aparecem na coluna f de D; portanto:

   select distinct p from D     -- estes são pais
   where p not in                -- que não podem aparecer
   (select f from D)             -- na lista de filhos
   

   Dica: na maioria das outras consultas você deverá usar o produto cartesiano de cópias da tabela D e fazer uma seleção apropriada sobre o mesmo; para obtê-la é importante raciocinar sobre as linhas do produto cartesiano.

7. Escreva uma consulta para verificar se a tabela "Pais e Filhos" D(p,f) está inconsistente no sentido de possuir registros de filhos em que apenas um dos pais está presente na tabela, ou seja, para um certo filho há apenas uma linha na tabela D.

8. Dentre os jogadores que venceram torneios, selecione aqueles que venceram exatamente um torneio.
   (Veja discussão desse problema na seção seguinte).

As consultas a seguir podem ser facilmente feitas com os recursos de contagem do SQL mas suponha que eles não estejam disponíveis numa implementação mais simples da linguagem.

   select distinct FM1.f
   from FM FM1, FM FM2
   where FM1.f=FM2.f and FM1.m != FM2.m

-- Fornecedores que fornecem exatamente um material
   select f from FM
   where f not in
   (select distinct FM1.f
    from FM FM1, FM FM2
    where FM1.f=FM2.f and FM1.m != FM2.m
   )