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.