Capítulo 2
3-Subconsultas, Continuação
Tabelas AninhadasSubconsultas Substituindo Valores
É possível usar uma consulta dentro de outra para "emular" um efeito de repetição (um laço ou loop que percorre as linhas de uma coluna). Acompanhe o exemplo.
Exemplo 1
Exemplo 1
Considere que se deseja montar uma tabela com os seguintes dados:
+-----------------------+-----------------+
| Assuntos | livros_lancados |
+-----------------------+-----------------+
| Banco de Dados | 2 |
| Programação | 2 |
| Redes | 1 |
| Sistemas Operacionais | 0 |
+-----------------------+-----------------+
Essa tabela foi obtida combinando-se as duas abaixo:
mysql> select * from livro;
+--------+-----------------------------------+--------+------------+---------+---------+
| codigo | titulo | preco | lancamento | assunto | editora |
+--------+-----------------------------------+--------+------------+---------+---------+
| 1 | Banco de Dados para Web | 31.20 | 1999-01-10 | B | 1 |
| 2 | Programando em Linguagem C | 30.00 | 1997-10-01 | P | 1 |
| 3 | Programando em Linguagem C++ | 111.50 | 1998-11-01 | P | 3 |
| 4 | Banco de Dados na Bioinformática | 48.00 | NULL | B | 2 |
| 5 | Redes de Computadores | 42.00 | 1996-09-01 | R | 2 |
+--------+-----------------------------------+--------+------------+---------+---------+
5 rows in set (0.00 sec)
mysql> select * from assunto;
+-------+-----------------------+
| sigla | descricao |
+-------+-----------------------+
| B | Banco de Dados |
| P | Programação |
| R | Redes |
| S | Sistemas Operacionais |
+-------+-----------------------+
4 rows in set (0.02 sec)
Ou seja, deseja-se obter a descrição dos assuntos e a quantidade de livros lançados em cada assunto.
Para obter a coluna Assuntos basta realizar uma seleção sobre a coluna descricao da tabela assuntos e usar o apelido Assuntos.
Para obter a coluna "livros_lançados" devemos contar, para cada assunto, quantos livros lançados existem na tabela livros. Veja a consulta como ficaria:
Exemplo 1, continuação
mysql> select descricao as Assuntos,
-> (
-> select count(*)
-> from livro L
-> where L.assunto = A.sigla
-> and lancamento is not null
-> ) as livros_lancados
-> from assunto A;
Explicação
Note que foi usada uma subconsulta correlacionada, que usa uma consulta no lugar de uma coluna. Essa consulta assume o papel de uma coluna. E para isso, usamos um apelido (no caso, "livros_lancados").
A consulta interna usada possui apenas uma coluna, gerada pelo COUNT(*). E como contamos valores ela retorna também apenas uma linha.
Como a consulta interna é executada uma vez para cada linha da tabela assunto, a relação dela com a consulta externa se faz com L.assunto = A.sigla. Isso faz com que a contagem de livros seja feita para cada assunto.
O resultado da consulta é dado abaixo.
Exemplo 1, continuação
mysql> select descricao as Assuntos,
-> (
-> select count(*)
-> from livro L
-> where L.assunto = A.sigla
-> and lancamento is not null
-> ) as livros_lancados
-> from assunto A;
+-----------------------+-----------------+
| Assuntos | livros_lancados |
+-----------------------+-----------------+
| Banco de Dados | 1 |
| Programação | 2 |
| Redes | 1 |
| Sistemas Operacionais | 0 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
Exemplo 2
Veja um outro exemplo:
Obtenha a lista com os nomes das editoras e o preço médio das publicações de cada uma.
Exemplo 2
SELECT nome,
(
select avg(preco)
from livro V
where V.editora = e.codigo
and lancamento is not null
) as preco_medio
from editora e
order by nome;
Exemplo 2, continuação
+-----------------------+-------------+
| nome | preco_medio |
+-----------------------+-------------+
| Editora Ilhas Tijucas | 111.500000 |
| Editora via-norte | 45.000000 |
| Maria Jose Editora | NULL |
| Mirandela Editora | 30.600000 |
+-----------------------+-------------+
Tabelas Aninhadas
Uma tabela é a materialização de uma relação. Quando realizamos uma consulta e indicamos uma tabela na cláusula FROM, estamos fazendo um consulta sobre uma relação.
Podemos substituir uma tabela por uma subconsulta que retorne uma relação. Isso é feito com uma construção chamada "tabela aninhada".
Para usarmos o resultado de uma consulta como uma tabela, devemos posicionar a consulta delimitada por parêntesis em local destinado a uma tabela.
Para que possamos acesasr as colunas do resultado da subconsulta como se acessássemos as colunas de uma tabela, pode ser necessário atribuir um apelido para a subconsulta.
A sintaxe é
SELECT col1, col2,...colN, from (select colX,colY,colZ from TAB_INTERNA) tab_consultaINNER JOIN TAB_EXTERNA
on tab_consulta.colX = TAB_EXTERNA.col1
Comentários
Repare que esse tipo de construção é diferente das subconsultas vistas até o momento. Apesar de serem semelhantes, pois todas usam uma "select dentro de uma select", essa construção da "tabela aninhada" usa o select interno como sendo uma tabela.
Note que a expressão tab_consulta.colX representa o acesso à coluna colX do resultado da consulta interna. Qualquer coluna da tabela aninhada poderá ser acessada como uma coluna de uma tabela.
Exemplos
Exemplo 3: Listar o nome das editoras e as publicações das editoras que lançaram ao menos dois livros, ordenados pelo nome da editora e pelo título da publicação.
Exemplo 3
SELECT nome, titulo
from (
select editora,count(*) as quantidade
from livro V where lancamento is not null
group by editora) eq
inner join livro on eq.editora = livro.editora
inner join editora on eq.editora = editora.codigo1
where quantidade = 2
order by nome;
+-------------------+-----------------------------------+
| nome | titulo |
+-------------------+-----------------------------------+
| Editora via-norte | Banco de Dados na Bioinformática |
| Editora via-norte | Redes de Computadores |
| Mirandela Editora | Banco de Dados para Web |
| Mirandela Editora | Programando em Linguagem C |
+-------------------+-----------------------------------+
Exemplo 4: Listar os títulos dos livros dos assuntos para os quais o preço médio das publicações é superior a R$40,00, juntamente com os respectivos assuntos.
Exemplo 4
SELECT titulo, descricao as assunto
from
(
select assunto, avg(preco) as preco_medio
from livro V group by assunto having avg(preco) > 40) assunto_preco
inner join livro
on assunto_preco.assunto = livro.assunto
inner join assunto
on assunto_preco.assunto = assunto.sigla
Exercícios
Usando o banco de dados livraria, faça as seguintes consultas:Liste o(s) código(s) da(s) editora(s) que lançou(aram) apenas livros cujo assunto é "Banco de Dados"
Liste o(s) nome(s) da(s) editora(s) que lançou(aram) apenas livros cujo assunto é "Banco de Dados"
Liste o(s) nome(s) da(s) editora(s) que não lançou(aram) livros.
-
Liste os títulos dos livros que foram lançados por editoras que contenham a palavra 'Mirandela' em seu nome e cujo assunto não é 'Banco de Dados'
-
Liste os títulos dos livros cujo assunto é 'Banco de Dados' e que não foram lançados por editoras que contenham 'Mirandela' no nomw.
Soluções para Exercícios 4 e 5
Exercício 4:
mysql> select titulo from livro
inner join editora e
on editora = e. codigo
where nome like '%mirandela%' and titulo not in
(
select titulo from livro
inner join assunto on assunto = sigla
where descricao like '%banco de dados%'
);
+----------------------------+
| titulo |
+----------------------------+
| Programando em Linguagem C |
+----------------------------+
1 row in set (0.01 sec)
Exercício 5:
mysql> select titulo from livro
inner join assunto on assunto = sigla
where descricao like '%banco de dados%'
and titulo not in
(select titulo from livro inner join editora e on editora = e.codigo
where nome like '%mirandela%'
);
+-----------------------------------+
| titulo |
+-----------------------------------+
| Banco de Dados na Bioinformática |
+-----------------------------------+
1 row in set (0.00 sec)