DECODE and CASE statement

CREATE TABLE students (
                         roll_no     NUMBER(4)    ,
                         Name        VARCHAR2(30) ,
                         dept_cd     VARCHAR2(20) ,
                         total_marks NUMBER(3)
                      );

BEGIN                    
     INSERT INTO students VALUES (2000,'Rahul','CSE',480);
     INSERT INTO students VALUES (2001,'Bala','IT', 390);
     INSERT INTO students VALUES (2002,'Ramesh','CSE',250);
     INSERT INTO students VALUES (2003,'Karthi','EEE',185);
     INSERT INTO students VALUES (2004,'Ravi','IT',345);
END;
/

SELECT * FROM students;

DECODE
    Decode is a function. Its a Oracle one.
    Works like IF-THEN-ELSE.
    You can use DECODE only in SELECT clause.
    In DECODE you can include 255 things include Exep, search , result and default value. 

Example

    SELECT roll_no,
           NAME,
           dept_cd,
           DECODE(dept_cd,
                  'CSE',
                  'Computer Science and Engineering',
                  'IT',
                  'Information Technology',
                  'Default Value') AS "DEPARTMENT NAME"
      FROM students;
     
Sample Output

------------------------------------------------------------
ROLL_NO    NAME       DEPT_CD  DEPARTMENT NAME
------------------------------------------------------------
   2000      Rahul    CSE      Computer Science and Engineering
   2001      Bala     IT       Information Technology
   2002      Ramesh   CSE      Computer Science and Engineering
   2003      Karthi   EEE      Default Value
   2004      Ravi     IT       Information Technology
------------------------------------------------------------

CASE
   CASE is an expression. Its a ANSI standard.
   Compare to DECODE case will be more faster.

Example

    SELECT roll_no,
           NAME,
           dept_cd,
           total_marks,
           CASE
               WHEN total_marks > 500 THEN
                'A Grade'
               WHEN total_marks > 400 THEN
                'B Grade'
               WHEN total_marks > 300 THEN
                'C Grade'
               ELSE
                'U Grade'
           END AS "GRADE"
      FROM students;
     
-----------------------------------------------
ROLL_NO  NAME     DEPT_CD  TOTAL_MARKS GRADE
-----------------------------------------------    
   2000  Rahul    CSE        480       B Grade
   2001  Bala     IT         390       C Grade
   2002  Ramesh   CSE        250       U Grade
   2003  Karthi   EEE        185       U Grade
   2004  Ravi     IT         345       C Grade   
-----------------------------------------------

Interview Questions:

What is DECODE?
What is CASE?
Difference between CASE and DECODE? Which one is faster?