SQL Interview questions -4

21)How does one implement IF-THEN-ELSE in a select statement?

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.

Some 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;

        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')...

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.

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

        SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END

        FROM   emp;

22)How can one dump/ examine the exact content of a database column?

        SELECT DUMP(col1)

        FROM tab1

        WHERE cond1 = val1;

        DUMP(COL1)

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

        Typ=96 Len=4: 65,66,67,32

For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

23)Can one drop a column from a table?

From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.

Other workarounds:

1. SQL> update t1 set column_to_drop = NULL;

   SQL> rename t1 to t1_base;

   SQL> create view t1 as select <specific columns> from t1_base;

2. SQL> create table t2 as select <specific columns> from t1;

   SQL> drop table t1;

   SQL> rename t2 to t1;

24)Can one rename a column in a table?

From Oracle9i one can RENAME a column from a table. Look at this example:

ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;

Other workarounds:

1. -- Use a view with correct column names...

   rename t1 to t1_base;

   create view t1 <column list with new name> as select * from t1_base;

2. -- Recreate the table with correct column names...

   create table t2 <column list with new name> as select * from t1;

   drop table t1;

   rename t2 to t1;

3. -- Add a column with a new name and drop an old column...

   alter table t1 add ( newcolame datatype );  

   update t1 set newcolname=oldcolname;

   alter table t1 drop column oldcolname;

25)How can I change my Oracle password?

Issue the following SQL command: ALTER USER <username> IDENTIFIED BY <new_password>
/

From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name".