w3resource

SQL Exercise: Employees salary higher than total SALESMAN salaries

SQL subqueries on employee Database: Exercise-13 with Solution

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

13. From the following table, write a SQL query to find those employees whose salary is more than the total remuneration (salary + commission) of the designation SALESMAN. Return complete information about the employees.

Sample table: employees


Sample Solution:

SELECT *
FROM employees
WHERE salary >
    (SELECT max(salary+commission)
     FROM employees
     WHERE job_name = 'SALESMAN');

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
(4 rows)

Explanation:

The given query in SQL that selects all employees who have a salary that is higher than the highest salary+commission earned by any salesman in the company from the 'employees' table.

The subquery in the WHERE clause that selects the maximum "salary+commission" of all employees who have a "job_name" of "SALESMAN". The "max" function is used to find the highest value of "salary+commission" among these employees.

The outer query then selects all employees who have a salary greater than the maximum value obtained from the subquery.

Practice Online


Structure of employee Database:

employee database structure

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

Previous SQL Exercise: Designation or salaries exceed match Marker or Adelyn's.
Next SQL Exercise: Employees, senior to BLAZE and work at PERTH, BRISBANE.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.