sql: select average grade for all subjects for one student and print an according message

Question

I have a table student with the following columns:

id  subject  grade   date
---|-------|------|-----------|
1  | A     |  1   | 01-MAR-10 | 
1  | A     |  5   | 05-APR-10 | 
1  | B     |  3   | 01-JUN-10 | 
2  | A     |  1   | 01-MAR-10 | 
2  | C     |  3   | 01-APR-10 |

In my Procedure I pass the student id (p_id), and I need to select an average grade of all subjects for that student, if a student has 2 grades for the same subject, only the latest grade counts, like this:

for p_id=1:

id  avgGrade
---|--------|
1  | 4      |

this is done by now

what I need to do now, to save the information about the student's success in a string and print it with DBMS_OUTPUT. the highest grade is 1, the lowest is 5. if a student had a single 5 (as a last grade for a subject), no matter what average he has, the string should contain "the student failed", if the student had no 5 as the last grade, and had an average of <= 1,5 , the string should contain "perfect average", otherwise just "the student passed"

the code so far is:

    CREATE OR REPLACE Procedure avg_grade
   ( p_id IN number )

IS

   cursor c1 is
   select a.id, avg(a.grade) avg_grade
   from student a
   inner join 
   (
    select id, subject, max(date) max_date
    from student
    where id=p_id
    group by id, subject
   )b ON a.id=b.id and
      a.subject=b.subject and
      a.date=b.max_date
where a.id=p_id
group by id;

cursor c2 is
select grade
from student
where id=p_id;

DECLARE @out as varchar(50)
SET @out=NULL

IF c2.grade IN(5)
BEGIN
SET @out='student failed'
END

ELSE IF c2.grade NOT IN(5) AND c1.avg_grade IN (BETWEEN 1 AND 1,5)
BEGIN
SET @out='student has a perfect average'
END

ELSE 
BEGIN
SET @out='student passed'
END

DBMS_OUTPUT.PUT_LINE(@out);

please help

score 1 · Accepted Answer · answered May 04 '13 at 16:59

1

SELECT  a.ID, AVG(a.grade) AVG_GRADE
FROM    TableName a
        INNER JOIN
        (
            SELECT  ID, Subject, MAX(date) max_date
            FROM    TableName
            WHERE   ID = 1            -- <<== change it to p_id
            GROUP   BY ID, Subject
        ) b ON  a.ID = b.ID AND
                a.Subject = b.Subject AND
                a.date = b.max_date
WHERE   a.ID = 1                      -- <<==  change it to p_id
GROUP   BY a.ID

SQLFiddle Demo

answered May 04 '13 at 16:59

John Woo

258,903
69
498
492

thanks a lot! how can I now access this only value in the avg_grade column? (I need to declare a string, and overwrite its content according to the average grade). If I use select, it will return a colum... – IAM May 04 '13 at 17:15
what are you trying to do? what RDBMS are you using? – John Woo May 04 '13 at 17:18
I use Oracle Database 11g Enterprise Edition. what I need to do now, to save the information about the student's success in a string. the highest grade is 1, the lowest is 5. if a student had a single 5 (as a last grade for a subject), no matter what average he has, the string should contain "the student failed", if the student had no 5 as the last grade, and had an average of <= 1,5 , the string should contain "perfect average", otherwise just "the student passed" – IAM May 04 '13 at 17:24

sql: select average grade for all subjects for one student and print an according message

1 Answers1