Few Complex SQL query- asked as routine Questions in PL/SQL interviews

Eight Years Back, i was naive developer, yearning to get a job into PL/SQL development, I faced multiple interviews. I still have some fading memories of those strenuous interviews. I could not get through any of those until, i developed a good understanding of sql. The sql queries asked in all of the interviews are more or less the same and even now after about a 10 years of IT experience, i can hear people discussing the same questions. These are tricky question and you need to be well primed before landing into any such interviews.

In this post, i will share some of those tricky sql asked generally in interviews.

In one of my interviews back then i can recall a event when interviewer asked me. "Write a query to find the count of alphabet 'i' in the letter 'INDIA'. i confirmed with him twice before i started to scribble something as a escape whether he wanted me write a program or just a query. I could not answer any of his question on complex query. In this post i will share some of the complex sql generally asked in interviews.

HOW many count of alphabet 'I' in the string 'INDIA' ?

Select length('india') - length(replace('india','i')) ANSWER from dual;
-----------------------------------------------------------------------
2
  

How to find duplicate rows from table?

This is most common question asked widely in the interviews. There are many ways we can achieve this.

Select * from employees a where exists
(select 1 from employees b where a.employee_id = b.employee_id and a.rowid >b.rowid)

How to find Third highest salary in Employee table?

SELECT *
FROM(
SELECT 
    ROW_NUMBER() OVER(
        ORDER BY SALARY DESC
    ) row_num, 
    FIRST_NAME|| ' '||LAST_NAME, 
    EMPLOYEE_ID,
    MANAGER_ID
FROM 
    EMPLOYEES)t
    WHERE t.row_num =3;

.How to display following using query?

*

**

***

SELECT lpad ('*', ROWNUM,'*') FROM employees WHERE ROWNUM <4;

How to display the your name vertically in Oracle?

SELECT SUBSTR ('ASHISH', LEVEL, 1)
FROM dual
CONNECT BY LEVEL <= length ('ASHISH');

How to get number of Weekends of current month?

SELECT count(*) AS Weekends
  FROM (SELECT TRUNC(SYSDATE, 'mm') + LEVEL - 1 Current_dt
          FROM Dual
        CONNECT BY LEVEL <= last_day(SYSDATE) - TRUNC(SYSDATE, 'mm') + 1
      )
Where TO_CHAR(Current_dt, 'dy') IN ('sat', 'sun');

How to convert Integer to Time

select to_char ( to_date ( '2300', 'HH24MI'), 'HH:MI AM') integer_time from dual;  

select to_date( to_char(201311080300) ,'yyyymmddhh24mi') from dual;

How to fetch last record from the table?

  select * from employees ORDER BY employee_id DESC FETCH FIRST 1 ROWS ONLY

 select * from employees where rowid in(select max(rowid) from employees);

select * from employees
minus
select * from employees
where rownum < (select count(*) from employees)

Fetch last five records from a table

 SELECT *
FROM(
SELECT 
    ROW_NUMBER() OVER(
        ORDER BY ROWID DESC
    ) row_num, 
    FIRST_NAME|| ' '||LAST_NAME, 
    EMPLOYEE_ID,
    MANAGER_ID,
    SALARY
FROM 
    EMPLOYEES)t
    WHERE t.row_num <6;

Alternate Records In oracle

SELECT *
      FROM   ( SELECT rownum rn, employee_id, last_name|| ' '||First_name
               FROM employees
             ) temp
      WHERE  MOD(temp.rn,2) = 1

Comma separated Value

      SELECT department_id,
      LISTAGG(employee_id, ',')
         WITHIN GROUP (ORDER BY employee_id) 
         AS employees
FROM   employees
GROUP BY department_id;

Email Validation

SELECT
Email
FROM
employees
where NOT REGEXP_LIKE(Email, '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}', 'i');

Extract Number from a string

select regexp_replace('1234bsdfs3@23##PU', '[^0-9]', '') nums
from   dual;

Extract alpha characters from string

select regexp_replace('NIC132DA.1','[^A-Za-z]') from dual;