How to complement this sql query?

Asked

Viewed 1,006 times

3

I have the following tables below, where schemes(columns) are described in parentheses and the primary key appears in bold:

pupil(codpupil, named, dt_ticket, codcurso)
course(codcurso, naming)
registration(codpupil, codturma, media, result)
gang(codturma, periodolent, room, codprof, coddisc)
discipline(coddisc, title, credits)
teacher(codprof, teacher name, degree)

I wish to perform the following sql query:

What was the name, the average grade (considering the averages of all the disciplines in which you enrolled) and the total credit obtained by each student? Remember that a student only gets credits in a discipline if he or she passes this class.

I already got the following sql expression:

SELECT nomealuno, sum(media)/count(codaluno) FROM aluno 
NATURAL JOIN matricula GROUP BY aluno.codaluno;

Where are listed the names of the students and their respective media, but still have to obtain the total credits and still perform the condition "only get credits in a discipline if you pass this discipline". How to complement my sql query to get the remaining requirements above?

Relationship as requested in the comments:

Relacionamento

Note: These arrows indicate the foreign keys that were imported.

mysql sql query

  • @gmsantos sim matrícula(codaluno, codturma, media, resultado) está na coluna resultado... que é o resultado da matrícula se o estudante foi aprovado ou não.

  • What is the type of data in this result column? It says if the student has passed the course, is it? Is it a boolean? 0/1?

  • 1

    @bfavaretto yes

  • 1

    Try : SELECT aluno.nomealuno, sum(matricula.media)/count(aluno.codaluno), sum(disciplina.creditos) FROM aluno, matricula, disciplina, turma WHERE aluno.codaluno = matricula.codaluno AND matricula.codturma = turma.codturma AND turma.coddisc = disciplina.coddisc GROUP BY aluno.codaluno; .

  • 2

    And I suggest: SELECT a.nomealuno, AVG(m.media), SUM(IF(m.resultado=1, d.creditos, 0)) FROM aluno a 
LEFT JOIN matricula m ON a.codaluno = m.codaluno 
LEFT JOIN turma t ON m.codturma = t.codturma 
LEFT JOIN disciplina d ON t.coddisc = d.coddisc 
GROUP BY a.codaluno I have no bench to test

  • 2

    @Pedrorangel You can test with more fake data by editing the SQL Fiddle I created: http://sqlfiddle.com/#! 2/d8755/5

Show 1 more comment

1 answer

4


Solution

SELECT a.nomealuno, AVG(m.media), SUM(IF(m.resultado=1, d.creditos, 0)) 
FROM aluno a LEFT JOIN matricula m ON a.codaluno = m.codaluno 
LEFT JOIN turma t ON m.codturma = t.codturma 
LEFT JOIN disciplina d ON t.coddisc = d.coddisc 
GROUP BY a.codaluno

References

  • Just as a matter of curiosity what this on does?

  • 1

    is a condition for merging the data. It can be represented in the WHERE. I believe the same query could be represented like this: SELECT a.nomealuno, AVG(m.media), SUM(IF(m.resultado=1, d.creditos, 0)) 
FROM aluno a, matricula m, turma t, disciplina d 
WHERE a.codaluno = m.codaluno AND m.codturma = t.codturma AND t.coddisc = d.coddisc 
GROUP BY a.codaluno

  • 1

    vlw this select aew works tmb but does not show all students who do not have matricula thank you again.

  • 1

    Additional detail: when you do left Join, you are saying that "who commands" in the query is the table on the left side (left), with this, if I have no data on the right side, it returns null. If you want to reverse the priority, you make a right Join. If you want to make the data only come if there is data in all tables you do only JOIN (removing left and right).

  • 1

    is, I forgot a detail, it doesn’t show why when you do Join by Where, you are forcing a JOIN (without left or right Join). rs

  • That’s right, Thanks^^

  • As Keywords INNER and OUTER are expendable?

  • @Patrick see this link: http://answall.com/questions/6441/qual-%C3%A9-a-difference%C3%A7a-entre-Inner-Join-e-outer-Join

Show 3 more comments

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.