How To Discovery Instant Highest Or Maximum Salary Of Employee Inward Sql - Interview Question
How to honor minute highest or minute maximum salary of an Employee is i of the most oft asked SQL interview query similar to finding duplicate records inwards table in addition to when to role truncate vs delete. There are many ways to honor minute highest salary based upon which database y'all are using equally dissimilar database provides dissimilar characteristic which tin survive used to honor second maximum or Nth maximum salary of employee. Well this query tin also survive generalized amongst other scenario similar finding minute maximum historic menses etc. In this SQL tutorial nosotros volition encounter dissimilar instance of SELECT SQL query to honor minute highest salary independent of databases or y'all may telephone telephone inwards ANSI SQL in addition to other SQL queries which uses database specific characteristic to honor minute maximum salary.
SQL query to honor 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 survive familiar amongst schema equally good equally information inwards table. Here is the Employee tabular array nosotros volition survive 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 hold off data, y'all volition honor that minute maximum salary inwards this instance is 6000 in addition to employee parent is Henry. Now let’s encounter roughly SQL instance to honor out this minute maximum salary.
Second maximum salary using sub query in addition to IN clause
Sub queries inwards SQL are bully tool for this sort of scenario, hither nosotros kickoff lead maximum salary in addition to thence roughly other maximum excluding lawsuit of subquery. To acquire to a greater extent than near 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 honor 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 inwards a higher house SQL instance volition lead house all database including Oracle, MySQL, Sybase in addition to SQL Server equally they are written using measure SQL keywords. But sometime y'all tin also role database specific features similar TOP keyword of SQL Server or Sybase database to honor 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 lead exceed tape or row of whatever lawsuit set, past times carefully using TOP keyword y'all tin honor 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 honor out exceed two salary from Employee table in addition to listing them inwards descending order, Now minute highest salary of employee is at exceed thence only accept that value. Though y'all demand to continue inwards hear of using distinct keyword if at that spot are to a greater extent than than i employee amongst exceed salary, because inwards that instance same salary volition survive 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 fleck similar amongst TOP keyword of SQL Server database in addition to allows to accept solely sure as shooting rows from lawsuit set. If y'all hold off 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 honor minute highest salary of Employee using SQL query. This is skilful query which actually attempt out your SQL knowledge, its non tough but definitely tricky for beginners. As follow upwards query y'all tin inquire him to honor tertiary 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 y'all may like
0 Response to "How To Discovery Instant Highest Or Maximum Salary Of Employee Inward Sql - Interview Question"
Post a Comment