DB - Interview Questions

DELETE DUPLICATE RECORD:

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2

WHERE 

e1.NAME = e2.NAME

AND

e1.ID > e2.ID;

FIND THE 3 HIGHEST VALUE:

WITH CTE AS

(

SELECT 

NAME,SALARY,RN=ROW_NUMBER() OVER (ORDER BY SALARY DESC) 

FROM EMPLOYEE

)

SELECT NAME,SALARY FROM CTE WHERE RN = 3; 

FIND THE 2 HIGHEST VALUE:

SELECT 

MAX(SALARY) 

FROM EMPLOYEE

WHERE

SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

GET EVEN/ODD ROWS: 

SELECT * FROM 

(SELECT *,ROW_NUMBER() OVER (ORDER BY id) AS ROW_ID FROM STUDENT)

WHERE ROW_ID %2 = 0