How To Honor 2Nd Highest Or Maximum Salary Of Employee Inwards Sql - Interview Question
How to discovery minute highest or minute maximum salary of an Employee is 1 of the most oft asked SQL interview query similar to finding duplicate records inward table together with when to role truncate vs delete. There are many ways to discovery minute highest salary based upon which database you lot are using equally dissimilar database provides dissimilar characteristic which tin post away move used to discovery second maximum or Nth maximum salary of employee. Well this query tin post away also move generalized amongst other scenario similar finding minute maximum historic menstruum etc. In this SQL tutorial nosotros volition run across dissimilar representative of SELECT SQL query to discovery minute highest salary independent of databases or you lot may telephone band inward ANSI SQL together with 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 expert to move familiar amongst schema equally good equally information inward table. Here is the Employee tabular array nosotros volition move 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 inward this instance is 6000 together with employee elevate is Henry. Now let’s run across roughly SQL representative to discovery out this minute maximum salary.
Second maximum salary using sub query together with IN clause
Sub queries inward SQL are cracking tool for this form of scenario, hither nosotros outset pick out maximum salary together with and hence roughly other maximum excluding consequence of subquery. To larn to a greater extent than close Subquery see correlate together with 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 roughly other SQL query to discovery minute highest salary using subquery together with < 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 representative volition hand all database including Oracle, MySQL, Sybase together with SQL Server equally they are written using touchstone SQL keywords. But sometime you lot tin post away 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 together with SQL Server database is used to pick out move past times tape or row of whatever consequence set, past times carefully using TOP keyword you lot tin post away discovery out minute maximum or Nth maximum salary equally 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 move past times ii salary from Employee table together with listing them inward descending order, Now minute highest salary of employee is at move past times hence only guide keep that value. Though you lot demand to proceed inward take away heed of using distinct keyword if in that place are to a greater extent than than 1 employee amongst move past times salary, because inward that instance same salary volition move repeated together with TOP ii may listing same salary twice.
Second maximum salary using LIMIT keyword of MYSQL database
LIMIT keyword of MySQL database is piddling flake similar amongst TOP keyword of SQL Server database together with allows to guide keep alone for certain rows from consequence set. If you lot await at below SQL example, its really 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 expert query which actually evidence your SQL knowledge, its non tough but definitely tricky for beginners. As follow upward query you lot tin post away inquire him to discovery 3rd maximum salary or Nth maximum salary equally 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
Belum ada Komentar untuk "How To Honor 2Nd Highest Or Maximum Salary Of Employee Inwards Sql - Interview Question"
Posting Komentar