SQL question to seek out second most wage of Worker
On this part, we are going to write SQL queries to get the second highest wage of Workers. Earlier than writing question its good to be accustomed to the schema in addition to knowledge in desk. Right here is the Worker desk we might be utilizing this SQL instance:
mysql> SELECT * FROM Worker;
+——–+———-+———+——–+
| emp_id | emp_name | dept_id | wage |
+——–+———-+———+——–+
| 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 happen to have a look at knowledge, you can see that the second most wage, on this case, is 6000, and the worker title is Henry.
Now let’s see some SQL examples to seek out out this second most wage.
Second most wage utilizing subquery and IN clause
mysql> SELECT max(wage) FROM Worker WHERE wage NOT IN (SELECT max(wage) FROM Worker);
+————-+
| max(wage) |
+————-+
| 6000 |
+————-+
1 row IN SET (0.00 sec)
Right here is one other SQL question to seek out second highest wage utilizing subquery and < operator as an alternative of IN clause:
mysql> SELECT max(wage) FROM Worker WHERE wage < (SELECT max(wage) FROM Worker);
+————-+
| max(wage) |
+————-+
| 6000 |
+————-+
1 row IN SET (0.00 sec)
Second highest wage utilizing the TOP key phrase of Sybase or SQL Server database
TOP key phrase of Sybase and SQL Server database is used to pick out high document or row of any outcome set, by rigorously utilizing TOP key phrase you’ll find out second most or Nth most wage as proven beneath.
SELECT TOP 1 wage FROM ( SELECT TOP 2 wage FROM staff ORDER BY wage DESC) AS emp ORDER BY wage ASC
Here’s what this SQL question is doing : First discover out high 2 wage from Worker desk and checklist them in descending order, Now second highest wage of worker is at high so simply take that worth.
Second most wage utilizing LIMIT key phrase of MYSQL database
LIMIT key phrase of MySQL database is little bit related with TOP key phrase of SQL Server database and permits to take solely sure rows from outcome set. If you happen to have a look at beneath SQL instance, its very a lot much like SQL Server TOP key phrase instance.
mysql> SELECT wage FROM (SELECT wage FROM Worker ORDER BY wage DESC LIMIT 2) AS emp ORDER BY wage LIMIT 1;
+——–+
| wage |
+——–+
| 6000 |
+——–+
1 row IN SET (0.00 sec)
That’s on Methods to discover the second highest wage of Worker utilizing SQL question. That is good query which actually check your SQL information, its not robust however positively tough for newcomers. As observe up query you possibly can ask him to seek out third most wage or Nth most wage as nicely.
Different SQL Interview Query solutions it’s possible you’ll like
