Decode And Case in SQL

select * from XX_STUDENT_TL

STUDENT_NAME	MARKS	PHYSICAL_MARKS	RESULT	GRADE
Amit	33	80	F	A
Bharat	20	30	F	C
Jai Prakash	99	100	P	A++
Manoj	20	98	F	A+
Pinky	90	30	P	A+
Rahul	45	55	P	B
Ravi	50	90	P	A+
Sanjay	80	40	P	A+

edit XX_STUDENT_TL

--====================================================

-- DECODE Example --

--====================================================

select STUDENT_NAME, DECODE(RESULT, 'F', 'Fail', 'P', 'Pass', 'Result not Availabe'   ) RESULT

from XX_STUDENT_TL

STUDENT_NAME	MARKS	PHYSICAL_MARKS	RESULT	GRADE
Amit	33	80	F	A
Bharat	20	30	F	C
Jai Prakash	99	100	P	A++
Manoj	20	98	F	A+
Pinky	90	30	P	A+
Rahul	45	55	P	B
Ravi	50	90	P	A+
Sanjay	80	40	P	A+
		F	Fail
		P	Pass
		A	good
		A+	Very Good
		A++	Excellent
		B	Average
		C	Poor

Output:-

STUDENT_NAME	RESULT
Amit	Fail
Bharat	Fail
Jai Prakash	Pass
Manoj	Fail
Pinky	Pass
Rahul	Result not Availabe
Ravi	Pass
Sanjay	Pass

--====================================================

-- Case  same as Decode with Example --

--====================================================

select STUDENT_NAME,

DECODE(RESULT, 'F', 'Fail', 'P', 'Pass', 'Result not Availabe') RESULT_Decode ,

CASE  RESULT

WHEN  'F'  THEN 'Fail'

WHEN  'P'  THEN 'Pass'

ELSE 'Result not Availabe'

END RESULT_Case

from XX_STUDENT_TL

STUDENT_NAME	RESULT_DECODE	RESULT_CASE
Amit	Fail	Fail
Bharat	Fail	Fail
Jai Prakash	Pass	Pass
Manoj	Fail	Fail
Pinky	Pass	Pass
Rahul	Result not Availabe	Result not Availabe
Ravi	Pass	Pass
Sanjay	Pass	Pass

--====================================================

-- Next level Case Example --

--====================================================

select STUDENT_NAME, MARKS, PHYSICAL_MARKS,

DECODE(RESULT, 'F', 'Fail', 'P', 'Pass', 'Result not Availabe') RESULT_Decode ,

CASE 

WHEN RESULT =  'F' AND   PHYSICAL_MARKS > 50 THEN 'Pass'

WHEN RESULT =  'P'   THEN 'Pass'

WHEN RESULT IS NULL   THEN 'Result not Availabe'

ELSE 'FAIL'

END NEW_RESULT_Case

from XX_STUDENT_TL

STUDENT_NAME	MARKS	PHYSICAL_MARKS	RESULT	RESULT_DECODE	NEW_RESULT_CASE
Amit	33	80	F	Fail	Pass
Bharat	20	30	F	Fail	FAIL
Jai Prakash	99	100	P	Pass	Pass
Manoj	20	98	F	Fail	Pass
Pinky	90	30	P	Pass	Pass
Rahul	45	55		Result not Availabe	Result not Availabe
Ravi	50	90	P	Pass	Pass
Sanjay	80	40	P	Pass	Pass