Subconsultas
Subconsultas em SQL permitem aumentar consideravelmente o poder de expressão da linguagem e seu alcance, pois tornam possível que uma consulta faça referência ao resultado de outras consultas nela embutidas. Essas construções abrem um considerável leque de opções para o programador SQL e sua compreensão é indispensável para a confecção de consultas mais sofisticadas.
9.1 POR QUE SUBCONSULTAS?
Para ilustrar a utilização de subconsultas, vamos considerar inicialmente uma consulta que produz a lista de alunos cujas médias são maiores ou iguais à média global de todos os alunos.
Esta consulta pode ser expressa como
select a.matricula, a.nome, avg(i.nota) as nota_media from aluno a, inscricao i
where a.matricula = i.matricula group by a.matricula, a.nome having avg(i.nota) > 6.74 order by a.nome
produzindo
matricula nome nota_media
1007 Carlos Maradona 7,00
1010 Maria Lucia Silva 7,75 1002 Maria Rita Colatti 9,50
1001 Ricardo Biondi 8,00
A constante 6,74 é o valor arredondado da média global, calculada na seção 6.1, com a consulta
select avg(nota) as media_das_notas from inscricao
O problema desta consulta assim escrita é que sempre que houver modificação em alguma das notas será preciso rescrevê-la, corrigindo o valor da média global. O ideal seria que isso não fosse necessário. Com o emprego de subconsultas, a consulta poderia ser rescrita como
select a.matricula, a.nome, avg(i.nota) as nota_media from aluno a, inscricao i
where a.matricula = i.matricula group by a.matricula, a.nome
having avg(i.nota) > ( select avg(nota) from inscricao ) order by a.nome
Agora, a subconsulta
( select avg(nota) from inscricao )
(que produz a média global) permite que o cálculo desse valor seja embutido na consulta, sem necessidade de transcrição. Note que não foi preciso renomear a coluna que contém a expressão de média.
Em suma, com a estrutura de subconsultas é possível, numa consulta SQL, trabalhar com o resultado de uma outra consulta.
∴
Um segundo exemplo ilustrativo requer a lista de alunos que não têm reprovação alguma. Esse resultado não pode ser construído sem o auxílio de subconsultas, porque requer uma construção equivalente à operação de diferença da álgebra relacional. Recorde que, até o momento, somente focalizamos expressões SQL que correspondam às combinações das operações de seleção, projeção e produto. Esquematicamente, a lista de alunos que não têm reprovações é o resultado da diferença entre todos os alunos e aqueles que têm alguma reprovação.
Em SQL, uma expressão desse tipo pode ser obtida com auxílio do operador NOT IN associado a uma subconsulta, como abaixo
select a.matricula, a.nome from aluno a
where a.matricula not in ( select i.matricula from inscricao i where i.nota < 5 ) order by a.nome
obtendo-se como resultado
matricula nome
1007 Carlos Maradona 1010 Maria Lucia Silva 1002 Maria Rita Colatti 1004 Oscarito Vianna 1001 Ricardo Biondi 1008 Sacadura Miranda
Note que a subconsulta
( select i.matricula from inscricao i where i.nota < 5 )
produz as matrículas dos alunos que têm alguma reprovação. Como a consulta acima requer os alunos cujas matrículas não estão nesta lista, tem-se exatamente o efeito da operação de diferença, como definida no capítulo 3.
O único aluno com reprovações é o de matrícula 1005, que tem duas. Assim, o resultado da subconsulta, executada isoladamente, é a lista
matricula 1005 1005
Portanto, o efeito da consulta acima é o mesmo que teria a construção
select a.matricula, a.nome from aluno a
where a.matricula not in ( 1005, 1005 ) order by a.nome
Alunos que não têm reprovações (diferença)
Alunos que têm alguma reprovação Todos alunos
9.2 SUBCONSULTAS SUBORDINADAS E NÃO SUBORDINADAS
Vejamos um exemplo em que se produz a lista de alunos cujas notas, numa determinada disciplina, foram maiores que a média das notas dessa disciplina.
select d.disciplina, a.matricula, a.nome, i.nota from aluno a, inscricao i, disciplina d where a.matricula = i.matricula and i.coddisciplina=d.coddisciplina and i.nota >= ( select avg(nota) from inscricao ii
where ii.coddisciplina = i.coddisciplina ) order by d.disciplina, a.nome
O resultado apresenta, para cada disciplina, a lista de alunos que obtiveram nota igual ou superior à média geral daquela disciplina.
disciplina matricula nome nota
Compiladores 1002 Maria Rita Colatti 9,5 Direito Civil 1007 Carlos Maradona 7,0
Estatística 1005 Barbara Carlito 4,2
Português 1001 Ricardo Biondi 8,0
Sociologia 1010 Maria Lucia Silva 10,0
Para cada combinação de registros de ALUNO,INSCRIÇÃO E DISCIPLINA sendo testada, a subconsulta deve produzir a média das notas de uma disciplina especificamente. Isso significa que deve haver alguma ligação entre a subconsulta sendo executada e a consulta SQL que a envolve. No presente caso, esta ligação é feita pela variável de registro i, declarada na consulta externa e utilizada na subconsulta. Pode-se imaginar essa ligação como uma espécie de parâmetro, que é passado pela consulta externa para a subconsulta cada vez que esta é avaliada.
Esta é uma diferença marcante em relação aos dois exemplos de subconsultas apresentados na seção anterior. Ali, as subconsultas eram não subordinadas às suas consultas externas, uma vez que não havia qualquer ligação entre elas como há no exemplo acima.
Isto posto, podemos definir uma subconsulta como subordinada quando a mesma faz referência a uma ou mais variáveis das consultas que as envolvem. Note que uma subconsulta pode envolver outras subconsultas, a ela subordinadas ou não, e pode ser envolvida por mais de uma subconsulta em diferentes camadas.
9.3 CONSTRUÇÕES BÁSICAS PARA UTILIZAÇÃO DE SUBCONSULTAS
Há cinco construções básicas que permitem a utilização de subconsultas. A primeira delas é uma extensão do operador IN, visto no capítulo 3, que serve para testar se o valor de uma expressão simples está, ou não, no resultado de uma subconsulta.
<expressão> in ( <subconsulta> )
<expressão> not in ( <subconsulta> )
A segunda construção básica permite utilizar subconsultas no papel de expressões simples, tanto na construção de termos lógicos como na lista alvo de uma consulta.
<expressão> <comparação> ( subconsulta )
select { {<expressão> | ( subconsulta ) } [ as <coluna> ] },...
A terceira construção permite fazer comparações coletivas entre uma expressão simples e um conjunto de valores retornados por uma subconsulta. Esta comparação pode ser feita com qualquer dos operadores de comparação vistos no capítulo 3 e apresenta duas variações:
<expressão> <comparação> { any | some } ( <subconsulta> )
<expressão> <comparação> all ( <subconsulta> )
Com o modificador ANY, testa-se se algum (qualquer um) dos valores no resultado da subconsulta atende ao critério de comparação. Com o modificado ALL, testa-se se todos os valores do resultado da subconsulta atendem ao critério de comparação. A palavra-chave SOME
é um sinônimo válido para ANY.
A quarta construção básica permite testar se o resultado de uma subconsulta é vazio ou não.
EXISTS ( subconsulta ) NOT EXISTS ( subconsulta )
Nos quatro tipos de construção apresentados acima, as subconsultas podem ser subordinadas ou não. Uma quinta construção permite que fontes de registros, na cláusula from, sejam especificadas a partir de subconsultas que, à exceção do SQL Server, não podem ser subordinadas.
SELECT ....
FROM ..., ( subconsulta ) x, ...
Cada uma dessas construções é analisada e ilustrada com exemplos nas seções seguintes.
9.4 SUBCONSULTAS COM O OPERADOR IN
Subconsultas em conjunto com o operador IN definem termos lógicos que podem ser verdadeiros ou falsos. O termo lógico
expressão IN ( subconsulta )
é verdadeiro quando o valor representado por expressão está contido no resultado da subconsulta. Quando o termo lógico é utilizado na forma
expressão NOT IN ( subconsulta )
tem-se o inverso: é verdadeiro quando o valor de expressão não está contido no resultado da subconsulta. Subconsultas utilizadas em conjunto com o operador IN ou NOT IN podem ser subordinadas ou não.
Observe a seguinte consulta
select nome from aluno
where matricula in (select matricula from inscricao) order by nome
O resultado produzido contém a lista de alunos que têm alguma inscrição.
nome Barbara Carlito Carlos Maradona Maria Lucia Silva Maria Rita Colatti Ricardo Biondi
Uma consulta semelhante à do exemplo acima, porém com a construção NOT IN, resultaria nos alunos que não estão inscritos em disciplina alguma.
select nome from aluno
where matricula not in (select matricula from inscricao) order by nome
O resultado então seria
nome Oscarito Vianna Sacadura Miranda
Esta última consulta, similarmente ao que foi comentado no início deste capítulo, corresponde à operação de diferença da álgebra relacional, uma vez que o resultado contém a lista de todos os alunos menos aqueles que aparecem no resultado da subconsulta, isto é, alunos que têm alguma inscrição. Note que esta subconsulta é não subordinada.
Vejamos dois exemplos que ilustram o uso de subconsultas subordinadas com o operador IN. No primeiro, vamos considerar que seja preciso produzir a lista de cursos e disciplinas para as quais haja algum aluno daquele curso com nota nula naquela disciplina. A consulta
select c.curso, d.disciplina from curso c, disciplina d
where d.coddisciplina in ( select i.coddisciplina from inscricao i, aluno a where nota is null
and i.matricula = a.matricula and a.codcurso = c.codcurso )
produz
curso disciplina Direito Dir. Constitucional
pois somente dois alunos têm notas nulas, ambos no curso e na disciplina listados acima. Neste caso, a subconsulta é subordinada porque a variável c, referenciada na subconsulta, está declarada somente na consulta externa.
Um segundo exemplo, que ilustra o uso de subconsultas subordinadas em conjunto com o operador NOT IN, produz a lista de cursos e disciplinas para as quais não haja inscrições dos alunos do curso naquela disciplina.
select c.curso, d.disciplina from curso c, disciplina d
where d.coddisciplina not in ( select i.coddisciplina from inscricao i, aluno a where i.matricula = a.matricula and a.codcurso = c.codcurso) order by c.curso
A consulta tem como resultado
curso disciplina Direito Sociologia Direito Bancos de Dados Direito Compiladores Direito Estatística Informática Português Informática Sociologia Informática Bancos de Dados Informática Estatística Informática Direito Civil Informática Dir. Constitucional Jornalismo Bancos de Dados Jornalismo Compiladores Jornalismo Direito Civil Jornalismo Dir. Constitucional
onde aparecem todas as combinações de cursos e disciplinas para as quais não há alunos inscritos. Note que a subconsulta, cada vez que é invocada, produz a lista de códigos de disciplinas para as quais há inscrições para um ou mais alunos do curso referenciado pela variável de registro c.
9.5 SUBCONSULTAS COMO EXPRESSÕES SIMPLES
Subconsultas que retornam um único valor podem assumir o papel de expressões simples, tanto na lista alvo como em termos lógicos de consultas SQL. Essas subconsultas podem ser subordinadas ou não. Se a subconsulta retornar mais de um valor, ocorre uma condição de erro na execução da consulta.
Tipicamente, subconsultas com essas características retornam resultados obtidos a partir da aplicação de funções de agregação. Considere o exemplo de um consulta que retorna os alunos que obtiveram notas iguais à maior nota.
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina
and i.nota = ( select max(nota) from inscricao ) order by a.nome
O resultado produzido
nome disciplina nota
Maria Lucia Silva Sociologia 10,0
mostra o aluno e a disciplina na qual ocorreu a nota máxima. Note que outros alunos poderiam estar listados se houvesse mais notas iguais à máxima.
O próximo exemplo mostra uma consulta semelhante, porém com o aluno (ou alunos) que obteve a melhor nota em cada disciplina.
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina
and i.nota = (select max(nota) from inscricao ii where ii.coddisciplina = i.coddisciplina) order by a.nome
Neste caso, a subconsulta é subordinada porque é preciso obter o máximo apenas dentre as notas da disciplina correntemente sendo analisada na consulta mais externa. Essa ligação, entre a disciplina sendo analisada e as disciplinas consideradas pela subconsulta é feita através da variável de registro i, definida na consulta externa. O resultado
nome disciplina nota
Barbara Carlito Estatística 4,2 Carlos Maradona Direito Civil 7 Maria Lucia Silva Sociologia 10 Maria Rita Colatti Compiladores 9,5
Ricardo Biondi Português 8
mostra, para cada disciplina que possui inscrições, os alunos que obtiveram as melhores notas.
Todos os alunos eventualmente empatados com as mesmas notas seriam igualmente listados.
∴
Uma subconsulta pode ser utilizada na lista alvo. No exemplo, suponha que seja preciso produzir um resultado que mostre, para cada nota de cada aluno, o percentual de sua nota em relação à nota máxima.
select a.nome, d.disciplina, i.nota,
(select max(nota) from inscricao ii where ii.coddisciplina = i.coddisciplina) as nota_maxima, i.nota / (select max(nota) from inscricao ii where ii.coddisciplina = i.coddisciplina) * 100 as percentual from aluno a, disciplina d, inscricao i
where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and i.nota is not null
order by a.nome
Note que a quarta coluna do resultado é formada pelo resultado de uma subconsulta subordinada que retorna sempre apenas um valor (a nota máxima obtida numa certa disciplina referenciada pela variável de registro d). A quinta coluna decorre de uma expressão aritmética onde um dos termos é o resultado de uma subconsulta. O resultado final é mostrado abaixo.
nome disciplina nota nota_maxima percentual
Barbara Carlito Sociologia 3 10 30,00
Barbara Carlito Estatística 4,2 4,2 100,00
Carlos Maradona Direito Civil 7 7 100,00
Maria Lucia Silva Português 5,5 8 68,75
Maria Lucia Silva Sociologia 10 10 100,00
Maria Rita Colatti Compiladores 9,5 9,5 100,00
Ricardo Biondi Português 8 8 100,00
A variável de registro ii ocorre em duas subconcultas distintas...???
9.6 COMPARAÇÕES COLETIVAS COM SUBCONSULTAS
Termos lógicos de comparação coletiva podem assumir duas formas básicas que determinam comparações com os valores resultantes de uma subconsulta. Essas formas podem ser combinadas com subconsultas subordinadas ou não.
COMPARAÇÃO COM QUALQUER DOS VALORES DE UMA SUBCONSULTA
O primeiro tipo de comparação coletiva segue o formato
expressão <comparação> ANY ( subconsulta )
ou, em outra forma equivalente,
expressão <comparação> SOME ( subconsulta )
e estabelece que o termo lógico é verdadeiro se valor da expressão atende o critério de comparação com pelo menos um dos valores retornados pela subconsulta. Quando o operador de comparação é a igualdade (=), esta construção funciona exatamente como as subconsultas associadas ao operador IN. A consulta
select nome from aluno
where matricula = any ( select matricula from inscricao) order by nome
produz resultado ao obtido anteriormente neste capítulo, quando foi construída a lista de alunos que têm alguma inscrição.
nome Barbara Carlito Carlos Maradona Maria Lucia Silva Maria Rita Colatti Ricardo Biondi
Outra consulta apresentada anteriormente com o operador IN, que produzia a lista de cursos e disciplinas para os quais há alguma nota nula, pode ser refraseada como
select c.curso, d.disciplina from curso c, disciplina d
where d.coddisciplina = any ( select coddisciplina
from inscricao i, aluno a where i.nota is null
and i.matricula = a.matricula and a.codcurso = c.codcurso )
produzindo
curso disciplina Direito Dir. Constitucional
Esta subconsulta é subordinada à consulta que a envolve através da variável de registro c.
COMPARAÇÃO COM TODOS OS VALORES DE UMA SUBCONSULTA
O segundo tipo de comparação coletiva segue o modelo
expressão <comparação> ALL ( subconsulta )
e estabelece que o termo lógico é verdadeiro se o valor da expressão atende ao critério de comparação para todos os valores retornados pela subconsulta.
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and i.nota >= all ( select nota from inscricao
where nota is not null ) order by a.nome
O resultado
nome disciplina nota
Maria Lucia Silva Sociologia 10,0
contém o nome da aluna que obteve a nota mais alta. Neste caso, é preciso suprimir as notas nulas do resultado da subconsulta, pois sem fazer isso o teste falha, porque o critério de comparação é sempre falso para valores nulos. Note que se um valor x é maior ou igual a todos de um conjunto, então nenhum deste conjunto é maior que x. Ou seja, o teste utilizado equivale à identificação do valor máximo de um conjunto.
∴
O próximo exemplo dá as notas máximas por disciplina e toma como base a consulta anteriormente apresentada
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and i.nota >= all ( select nota from inscricao ii
where ii.coddisciplina = i.coddisciplina and ii.nota is not null )
order by a.nome
O resultado
nome disciplina nota
Barbara Carlito Estatística 4,2 Carlos Maradona Direito Civil 7 Carlos Maradona Dir. Constitucional NULL Maria Lucia Silva Sociologia 10 Maria Rita Colatti Compiladores 9,5
Ricardo Biondi Português 8
Ricardo Biondi Dir. Constitucional NULL
entretanto, não é exatamente o mesmo da seção 9.5. Por quê? Tente analisar a consulta e descobrir a causa antes de prosseguir na leitura!
Ocorre que quando a variável de registro referencia a disciplina Dir. Constitucional não há nota alguma diferente de nulo. Logo, o resultado da subconsulta é vazio. considera o critério de comparação coletiva verdadeiro. Por esta razão, para as disciplinas que não possuem nota alguma preenchida, com resultado vazio para a subconsulta, qualquer inscrição é tida como possuindo a nota mais alta.
Para contornar este problema, basta filtrar as notas nulas também na consulta mais externa como mostrado abaixo.
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and i.nota is not null
and i.nota >= all ( select ii.nota from inscricao ii
where ii.coddisciplina = i.coddisciplina and ii.nota is not null )
order by a.nome
O resultado agora não contém notas nulas.
nome disciplina nota
Barbara Carlito Estatística 4,2 Carlos Maradona Direito Civil 7 Maria Lucia Silva Sociologia 10 Maria Rita Colatti Compiladores 9,5
Ricardo Biondi Português 8
Sempre que o SQL faz uma comparação coletiva com o modificador ALL em relação a uma subconsulta vazia, o resultado é verdadeiro, qualquer que seja o operador de comparação.
Para contornar este problema, basta filtrar as notas nulas também na consulta mais externa como mostrado abaixo.
9.7 SUBCONSULTAS COM A CLÁUSULA EXISTS
A cláusula EXISTS permite testar se o resultado de uma consulta é vazio ou não. Um resultado vazio, isto é, com zero linhas, faz com que o termo lógico
EXISTS ( subconsulta )
receba o valor lógico false. Se a subconsulta retorna uma ou mais linhas, o termo lógico é true.
O teste pode ser invertido quando se utiliza a sintaxe
NOT EXISTS ( subconsulta )
Embora as subconsultas utilizadas com a cláusula EXISTS possam ser subordinadas ou não , é bastante rara a ocorrência de consultas onde esse tipo de termo lógico contenha subconsultas não subordinadas.
∴
Como exemplo de uma subconsulta subordinada na primeira das formas sintáticas apresentadas acima, considere a consulta que produz a lista de alunos que têm alguma inscrição, apresentada no início da seção 9.4.
select nome from aluno a
where exists (select * from inscricao i where a.matricula = i.matricula) order by nome
Agora, o resultado é
nome Barbara Carlito Carlos Maradona Maria Lucia Silva Maria Rita Colatti Ricardo Biondi
Note que o significado desta construção pode ser interpretado como um teste que verifica se existe algum registro em INSCRIÇÃO cuja matrícula seja a mesma do registro representado pela variável de registro a. Em função disso, a lista alvo da subconsulta não é relevante (por isso usamos o construtor *) uma vez que basta verificar se há alguma linha no resultado ou não.
A consulta
select nome from aluno a
where not exists ( select * from inscricao i where a.matricula = i.matricula ) order by nome
mostra os alunos que não têm inscrições, ou seja, para os quais não existe um registro i em INSCRIÇÃO com a mesma matrícula. Essa forma sintática corresponde à operação de diferença da álgebra relacional, pois de todos os alunos excluem-se os que não possuem inscrição alguma.
9.8 SUBCONSULTAS COMO FONTES DE REGISTROS NA CLÁUSULA FROM
Em todos exemplos até aqui, somente tabelas foram utilizadas como fontes de registros na cláusula from. Subconsultas também podem ter este papel, como mostra o exemplo abaixo.1
select aa.nome
from ( select * from aluno a where a.sexo = 'F' ) aa order by aa.nome
O resultado contém os nomes dos alunos do sexo feminino.
nome Barbara Carlito Maria Lucia Silva Maria Rita Colatti
A fonte de registros sobre a qual a variável de registro aa foi definida é o resultado da consulta
( select * from aluno a where a.sexo = 'F' )
Esta construção não é aceita no Access.
∴
Fontes de registros definidas por subconsultas podem aparecer em qualquer das construções sintáticas válidas para tabelas. A consulta
select c.curso, aa.nome
from ( select * from aluno a where a.sexo = 'F' ) aa inner join curso c on aa.codcurso = c.codcurso
order by c.curso, aa.nome
produz a lista de alunas com os respectivos cursos.
curso nome Informática Maria Rita Colatti
1 Em SQL, views (ou visões, apresentadas no capítulo 13) também podem ser utilizadas para a definição de fontes de registros. Alguns autores denominam unnamed views (visões sem nome) as fontes de registros definidas por subconsultas.
Jornalismo Barbara Carlito Jornalismo Maria Lucia Silva
Neste exemplo, a fonte de registros é combinada com a tabela CURSO através de uma junção do tipo inner join, exatamente como se fosse uma tabela qualquer da base de dados.
∴
A construção da subconsulta que suporta uma fonte de registros determina com serão as referências aos seus registros e atributos na consulta que a utiliza. O exemplo anterior poderia ter sido escrito como
select c.curso, aa.nome_aluno
from ( select a.matricula as registro, a.nome as nome_aluno, a.codcurso as codigo_curso from aluno a
where a.sexo = 'F'
) aa inner join curso c on aa.codigo_curso = c.codcurso order by c.curso, aa.nome_aluno
Aqui, a fonte de registros tem três colunas, denominadas registro, nome_aluno e codigo_curso, que renomeiam as colunas originalmente denominadas matricula, nome e codcurso, respectivamente. A operação de junção é especificada com os novos nomes das colunas, assim como a lista alvo e a cláusula order by referenciam esses mesmos nomes.
9.9 EQUIVALÊNCIA ENTRE DIFERENTES CONSTRUÇÕES COM SUBCONSULTAS
Algumas das construções que envolvem subconsultas sob diferentes sintaxes são equivalentes entre si. Por exemplo, a consulta que produz a lista de alunos que não têm reprovações
select a.matricula, a.nome from aluno a
where a.matricula not in ( select i.matricula from inscricao i where i.nota < 5 ) order by a.nome
pode ser rescrita como
select a.matricula, a.nome from aluno a
where a.matricula <> all ( select i.matricula from inscricao i where i.nota < 5 ) order by a.nome
ou
select a.matricula, a.nome from aluno a
where not exists ( select * from inscricao i
where i.nota < 5 and i.matricula = a.matricula ) order by a.nome
ou, ainda,
select a.matricula, a.nome from aluno a
where 0 = ( select count(*) from inscricao i
where i.nota < 5 and i.matricula = a.matricula ) order by a.nome
sempre com resultados idênticos.
∴
Essas possibilidades freqüentemente confundem programadores SQL iniciantes, que ficam indecisos quanto à alternativa a ser escolhida, além de constituírem fonte de críticas à própria estrutura sintática do SQL.
Como regras gerais, podemos observar que:
• termos lógicos da forma expressão IN ( subconsulta ) são equivalentes a
expressão = any ( subconsulta )
• termos lógicos da forma
expressão not in ( subconsulta ) são equivalentes a
expressão <> all ( subconsulta )
• termos lógicos da forma
expressão in ( select ... ) podem ser transformados em termos da forma
exists ( select ... where ... and expressão = ... )
• termos lógicos da forma
<expressão> not in (select ... ) podem ser transformados em termos da forma
not exists ( select ... where ... and expressão = ... )
• termos lógicos da forma
exists ( select * from ... ) podem ser transformados em termos da forma
( select count (*) from ...) > 0
onde a subconsulta retorna uma função de agregação de contagem
• termos lógicos da forma
not exists ( select * from ... ) podem ser transformados em termos da forma ( select count (*) from ...) = 0
onde a subconsulta retorna uma função de agregação de contagem
• termos lógicos da forma
expressão in ( subconsulta )
onde a subconsulta não possui funções de agregação na lista alvo, podem ser sempre eliminados e incorporados à consulta externa
• termos lógicos da forma
exists ( subconsulta )
onde a subconsulta não possui funções de agregação na lista alvo, podem ser sempre eliminados e incorporados à consulta externa
• fontes de registros constituídas por subconsultas sempre podem ser eliminadas e incorporadas à consulta
A lista de exercícios deste capítulo explora exaustivamente essa equivalência e transformações e sua confecção é extremamente importante para a compreensão e domínio das regras acima.
9.10 COMBINANDO TERMOS LÓGICOS E ANINHANDO SUBCONSULTAS
Uma consulta SQL pode ter termos lógicos baseados em subconsultas combinados livremente.
Além disso, subconsultas podem possuir termos lógicos que contêm subconsultas que por sua vez contêm subconsultas, que contêm subconsultas e assim sucessivamente.
Na seção 9.1, a consulta
select a.matricula, a.nome from aluno a
where a.matricula not in ( select i.matricula from inscricao i where i.nota < 5 ) order by a.nome
produz a lista de alunos que não têm reprovações. Entretanto, alunos que não têm inscrições também aparecem no resultado. Para mostrar somente alunos que têm alguma inscrição, nenhuma delas com reprovação, a consulta poderia ser rescrita como
select a.matricula, a.nome from aluno a
where exists ( select * from inscricao i where i.matricula = a.matricula ) and a.matricula not in ( select i.matricula from inscricao i where i.nota < 5 ) order by a.nome
Nesta versão, a consulta combina o resultado de dois termos lógicos baseados em subconsultas:
o primeiro garante que o aluno tem pelo menos uma inscrição e o segundo garante que o aluno não tem reprovações. O resultado, agora, contém apenas quatro alunos.
matricula nome 1007 Carlos Maradona 1010 Maria Lucia Silva 1002 Maria Rita Colatti 1001 Ricardo Biondi
∴
Subconsultas aninhadas constituem uma poderosa ferramenta para a construção de consultas logicamente complexas. Para ilustrar isso, vamos revisitar uma consulta apresentada no capítulo 3, na álgebra relacional, cujo objetivo era produzir a lista de disciplinas cursadas por todos os alunos do curso de Jornalismo (“JOR”). Como vimos, a obtenção desse resultado através da álgebra requer o emprego da operação de diferença duas vezes. Em SQL poderíamos escrever
select d.disciplina from disciplina d
where not exists ( select * from aluno a
where a.codcurso = 'JOR' and not exists ( select * from inscricao i
where i.coddisciplina = d.coddisciplina and i.matricula = a.matricula )
)
Logicamente, os termos significam que se deve selecionar as disciplinas tal que não existem alunos do curso de Jornalismo que não as estejam cursando. Se não é possível encontrar um aluno de Jornalismo que não esteja cursando determinada disciplina, então pode-se concluir que todos os alunos de Jornalismo cursam aquela disciplina.
A subconsulta
( select * from aluno a
where a.codcurso = 'JOR' and not exists ( select *
from inscricao i
where i.coddisciplina = d.coddisciplina and i.matricula = a.matricula )
)
produz a lista de alunos de Jornalismo que não estão inscritos na disciplina correspondente ao conteúdo da variável de registro d, no instante em que a subconsulta é executada. Note que essa variável é declarada na consulta mais externa. Todas as disciplinas para as quais este resultado é vazio aparecem no resultado.
Há programadores SQL que sentem-se mais confortáveis resolvendo problemas que envolvem a operação de diferença simplesmente pela contagem dos elementos dos conjuntos envolvidos.
No caso acima, pode-se pensar que se o número de inscrições, por parte de alunos de Jornalismo, numa determinada disciplina é igual ao número total de alunos de Jornalismo, então todos os alunos estão inscritos na referida disciplina. Em SQL isso pode ser escrito como
select d.disciplina from disciplina d
where ( select count(*) from aluno a where a.codcurso = 'JOR' ) =
( select count(*) from inscricao i, aluno a
where i.coddisciplina = d.coddisciplina and i.matricula = a.matricula and a.codcurso = 'JOR' )
A subconsulta que conta os alunos de Jornalismo não é subordinada, porque a contagem não depende de qual a disciplina que está sendo analisada. Já para a segunda subconsulta, subordinada pela variável de registro d, a contagem é feita para uma disciplina específica.
∴
Um fato curioso sobre os dois últimos exemplos é que se considerarmos um curso que não existe, ou que não tenha aluno algum, todas as disciplinas serão listadas no resultado. Por exemplo, se a consulta for modificada para
select d.disciplina from disciplina d
where ( select count(*) from aluno a where a.codcurso = 'XXX' ) =
( select count(*) from inscricao i, aluno a
where i.coddisciplina = d.coddisciplina and i.matricula = a.matricula and a.codcurso = 'XXX' )
o resultado incluiria todas as disciplinas como se as mesmas estivessem sendo cursadas por todos os alunos do curso “XXX”, que não existe! Para evitar este caso anômalo, pode-se rescrever as consultas como
select d.disciplina from disciplina d
where exists ( select * from aluno a where a.codcurso = 'JOR' ) and not exists ( select *
from aluno a
where a.codcurso = 'JOR' and not exists ( select * from inscricao i
where i.coddisciplina = d.coddisciplina and i.matricula = a.matricula )
)
e
select d.disciplina from disciplina d
where ( select count(*) from aluno a where a.codcurso = 'JOR' ) > 0 and ( select count(*) from aluno a where a.codcurso = 'JOR' )
=
( select count(*) from inscricao i, aluno a
where i.coddisciplina = d.coddisciplina and i.matricula = a.matricula and a.codcurso = 'JOR' )
9.11 AVALIAÇÃO DE CONSULTAS COM SUBCONSULTAS
Uma consulta SQL contendo subconsultas pode ter uma formulação bastante complexa e é preciso interpretar cuidadosamente como é feita sua avaliação. Em princípio, consultas admitem subconsultas:
• nas fontes de registros;
• nos termos lógicos da cláusula where;
• na lista alvo;
• na cláusula order by;
• na cláusula having.
Para ilustrar o mecanismo de avaliação utilizado pelo SQL vamos considerar a consulta
select a.nome, d_i.disciplina, d_i.nota,
d_i.nota / (select max(nota) from inscricao ii where ii.coddisciplina = d_i.coddisciplina) * 100 as percentual from ( select nome, matricula from aluno ) a,
( select d.disciplina, d.coddisciplina, i.matricula, i.nota from disciplina d, inscricao i
where d.coddisciplina = i.coddisciplina and i.nota is not null ) d_i where a.matricula =d_i.matricula
and ( select count(*) from inscricao i where i.matricula = d_i.matricula ) >= 2
and not exists ( select * from inscricao i where i.matricula = d_i.matricula and nota < 5 ) order by ( select avg(nota) from inscricao i where i.matricula = d_i.matricula ) desc
Embora esta consulta pudesse ter sido formulada de maneira bem mais simples, é interessante analisá-la tal como foi apresentada acima. O resultado produzido
nome disciplina nota percentual
Ricardo Biondi Português 8,0 100,00
Maria Lucia Silva Português 5,5 68,75
Maria Lucia Silva Sociologia 10,0 100,00 Carlos Maradona Direito Civil 7,0 100,00
contém a lista de alunos que possuem pelo menos duas inscrições e não têm reprovações, mostrando, para cada aluno, a nota em cada disciplina e o percentual que esta nota representa em relação à nota máxima obtida na mesma. Os alunos são listados em ordem decrescente de média geral. Não são mostradas as notas nulas.
A primeira providência para a avaliação de uma consulta é definir as fontes de registros, que determinam os valores que as variáveis de registro podem adquirir. Nossa consulta exemplo possui duas fontes de registros, ambas definidas através de subconsultas. A primeira
( select nome, matricula from aluno )
tem a ela associada a variável de registro a. A segunda fonte de registros, sobre a qual está associada a variável d_i, é definida pela subconsulta
( select d.disciplina, d.coddisciplina, i.matricula, i.nota from disciplina d, inscricao i
where d.coddisciplina = i.coddisciplina and i.nota is not null )
A instância de cada uma das fontes de registros, após a avaliação das respectivas consultas, é mostrada abaixo. Note que cada uma delas tem, coincidentemente, 7 registros. As colunas que
numeram as linhas de cada fonte são meramente ilustrativas, não fazendo parte do resultado das consultas.
Como visto no capítulo 5, é preciso verificar a expressão lógica da cláusula where para cada combinação das possíveis instâncias das variáveis de registro a e d_i. Toda vez que uma determinada combinação resultar no valor verdadeiro para a expressão lógica, uma nova linha do resultado é montada.
A cláusula where da nossa consulta exemplo
where a.matricula =d_i.matricula
and ( select count(*) from inscricao i where i.matricula = d_i.matricula ) >= 2
and not exists ( select * from inscricao i where i.matricula = d_i.matricula and nota < 5 )
é formada pela conjunção de três termos lógicos. Dois deles contêm subconsultas subordinadas pois referem-se a variáveis de registros externas à subconsulta, que devem estar instanciadas quando da avaliação dessas subconsultas.
Vamos considerar a avaliação da expressão lógica quando a combinação do primeiro registro de cada fonte de registro estiver sendo analisada. A variável a contém o registro
nome matricula Ricardo Biondi 1001
e a variável d_i contém o registro
disciplina coddisciplina matricula nota
Português 317 1001 8
Neste caso, a expressão lógica sendo avaliada corresponde a
where 1001 = 1001
and ( select count(*) from inscricao i where i.matricula = 1001 ) >= 2
and not exists ( select * from inscricao i where i.matricula = 1001 and nota < 5 )
se substituirmos as expressões por seus valores atuais. O primeiro termo lógico é verdadeiro, porque 1001 é igual a 1001, evidentemente. O segundo termo lógico testa a condição
( select count(*) from inscricao i where i.matricula = 1001 ) >= 2
Para este teste, é preciso avaliar a subconsulta. Como esta subconsulta foi utilizada como uma expressão simples deve retornar sempre um único valor. O resultado2
Expr1000 2
pode então ser substituído no termo lógico original, que passa a ser
( 2 ) >= 2
2 O nome da coluna no resultado da subconsulta é irrelevante. No caso, mostramos uma consulta submetida ao Access.
. . . . . .
1 2 3 4 5 6 7
disciplina coddisciplina matricula nota
Português 317 1001 8
Compiladores 210 1002 9,5
Sociologia 316 1005 3
Estatística 117 1005 4,2
Direito Civil 114 1007 7
Português 317 1010 5,5
Sociologia 316 1010 10
d_i .
. . . . .
1 2 3 4 5 6 7
nome matricula Ricardo Biondi 1001 Maria Rita Colatti 1002 Oscarito Vianna 1004 Barbara Carlito 1005 Carlos Maradona 1007 Sacadura Miranda 1008 Maria Lucia Silva 1010 a
que é verdadeiro. O terceiro termo lógico usa uma subconsulta com a cláusula EXISTS.
not exists ( select * from inscricao i where i.matricula = 1001 and nota < 5 )
Como o resultado da subconsulta é vazio, pois o aluno cuja inscrição é 1001 não possui reprovações, tem-se que o resultado final do termo lógico é verdadeiro. Note que
exists ( select * from inscricao i where i.matricula = 1001 and nota < 5 )
é falso e, portanto, sua negação é verdadeira.
Já que a combinação das duas primeiras instâncias das variáveis a e d_i passam no critério de filtragem da cláusula where, pode-se montar uma linha do resultado a partir das mesmas. Uma linha do resultado é definida pela cláusula
select a.nome, d_i.disciplina, d_i.nota,
d_i.nota / (select max(nota) from inscricao ii where ii.coddisciplina = d_i.coddisciplina) * 100 as percentual
As três primeiras colunas do resultado vêm diretamente das colunas nome, disciplina e nota, e no caso correspondem aos valores “Ricardo Biondi”, “Português” e 8. A quarta coluna resulta de uma expressão aritmética que contém uma expressão constituída por uma subconsulta.
Como esta subconsulta é subordinada, é preciso determinar o valor da expressão d_i.coddisciplina que depende de uma variável de registro externa. Após a substituição, a expressão passa para
8 / (select max(nota) from inscricao ii where ii.coddisciplina = 317) * 100 as percentual
podendo agora ser avaliada. O resultado da subconsulta
(select max(nota) from inscricao ii where ii.coddisciplina = 317)
é
Expr1000 8
e a expressão que define a quarta coluna do resultado deve ser avaliada como
8 / (8) * 100
com o valor final 100.
As linhas do resultado final da consulta devem ser ordenadas como especifica a cláusula order by
order by ( select avg(nota) from inscricao i where i.matricula = d_i.matricula ) desc
Sendo assim, é preciso calcular o valor para o critério de ordenação de cada linha. Sendo a subconsulta subordinada e após a substituição dos valores oriundos de variáveis externas
( select avg(nota) from inscricao i where i.matricula = 1001 )
pode-se avaliar a subconsulta, cujo resultado
Expr1000 8
será utilizado na fase de ordenação final das linhas do resultado.
∴
Quando a variável de registro a está instanciada pelo registro 4
nome matricula Barbara Carlito 1005
e a variável de registro d_i pelo registro 3
disciplina coddisciplina matricula nota
Sociologia 316 1005 3
das respectivas fontes de registros, após a substituição dos valores das variáveis a expressão lógica da cláusula where passa para
where 1005 = 1005
and ( select count(*) from inscricao i where i.matricula = 1005 ) >= 2
and not exists ( select * from inscricao i where i.matricula = 1005 and nota < 5 )
Neste caso, o primeiro termo lógico é verdadeiro e o segundo também, porque a aluna de matrícula 1005 possui duas inscrições. Porém, a subconsulta
( select * from inscricao i where i.matricula = 1005 and nota < 5 )
produz
matricula coddisciplina nota
1005 316 3
1005 117 4,2
Este resultado faz com que o termo lógico
exists ( select * from inscricao i where i.matricula = 1005 and nota < 5 )
seja verdadeiro (porque existe algum registro de INSCRIÇÃO que atende ao critério de filtragem!) e, portanto, sua negação é falsa. Sendo falso o terceiro termo lógico, a conjunção da cláusula where é falsa e a combinação de registros sendo testada não gera linha alguma no resultado final da consulta.
∴
Consultas contendo subconsultas na cláusula having são avaliadas de forma semelhante. Como vimos no capítulo 6, a avaliação de consultas com funções de agregação passa pela construção do resultado pré-agregação antes que os registros sejam grupados e essas funções sejam efetivamente aplicadas. A cláusula having aplica-se como um filtro final, que atua no resultado após os cálculo de agregação terem sido efetuados.
Nesta fase, não há mais referência às instâncias das variáveis de registros utilizadas para a construção de cada uma das linhas do resultado pré-agregação, já que várias linhas podem ter sido combinadas na fase de grupamento (de acordo com a cláusula group by). Por este motivo, não é possível utilizar subconsultas subordinadas imediatamente à consulta que as envolve como expressões na cláusula having. A consulta apresentada no início deste capítulo
select a.matricula, a.nome, avg(i.nota) as nota_media from aluno a, inscricao i
where a.matricula = i.matricula group by a.matricula, a.nome
having avg(i.nota) > ( select avg(nota) from inscricao ) order by a.nome
é um exemplo de uso de subconsultas na fase de filtragem dos grupos. Note que a subconsulta em tela não é subordinada. Após sua avaliação, a expressão correspondente assume o valor do resultado da subconsulta, como abaixo
select a.matricula, a.nome, avg(i.nota) as nota_media from aluno a, inscricao i
where a.matricula = i.matricula group by a.matricula, a.nome having avg(i.nota) > ( 6.74 ) order by a.nome
Note, porém, que uma subconsulta que aparece numa cláusula having de uma subconsulta pode estar subordinada às consultas que são externas a ambas.
∴
É importante que o leitor tenha em mente que, embora a seqüência aqui apresentada
estabeleça uma ordem lógica para avaliação de consultas SQL contendo subconsultas, cada implementação pode seguir diferentes caminhos na construção do resultado final. Entretanto, esse resultado deve ser, sempre, rigorosamente o mesmo. Este fato deve-se à complexa manipulação lógica que é levada a efeito quando uma consulta SQL é aceita e preparada para avaliação.
9.12 IMPLEMENTAÇÃO DE SUBCONSULTAS
As seções a seguir destacam algumas das peculiaridades de cada implementação no trato de subconsultas.
SUBCONSULTAS NO ACCESS
As seguintes regras e restrições aplicam-se ao tratamento de subconsultas pelo Access:
• subconsultas não podem ser utilizadas como fontes de registros. Se isso for necessário, é preciso transformar a subconsulta numa view (assunto abordado no capítulo 13). Este recurso substitui subconsultas que não são subordinadas; no caso de subconsultas subordinadas, a presença de views não é totalmente equivalente pois não se pode trabalhar com variáveis externas numa view do Access;
• a lista alvo de um consulta de referência cruzada não pode conter uma expressão formada por uma subconsulta;
• subconsultas utilizadas como expressões podem ser ordenadas e combinadas com a cláusula top. Este caso é bem ilustrado pela consulta abaixo, equivalente à consulta apresentada na seção 9.5, que retorna a lista de alunos, disciplinas e notas tal que a nota tenha sido igual à nota máxima. Observe que a máxima nota pode ser obtida tomando-se o primeiro valor do conjunto de notas ordenado do maior para o menor.
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina
and i.nota = ( select top 1 nota from inscricao order by nota desc ) order by a.nome
SUBCONSULTAS NO MYSQL
As seguintes regras e restrições aplicam-se ao tratamento de subconsultas pelo MySQL:
• subconsultas que definem fontes de registros não podem ser subordinadas;
• a exemplo do Access, subconsultas ordenadas podem utilizar cláusulas que limitam o número de linhas do resultado. A construção equivalente à consulta apresentada na seção 9.5, que retorna a lista de alunos, disciplinas e notas tal que a nota tenha sido igual à nota máxima, é mostrada a seguir.
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina
and i.nota = ( select nota from inscricao order by nota desc limit 1 ) order by a.nome
SUBCONSULTAS NO ORACLE
As seguintes regras e restrições aplicam-se ao tratamento de subconsultas pelo Oracle:
• subconsultas que definem fontes de registros não podem ser subordinadas;
• subconsultas como expressões podem ser utilizadas na lista de valores de comandos insert (veja capítulo 12).
∴
Oracle oferece uma construção sintática que permite o fatoramento de subconsultas. Com isso, é possível especificar subconsultas em separado, nomeá-las e depois utilizá-las em outras subconsultas ou comandos SQL.
A sintaxe dessa construção é bastante simples:
<cláusula with> :=
with { <nome da subconsulta> as <subconsulta> },...
O exemplo abaixo define a subconsulta reprovados e a utiliza mais adiante num comando select.
with reprovados as
(select distinct i.matricula from inscricao i
where i.nota < 5) select a.matricula, a.nome from aluno a
where a.matricula not in (select * from reprovados) order by a.nome;
O resultado contém a lista de alunos que não possuem reprovação alguma (alunos cujas matrículas não aparecem na lista de matrículas dos alunos reprovados).
matricula nome
1007 Carlos Maradona 1010 Maria Lucia Silva 1002 Maria Rita Colatti 1004 Oscarito Vianna 1001 Ricardo Biondi 1008 Sacadura Miranda
Subconsultas definidas na cláusula with podem aparecer também como fontes de registros num comandos SQL, como no próximo exemplo.
with reprovados as
(select i.matricula from inscricao i where i.nota < 5), alunas as
(select * from aluno a where a.sexo='F') select a.matricula, a.nome from alunas a
where a.matricula not in (select * from reprovados) order by a.nome;
Neste caso, são produzidos os nomes alunas que não têm reprovações. Note a primeira consulta define o conjunto de alunas e a segunda subconsulta nomeada define as matrículas dos alunos com reprovação. O resultado é mostrado abaixo.
matricula nome
1010 Maria Lucia Silva 1002 Maria Rita Colatti
A cláusula with não pode ser estar aninhada numa subconsulta que também contém uma cláusula with. Várias subconsultas podem ser especificadas, desde que separadas por vírgulas.
Note que, neste caso, não é possível o uso de variáveis subordinadas.
A cláusula with é um recurso interessante quando são construídos comandos SQL muito longos, de difícil compreensão. Seu uso, entretanto, é restrito ao Oracle.
∴
Outro recurso bastante útil disponível no Oracle é a possibilidade de escrever expressões com mais de uma valor quando do teste de pertinência numa subconsulta. Observe o exemplo abaixo.
select a.nome, d.disciplina from aluno a, disciplina d
where (a.matricula,d.coddisciplina) not in (select matricula,coddisciplina from inscricao)
Neste caso, o teste de pertinência é efetuado simultaneamente com duas expressões combinadas.
Para programadores que preferem o uso da cláusula in ao invés da cláusula exists, essa forma sintática facilita bastante. O exemplo retorna a lista de combinações de alunos e disciplinas para os quais não há inscrições. Na falta desse recurso, resultado idêntico poderia ser obtido pelo comando
select a.nome, d.disciplina from aluno a, disciplina d
where not exists (select matricula,coddisciplina from inscrição i
where a.matricula=i.matricula and d.coddisciplina=i.coddisciplina) SUBCONSULTAS NO SQLSERVER
As seguintes regras e restrições aplicam-se ao tratamento de subconsultas pelo SQL Server:
• a exemplo do Access, subconsultas podem ser ordenadas se contiverem a cláusula top limitando o número de linhas do resultado. Vide o exemplo apresentado para o Access;
• subconsultas que definem fontes de registros podem ser subordinadas. O exemplo a seguir ilustra este caso, com uma consulta que retorna disciplinas, matrículas, nomes e notas para as inscrições cujas notas são superiores à média das notas na referida disciplina (equivalente à consulta da seção 9.2. Note que a subconsulta refere-se a uma fonte de registros definida sobre o resultado da subconsulta
(select * from inscricao where inscricao.coddisciplina = i.coddisciplina )
que utiliza a variável de registro i, definida na consulta externa;
select d.disciplina, a.matricula, a.nome, i.nota from aluno a, inscricao i, disciplina d where a.matricula = i.matricula and i.coddisciplina=d.coddisciplina and i.nota >= ( select avg(x.nota)
from ( select * from inscricao
where inscricao.coddisciplina = i.coddisciplina ) x ) order by d.disciplina, a.nome
• a lista alvo de uma subconsulta não pode conter colunas dos tipos ntext, text e image;
• a lista alvo de uma subconsulta associada à clausula exists é sempre equivalente a select *
porque a subconsulta não retorna dados pois constitui apenas um teste de existência;
• uma subconsulta empregada como expressão não pode conter cláusulas group by e having;
• subconsultas com a cláusula group by não podem conter a opção distinct na cláusula select;
• subconsultas não podem conter a cláusula compute (vide capítulo 6).
EXERCÍCIOS
9.1 Escreva uma consulta equivalente a
select nome from aluno
where matricula in (select matricula from inscricao) order by nome
a) utilizando uma construção do tipo exists ( subconsulta );
b) sem utilizar subconsultas.
9.2 Escreva uma consulta equivalente a
select c.curso, d.disciplina from curso c, disciplina d
where d.coddisciplina not in ( select i.coddisciplina from inscricao i, aluno a where i.matricula = a.matricula and a.codcurso = c.codcurso) order by c.curso
a) utilizando uma construção do tipo not exists ( subconsulta );
b) utilizando uma construção do tipo expressão <comparação> any ( subconsulta ).
9.3 Escreva uma consulta equivalente a
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina
and i.nota = ( select max(nota) from inscricao ) order by a.nome
sem utilizar subconsultas na cláusula where.
9.4 Escreva uma consulta equivalente a
select a.nome, d.disciplina, i.nota from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina
and i.nota = (select max(ii.nota) from inscricao ii where ii.coddisciplina = i.coddisciplina) order by a.nome
sem utilizar subconsultas na cláusula where.
9.5 Altere o mínimo possível a consulta
select c.curso, d.disciplina from curso c, disciplina d
where d.coddisciplina = some ( select i.coddisciplina from inscricao i, aluno a where i.nota is null
and i.matricula = a.matricula and a.codcurso = c.codcurso )
para produzir uma versão equivalente que utilize a comparação coletiva com o modificador all ao invés de some.
9.6 Escreva uma consulta SQL que produza uma lista de cursos e disciplinas, em ordem alfabética, tal que para curso/disciplina que aparecem no resultado nenhum dos alunos daquele curso obteve aprovação nem obteve nota acima da média daquela disciplina. O resultado deve ser
curso disciplina
Jornalismo Estatística Jornalismo Português
Note que na disciplina de Português (317) há um aluno de Jornalismo inscrito (1010) que obteve aprovação (5,5) mas que não obteve nota acima da média da disciplina ( (5,5 + 8)/2 = 6,75 ).
9.7 Escreva uma consulta SQL que produza a lista dos cursos, em ordem alfabética, para os quais nenhum dos alunos tenha sido reprovado. O resultado deve ser
curso Direito Informática
9.8 Escreva uma consulta SQL que produza a lista dos alunos, em ordem alfabética, que inscreveram-se em Português ou Sociologia, ou ambas, mas não inscreveram-se em Bancos de Dados nem Direito Civil. O resultado deve ser
nome Barbara Carlito Maria Lucia Silva Ricardo Biondi
9.9 Escreva uma consulta equivalente a
select a.nome, d_i.disciplina, d_i.nota,
d_i.nota / (select max(ii.nota) from inscricao ii
where ii.coddisciplina = d_i.coddisciplina) * 100 as percentual from ( select nome, matricula from aluno ) a,
( select d.disciplina, d.coddisciplina, i.matricula, i.nota from disciplina d, inscricao i
where d.coddisciplina = i.coddisciplina and i.nota is not null ) d_i where a.matricula = d_i.matricula
and ( select count(*) from inscricao i where i.matricula = d_i.matricula ) >= 2
and not exists ( select * from inscricao i where i.matricula = d_i.matricula and nota < 5 ) order by ( select avg(iii.nota) from inscricao i where iii.matricula = d_i.matricula ) desc
sem utilizar fontes de registros definidas sobre subconsultas.
9.10 Indique qual é o resultado da consulta abaixo e explique o porquê.
select * from aluno
where exists ( select count(*) from inscricao
where aluno.matricula=aluno.matricula )
9.11 Escreva uma consulta SQL que produza, para cada disciplina, o aluno que obteve a melhor nota. Notas nulas não devem ser consideradas. O resultado deve estar no formato mostrado a seguir.
disciplina nome nota
Compiladores Maria Rita Colatti 9,5 Direito Civil Carlos Maradona 7 Estatística Barbara Carlito 4,2
Português Ricardo Biondi 8
Sociologia Maria Lucia Silva 10