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:

Veja agora o efeito do agrupamento por aluno, num retorno imaginário para o comando SELECT:

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)