SQL Interview Questions

Question: How will you delete duplicating rows from a base table?

Answer: 

DELETE FROM table_name A WHERE rowid > (SELECT MIN(rowid) FROM table_name B WHERE A.key_values = B.key_values);

DELETE FROM emp e WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM emp a WHERE e.empno = a.empno);

DELETE FROM EMP WHERE ROWID NOT IN (SELCT MAX(ROWID) FROM EMP GROUP BY EMPNO);

Question: Find out nth highest salary from emp table?

Answer: 
SELECT DISTINCT (A.SAL) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (B.SAL)) FROM EMP B WHERE A.SAL<=B.SAL);

SELECT MIN(SAL) FROM (SELECT DISTINCT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <=&N;

SELECT * FROM (SELECT RANK() OVER (PARTITION BY SAL ORDER BY SAL DESC NULLS LAST) RN FROM TABLENAME) WHERE RN = &N;

SELECT ENAME, SAL, DEPTNO, JOB FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE LEVEL = &LEVELNO CONNECT BY PRIOR SAL>SAL GROUP BY LEVEL);

SELECT ROWNUM,SAL FROM (SELECT ROWNUM,SAL FROM EMP ORDER BY SAL DESC )GROUP BY ROWNUM,SAL HAVING ROWNUM=&N;

SELECT * FROM(SELECT EMPNO,ENAME,DEPTNO,SAL,RANK() OVER(ORDER BY SAL) TOPSAL FROM EMP) WHERE TOPSAL=&NTH;

SELECT DISTINCT A.SAL FROM EMP A, (SELECT ROWNUM AS CNT, A.* FROM (SELECT DISTINCT SAL FROM EMP ORDER BY SAL DESC) A) B WHERE A.SAL = B.SAL AND B.CNT = :A;

SELECT LEVEL,MAX(SAL) FROM EMP WHERE LEVEL=&LEVELNO CONNECT BY PRIOR SAL>SAL GROUP BY LEVEL;

Question: Which datatype is used for storing graphics and images?

Answer:  BLOB or BFILE. Long raw is obsolete now.

Question: Which is more faster - IN or EXISTS?

Answer: EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an index scan. EXISTS is faster when sub-query result is large. IN is often better if the result of sub-query are very small. But using EXISTS is better choice when sub-query result is unpredictable.