Start New Discussion within our Databases Community

Hey guys...I have a MySql table like this. and Grade column is a derived attribute. and it is based on Marks. u know the basic thing.

create table Enrolled(
    student_id char(14),
    subject_code varchar(10),
    marks int,
    grade char(2)
);

I want to fill the Grade column based on each student's marks. I don't want to fill it by my self. Can I use a function or a procedure or something? How can I do this?

Tell me if i want to make my self more clear...ok..Thankx in advance!

update enrolled set grade= case when marks <35 then 'F'
                    when marks>=35 and marks <45 then 'D'
                    when marks>=45 and marks <55 then 'C'
                    when marks>=55 and marks <65 then 'B'
ELSE 'A' END
UPDATE enrolled
SET grade = CASE    WHEN marks <=24 THEN 'F'
                    WHEN marks>=25 AND marks <30 THEN 'D-'
                    WHEN marks>=30 AND marks <35 THEN 'D'
                    WHEN marks>=35 AND marks <40 THEN 'D+'
                    WHEN marks>=40 AND marks <45 THEN 'C-'
                    WHEN marks>=45 AND marks <55 THEN 'C'
                    WHEN marks>=55 AND marks <60 THEN 'C+'
                    WHEN marks>=60 AND marks <65 THEN 'B-'
                    WHEN marks>=65 AND marks <70 THEN 'B'
                    WHEN marks>=70 AND marks <75 THEN 'B+'
                    WHEN marks>=75 AND marks <80 THEN 'A-'
                    ELSE 'A' END

It works well when I execute your code segment after each time I insert some values to other columns. What I want is to make it happen automatically.

when I enter values to other columns, it should automatically fill grade column with relevant grade. any Ideas How to get this done? any procedures or functions that i could use?

Thanx...

Don't do this.

Calculate the value after you extract it from the database and before you display it to the user. There is no need to store derived values. As you can see , it is causing you problems already.

Whenever anyone changes a mark, you will have this problem using your approach.

What is some course uses a different grading scheme? If you calculate the grade after extracting it from the table, you can use an if or case select to decide which grading scheme to apply.

Don't do this.

Calculate the value after you extract it from the database and before you display it to the user. There is no need to store derived values. As you can see , it is causing you problems already.

Whenever anyone changes a mark, you will have this problem using your approach.

What is some course uses a different grading scheme? If you calculate the grade after extracting it from the table, you can use an if or case select to decide which grading scheme to apply.

I knew it is stupid. Thank you so much for giving me the easy way. U solved My problem!

drjohn is right, and savalan85 is right too.
If you have a large numbers of records(e.g. more than 500,000), or query it very frequently, go with savalan85; otherwise you should go with drjohn.

This article has been dead for over six months. Start a new discussion instead.