Member Avatar for 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!

  • 5 Contributors
  • 6 Replies
  • 3K Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by datakeyword
Member Avatar for 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
Member Avatar for 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...

Edited by dirnthelord because: n/a
Member Avatar for 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.

Member Avatar for 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!

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.