Doubt in exercise query on sql

Question

Doubt in exercise query on sql

Navigation

#1 by (1 votes)

1

I have the following relationships:

The statement of the exercise is:

What is the average grade of teachers assigned by course discipline Geography in the first semester of 2013. Present the name of the teacher, discipline and the average. Sort by teacher's name.

I made the following query:

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: the way my query is, it is grouping by name of teachers, and the exercise only wants you to group by discipline of the course, but how will I be able to return the names of these teachers without grouping? And instead of using INNER JOIN, are those joins with WHERE going to work?

sql

asked by anonymous 20.10.2017 / 17:10

1 answer

Object Keys from a JSON is saving the last value Height of the total div proportional to the content

score 1 · Accepted Answer

Try this:

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 have not tested because it does not exist here!)

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 the understanding is because of the filters and "grouping" because you do not see the lines.