w3resource

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:

employee database structure

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.

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.