Oracle: range of standard deviation of salary in correlated subquery

May 20, 2022

I am using an Oracle Developer Database and I have the two tables, Employees and Departments :

I am supposed to find (using a correlated subquery) for each department all the employees, which have a salary within the range of the salary-standard deviation of every single department.

This is what I have tried so far:

SELECT d.department_name, e.employee_id, e.last_name, AVG(e.salary), e.salary
FROM hr.employees e
JOIN hr.departments d
on e.department_id = d.department_id
WHERE e.salary IN 
(SELECT salary  
FROM hr.employees 
WHERE salary > 
(SELECT ROUND(AVG(e.salary)-STDDEV(e.salary),2)
         FROM hr.employees) AND salary < (SELECT ROUND(AVG(e.salary) + STDDEV(e.salary),2) FROM hr.employees)
GROUP BY d.department_name,
e.employee_id, e.last_name, e.salary;

Even though this query has the correct syntax, it does not show any result.

This other (partial) approach works but how can I query at the same time the employee table?

SELECT d.department_name, e.department_id, ROUND(AVG(e.salary) + STDDEV(e.salary), 2) 
AS standard_deviation_max,
ROUND(AVG(e.salary) - STDDEV(e.salary), 2) 
AS standard_deviation_min
FROM hr.employees e
JOIN hr.departments d
on e.department_id = d.department_id
GROUP BY department_name, e.department_id;

Since I am new to SQL, I would really appreciate any hint. Thank you in advance

>Solution :

You can use your query to find the employee details and then correlate the salary to the average ± the standard deviation:

SELECT d.department_name,
       e.employee_id,
       e.last_name,
       e.salary
FROM   hr.employees e
       JOIN hr.departments d
       on e.department_id = d.department_id
WHERE  EXISTS (
         SELECT 1
         FROM   hr.employees x
         HAVING e.salary BETWEEN AVG(e.salary) + STDDEV(e.salary)
                             AND AVG(e.salary) + STDDEV(e.salary)
       );

or, you can use analytic functions:

SELECT department_name,
       employee_id,
       last_name,
       salary
FROM   (
  SELECT d.department_name,
         e.employee_id,
         e.last_name,
         e.salary,
         AVG(e.salary) OVER () AS avg_salary,
         STDDEV(e.salary) OVER () AS stddev_salary
  FROM   hr.employees e
         JOIN hr.departments d
         on e.department_id = d.department_id
)
WHERE  salary BETWEEN avg_salary - stddev_salary
                  AND avg_salary + stddev_salary;