Loading

Friday, October 28, 2011

ORACLE SQL TUTORIAL: 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.


1 Responses to “ORACLE SQL TUTORIAL: CASE AND DEFAULT”

Sridevi K said...
October 8, 2016 at 12:59 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.