Important SQL Interview Questions

Important SQL Interview Questions

To get nth highest salary

SELECT SALARY FROM(SELECT SALARY ,DENSE_RANK() OVER(ORDER BY SALARY desc) AS R FROM EMP) WHERE R=2;



To Get duplicate Rows

SELECT COLUMN NAMES FROM TABLE NAME GROUP BY COUMNS NAME HAVING COUNT (*)>1


To Delete Duplicate Rows

DELETE FROM TABLE WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM TABLE GROUP BY COLUMN NAME

Top 5 having highest salary first.
Select ename, sal from( select * from emp order by sal desc) where rownum<=5



Bottom 5 .
SELECT COLUMNNAME FROM (SELECT COLUMN NAME FROM TABLENAME ORDER BY SAL) WHERE ROWNUM<=-5

Max Sal in their Respective Dept No
SELECT COLUMNS NAME, MAX(SAL) OVER (PARTITION BY DEPTNO) AS M_SAL FROM EMP


Display in first row
SELECT COLUMN NAME FROM TABLE NAME ORDER BY DECODE (COLUMN NAME, 'WHAT U NEED IN FIRST ROW','1','2'),COLUMN NAME


Display rows which is multiple of 3
SELECT * FROM (SELECT ROWNUM R, ENAME FROM TABLENAME) WHERE MOD(R,3)=0

Get 3 max sal
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;


Get 3 min Sal
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);