Doubt in sql exercise query

Question

I have the following relationships:

The statement of the financial year is:

The average grade given by teachers per course discipline Geography in the first half of 2013. Name teacher, discipline and media. Sort by the name of the teacher.

I made the following consultation:

SELECT Professor.nome, Disciplina.nome, AVG(Aula.nota) as Media
FROM Aluno, Disciplina, Professor, Disciplina, DisciplinaCurso, Curso
WHERE DisciplinaCurso.NumDisp = Curso.NumDisp AND 
      DisciplinaCurso.NumDisp = Disciplina.NumDisp AND
      Aula.NumDisp = Disciplina.NumDisp AND Aula.NumFunc = Professor.NumFunc 
      AND Curso.Nome = 'Geografia' AND Aula.Semestre = '1º Semestre de 2013' 
GROUP BY Professor.nome, Disciplina.nome 
ORDER BY Professor.nome;

My question is: as my query is, she is grouping by name of teachers, and the exercise just wants me to group by course discipline, but how will I be able to return the name of these teachers without grouping? And instead of using INNER JOIN, these junctions with WHERE will work?

Answer 1

Try this way:

SELECT PF.nome, DI.nome, AVG(AL.nota) as Media
FROM Aluno AL
LEFT OUTER JOIN Professor PF ON PF.NumFunc = AL.NumFunc
LEFT OUTER JOIN Disciplina DI ON DI.NumDisp = AL.NumDisp
LEFT OUTER JOIN DisciplinaCurso DC ON DC.NumDisp = DI.NumDisp
LEFT OUTER JOIN Curso CS ON CS.NumDisp = DC.NumDisp
WHERE CS.Nome = 'Geografia' AND AL.Semestre = '1º Semestre de 2013' 
GROUP BY DI.nome, PF.nome
ORDER BY DI.nome

(I didn’t test it because there’s no way here ! rs)

Explicando por linha:

-- Define os campos a serem exibidos, com cálculo da média do campo "nota"
SELECT PF.nome, DI.nome, AVG(AL.nota) as Media
-- Aqui é sua tabela de pesquisa (tabela LEFT, esquerda)
FROM Aluno AL
-- Esses JOINS trás TUDO da sua tabela da "esquerda" (Aluno), mais os registros da sua tabela da "direita" (ex.: primeiro LEFT é a tabela Professor) que TEM VÍNCULO (relação) com a tabela da "esquerda" (Alunos)
LEFT OUTER JOIN Professor PF ON PF.NumFunc = AL.NumFunc
LEFT OUTER JOIN Disciplina DI ON DI.NumDisp = AL.NumDisp
LEFT OUTER JOIN DisciplinaCurso DC ON DC.NumDisp = DI.NumDisp
LEFT OUTER JOIN Curso CS ON CS.NumDisp = DC.NumDisp
-- Aqui você faz seus filtros
WHERE CS.Nome = 'Geografia' AND AL.Semestre = '1º Semestre de 2013'
-- Aqui você está agrupando
GROUP BY DI.nome, PF.nome
ORDER BY DI.nome

Sometimes your confusion in understanding is because of the filters and "grouping", because you don’t see the lines.