SQL Exercise: Display the total salary of employees with grade 3

SQL subqueries on employee Database: Exercise-27 with Solution

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

27. From the following table, write a SQL query to compute the total salary of employees of grade 3. Return total salary.

Sample table: employees

Sample table: salary_grade

Sample Solution:

SELECT sum(salary)
FROM employees
WHERE emp_id IN
    (SELECT emp_id
     FROM employees e,
          salary_grade s
     WHERE e.salary BETWEEN s.min_sal AND s.max_sal
       AND s.grade = 3);

Sample Output:

   sum
---------
 3300.00
(1 row)

Explanation:

The said query in SQL that returns the total salary of all the employees from the 'employees' table whose salary falls within the range of salary grade 3.

The subquery selects the "emp_id" of employees who have a salary that falls within the range of salary grade 3.

On the condition that an employee's salary falls within the "min_sal" and "max_sal" range of their salary grade, the query joins the 'employees' and 'salary_grade' tables.

As a result of the outer query, all employees whose "emp_id" is included in the subquery are selected and their salary is calculated.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Find the total salary given to the MANAGER.
Next SQL Exercise: Employees in department 1001, salary higher than 2001.