Atenção: por essa edição ser muito antiga não há arquivo PDF para download.
Os artigos dessa edição estão disponíveis somente através do formato HTML.
Clique aqui para ler todos os artigos desta edição
SIMULADO SQL – Respostas e comentários
Na edição anterior propomos um teste sobre a linguagem SQL e um desafio: os dez primeiros que mandassem todas as respostas corretas ganhariam um brinde surpresa e teriam seus nomes publicados no artigo. A iniciativa foi bastante elogiadada e inúmeros emails com respostas foram enviados – os comentários sobre as mensagens recebidas estão no box “Comentários”.
A seguir temos as soluções explicadas para cada questão.
Respostas
1ª Questão:
SELECT T.ANO,T.PERIODO,T.DESCRICAO, A.NOME, D.NO_DISCIPLINA,
SUM(AV.NT_AVALIACAO)/T.NUM_PROVAS MEDIA
FROM ALUNO_TURMA ALT JOIN ALUNO A ON (ALT.CO_ALUNO=A.CO_ALUNO)
JOIN AVALIACAO AV ON (ALT.CO_ALUNO=AV.CO_ALUNO AND
ALT.CO_TURMA=AV.CO_TURMA)
JOIN TURMA T ON (T.CO_TURMA=AV.CO_TURMA)
JOIN DISCIPLINA D ON (AV.CO_DISCIPLINA=D.CO_DISCIPLINA)
WHERE T.ANO=2002 AND ALT.DT_CANCELAMENTO IS NULL
GROUP BY T.ANO,T.PERIODO,T.DESCRICAO,A.CO_ALUNO, A.NOME,D.NO_DISCIPLINA,T.NUM_PROVAS
ORDER BY ANO,PERIODO, NOME
Explicação:
O primeiro passo para resolver esta questão é realizar um join entre as tabelas que possuem as informações solicitadas: aluno_turma, aluno, avaliacao, turma e disciplina. A tabela aluno_turma informa, através do campo dt_cancelamento, se o aluno cancelou ou não a matrícula. Um valor vazio para esse campo significa que a matrícula não foi cancelada.
As notas são recuperadas através da tabela avaliacao. A informação sobre o número de provas, usada no cálculo da média, está armazenada na tabela turma (campo num_provas). Para o cálculo da média, somamos as notas obtidas e dividimos pelo número de provas que a turma em questão deve realizar:
SUM(AV.NT_AVALIACAO)/T.NUM_PROVAS
Repare que o comando Group By faz a quebra pelo campo no_disciplina, permitindo que o cálculo da média seja efetuado para cada disciplina. Se o agrupamento não fosse realizado, a média seria calculada sobre todas as disciplinas. Observe também que os campos Ano, Periodo, Descricao e Nome foram passados como parâmetro do Group By apenas para permitir sua exibição através do comando SELECT:
SELECT T.ANO,T.PERIODO,T.DESCRICAO, A.NOME, D.NO_DISCIPLINA
Outro detalhe é que a presença do campo Num_provas no Group By permite o seu uso no cálculo da média (executado no comando SELECT).
Para filtragem do ano foi utilizada a sentença T.ANO=2002. Neste caso, não é correto utilizar o campo dt_matricula, da tabela aluno_turma, visto que o aluno pode realizar a matrícula antes do início da turma. Por exemplo, o aluno pode ter realizado a matrícula em dezembro de 2001 (ex: dt_matricula = ‘10/12/2001’) e a turma ter sido efetivamente iniciada em janeiro de 2002.
2ª Questão:
SELECT T.PERIODO, T.CO_TURMA,
(SELECT COUNT(*)
FROM ALUNO_TURMA AL
WHERE AL.CO_TURMA = T.CO_TURMA) INICIARAM,
(SELECT COUNT(*)
FROM ALUNO_TURMA AL
WHERE AL.CO_TURMA = T.CO_TURMA AND
DT_CANCELAMENTO IS NOT NULL) CANCELARAM
FROM TURMA T
WHERE T.ANO=2002
ORDER BY T.PERIODO
Explicação:
Este exemplo foge do padrão select atributo from tabela where condição. A técnica utilizada nesta questão é o uso de subconsultas.
Para cada turma do ano de 2002 (T.ANO=2002), realizamos duas subconsultas:
1) (SELECT COUNT(*)
FROM ALUNO_TURMA AL
WHERE AL.CO_TURMA=T.CO_TURMA) INICIARAM
Esse comando calcula a quantidade de alunos da turma retornada pela consulta principal. Observe na sentença WHERE AL.CO_TURMA = T.CO_TURMA que apenas os alunos da turma em questão serão selecionados.
2) (SELECT COUNT(*)
FROM ALUNO_TURMA AL
WHERE AL.CO_TURMA = T.CO_TURMA AND
DT_CANCELAMENTO IS NOT NULL) CANCELARAM
A diferença da segunda subconsulta é a sentença DT_CANCELAMENTO IS NOT NULL, que seleciona apenas os alunos que cancelaram a participação na turma.
3ª Questão:
CREATE VIEW VIEW_MEDIA AS
SELECT T.ANO,T.PERIODO,T.CO_TURMA,T.DESCRICAO, A.CO_ALUNO,A.NOME, D.NO_DISCIPLINA,
SUM(NT_AVALIACAO)/T.NUM_PROVAS MEDIA
FROM ALUNO_TURMA ALT
JOIN ALUNO A ON (ALT.CO_ALUNO=A.CO_ALUNO)
JOIN AVALIACAO AV ON (ALT.CO_ALUNO=AV.CO_ALUNO AND
ALT.CO_TURMA=AV.CO_TURMA)
JOIN TURMA T ON (ALT.CO_TURMA=T.CO_TURMA)
JOIN DISCIPLINA D ON (AV.CO_DISCIPLINA=D.CO_DISCIPLINA)
GROUP BY T.ANO,T.PERIODO,T.CO_TURMA,T.DESCRICAO,A.CO_ALUNO,
A.NOME,D.NO_DISCIPLINA,T.NUM_PROVAS
SELECT VM.DESCRICAO,VM.CO_TURMA, VM.CO_ALUNO, VM.NOME, AVG(MEDIA)MG
FROM VIEW_MEDIA VM
WHERE VM.ANO=2002 AND VM.PERIODO=1
GROUP BY VM.DESCRICAO, VM.CO_TURMA,VM.CO_ALUNO, VM.NOME
HAVING AVG(MEDIA)>
(SELECT SUM(MEDIA)/COUNT(*)
FROM VIEW_MEDIA VM
WHERE VM.CO_TURMA=01)
Explicação:
O conteúdo da view é semelhante ao resultado da questão 1. Ela retorna a média do aluno para cada disciplina cursada em uma determinada turma.
No comando SELECT utilizamos a sentença VM.ANO=2002 para filtrar as turmas de 2002. Observe que existe um agrupamento por aluno, permitindo que a média geral seja calculada através da função AVG(MEDIA). Para exemplificar, observe um retorno imaginário da view em questão:
|
ANO |
PERIODO |
CO_TURMA |
DESCRICAO |
CO_ALUNO |
NOME |
NO_DISCIPLINA |
MEDIA |
|
2003 |
1 |
2 |
turma 1/2003 |
1 |
Reinaldo |
Matemática |
8 |
|
2003 |
1 |
2 |
turma 1/2003 |
1 |
Reinaldo |
História |
6 |
Veja agora o efeito do agrupamento por aluno, num retorno imaginário para o comando SELECT:
|
DESCRICAO |
CO_TURMA |
CO_ALUNO |
NOME |
MEDIA |
|
turma 1/2003 |
2 |
1 |
Reinaldo |
7 |
Temos ainda que o aluno deve possuir média geral maior do que a média geral da turma de código 01. Lembrando que a média geral de uma turma é a média de todas as notas obtidas. O comando HAVING realiza esse filtro, fazendo uso também da view Media:
HAVING AVG(MEDIA)>
(SELECT SUM(MEDIA)/COUNT(*)
FROM VIEW_MEDIA VM
WHERE VM.CO_TURMA=01)
4ª Questão:
SELECT A.NOME,
(SELECT COUNT(*)
FROM FREQUENCIA F
WHERE F.CO_ALUNO=A.CO_ALUNO AND F.FREQUENCIA='S') PRESENCAS,
(SELECT COUNT(*)
FROM FREQUENCIA F
WHERE F.CO_ALUNO=A.CO_ALUNO AND F.FREQUENCIA='N') FALTAS
FROM ALUNO A
ORDER BY A.NOME
Explicação:
Essa questão é semelhante a número 2. Note que as subconsultas estão ligadas à SELECT principal através da condição WHERE F.CO_ALUNO=A.CO_ALUNO.
Uma observação: o relatório gerado por essa consulta inclui todos os alunos cadastrados, independente de eles terem realizado alguma matrícula. Se a aplicação possuir muitos alunos que nunca se matricularam, podemos modificar o código da seguinte forma:
SELECT DISTINCT A.NOME,
(SELECT COUNT(*)
FROM FREQUENCIA F
WHERE F.CO_ALUNO=A.CO_ALUNO AND F.FREQUENCIA='S') PRESENCAS,
(SELECT COUNT(*)
FROM FREQUENCIA F
WHERE F.CO_ALUNO=A.CO_ALUNO AND F.FREQUENCIA='N') FALTAS
FROM ALUNO A
JOIN ALUNO_TURMA ALT ON (A.CO_ALUNO = ALT.CO_ALUNO)
ORDER BY A.NOME
Esse novo join gera um custo, que deve ser analisado tendo em vista o cenário da aplicação.
5ª Questão:
SELECT VM.DESCRICAO,VM.CO_TURMA, VM.CO_ALUNO, VM.NOME, AVG(MEDIA) MG
FROM VIEW_MEDIA VM
WHERE VM.ANO=2002
GROUP BY VM.DESCRICAO, VM.CO_TURMA,VM.CO_ALUNO, VM.NOME
Explicação:
Essa consulta é apenas uma simplificação da questão 3. Observe o uso da view Media e o agrupamento por aluno, que permite o cálculo da média geral.
6ª Questão:
SELECT ALT.CO_ALUNO, A.NOME, 'SIM' NOVOALUNO
FROM ALUNO_TURMA ALT
JOIN ALUNO A ON (ALT.CO_ALUNO=A.CO_ALUNO)
JOIN TURMA T ON (ALT.CO_TURMA=T.CO_TURMA)
WHERE ALT.CO_ALUNO NOT IN
(SELECT CO_ALUNO
FROM ALUNO_TURMA AT
JOIN TURMA TU ON (AT.CO_TURMA=TU.CO_TURMA)
WHERE TU.ANO <> 2003 AND AT.CO_ALUNO = A.CO_ALUNO)
AND T.ANO='2003'
UNION
SELECT ALT.CO_ALUNO, A.NOME, 'NÃO' NOVOALUNO
FROM ALUNO_TURMA ALT
JOIN ALUNO A ON (ALT.CO_ALUNO=A.CO_ALUNO)
JOIN TURMA T ON (ALT.CO_TURMA=T.CO_TURMA)
WHERE ALT.CO_ALUNO IN
(SELECT CO_ALUNO FROM ALUNO_TURMA ALT
JOIN TURMA TU ON (ALT.CO_TURMA=TU.CO_TURMA)
WHERE TU.ANO <> 2003 AND ALT.CO_ALUNO = A.CO_ALUNO)
AND T.ANO='2003'
Explicação:
Como temos dois cenários distintos (aluno novo e aluno antigo) o recomendável é utilizar a cláusula UNION. No primeiro SELECT recuperamos os alunos novos, que são aqueles que não possuem matrícula anterior ao ano de 2003. Esse filtro é realizado através de NOT IN:
WHERE ALT.CO_ALUNO NOT IN
(SELECT CO_ALUNO
FROM ALUNO_TURMA AT
JOIN TURMA TU ON (AT.CO_TURMA=TU.CO_TURMA)
WHERE TU.ANO <> 2003 AND AT.CO_ALUNO = A.CO_ALUNO)
No segundo SELECT, os alunos antigos são representados por aqueles que efetuaram matricula em alguma turma anterior a 2003:
WHERE ALT.CO_ALUNO IN
(SELECT CO_ALUNO FROM ALUNO_TURMA ALT
JOIN TURMA TU ON (ALT.CO_TURMA=TU.CO_TURMA)
WHERE TU.ANO <> 2003 AND ALT.CO_ALUNO = A.CO_ALUNO)
7ª Questão:
SELECT A.NOME
FROM ALUNO A
WHERE NOT EXISTS (SELECT CO_ALUNO FROM ALUNO_TURMA WHERE ALUNO_TURMA.CO_ALUNO=A.CO_ALUNO)
Explicação:
A cláusula NOT EXISTS garante que apenas os alunos que não possuem registro equivalente na subconsulta serão exibidos. O filtro na subconsulta é importante para garantir performance, pois retorna apenas as ocorrências do aluno selecionado na consulta principal:
WHERE ALUNO_TURMA.CO_ALUNO=A.CO_ALUNO.
8ª Questão:
SELECT AL.NOME, AL.DT_NASCIMENTO
FROM ALUNO_TURMA ALT
JOIN TURMA T ON (ALT.CO_TURMA=T.CO_TURMA)
JOIN ALUNO AL ON (ALT.CO_ALUNO=AL.CO_ALUNO)
WHERE T.ANO='2003' AND AL.CO_ESTADOCIVIL='S'
UNION
SELECT VM.NOME , AL.DT_NASCIMENTO
FROM VIEW_MEDIA VM
JOIN ALUNO AL ON (VM.CO_ALUNO=AL.CO_ALUNO )
WHERE ANO='2002' AND PERIODO=2 AND AL.CO_ESTADOCIVIL='S' AND
NO_DISCIPLINA='BIOLOGIA' AND MEDIA >8
Explicação:
A cláusula UNION foi utilizada para mesclar as informações de alunos inseridos em dois contextos diferentes. Mesmo que tenhamos algum aluno presente nos dois casos, ele aparecerá apenas uma vez na listagem, visto que UNION elimina as repetições. Uma dica: se você precisar incluir as repetições, utilize UNION ALL.
9ª Questão:
SELECT A.NOME
FROM ALUNO_TURMA ALT
JOIN ALUNO A ON (ALT.CO_ALUNO = A.CO_ALUNO)
JOIN TURMA T ON (ALT.CO_TURMA = T.CO_TURMA)
WHERE A.CO_ESTADOCIVIL='C' AND
T.ANO='2003' AND ALT.DT_CANCELAMENTO IS NULL
AND EXISTS
( SELECT F.CO_ALUNO
FROM FREQUENCIA F
JOIN TURMA T ON (F.CO_TURMA=T.CO_TURMA)
WHERE F.FREQUENCIA='S' AND
F.CO_ALUNO=A.CO_ALUNO AND
T.ANO='2002'
GROUP BY T.CO_TURMA, F.CO_ALUNO
HAVING COUNT(*)>=8)
Explicação:
A sentença A.CO_ESTADOCIVIL='C' AND T.ANO='2003' AND ALT.DT_CANCELAMENTO IS NULL recupera os alunos casados, que estudaram em alguma turma de 2003 e não cancelaram. Já HAVING COUNT(*)>=8 verifica se o aluno obteve pelo menos oito presenças em alguma turma de 2002.
10ª Questão:
SELECT VM.ANO,VM.CO_TURMA,
SUM(MEDIA)/COUNT(*) MEDIATURMA
FROM VIEW_MEDIA VM
WHERE VM.ANO < '2003'
GROUP BY VM.ANO,VM.CO_TURMA
ORDER BY VM.ANO
Explicação:
A view da questão 03 calcula a média do aluno para cada disciplina. Agrupando o resultado da view pelo campo co_turma, podemos obter facilmente a média geral de cada turma.
Conclusão
Este artigo tem como objetivo realizar uma avaliação sobre SQL. No simulado, foram vistos aspectos triviais e não triviais da linguagem. Uma boa maneira de compreender as soluções é executá-las – para isso, o script completo do banco de dados, construído no SQL Server, está disponível para download no site deste artigo.
Continuo aberto a comentários, dúvidas ou sugestões sobre o simulado. Se você conhece uma maneira mais eficaz de resolver alguma questão, entre em contato para que possamos compartilhar a técnica com os demais leitores. Agradeço a todos que participaram e desejo bons estudos!
[BOX] Comentários
Recebi inúmeros emails, contendo respostas, elogios e sugestões em relação ao artigo. No entanto, ninguém acertou todas as questões. Vejamos alguns comentários sobre as mensagens recebidas:
Elogios:
- Aplicação do simulado como exercício prático na disciplina de banco de dados em algumas universidades, incentivando o aprendizado dos alunos;
- Possibilidade do leitor avaliar seus conhecimentos sobre SQL.
Críticas:
- Não disponibilização de um script para criação do banco de dados, bem como para a inserção de alguns dados de teste.
Resposta do autor: a idéia foi proposital, para forçar o leitor a se abstrair ao máximo.
Respostas:
Curiosamente, alguns erros se repetiram em diversas respostas:
- O campo dt_matricula da tabela aluno_turma foi utilizado para filtrar o ano da turma (vide questão 1).
- Na questão 03 alguns leitores, ao invés de usarem o campo período para a ordenação do resultado, usaram os campos dt_inicial e dt_final.
- Foi pedido que a view da questão 3 retornasse o campo matrícula do aluno. Algumas respostas utilizaram o campo dt_matricula, ao invés do campo co_aluno – que foi especificado como matrícula no dicionário de dados.
- A questão 08 pede uma listagem essencialmente de alunos solteiros, em todas as situações. Algumas respostas realizaram o filtro de estado civil apenas sobre os alunos que realizaram matrícula em alguma turma de 2003.
Algumas respostas interessantes:
- Na questão 01 realizamos o cálculo da média no SELECT principal. A resposta abaixo calcula a média dentro de um outro SELECT:
select TUR.ANO,
TUR.PERIODO,
TUR.DESCRICAO as DESCRICAOTURMA,
ALU.NOME,
DIS.NO_DISCIPLINA,
(select sum(NT_AVALIACAO) / TUR.NUM_PROVAS
from AVALIACAO
where CO_ALUNO = ALU.CO_ALUNO
and CO_DISCIPLINA = DIS.CO_DISCIPLINA
and CO_TURMA = TUR.CO_TURMA) as MEDIAFINAL
from AVALIACAO AVA
left join ALUNO ALU
on AVA.CO_ALUNO = ALU.CO_ALUNO
left join DISCIPLINA DIS
on AVA.CO_DISCIPLINA = DIS.CO_DISCIPLINA
left join TURMA TUR
on AVA.CO_TURMA = TUR.CO_TURMA
where TUR.ANO = 2002
order by TUR.ANO, TUR.PERIODO, ALU.NOME
No entanto, a consulta apresenta falhas. Ela não verifica se o aluno foi ou não cancelado, exigência da questão. Além disso, o comando traz resultado repetidos.
- Na questão 06 foi pedido um campo boolean informando se o aluno era novo ou não. Para resolver, usamos o operador UNION. Observe a solução de um leitor:
Select distinct ATU.CO_ALUNO,
ALU.NOME,
case
when exists(select CO_ALUNO
from ALUNO_TURMA
where CO_ALUNO = ATU.CO_ALUNO
and CO_ALUNO not in (select CO_ALUNO
from ALUNO_TURMA
where year(DT_MATRICULA) < 2003)) then
'Sim'
else
'Não'
end as ALUNONOVO
from ALUNO_TURMA ATU
left join ALUNO ALU
on ATU.CO_ALUNO = ALU.CO_ALUNO
where year(DT_MATRICULA) = 2003
Um problema nesta resposta é o filtro para as turmas de 2003 – como visto anteriormente, o campo dt_matricula não deve ser utilizado para esse fim.
- Na questão 09, um leitor apresentou uma alternativa que utiliza duas vezes o exists. Veja abaixo:
Select a.nome
From Aluno a
Where a.co_estadocivil = 'C' and
exists
(Select 1
From Aluno_turma at, Turma t
Where t.ano = 2003 and t.co_turma = at.co_turma and
at.co_aluno = a.co_aluno and
at.dt_cancelamento is null)
and
exists
(Select co_turma,count(*) as Presenca
From Frequencia
Where co_aluno = a.co_aluno and frequencia = 'S'
Group by co_turma
Having count(*) >= 8)
