SQL subqueries on employee Database: List the department id, name, designation, salary, and net salary of the employees only who gets a commission and earn the second highest earnings

SQL subqueries on employee Database: Exercise-57 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

57. From the following table, write a SQL query to find those employees who gets a commission and earn the second highest net salary (salary + commission). Return department id, employee name, designation, salary, and net salary.

Sample table: employees

Sample Solution:

SELECT dep_id,
       emp_name,
       salary,
       job_name,
       salary+commission "Net Salary"
FROM employees e
WHERE 2-1 = (
  SELECT count(DISTINCT emp.salary+emp.commission)
  FROM employees emp WHERE emp.salary+emp.commission>e.salary+e.commission);

Sample Output:

 dep_id | emp_name | salary  | job_name | Net Salary
--------+----------+---------+----------+------------
   3001 | ADELYN   | 1700.00 | SALESMAN |    2100.00
(1 row)

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find those employees who get a commission percent and works as a SALESMAN and earn maximum net salary. Return department ID, name, designation, salary, and net salary (salary+ commission).
Next: From the following table, write a SQL query to find those departments where the average salary is less than the averages for all departments. Return department ID, average salary.