MSSQL Server 2012 Query Do we Need T-SQL for query with IF Condition?

Question

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
UNION
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.

Answer 1

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
 ,CASE 
 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
 ,CASE 
 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.

• HunterX3: 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.....

Answer 2

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

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