SQL Exercise: Employees who get commissions, second highest salary

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)

Explanation:

The given query in SQL that selects the department ID, employee name, salary, job title, and net salary of employees from the 'employees' table whose net salary is not unique.

The WHERE clause filters only those rows where the expression 2-1 is equal to the count of distinct net salaries (salary+commission) of all employees in the 'employees' table who have a net salary greater than the net salary of the current employee being evaluated (e.salary+e.commission).

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List SALESMAN who are earning maximum net salary.
Next SQL Exercise: Department average salaries less than averages of all.