SQL Exercise: Total remuneration of all sales person of a department

SQL subqueries on employee Database: Exercise-69 with Solution

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

69. From the following tables, write a SQL query to find compute the total remuneration (salary + commission) of all sales person of MARKETING department. Return complete information about the employees.

Sample table: employees

Sample table: department

Sample Solution:

SELECT *
FROM employees e
WHERE salary+commission IN
    (SELECT salary+commission
     FROM employees e,
          department d
     WHERE e.dep_id=d.dep_id
       AND d.dep_name = 'MARKETING'
       AND e.job_name = 'SALESMAN');

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
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
(4 rows)

Explanation:

The said query in SQL that retrieves all the records from the 'employees' table where the sum of the salary and commission is present in the subquery that selects the sum of the salary and commission for all employees in the Marketing department with the job_name 'SALESMAN'.

The subquery includes a join with the department table to filter only employees in the Marketing department.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Employees whose salary is same as any one employee.
Next SQL Exercise: Most recently hired employees of department 3001.