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;
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)
___________________________________________________________
