Important SQL Queries related to Employee table

Important SQL Queries related to Employee table ( highest salary, second highest salary, etc )

Write a query to get the details of emp where ename can start with any character from A to S

SELECT * FROM EMP WHERE ENAME LIKE ‘[A-S]%’                                                                                                                                                                                
Write a query to get the highest salary from each department

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO

Write a query to get the second highest salary from the Employee table.

Without using LIMIT:

SELECT MAX(salary) FROM Employee 

WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

SELECT MAX(Salary) From Employee

WHERE Salary < ( SELECT Max(Salary) FROM Employee);

With using LIMIT:

SELECT * FROM Employee

ORDER BY salary DESC LIMIT 1,1;

SQL Query to select Nth highest salary

SELECT salary 

FROM Employee

ORDER BY salary desc limit n-1,1

The following query will return 3rd highest salary

SELECT salary 

FROM Employee

ORDER BY salary desc limit 2,1

 Second Highest Salary or Nth Highest Salary With using dense_rank():

SELECT * FROM(

SELECT ename, sal, dense_rank() 

over(order by sal desc)r FROM Employee

) WHERE r=&n;

-- ----------------

SELECT * FROM(

SELECT ename, sal, dense_rank() 

over(order by sal desc)r FROM Employee

) WHERE r=2;

Write a query to get the highest salary from each job in each dept

SELECT DEPTNO, JOB, MAX(SAL) FROM EMP GROUP BY DEPTNO,JOB

Write a query to display no. of clerks present in each department.

SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=‘CLERK’

Write a query to get details of employees who is getting the highest salary in each department

SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)

Write a query to get the details of the employee who is getting the highest salary in deptno 10.

SELECT * FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10)

___________________________________________________________

See More Popular Posts:

C# Interview Questions  

OOPS Intrview Questions:

Useful Shortcut: for windows

Go To Home Page