SQL Interview Questions and Answers part2

21. Query for removing all non-numeric :

SELECT

TRANSLATE(LOWER(ssn),'abcdefghijklmnopqrstuvwxyz- ','')

FROM DUAL;

22. Query for translating a column values to INITCAP :

SELECT

TRANSLATE(INITCAP(temp),

SUBSTR(temp, INSTR(temp,'''')+1,1), LOWER(SUBSTR(temp, INSTR(temp,'''')+1)))

FROM srinu1;

23. Function for displaying Rupees in Words :

CREATE OR REPLACE FUNCTION RUPEES_IN_WORDS(amt IN NUMBER) RETURN CHAR

IS

  amount       NUMBER(10,2);

  v_length     INTEGER         := 0;

  v_num2       VARCHAR2 (50)   := NULL;

  v_amount     VARCHAR2 (50);

  v_word       VARCHAR2 (4000) := NULL;

  v_word1      VARCHAR2 (4000) := NULL;

  TYPE myarray IS TABLE OF VARCHAR2 (255);

  v_str myarray := myarray (' thousand ',

                            ' lakh ',

                            ' crore ',

                            ' arab ',

                            ' kharab ',

                            ' shankh ');

BEGIN

      amount := amt;

      IF ((amount = 0) OR (amount IS NULL)) THEN

      v_word := 'zero';

      ELSIF (TO_CHAR (amount) LIKE '%.%') THEN

      IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0) THEN

                  v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);

                  IF (LENGTH (v_num2) < 2) THEN

                  v_num2 := v_num2 * 10;

                  END IF;

                  v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1,2), 'J'),

                           'JSP' ))|| ' paise ';

                  v_amount := SUBSTR(amount,1,INSTR (amount, '.')-1);

            v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2,3), 'J'), 'Jsp' ) || v_word;

                  v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);

            FOR i in 1 .. v_str.COUNT

            LOOP

                        EXIT WHEN (v_amount IS NULL);

                        v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,2), 'J'), 'Jsp' ) || v_str (i) || v_word;

                        v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);

            END LOOP;

      END IF;

      ELSE

            v_word := TO_CHAR (TO_DATE (SUBSTR (amount, LENGTH (amount) - 2,3), 'J'), 'Jsp' );

            amount := SUBSTR (amount, 1, LENGTH (amount) - 3);

            FOR i in 1 .. v_str.COUNT

            LOOP

                  EXIT WHEN (amount IS NULL);

                  v_word := TO_CHAR (TO_DATE (SUBSTR (amount, LENGTH (amount) - 1,2), 'J'), 'Jsp' ) || v_str (i) || v_word;

                  amount := SUBSTR (amount, 1, LENGTH (amount) - 2);

            END LOOP;

      END IF;

      v_word := v_word || ' ' || v_word1 || ' only ';

      v_word := REPLACE (RTRIM (v_word), '  ', ' ');

      v_word := REPLACE (RTRIM (v_word), '-', ' ');

      RETURN INITCAP (v_word);

END;

24. Function for displaying Numbers in Words:

SELECT TO_CHAR( TO_DATE( SUBSTR( TO_CHAR(5373484),1),'j'),'Jsp') FROM DUAL;       

Only up to integers from 1 to 5373484

25. Query for deleting alternate even rows FROM a table :

DELETE

FROM   srinu

WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2)

                                     FROM   srinu);

26. Query for deleting alternate odd rows FROM a table :

DELETE

FROM   srinu

WHERE  (ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2)

                                     FROM   srinu);

27. Procedure for sending Email :

CREATE OR REPLACE PROCEDURE Send_Mail

IS

sender VARCHAR2(50) := 'sender@something.com';

recipient VARCHAR2(50) := 'recipient@something.com';

subject VARCHAR2(100) := 'Test Message';

message VARCHAR2(1000) := 'This is a sample mail ....';

lv_mailhost VARCHAR2(30) := 'HOTNT002';

l_mail_conn utl_smtp.connection;

lv_crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );

BEGIN

l_mail_conn := utl_smtp.open_connection (lv_mailhost, 80);

utl_smtp.helo ( l_mail_conn, lv_mailhost);

utl_smtp.mail ( l_mail_conn, sender);

utl_smtp.rcpt ( l_mail_conn, recipient);

utl_smtp.open_data (l_mail_conn);

utl_smtp.write_data ( l_mail_conn, 'FROM: ' || sender || lv_crlf);

utl_smtp.write_data ( l_mail_conn, 'To: ' || recipient || lv_crlf);

utl_smtp.write_data ( l_mail_conn, 'Subject:' || subject || lv_crlf);

utl_smtp.write_data ( l_mail_conn,  lv_crlf || message);

utl_smtp.close_data(l_mail_conn);

utl_smtp.quit(l_mail_conn);

EXCEPTION

            WHEN OTHERS THEN

                        DBMS_OUTPUT.PUT_LINE('Error');

END;

/

28. Alternate Query for DECODE function :

SELECT case

WHEN sex = 'm' THEN 'male'

WHEN sex = 'f' THEN 'female'

ELSE 'unknown'

END

FROM mytable;

29.  Create table adding Constraint to a date field to SYSDATE or 3 months later:

CREATE TABLE srinu(dt1 date DEFAULT SYSDATE, dt2 date,

CONSTRAINT check_dt2 CHECK ((dt2 >= dt1) AND (dt2 <= ADD_MONTHS(SYSDATE,3)));

30.  Query to list all the suppliers who supply all the parts supplied by supplier 'S2' :

SELECT DISTINCT a.SUPP

FROM ORDERS a

WHERE a.supp != 'S2'

AND a.parts IN

(SELECT DISTINCT PARTS FROM ORDERS WHERE supp = 'S2')

GROUP BY a.SUPP

HAVING

COUNT(DISTINCT a.PARTS) >=

(SELECT COUNT(DISTINCT PARTS) FROM ORDERS WHERE supp = 'S2');

Table : orders

SUPP                 PARTS

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

S1                   P1

S1                   P2

S1                   P3

S1                   P4

S1                   P5

S1                   P6

S2                   P1

S2                   P2

S3                   P2

S4                   P2

S4                   P4

S4                   P5

31. Query to get the last Sunday of any month :

SELECT NEXT_DAY(LAST_DAY(TO_DATE('26-10-2001','DD-MM-YYYY')) - 7,'sunday')

FROM DUAL;

32. Query to get all those who have no children themselves :

table data :

id         name   parent_id

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

1          a          NULL - the top level entry

2          b          1 - a child of 1

3          c          1

4          d          2 - a child of 2

5          e          2

6          f           3

7          g          3

8          h          4

9          i           8

10        j           9

SELECT ID

FROM MY_TABlE

WHERE PARENT_ID IS NOT NULL

MINUS

SELECT PARENT_ID

FROM MY_TABlE;

33. Query to SELECT last N rows FROM a table :

SELECT empno FROM emp WHERE ROWID in

(SELECT ROWID FROM emp

MINUS

SELECT ROWID FROM emp WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM emp));

34. SELECT with variables:

CREATE OR REPLACE PROCEDURE disp

AS

xTableName varchar2(25):='emp';

xFieldName varchar2(25):='ename';

xValue NUMBER;

xQuery varchar2(100);

name varchar2(10) := 'CLARK';

BEGIN

xQuery := 'SELECT SAL FROM ' || xTableName || ' WHERE ' || xFieldName ||

              ' = ''' || name || '''';

DBMS_OUTPUT.PUT_LINE(xQuery);

EXECUTE IMMEDIATE xQuery INTO xValue;

DBMS_OUTPUT.PUT_LINE(xValue);

END;

35. Query to get the DB Name:

SELECT name FROM v$database;

36. Getting the current default schema :

SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

37. Query to get all the column names of a particular table :

SELECT column_name

FROM all_tab_columns

WHERE TABLE_NAME = 'ORDERS';

38. How do I spool only the query result to a file in SQLPLUS :

Place the following lines of code in a file and execute the file in SQLPLUS :

set heading off

set feedback off

set colsep '        '

set termout off

set verify off

spool c:\srini.txt

SELECT empno,ename FROM emp; /* Write your Query here */

spool off

/

39.  Query for getting the current SessionID :

SELECT SYS_CONTEXT('USERENV','SESSIONID') Session_ID FROM DUAL;

40.  Query to display rows FROM m to n :

To display rows 5 to 7 :

SELECT DEPTNO, ENAME, SAL

FROM EMP

WHERE ROWID IN

                (SELECT ROWID FROM EMP

                 WHERE ROWNUM <= 7

                 MINUS

                SELECT ROWID FROM EMP

                WHERE ROWNUM < 5);

OR

SELECT ename

FROM emp

GROUP BY ROWNUM, ename

HAVING ROWNUM > 1 and ROWNUM < 3;