SQL Interview Questions

SQL Interview Questions
SQL Interview Questions



Q1. Find the nth highest salary paying employee from employee table?

1. SELECT * FROM (SELECT ENAME, ESALARY, DENSE_RANK() OVER(ORDER BY ESALARY BY DESC)R FROM 
EMPLOYEE) WHERE R=&N;

2. select min(salary) from (select distinct salary from emp order by salary desc) where 
rownum < 3 (for nth - put n+1)

Q2. Find the highest salary paying employee details from each department of employee table?

SELECT A.EMPLOYEE_ID, A.SALARY, A.DEPARTMENT_ID FROM EMPLOYEES A JOIN(SELECT MAX(SALARY) 
AS HIGHEST, DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID)B ON 
A.DEPARTMENT_ID = B.DEPARTMENT_ID AND A.SALARY= B.HIGHEST;

Q3. Find the 3rd Maximum Salary for each department based on employee table data?

SELECT EMPID,EMPNAME,DEPTID,SALARY
 FROM (
SELECT *
      ,RN  = ROW_NUMBER() OVER (PARTITION BY DEPTID ORDER BY SALARY)
      ,CNT = SUM(1) OVER (PARTITION BY DEPTID)
 FROM  EMPLOYEE1
      ) A
 WHERE RN = CASE WHEN CNT<3 THEN CNT ELSE 3 END

Q4. Find employees born after 1990?

SELECT * FROM EMPLOYEE WHERE DOB>='1991-01-01';

Q5. How to get the employees with their managers using self join?

SELECT e.first_name, e.EMPLOYEE_ID, m.first_name  as manager, e.MANAGER_ID
FROM employees e, employees m
WHERE e.MANAGER_ID = m.EMPLOYEE_ID

Q6. Select the count of employees in each department?

select COUNT(*),DEPARTMENT_ID from employees group by DEPARTMENT_ID;

Q7. Select the count of employees in each department whose salary is greater than 20000?

SELECT COUNT(*),DEPARTMENT_ID  FROM EMPLOYEES WHERE SALARY>5000 GROUP BY DEPARTMENT_ID

Q8.

Tags