pl/sql cursor that calculates the average and update the column and ordering the records at the same time by that sum calculated average

Question

I have the following tables as it showed in the pictures.

I want to calculate the average of grades for each student {Avg = Sum(note * coefficient) / Sum(coefficients)} and then update the range's column in the ETUDIANT table depending on the average and skipping the range for the equal averages using cursors in PL/SQL! like in the pic below

How is it possible to approache this because i'm kinda new to pl/sql in general and thank you!

I wrote a select statement that selects the notes and coeff that's all what i could figure

select NOTE,COEFFICIENT from notes left join module on notes.CODE_M = module.CODE_M order by notes.NOTE desc;

i'm expecting at least a way to approach this problem and thanks

Answer 1

Don't use PL/SQL or cursors, just use SQL and a MERGE statement and aggregate the notes and coefficients to get the weighted average for each student and then use the analytic RANK function to calculate the range:

MERGE INTO etudiant 
USING (
  SELECT n.code_e,
         SUM(n.note * m.coefficient) / SUM(m.coefficient) AS weighted_note,
         RANK() OVER (ORDER BY SUM(n.note * m.coefficient) / SUM(m.coefficient)) AS rnk
  FROM   notes n
         INNER JOIN module m
         ON (m.code_m = n.mode_m)
) c
ON (e.code_e = c.code_e)
WHEN MATCHED THEN
  UPDATE
  SET moyenne = c.weighted_note,
      rang    = c.rnk;

---

If you want blanks then do that when you display the data:

SELECT NULLIF(rang, LAG(rang) OVER (ORDER BY moyenne DESC, code_e)) AS rang,
       code_e,
       nom,
       prenom,
       moyenne,
       spcialite
FROM   etudiant;