93. CASE AND DEFAULT

CASE

Case is similar to decode but easier to understand while going through coding

Ex:

SQL> Select sal,

          Case sal

                    When 500 then ‘low’

                    When 5000 then ‘high’

                    Else ‘medium’

          End case

          From emp;

       SAL          CASE

       -----       --------

       500          low

      2500         medium

      2000         medium

      3500         medium

      3000         medium

      5000         high

      4000         medium

      5000         high

      1800         medium

      1200         medium

      2000         medium

      2700         medium

      2200         medium

      3200         medium

DEFAULT

Default can be considered as a substitute behavior of not null constraint when applied to new rows being entered into the table.

When you define a column with the default keyword followed by a value, you are actually telling the database that, on insert if a row was not assigned a value for this column, use the default value that you have specified.

Default is applied only during insertion of new rows.

Ex:

     SQL> create table student(no number(2) default 11,name varchar(2));

     SQL> insert into student values(1,'a');

     SQL> insert into student(name) values('b');

    

     SQL> select * from student;

        NO   NAME

      ------ ---------

         1             a

        11            b

       SQL> insert into student values(null, ‘c’);

      SQL> select * from student;

        NO   NAME

      ------ ---------

         1             a

        11            b

                       C

-- Default can not override nulls.