New Arrival of Clothing for all, Big brands at affordable prices. Don't forget to check the daily deals!
ShowOff your Trendy self. Discover the fashionista in You. Clothing For the more confident YOU 👍
SQL Interview Question: How to find the Second Highest Salary Of an Employee in SQL database?
Answer: SQL is a language used in programming and designed for managing data in a relational database management system (RDBMS).
Must Read : How to do mobile test automation?
Select distinct salary from Empoyee e1 where 2 = (select count(distinct salary) from Employee e2 where e1.salary <= e2.salary);
Step 1: So to understand the above query we will start with simple select statement.
Select * from Employee; Output:
Employee_num Employee_name Department Salary 1 Mohan CAD1 920000 2 Gagan CAD2 580000 3 Sumit CAD3 620000
We need to fetch the record where salary is second highest.
Step 2: How to fetch distinct Salary of employees and give the alias to it.
Select distinct Salary from Employee e1;
Output:
Salary 920000 580000 620000
Step 3: How to calculate the Second highest salary. So we need to get the count of all distinct salaries.
Select count(distinct Salary) from Employee e2;
Output:
3
Step 4 : Modify the above query to get the second-highest
Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;
Output:
550000
The above query will give us the second highest salary of the employee.
Note:
1.Distinct keyword:–>The Distinct keyword will eliminate duplicates.
2.Aliases–> Alias is an obfuscation technique to protect the real names of database fields
3.Count()–>Count() function is to count the number of records in the table.