How To Discovery Mo Highest Or Maximum Salary Of Employee Inward Sql - Interview Question
How to notice instant highest or instant maximum salary of an Employee is ane of the most oftentimes asked SQL interview inquiry similar to finding duplicate records inward table in addition to when to role truncate vs delete. There are many ways to notice instant highest salary based upon which database you lot are using every bit unlike database provides unlike characteristic which tin forcefulness out move used to notice second maximum or Nth maximum salary of employee. Well this inquiry tin forcefulness out also move generalized alongside other scenario similar finding instant maximum historic catamenia etc. In this SQL tutorial nosotros volition run across unlike illustration of SELECT SQL query to notice instant highest salary independent of databases or you lot may telephone telephone inward ANSI SQL in addition to other SQL queries which uses database specific characteristic to notice instant maximum salary.
SQL query to notice instant maximum salary of Employee
In this department nosotros volition write SQL query to acquire instant highest salary of Employee. Before writing query its expert to move familiar alongside schema every bit good every bit 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 notice that instant maximum salary inward this instance is 6000 in addition to employee cite is Henry. Now let’s run across closed to SQL illustration to notice out this instant maximum salary.
Second maximum salary using sub query in addition to IN clause
Sub queries inward SQL are slap-up tool for this form of scenario, hither nosotros outset conduct maximum salary in addition to and thus closed to other maximum excluding lawsuit of subquery. To larn to a greater extent than almost 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 closed to other SQL query to notice instant 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 upwards SQL illustration volition plough over off all database including Oracle, MySQL, Sybase in addition to SQL Server every bit they are written using touchstone SQL keywords. But sometime you lot tin forcefulness out also role database specific features similar TOP keyword of SQL Server or Sybase database to notice out instant 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 conduct exceed tape or row of whatever lawsuit set, yesteryear carefully using TOP keyword you lot tin forcefulness out notice out instant 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 notice out exceed two salary from Employee table in addition to listing them inward descending order, Now instant highest salary of employee is at exceed thus merely accept that value. Though you lot demand to proceed inward hear of using distinct keyword if at that topographic point are to a greater extent than than ane employee alongside exceed salary, because inward that instance same salary volition move 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 footling chip similar alongside TOP keyword of SQL Server database in addition to allows to accept entirely certainly rows from lawsuit 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 notice instant highest salary of Employee using SQL query. This is expert inquiry which actually essay out your SQL knowledge, its non tough but definitely tricky for beginners. As follow upwards inquiry you lot tin forcefulness out enquire him to notice tertiary maximum salary or Nth maximum salary every bit well.
Further Learning
Introduction to SQL yesteryear Jon Flemish region
Introduction to SQL Server
Head First SQL
Other SQL Interview Question answers you lot may like
0 Response to "How To Discovery Mo Highest Or Maximum Salary Of Employee Inward Sql - Interview Question"
Post a Comment