Oracle Conditional Execution

CASE Expressions

From Oracle 8i one can use CASE statements in SQL. Look at this example:

SELECT ename, CASE WHEN sal = 1000 THEN 'Minimum wage'
                   WHEN sal > 1000 THEN 'Over paid'
                   ELSE 'Under paid'
              END AS "Salary Status"
FROM   emp;

DECODE() Function

The Oracle decode function acts like a procedural statement inside an 
SQL statement to return different values or columns based on the values of
other columns in the select statement. Examples:

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from   employees;

select a, b, decode( abs(a-b), a-b, 'a > b',
                               0,   'a = b',
                                    'a < b') from  tableX;

Note: The decode function is not ANSI SQL and is rarely implemented
in other RDBMS offerings. It is one of the good things about Oracle,
but use it sparingly if portability is required.

GREATEST() and LEAST() Functions

select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 
                                 'B is greater than A')...

select decode( GREATEST(A,B), 
               A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'), 
               'A NOT GREATER THAN B')...

NVL() and NVL2() Functions

NVL and NVL2 can be used to test for NULL values.

NVL(a,b) == if 'a' is null then return 'b'.

SELECT nvl(ename, 'No Name') 
  FROM emp;

NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.

SELECT nvl2(ename, 'Do have a name', 'No Name') 
  FROM emp;

COALESCE() Function

COALESCE() returns the first expression that is not null. Example:

SELECT 'Dear '||COALESCE(preferred_name, first_name, 'Sir or Madam')
  FROM emp2;

NULLIF() Function

NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:

SELECT NULLIF(ename, ename)
  FROM emp;

Source: orafaq.com website.

Oracle syntax lesson 1: Alter database commands

1. In order to add new column to a table:

                    ALTER TABLE   tbl_emp ADD col_name  VARCHAR2(160) NOT NULL ;

     2.  In order to add multiple new columns at once to a table:

       

                   ALTER TABLE   tbl_emp ADD

                   (

                      col_name1  VARCHAR2(160) NOT NULL,

                      col_name 2 VARCHAR2(30) NOT NULL

                   );

     3. In Order to rename a table name:

       

                  ALTER TABLE tbl_old_name RENAME TO tbl_new_name;

     4. In Order to Modify a column properties in a table:

                  ALTER TABLE tbl_name MODIFY 

                  (

                      col_name1 VARCHAR2(25) NOT NULL,

                      col_name2 number,

                      col_name3 VARCHAR2(100)

                   );

     5. In Order to Drop a column from a table:

                 ALTER TABLE tbl_name DROP COLUMN col_name1;

     6.  In Order to Rename a column in a table:

                  ALTER TABLE tbl_name  RENAME COLUMN col_old_name to col_new_name;

     7.  In Order to Change the password of a user:

             
                  ALTER USER myuser IDENTIFIED BY new_password;

     8.  In order to Lock a User Account:

                 ALTER USER myuser ACCOUNT LOCK;

     9.  In order to Unlock a User Account:

                 ALTER USER myuser ACCOUNT UNLOCK;

    10. In order to Expire the password of a User:

                ALTER USER myuser PASSWORD EXPIRE;