trying to develop a system to assign grades to students. The system reads marks from my database, and assigns a grade after getting average of scores.
Used case statements, keep throwing errors. So i tried if statements but its giving me issues. Someone know a better way to do it?
delimiter //
create function assign_grade(v_idnumber int)
returns varchar(50) deterministic
begin
declare v_marks1 varchar(50);
declare v_marks2 varchar(20);
declare phyz varchar(50);
declare Chem varchar(50);
declare grade varchar(50);
set phyz = (select Physics from marks where Sid=v_idnumber);
set Chem=(select Chemistry from marks where Sid=v_idnumber);
if(phyz>Chem)
begin
set v_marks1=(select(English+Kisw+Math+Physics+Bio+Business+Geog+Cre)/8 from marks where Sid=v_idnumber);
if (v_marks1>= 80 && v_marks1<=100) then
set grade ='A';
else if (v_marks1>=60&& v_marks1<=79)
set grade='B';
else if (v_marks1>=40&& v_marks1<=59)
set grade ='C';
else if (v_marks1>=30&& v_marks1<=49)
set grade ='D';
else if (v_marks1>=0&& v_marks1<=29)
set grade ='E';
else
set grade = 'Failed Miserably';
end if
end if
end if
end if
end if
return grade;
end;
if(Chem>phyz)
begin
set v_marks2=(select(English+Kisw+Math+Chemistry+Bio+Business+Geog+Cre)/8 from marks where Sid=v_idnumber);
if (v_marks2>= 80 && v_marks2<=100) then
set grade ='A';
else if (v_marks2>=60&& v_marks2<=79)
set grade='B';
else if (v_marks2=40&& v_marks2<=59)
set grade ='C';
else if (v_marks2>=30&& v_marks2<=49)
set grade ='D';
else if (v_marks2>=0&& v_marks2<=29)
set grade ='E';
else
set grade = 'Failed Miserably';
end if
end if
end if
end if
end if
end;
return grade;
//
