How to honour minute highest or minute maximum salary of an Employee is i of the most oftentimes asked SQL interview enquiry similar to finding duplicate records inward table in addition to when to role truncate vs delete. There are many ways to honour minute highest salary based upon which database y'all are using every bit dissimilar database provides dissimilar characteristic which tin laissez passer on the sack last used to honour second maximum or Nth maximum salary of employee. Well this enquiry tin laissez passer on the sack also last generalized amongst other scenario similar finding minute maximum historic catamenia etc. In this SQL tutorial nosotros volition encounter dissimilar illustration of SELECT SQL query to honour minute highest salary independent of databases or y'all may telephone telephone inward ANSI SQL in addition to other SQL queries which uses database specific characteristic to honour minute maximum salary.
SQL query to honour minute maximum salary of Employee
In this department nosotros volition write SQL query to acquire minute highest salary of Employee. Before writing query its practiced to last familiar amongst schema every bit good every bit information inward table. Here is the Employee tabular array nosotros volition last 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 y'all expect data, y'all volition honour that minute maximum salary inward this instance is 6000 in addition to employee elevate is Henry. Now let’s encounter to a greater extent than or less SQL illustration to honour out this minute maximum salary.
Second maximum salary using sub query in addition to IN clause
Sub queries inward SQL are bang-up tool for this sort of scenario, hither nosotros get-go pick out maximum salary in addition to and hence to a greater extent than or less other maximum excluding trial of subquery. To larn to a greater extent than close Subquery see correlate in addition to non-correlate subquery inward 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 to a greater extent than or less other SQL query to honour 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 inward a higher house SQL illustration volition run on all database including Oracle, MySQL, Sybase in addition to SQL Server every bit they are written using criterion SQL keywords. But sometime y'all tin laissez passer on the sack also role database specific features similar TOP keyword of SQL Server or Sybase database to honour 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 pick out spill out tape or row of whatever trial set, past times carefully using TOP keyword y'all tin laissez passer on the sack honour 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 honour out spill out ii salary from Employee table in addition to listing them inward descending order, Now minute highest salary of employee is at spill out hence only accept that value. Though y'all demand to give-up the ghost on inward hear of using distinct keyword if at that topographic point are to a greater extent than than i employee amongst spill out salary, because inward that instance same salary volition last repeated in addition to TOP ii 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 sure enough rows from trial set. If y'all expect 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 honour minute highest salary of Employee using SQL query. This is practiced enquiry which actually attempt out your SQL knowledge, its non tough but definitely tricky for beginners. As follow upwardly enquiry y'all tin laissez passer on the sack enquire him to honour 3rd maximum salary or Nth maximum salary every bit well.
P. S. - Are y'all gain for Interview? Take TripleByte's quiz in addition to acquire straight to the terminal circular of interviews amongst spill out tech companies similar Coursera, Adobe, Dropbox, Grammarly, Uber, Quora, Evernote, Twitch, in addition to many more.