I have a database with tables Student(StudId, StudName,...) and Grades (GradeExam1, GradeExam2,..) etc. And I want to do a query to assign a letter grade to each grade. I am thinking of doing something like:

SELECT StudId, GradeExam1 AS 'A' IF GradeExam1>= 90 AND GradeExam1 <=100
SELECT StudID, GradeExam1 AS 'B' ..........

I know that I can use non-conditional aliases within plain SQL, but I have never done conditional aliases within an SQL query.

Or maybe I need T-SQL in order to do a loop for a given exam, instead of doing a union of select statements for different conditions?

Thanks in Advance.


2 Answers 2


You're talking about a CASE expression:

DECLARE @Table table (StudentId INT, GradeExam1 int, GradeExam2 int);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (1,95,88);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (2,85,93);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (3,75,87);

SELECT StudentId
        WHEN GradeExam1 >= 90
            AND GradeExam1 <= 100
            THEN 'A'
        WHEN GradeExam1 >= 80
            AND GradeExam1 <= 90
            THEN 'B'
        WHEN GradeExam1 >= 70
            AND GradeExam1 <= 80
            THEN 'C'
        END AS GradeExam1
        WHEN GradeExam2 >= 90
            AND GradeExam2 <= 100
            THEN 'A'
        WHEN GradeExam2 >= 80
            AND GradeExam2 <= 90
            THEN 'B'
        WHEN GradeExam2 >= 70
            AND GradeExam2 <= 80
            THEN 'C'
        END AS GradeExam2
FROM @table;

My example of a CASE expression is Standard SQL and not specific to just the T-SQL syntax.

Check here, if you'd like to learn more about the implementation of the CASE expression.

  • 1
    Although @ScottHodgin sample works, it could be slightly confusing for an inexperienced dev. The evaluation of the case statement happens in order written, so using 90 as a grade, will result in A. However, reading it by eye, 90 could be A or B. It would be my suggestion to do GradeExam1 > 89 and GradeExam1 <= 100 then A, followed by GradeExam1 > 79 and GradeExam1 <= 89 then B.....
    – HunterX3
    Sep 21, 2016 at 20:42

Give the check to Scott Hodgin
It exits when a condition is met so you don't need to range check

SELECT StudentId
    WHEN GradeExam1 >= 90 THEN 'A'
    WHEN GradeExam1 >= 80 THEN 'B'
    WHEN GradeExam1 >= 70 THEN 'C'
    ELSE 'incomplete'
    END AS GradeExam1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.