artigo SQL Magazine 04 - SIMULADO SQL

Esse artigo apresenta um simulado sobre SQL e é uma oportunidade para você avaliar seu domínio sobre a linguagem. O teste é composto por 10 questões e as respostas serão publicadas e comentadas na próxima edição da SQL Magazine. As perguntas foram baseadas em um banco de dados utilizado por uma escola de ensino médio.

Abrimos também uma proposta aos leitores: os dez primeiros que enviarem as respostas corretas ganharão um brinde surpresa e terão seus nomes publicados na próxima edição da revista.

O sistema

Na escola existe um controle de alunos, disciplinas, turmas, avaliações e matrículas realizadas. Uma turma possui um ou diversos alunos matriculados. Cada turma possui um número de provas específico, que será igual para todas as disciplinas vinculadas.

O número de provas que o aluno vai realizar para cada disciplina durante um período depende da turma que ele está matriculado e esse número será igual para todas as disciplinas na turma em questão. Um professor pode ensinar várias disciplinas em várias turmas.

Existe um controle das avaliações realizadas pelos alunos. Para cada prova aplicada, deve-se guardar a nota, a data e a disciplina referente. Se o aluno não realizar a prova, a mesma será cadastrada com nota 0. Sendo assim, os alunos sempre possuem todas as provas cadastradas no sistema.

Cada disciplina cursada pelo aluno possui um controle de freqüências.

O modelo

A figura 1 mostra as entidades que nos interessam para o simulado. Foi utilizado o SQL Server para construção do diagrama. Observe que o ícone de chave representa 1 e o símbolo de infinito representa N.

Vamos a descrição das tabelas:

• CURSO: Armazena os cursos da escola. Relaciona-se com a tabela Turmas, onde uma turma faz parte de um curso. Por exemplo, uma turma pode ser do curso de informática ou enfermagem.
• TURMA: Armazena as turmas do ano letivo. Uma turma contém vários alunos e várias disciplinas.
• ALUNO: Armazena as informações de cada aluno.
• ALUNO_TURMA: Representa a matrícula de um aluno em uma turma. Cada registro contém diversas informações relacionadas, como número de frequências por disciplina e notas de provas.
• PROFESSOR: Identifica os professores.
• DISCIPLINA: Armazena as disciplinas da escola, como português, matemática e história. Observe que uma mesma disciplina pode ser ministrada em mais de um curso.
• FREQUENCIA: Armazena as frequências do aluno para cada disciplina na turma em que ele está matriculado.
• PROVA: Descrição das provas realizadas. Exemplo: teste 1, prova final.
• AVALIACAO: Armazena as notas do aluno para cada disciplina na turma em que ele está matriculado.
• PROF_TURM_DISC: Indica o professor que está lecionando uma determinada disciplina em uma turma. Um professor pode lecionar mais de uma disciplina na mesma escola.

Observações sobre o modelo

• O campo período, da tabela turma, possui a seguinte convenção: 1 para o primeiro semestre e 2 para o segundo;
• O campo frequência, da tabela freqüência, assume S para presença ou N para falta;
• todo campo sg_sexo assume M para masculino ou F para feminino;
• O campo co_aluno, da tabela aluno, representa o número da matrícula;
• O campo co_estadocivil, da tabela aluno, assume S para solteiro ou C para casado;
• Um valor diferente de NULL para o campo dt_cancelamento significa que o aluno cancelou a turma.

Questões

1. Para cada turma de 2002, obter uma listagem com os alunos que não cancelaram, ordenada por ano, período e nome do aluno, contendo os seguintes campos: ano, período e descrição da turma; nome do aluno, nome da disciplina e média final da disciplina. Para a média, some as avaliações de cada disciplina e divida pelo número de provas que a turma deve realizar.
2. Para cada turma de 2002, obtenha uma listagem com o período da turma, código da turma, o número de alunos que iniciaram e o número de alunos que cancelaram. A consulta deve estar ordenada por período.
3. Obtenha uma listagem que retorne a descrição e o código da turma, matrícula, nome e média geral dos alunos que estudaram no primeiro período de 2002 e cuja média seja maior que a média geral da turma de código 1. A média geral do aluno é a média das disciplinas cursadas por ele em determinada turma. A média geral da turma é a média das notas de todos os alunos. Resolva essa query criando uma view que retorne os campos ano, período, código e descrição da turma, matrícula e nome do aluno, bem como nome e média de cada disciplina.
4. Para cada aluno, obtenha uma listagem que mostre seu nome, a quantidade total de presenças e a quantidade total de faltas ocorridas em todas as turmas cursadas. A consulta deve ser ordenada pelo nome do aluno.
5. Para cada turma de 2002 calcule a média geral de cada aluno. Sugestão: utilize a view da questão 3. O resultado deve retornar a descrição e o código da turma, a matrícula, o nome e a média geral do aluno.
6. Para os alunos que realizaram matrícula nas turmas de 2003, obtenha uma listagem composta pelo código, nome e um campo do tipo boolean que informe se esse aluno é novo ou não. Um aluno novo é aquele que apenas realizou matrícula em alguma turma de 2003.
7. Listar os alunos que não realizaram matrícula em nenhuma turma.
8. Selecionar o nome e a data de nascimento dos alunos solteiros que se matricularam em alguma turma de 2003 e/ou obtiveram média maior que 8 em biologia em alguma turma do segundo semestre de 2002. Caso algum aluno se enquadre nessas duas condições, liste-o apenas uma vez. Sugestão: utilizar a view da questão 3.
9. Selecionar o nome dos alunos casados que matricularam e não cancelaram alguma turma em 2003 e tenham tido pelo menos 8 presenças em alguma turma do ano de 2002.
10. Mostrar o ano, o código e a média geral de todas as turmas anteriores a 2003. Ordenar a listagem por ano. Sugestão: utilizar a view da questão 3.

Conclusão

As consultas devem seguir o padrão SQL-92. Como dica, tente compreender o modelo de dados, pois ele é base das perguntas. Aguardo seu email com as respostas ou mesmo com comentários a respeito do artigo. Até a próxima edição!