How to complement this sql query?

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

student ( codaluno , nombrealuno, dt_ingresso, codcurso)
course ( coding , coursename)
( codaluno , codturma , average, result)
class ( codturma , periodoletive, room, codprof, coddisc)
discipline ( coddisc , title, credits)
teacher ( codprof , teacher_name, titling)

I want to perform the following sql query:

  

What is the name, the average grade (considering the averages all the subjects in which you enrolled) and the total credits obtained by each student? Please note that a student only earns credits in a course if they pass this course.

I’ve already gotten the following sql expression:

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

Where students’ names and their media are listed, but you still need to get the total credits and still have the condition “you only get credits in a subject if you pass this course”. How to complement my sql query to get the remaining requirements above?

Relationship as request in comments:

  

Note: These arrows indicate foreign keys that have been imported.

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

• GROUP BY functions
• IF Syntax