Friday, May 2, 2014

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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.