stored procedures - sql: select average grade for all subjects for one student and print an according message -
i have table student following columns:
id subject grade date ---|-------|------|-----------| 1 | | 1 | 01-mar-10 | 1 | | 5 | 05-apr-10 | 1 | b | 3 | 01-jun-10 | 2 | | 1 | 01-mar-10 | 2 | c | 3 | 01-apr-10 | in procedure pass student id (p_id), , need select average grade of subjects student, if student has 2 grades same subject, latest grade counts, this:
for p_id=1:
id avggrade ---|--------| 1 | 4 | this done now
what need now, save information student's success in string , print dbms_output. highest grade 1, lowest 5. if student had single 5 (as last grade subject), no matter average has, string should contain "the student failed", if student had no 5 last grade, , had average of <= 1,5 , string should contain "perfect average", otherwise "the student passed"
the code far is:
create or replace procedure avg_grade ( p_id in number ) cursor c1 select a.id, avg(a.grade) avg_grade student inner join ( select id, subject, max(date) max_date student id=p_id group id, subject )b on a.id=b.id , a.subject=b.subject , a.date=b.max_date a.id=p_id group id; cursor c2 select grade student id=p_id; declare @out varchar(50) set @out=null if c2.grade in(5) begin set @out='student failed' end else if c2.grade not in(5) , c1.avg_grade in (between 1 , 1,5) begin set @out='student has perfect average' end else begin set @out='student passed' end dbms_output.put_line(@out); please help
select a.id, avg(a.grade) avg_grade tablename inner join ( select id, subject, max(date) max_date tablename id = 1 -- <<== change p_id group id, subject ) b on a.id = b.id , a.subject = b.subject , a.date = b.max_date a.id = 1 -- <<== change p_id group a.id 
Comments
Post a Comment