oracle - PL/SQL cursor to find all classes that have more than two students whose test scores are above 90% of the average scores of their classrooms? -

i having problems working right value number of students. need use cursors , ouput of program should this... looking led right direction go here little confused. thanks!

class name    number of students ================================= biology                        6 calc                           3 german                         5 

here current code:

declare         cursor c_1             select c.class_name, avg(s.grade)               class c, student s                  c.class_id = s.class_id             group class_name                 order class_name;          grade_rec c_1%rowtype;  begin       dbms_output.put_line(('class name') || '    ' || ('number of students'));       dbms_output.put_line('--------------------------------------');       grade_rec in c_1 loop        dbms_output.put_line(rpad(grade_rec.class_name, 15) ||          '             ' || lpad(grade_rec.avg_grade, 10));     end loop; end; 

this bit tricky. can use having compare against group result.

your select gives average class. need count number of students of class have higher score average.

    select class_name, count(*) student s2,          (select c.class_name, class_id, avg(s.grade) avgg           class c, student s              c.class_id = s.class_id         group class_name, class_id) avg       avg.class_id = s2.class_id         , s2.grade > avg.avgg       group class_name     having count(*) > 2 

i have not tested creates subquery average class. join students again higher average grade. use having clause compare count.