Oracle SQL Interview Questions

1. How to print Mathematical table using SQL in oracle.

Solution : SELECT &TABLE_OF * ROWNUM AS Print_Table FROM DUAL 
                 CONNECT BY LEVEL <= 10;

2. How to split and print all the characters of a word in rows using SQL in Oracle. Exp - Input : Hello, Output :

                         H
                         E
                         L
                         L
                         O

Solution : SELECT substr('&YourWord',ROWNUM,1) FROM DUAL 

                 CONNECT BY LEVEL <= length('&YourWord');

3. How to print all the dates between two given dates.

Solution : SELECT TO_DATE('01-Dec-2013', 'DD-Mon-YYYY') + ROWNUM - 1

                 FROM DUAL

                CONNECT BY LEVEL <= TO_DATE('10-Dec-2013', 'DD-Mon-YYYY') + 1 -

                TO_DATE('01-Dec-2013', 'DD-Mon-YYYY')

4.Split comma separated string into rows in Oracle.

Solution : SELECT REGEXP_SUBSTR('Jai,Shri,Ram,Hare,Ram', '[^,]+', 1, LEVEL)

     FROM DUAL

   CONNECT BY REGEXP_SUBSTR('Jai,Shri,Ram,Hare,Ram', '[^,]+', 1, LEVEL) IS NOT NULL;

add to del.icio.us saved by 0 users