Derived Attribute...... Help! Help!

[dirnthelord]

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!

[urtrivedi]

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

[dirnthelord]

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...

[drjohn]

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.

[dirnthelord]

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!

[savalan85]

Its not stupid.
Use Triggers on the Derived attributes.
http://dev.mysql.com/doc/refman/5.0/en/triggers.html
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

[datakeyword]

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.