Artigo SQL Magazine 5 - SIMULADO SQL – Respostas e comentários

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 idimos 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

ATENÇÃO! A exibição deste artigo foi interrompida.

  #Este é um post fechado

Este post está disponível para assinantes da SQL Magazine DIGITAL ou para quem possui Créditos DevMedia. Clique aqui para saber mais sobre como abrir este post!