Este é um post disponível para assinantes MVPou para quem possui Créditos DevMedia. Clique aqui para saber mais!
Artigo SQL Magazine 5 - SIMULADO SQL – Respostas e comentários
Artigo da Revista SQL Magazine edição 05.
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
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:
"
Este é um post disponível para assinantes MVPou para quem possui Créditos DevMedia. Clique aqui para saber mais!
Reinaldo Viana Alvares
Reinaldo Viana Alvares (reinaldoviana@gmail.com) é Tecnólogo em Processamento de Dados pela UNAMA – Universidade da Amazônia ( www.unama.br ), Especialista em Análise de Sistemas pela UFPA – Universidade Federal do Pará ( www.ufpa.br ), Mestre em Computação pela UFF – Universidade Federal Flum...
