Calculating Grades in SQL

Very simply, I need to find student grades using SQL. If, for example, I have开发者_如何学运维 following table that define grades

Marks (int)
Grade (Char)

and the data like this:

Marks | Grade

__90 | A+

__80 | A

__70 | A-

__60 | B

__50 | C

__40 | D

Okay, having said that, if I have a student that gained marks 73, how do I calculate her grade using above gradings in SQL.

Thank you so much...

You want the highest value below or equal to your value, substitue 73 for your value...

select top 1 Grade from TableName where Mark <= 73 order by Mark desc

Assuming your GradeCutoff table is created with something like:

CREATE TABLE GradeCutoff
  ( mark int
  , grade char(3)
  )

and you want to check @studentMark

SELECT grade
FROM GradeCutoff
WHERE mark =
   ( SELECT max(mark)
     FROM gradeCutoff
     WHERE @studentMark >= mark
   )
;

Note: you may also have to add a (0, 'E') row in your cutoff table.

I think you should define a UDF for this which takes student markes as parameter and returns the grade according to the table given.

Then you can get grade for any student from student table as -

select studentID, getGrade(studentMarks) from student

Here getGrade(studentMarks) is UDF and studentMarks is column in student table with marks (for eg: in your case it is 73)

HINT: You need to use CASE construct in the UDF to get the grade.

SELECT Grade FROM 'table name here' WHERE student_mark <= 79 AND student_mark >= 70 - in order to be more specific I would need to see the actual layout of the tables. But, something to that affect would work.

If the marks are actually regular multiples of 10, you could look into SQLs MOD function

Since I didn't find any way to do it using MySQL, I had to do some PHP programming to achieve the result. The goal is to get the closest value from gradings.

Okay suppose, we have the grades as defined in my question.

$MarksObt = 73 <- Marks obtained by the student:

Step 1: Get grades from mysql ordered by Marks in ASC order (ASC order is important):

SELECT marks, grade FROM gradings ORDER BY marks

Step 2: Create an array variable "$MinGrades". Loop through the result of above query. Suppose MySQL results are stored in "$Gradings" array. Now on each iteration, do the following:

1. Subtract the $Grading['marks'] From $MarksObt
2. If result is greater than or equal to 0, add the result to "$MinGrades" array

Step 3: When loop ends, the "$MinGrades" array's first element will be the closest value ... DONE

Below is the PHP code that implements the above:

$MinGrades = array();

foreach($Gradings as $Key=>$Grading){
    $Subtract = $MarksObt - $Grading['marks'];
    if( $Subtract >= 0 )
        array_push($MinGrades, array($Key=>$Subtract))
}

$GradeKey = key($MinGrades[0]); // Get key of first element in the array
print $Gradings[$GradeKey]['grade'];

If you have some better approach, please mention here. Thanks for your contribution...