CASE condition & value matching in SELECT Query

There are two different types of CASE quires we will learn here. 

First Type 
MySQL Query Comparing with value, Syntax is here

CASE A_value
    WHEN B_value THEN B_statement
    [WHEN C_Value THEN C_statement] ...
    [ELSE X_statement]
END CASE

Here A_value is matched with different values given under WHEN. If A_value equals to B_value then B_statement is executed. If it is not matched than next WHEN value C_value is checked. If nothing is matched then statement under X_statement is executed. 

Let us try one example 
We will use our student table for this example. We have allotted rooms for the classes at different floors in a school. Our list should display student details with class and location of the class. Using CASE in our query statement is here.

SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , 
CASE class
WHEN  'four' THEN   '1st floor'
WHEN  'five' THEN    '2nd floor'
WHEN  'three' THEN    '2nd floor'
WHEN  'two' THEN    '1st floor'

ELSE 'Ground floor'
END AS  location
FROM  `student`

id	name	class	mark	sex	location
1	John Deo	Four	75	male	1st floor
2	Max Ruin	Three	85	male	2nd floor
3	Arnold	Three	55	male	2nd floor
4	Krish Star	Four	60	male	1st floor
5	John Mike	Four	60	male	1st floor
6	Alex John	Four	55	male	1st floor
7	My John Rob	Fifth	78	male	Ground floor
8	Asruid	Five	85	male	2nd floor
9	Tes Qry	Six	78	male	Ground floor
10	Big John	Four	55	male	1st floor

There are more records , here it is limited to 10 only. 

We can display based on the floor wise or location wise by using order by query

SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , 
CASE class
WHEN  'four' THEN   '1st floor'
WHEN  'five' THEN    '2nd floor'
WHEN  'three' THEN    '2nd floor'
WHEN  'two' THEN    '1st floor'

ELSE 'Ground floor'
END AS  location
FROM  `student` ORDER BY location

Second Type (Matching condition with CASE statement)
In this case we don't specify any value or data to be matched, instead we work on testing some conditions and if it is True then respective statement is to be executed.

CASE
    WHEN condition_to_check THEN statement
    [WHEN condition_to_check THEN statement ...
    [ELSE statement]
END CASE

Here we check the condition and if it is True then the respective statement is executed. 
Example :
We will try our student example. Here student grade is awarded based on the mark they got. Here is the query

SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , 
CASE 
WHEN mark >= 90 THEN   'A'
WHEN mark >= 80 THEN    'B'
WHEN mark >= 70 THEN   'C'
ELSE 'FAIL'
END AS  grade
FROM  `student`

id	name	class	mark	sex	grade
1	John Deo	Four	75	male	C
2	Max Ruin	Three	85	male	B
3	Arnold	Three	55	male	FAIL
4	Krish Star	Four	60	male	FAIL
5	John Mike	Four	60	male	FAIL
6	Alex John	Four	55	male	FAIL
7	My John Rob	Fifth	78	male	C
8	Asruid	Five	85	male	B
9	Tes Qry	Six	78	male	C
10	Big John	Four	55	male	FAIL
11	Ronald	Six	89	male	B
12	Recky	Six	94	male	A
13	Kty	Seven	88	male	B