Ao ver as soluções propostas pelo instrutor, a resposta da questão 4 não me parece responder o % de alunos aprovados por alguns pontos:
- Está contando o total de alunos do banco, sem considerar que tem alunos que não estão em nenhuma turma.
- Está considerando como alunos aprovados todas as notas maiores do que 7, porém existem alunos repetidos que tiveram essa nota em mais de uma disciplina
- Por fim, para o cálculo da % está considerando 50 que é o total de notas registradas no banco.
Como eu fiz: já que a questão pede a porcentagem dos alunos aprovados, calculei a média geral de cada aluno e contei como aprovados os alunos com média maior que 7, considerei para o total de alunos apenas aqueles que estão matriculados nas turmas.
Seguem todos os meus códigos:
-- 1. Retorne todas as disciplinas
SELECT nome_disciplina from Disciplinas;
-- 2. Retorne os alunos que estão aprovados na disciplina de matemática (considerando nota mínima de 7 pontos para aprovação)
select a.Nome_Aluno, d.Nome_Disciplina, n.Nota
from notas as n
JOIN alunos as a on a.ID_Aluno = n.ID_Aluno
join Disciplinas as d on d.ID_Disciplina = n.ID_Disciplina
where d.Nome_Disciplina = 'Matemática' and n.Nota >= 7
order by n.Nota
;
-- 3. Identificar o total de disciplinas por turma
SELECT t.Nome_Turma, count(td.ID_Disciplina) as Disciplinas_Por_Turma
from Turma_Disciplinas as td
join Turmas as t on t.ID_Turma = td.ID_Turma
GROUP by t.Nome_Turma
;
-- 4. Porcentagem dos alunos que estão aprovados (considerando média de todas as notas > 7)
SELECT
count(case when Média_Notas > 7 then 1 end) as Qtd_Alunos_Aprovados,
COUNT(Nome_Aluno) as Qtd_Total_Alunos,
ROUND (AVG(case when Média_Notas > 7 then 1.0 else 0 end)* 100.0, 2) || '%' as Porcentagem_Alunos_Aprovados
from
(select a.Nome_Aluno, round(AVG(n.Nota), 2) as Média_Notas
from notas as n
JOIN alunos as a on a.ID_Aluno = n.ID_Aluno
join Disciplinas as d on d.ID_Disciplina = n.ID_Disciplina
GROUP by a.Nome_Aluno)
;
-- 5. Porcentagem dos alunos que estão aprovados por disciplina
SELECT
Nome_Disciplina,
COUNT(CASE WHEN Média_Notas > 7 THEN 1 END) AS Qtd_Alunos_Aprovados,
COUNT(Nome_Aluno) AS Qtd_Total_Alunos,
ROUND(AVG(CASE WHEN Média_Notas > 7 THEN 1.0 ELSE 0 END) * 100.0, 2) || '%' AS Porcentagem_Alunos_Aprovados
FROM
(SELECT d.Nome_Disciplina, a.Nome_Aluno, AVG(n.Nota) AS Média_Notas
FROM notas n
JOIN alunos a ON a.ID_Aluno = n.ID_Aluno
JOIN disciplinas d ON d.ID_Disciplina = n.ID_Disciplina
GROUP BY d.Nome_Disciplina, a.Nome_Aluno)
GROUP BY Nome_Disciplina;
