Using CASE expression in SQL queries

Use of CASE Expressions in SQL query is sometimes extremely useful.For example Using CASE in SELECT statement provides developer the power to manipulates the data at presentaion layer without changing data at backend.So there are various use of CASE Expressions and it can be used in, including in statements like [SELECT, UPDATE, DELETE, SET ] and clauses like [ WHERE, ORDER BY, HAVING, GROUP BY ] Here is some more information on CASE expressions Type of CASE Expression: Simple Case Expression Searched Case Expression Basic SYNTAX for CASE experession :   #Type-I(Simple Case Expression)     CASE InputValue        WHEN WhenValue THEN ReturnValue        WHEN WhenValue THEN ReturnValue  ELSE DefaultReturnValue    Functionality : 1.In Simple CASE Expression, one value is checked against multiple values [ Each value at WHEN clause ] 2.Simple CASE Expression only allows equality check. 3.Returns ReturnValue of the first match i.s when InputValue = WhenValue. 4.If no matches is found returns NULL if ELSE clause is not specified otherwise DefaultReturnValue will be returned.    Note: 1.The DataType of InputValue and WhenValue must be same. 2.The DataType of ReturnValue and DeafaultReturnValue must be same. 3.Simple Case Expression can be nested up to three levels.

#Type-II(Searched Case Expression)     CASE        WHEN BooleanExpression THEN ReturnValue       WHEN BooleanExpression THEN ReturnValue  ELSE DefaultReturnValue    Functionality : 1.In Searched CASE Expression,BooleanExpression will be evaluted for each WHEN clause specified . 2.Returns ReturnValue of the first BooleanExpression evalutes to True. 3.Simple CASE Expression allows comparision using [AND OR] between boolean expression.  4.If no BooleanExpression is evaluted to True then returns NULL if ELSE clause is not specified otherwise DefaultReturnValue will be returned.    Note : 1.The DataType of ReturnValye and DeafaultReturnValue must be same 2.Searched Case Expression have no limit to the number of nesting levels.   Example : Use of CASE with SELECT statement.   Simple Case Expression with SELECT statement. SELECT     Name, UserType = CASE TypeID      WHEN '0' THEN 'Anonymous'      WHEN '1' THEN 'Registered'      WHEN '2' THEN 'Admin'      ELSE NULL     END FROM    User;   Searched CASE expression with SELECT statement  SELECT    Name, Marks, 'Division' =    CASE     WHEN Marks < 350 THEN 'Fail'     WHEN Marks >= 350 AND Marks < 450 THEN ' THIRD'     WHEN Marks >=450 AND Marks < 550 THEN 'SECOND'     WHEN Marks >=550 AND Marks < 650 THEN 'FIRST'     ELSE 'Excelent'    END  FROM      Student;