SQL Exercise: List employees whose netpay is more than others

SQL subqueries on employee Database: Exercise-40 with Solution

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

40. From the following table, write a SQL query to find those employees whose net pay is more than any other employee receive. Return employee name, salary, and commission.

Sample table: employees

Sample Solution:

SELECT e.emp_name,
       e.salary,
       e.commission,

  (SELECT sum(salary+commission)
   FROM employees) NETPAY
FROM employees e
WHERE
    (SELECT sum(salary+commission)
     FROM employees) > ANY
    (SELECT salary
     FROM employees
     WHERE emp_id =e.emp_id) ;

Sample Output:

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

Explanation:

The said query in SQL that returns employee's name, salary, commission, and the total net pay of all employees from the 'employees' table, only if at least one employee in the company has a higher salary than the employee being evaluated.

The subquery calculates the total net pay by summing the "salary" and "commission" columns from the 'employees' table.

The "ANY" operator within the subquery in the WHERE clause checks whether the employee being evaluated has a salary that is less than at least one other employee's salary in the company.

This subquery that selects the "salary" column from the 'employees' table for the employee whose "emp_id" matches the current employee being evaluated by the outer SELECT statement.

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 managers, not working under the PRESIDENT.
Next SQL Exercise: Number of employees equals to department length.