SQL Exercise: Employees, net pay is greater or equal than any other

SQL subqueries on employee Database: Exercise-73 with Solution

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

73. From the following table, write a SQL query to find those employees whose net pay are higher than or equal to the salary of any other employee in the company. Return employee name, salary, and commission.

Sample table: employees

Sample Solution:

SELECT e.emp_name,
       e.salary,
       e.commission
FROM employees e
WHERE
    (SELECT max(salary+commission)
     FROM employees) > ANY
    (SELECT salary
     FROM employees);

Sample Output:

 emp_name | salary  | commission
----------+---------+------------
 KAYLING  | 6000.00 |
 BLAZE    | 2750.00 |
 CLARE    | 2550.00 |
 JONAS    | 2957.00 |
 SCARLET  | 3100.00 |
 FRANK    | 3100.00 |
 SANDRINE |  900.00 |
 ADELYN   | 1700.00 |     400.00
 WADE     | 1350.00 |     600.00
 MADDEN   | 1350.00 |    1500.00
 TUCKER   | 1600.00 |       0.00
 ADNRES   | 1200.00 |
 JULIUS   | 1050.00 |
 MARKER   | 1400.00 |
(14 rows)

Explanation:

The said query in SQL that retrieves the names, salaries, and commissions of all employees from the 'employees' table whose net pay (salary +commission) is less than the maximum net pay of any employee in the company.

The WHERE clause filters the results to include only those employees whose net pay is less than the maximum net pay of any employee in the company.

The maximum net pay of any employee in the company is obtained by subquery, which selects the max(salary+commission) from the 'employees' table.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List the highest paid employees working under KAYLING.
Next SQL Exercise: Employees who earn more than their managers.