Salary related query interview question

emp_salary

Ques:- Maximum salary of the employee?
Ans:-

select max(salary) from employee;
Output:- 4000

Ques:- Second highest Maximum salary of the employee?
Ans:- We can get through sub-query, limit etc.
(i) Through Sub-query

SELECT max(salary) FROM `employee` where salary<(select max(salary) from employee)
Output:- 3000

(ii) Through Limit

SELECT salary FROM `employee` Order by salary desc limit 1,1
Output:- 4000

In this eg. limit is not work correctly because, salary may be same more than one person so its not good for use, it can use if employee salary is unique.

if Interviewer ask, how many person get second highest salary or third highest salary or fourth highest salary then you can use simple query method

SELECT salary FROM `employee` emp_a where (n-1)= (select count(distinct(salary)) from employee emp_b where emp_b.salary>emp_a.salary)

where n is use for number of highest salary

if you want to get second highest salary then

SELECT salary FROM `employee` emp_a where (2-1)= (select count(distinct(salary)) from employee emp_b where emp_b.salary>emp_a.salary)
Output:- 3000
3000

In this example 2 employee has second highest salary

if you want to get third highest salary then

SELECT salary FROM `employee` emp_a where (3-1)= (select count(distinct(salary)) from employee emp_b where emp_b.salary>emp_a.salary)
Output:- 2000