1,105,578 Community Members

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

Member Avatar
dirnthelord
Newbie Poster
18 posts since Jan 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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!

Member Avatar
urtrivedi
Posting Virtuoso
1,900 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 409 [?]
Skill Endorsements: 27 [?]
 
0
 
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
Member Avatar
dirnthelord
Newbie Poster
18 posts since Jan 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 
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...

Member Avatar
drjohn
Posting Pro
526 posts since Mar 2010
Reputation Points: 50 [?]
Q&As Helped to Solve: 106 [?]
Skill Endorsements: 4 [?]
 
0
 

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.

Member Avatar
dirnthelord
Newbie Poster
18 posts since Jan 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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!

Member Avatar
savalan85
Newbie Poster
1 post since Jan 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 
Member Avatar
datakeyword
Newbie Poster
3 posts since Feb 2012
Reputation Points: -4 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: