Query SQL display score-CodePudding

I need some help. I want to display All scores from students, If the score has been input to db, it will display the score, if not it will display null value.

Database structure like this :

students

nim
name
class_year (like : 2020, 2021, etc)

krs

code_krs
nim
academic_year (like : 20201, 20202, etc)
total_sks

krs_detail

id
code_krs
course_code
sks

courses

course_code
course_name
sks

scores

academic_year (like : 20201, 20202, etc)
course_code
nim
score

I try query like this : `

SELECT students.nim, students.name, krs_detail.course_code, courses.course_name, courses.sks, scores.score
FROM students
JOIN scores ON scores.nim = students.nim
RIGHT JOIN courses ON courses.course_code = scores.course
JOIN krs_detail ON krs_detail.course_code = courses.course_code
WHERE krs_detail.code_krs = '1411503293-20222';

Data displayed is correct, with nim & name student :

The score report needs to display

All the courses taken by a student, and
All the scores received by a student
If a score is not yet available, display the course code, course name and sks.

If the above is correct, the granularity of the dataset is (nim, academic_year, course_code)

We can

First, generate the result of all the courses taken by students
Second, left outer join from the above to scores to retrieve score.

select s.nim,
       s.name,
       k.academic_year,
       d.course_code,
       c.course_name,
       c.sks,
       sc.score
  from students s
  join krs k
    on s.nim = k.nim
  join krs_detail d
    on k.code_krs = d.code_krs
  join courses c
    on d.course_code = c.course_code
  left
  join scores sc
    on s.nim = sc.nim
   and k.academic_year = sc.academic_year
   and d.course_code = sc.course_code
 order by s.nim, d.course_code;

An example of result:

nim|name|academic_year|course_code|course_name                |sks|score|
--- ---- ------------- ----------- --------------------------- --- ----- 
101|adam|         2022|        401|An Introduction to Computer|  3|   85|
101|adam|         2022|        402|Programming Language       |  3|     |
201|eve |         2022|        401|An Introduction to Computer|  3|   90|
201|eve |         2022|        402|Programming Language       |  3|     |