SQL Interview Questions And Answers - Part 2

Frequently Asked Questions On SQL | SQL FAQs

16. Select last name from emp table of an employee while name is given full.

SELECT LTRIM(SUBSTR(name,INSTR(name,' ',1)),' ') FROM emp;

18. Write a query which shows all data if the NULL is also exists while passing NULL.

SELECT t.*, t.rowid FROM emp t WHERE NVL(t.empno,0) = NVL(&emp,nvl(t.empno,0));

19. Update the name of an employee in swaping manner for eg. If name is amit then rehan and rehan then amit by single update.

UPDATE emp SET empname = DECODE(empname, 'Amit', 'Rehan', 'Rehan', 'Amit');

20. Write a query to sort all columns independently

select tab_x.a, tab_y.b

  from (select tab_a.a, rownum r1

          from (select a from a_1 order by 1) tab_a) tab_x,

       (select tab_b.b, rownum r2

          from (select b from a_1 order by 1) tab_b) tab_y

 where tab_x.r1 = tab_y.r2;

21. Write a query to print distinct data in a single row

select a, b, sum(x), 'A' T1, sum(y), 'B' T2

  from (select a a, b b, c x, 0 y from a_dummy where d = 'A'

        union

        select a a, b b, 0 x, C y from a_dummy where d = 'B')

 group by a, b;

22. Write a query to select all tables in database does not contain any row.

select table_name, sum(num_nulls + num_distinct) table_rows

  from user_tab_columns

 group by table_name

having sum(num_nulls + num_distinct) = 0;

OR,

select table_name, num_rows table_rows
  from user_tables
 where a.num_rows = 0;

23. Write a query to change the amount in different formats.

SELECT ltrim(to_char(&P_AMOUNT,

                     '999G999G999G999D99',

                     'NLS_NUMERIC_CHARACTERS = ''.'''''),

             ' ') amt_converter

  FROM dual;

SELECT ltrim(to_char(&P_AMOUNT,

                     '999999999G999D99MI',

                     'NLS_NUMERIC_CHARACTERS = '',.'''),

             ' ') amt_converter

  FROM dual;

24. Write a query to convert the amount in words

SELECT to_char(to_date(&P_AMOUNT_LESS_THAN_5373484, 'J'), 'Jsp') || ' Only' amount_in_word

  FROM dual;

25. Write a query to find out number of particular character in a string

SELECT COUNT(NO_OF_CHAR) NO_OF_CHAR
  FROM (SELECT UPPER(SUBSTR('&str', ROWNUM, 1)) NO_OF_CHAR
          FROM USER_OBJECTS
         WHERE ROWNUM <= LENGTH('&str'))
 WHERE NO_OF_CHAR = UPPER('&char');

28. Count Numbers, Upper-Lower Characters, and Symbols

-- Just For Numbers
select LENGTH('&STRING') LEN#,
       LENGTH(TRANSLATE('&STRING', 'A1234567890', 'A')) "-- Non-Numeric Charcters --"
  from dual;

LEN#        -- Non-Numeric Charcters --
----------  ---------------------------
36          33

-- For Numbers, Upper-Lower Characters, Others

select length('&STRING') - LENGTH(TRANSLATE('&STRING', 'A1234567890', 'A')) "-- Numeric --",
       LENGTH(TRANSLATE('&STRING', 'A1234567890', 'A')) "-- Non-Numeric Charcters --",
       SUM(length('&STRING') - LENGTH(REPLACE('&STRING', Single_UpperChar))) Upper#,
       SUM(length('&STRING') - LENGTH(REPLACE('&STRING', Single_LowerChar))) Lower#
  from (select CHR(64 + ROWNUM) Single_UpperChar,
               CHR(96 + ROWNUM) Single_LowerChar
          from all_synonyms
         where rownum < 27);

-- Numeric --  -- Non-Numeric Charcters --  UPPER#  LOWER#
-------------  ---------------------------  ------  --------
3              33                           7       18

29. Write a query to display total vowels and consonants in a string

SELECT LENGTH(REGEXP_REPLACE('&STR', '([aeiouAEIOU])|.', '\1')) tot_vowels,

       LENGTH(REGEXP_REPLACE('&STR',

                             '([bcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ])|.',

                             '\1')) tot_consonants

  FROM DUAL;

Part-1