SQL subqueries on employee Database: Find the name and designation of the employees who earns a commission and salary is the maximum

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)

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 receive a salary higher than the average salary of their department. Sort the result-set in ascending order by department ID. Return employee name, salary, and department ID.
Next: From the following table, write a SQL query to find those employees who do not work in the department 1001 but work in the same designation and salary as the employees in department 3001. Return employee name, job name and salary.