SQL Exercise: Employees who earn a commission and maximum salary

SQL subqueries on employee Database: Exercise-54 with Solution

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

54. From the following table, write a SQL query to find those employees who earn a commission and receive maximum salary. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE salary =
    (SELECT max(salary)
     FROM employees
     WHERE commission IS NOT NULL);

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
(1 row)

Explanation:

The said query in SQL that selects all columns from the 'employees' table where the salary is equal to the maximum salary of employees who have a non-NULL commission value.

The subquery inside the WHERE clause finds the maximum salary among the employees who have a commission value that is not NULL. If there are no such employees, the subquery returns NULL, and the outer query will not return any rows.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Employees salary exceed department average, ASC order.
Next SQL Exercise: List employees not in the department 1001 but in 3001.