How To Notice 2Nd Highest Or Maximum Salary Of Employee Inwards Sql - Interview Question
Friday, June 15, 2018
Add Comment
How to discovery minute highest or minute maximum salary of an Employee is i of the most oft asked SQL interview inquiry similar to finding duplicate records inwards table in addition to when to role truncate vs delete. There are many ways to discovery minute highest salary based upon which database you lot are using every bit dissimilar database provides dissimilar characteristic which tin give the axe live on used to discovery second maximum or Nth maximum salary of employee. Well this inquiry tin give the axe also live on generalized amongst other scenario similar finding minute maximum historic menstruation etc. In this SQL tutorial nosotros volition encounter dissimilar illustration of SELECT SQL query to discovery minute highest salary independent of databases or you lot may telephone band inwards ANSI SQL in addition to other SQL queries which uses database specific characteristic to discovery minute maximum salary.
SQL query to discovery minute maximum salary of Employee
In this department nosotros volition write SQL query to acquire minute highest salary of Employee. Before writing query its skilful to live on familiar amongst schema every bit good every bit information inwards table. Here is the Employee tabular array nosotros volition live on using this SQL example: mysql> SELECT * FROM Employee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
| 1 | James | 10 | 2000 |
| 2 | Jack | 10 | 4000 |
| 3 | Henry | 11 | 6000 |
| 4 | Tom | 11 | 8000 |
+--------+----------+---------+--------+
4 rows IN SET (0.00 sec)
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
| 1 | James | 10 | 2000 |
| 2 | Jack | 10 | 4000 |
| 3 | Henry | 11 | 6000 |
| 4 | Tom | 11 | 8000 |
+--------+----------+---------+--------+
4 rows IN SET (0.00 sec)
If you lot await data, you lot volition discovery that minute maximum salary inwards this instance is 6000 in addition to employee advert is Henry. Now let’s encounter roughly SQL illustration to discovery out this minute maximum salary.
Second maximum salary using sub query in addition to IN clause
Sub queries inwards SQL are swell tool for this variety of scenario, hither nosotros commencement choose maximum salary in addition to thence roughly other maximum excluding resultant of subquery. To larn to a greater extent than virtually Subquery see correlate in addition to non-correlate subquery inwards SQL
mysql> SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)
Here is roughly other SQL query to discovery minute highest salary using subquery in addition to < operator instead of IN clause:
mysql> SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)
Both of higher upward SQL illustration volition grade off all database including Oracle, MySQL, Sybase in addition to SQL Server every bit they are written using criterion SQL keywords. But sometime you lot tin give the axe also role database specific features similar TOP keyword of SQL Server or Sybase database to discovery out minute highest salary of Employee.
Second highest salary using TOP keyword of Sybase or SQL Server database
TOP keyword of Sybase in addition to SQL Server database is used to choose transcend tape or row of whatever resultant set, past times carefully using TOP keyword you lot tin give the axe discovery out minute maximum or Nth maximum salary every bit shown below.
SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employees ORDER BY salary DESC) AS emp ORDER BY salary ASC
Here is what this SQL query is doing : First discovery out transcend two salary from Employee table in addition to listing them inwards descending order, Now minute highest salary of employee is at transcend thence simply accept that value. Though you lot demand to proceed inwards hear of using distinct keyword if at that spot are to a greater extent than than i employee amongst transcend salary, because inwards that instance same salary volition live on repeated in addition to TOP two may listing same salary twice.
Second maximum salary using LIMIT keyword of MYSQL database
LIMIT keyword of MySQL database is lilliputian flake similar amongst TOP keyword of SQL Server database in addition to allows to accept exclusively surely rows from resultant set. If you lot await at below SQL example, its real much similar to SQL Server TOP keyword example.
mysql> SELECT salary FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;
+--------+
| salary |
+--------+
| 6000 |
+--------+
1 row IN SET (0.00 sec)
+--------+
| salary |
+--------+
| 6000 |
+--------+
1 row IN SET (0.00 sec)
That’s on How to discovery minute highest salary of Employee using SQL query. This is skilful inquiry which actually attempt out your SQL knowledge, its non tough but definitely tricky for beginners. As follow upward inquiry you lot tin give the axe enquire him to discovery 3rd maximum salary or Nth maximum salary every bit well.
Further Learning
Introduction to SQL past times Jon Flemish region
Introduction to SQL Server
Head First SQL
Other SQL Interview Question answers you lot may like
0 Response to "How To Notice 2Nd Highest Or Maximum Salary Of Employee Inwards Sql - Interview Question"
Post a Comment