SQL Interview Questions

How to find the nth salary using rownum?

SELECT EMP.*, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SALARY DESC) ROWNUMB

FROM EMPLOYEE EMP)

WHERE ROWNUMB=N;

How to find the nth salary using rank?

SELECT * FROM (SELECT EMPID,SALARY,RANK() OVER (PARTITION BY DEPTNO ORDER BY SALARY DESC) RANKING

FROM EMPLOYEE)

WHERE RANKING=1;

How to perform a cross-join?

It returns all the rows in all the tables listed in the query. If two tables in a join query has no join conditions, returns their Cartesian product.

SELECT EMPNAME, CITY FROM TABLE1, TABLE2; OR

SELECT EMPNAME, CITY FROM TABLE1 CROSS JOIN TABLE2;

How to delete Duplicate rows in SQL?

DELETE FROM EMP A

WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP B WHERE A.EMPNO=B.EMPNO);

DELETE FROM EMP

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

How to count duplicates from a table?

SELECT NAME, COUNT(EMAIL)

FROM USERS GROUP BY EMAIL

HAVING COUNT(EMAIL) > 1;

How to count duplicates in One column?

SELECT NAME, COUNT(*) COUNT_DUP

FROM TABLE GROUP BY NAME

HAVING COUNT(*) >1 ORDER BY COUNT(*) DESC;

Duplicates in Two columns?

SELECT COL1,COL2,COUNT(*) COUNT_DUP

FROM TABLE GROUP BY COL1,COL2

HAVING COUNT(*)>1 ORDER BY COUNT(*) DESC;

Duplicates in all columns?

SELECT COUNT(*) COUNT_DUP

FROM TABLE GROUP BY HAVING COUNT(*)>1

ORDER BY COUNT(*) DESC;