Nested subqueries in SQL from hr.employees-CodePudding

There is a task: using the HR.EMPLOYEES table, get a list of departments in which the average work experience is above the average for the entire company.

I tried to implement it this way, I know that the request is not correct, but I don’t understand how to distribute it to the entire company

select department_id 
from hr.employees
where avg(MONTHS_BETWEEN(sysdate, hire_date)) > (select hire_date from hr.employees where avg(MONTHS_BETWEEN(sysdate, hire_date))

The database looks like this:

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
    100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000

CodePudding user response：

SELECT department
FROM HR.EMPLOYEES
GROUP BY department
HAVING AVG(work_experience) > (SELECT AVG(work_experience) FROM HR.EMPLOYEES)

Edit 1: As OP requested to provide him a solution using nested subqueries, here is version 2:

SELECT department
FROM HR.EMPLOYEES
WHERE department IN (SELECT department FROM HR.EMPLOYEES
GROUP BY department 
HAVING AVG(work_experience) > (SELECT AVG(work_experience) 
FROM HR.EMPLOYEES))

Edit 2: As OP asked to remove the repetitions, here is the version 3:

SELECT DISTINCT department
FROM HR.EMPLOYEES
WHERE department IN (SELECT department FROM HR.EMPLOYEES
GROUP BY department
HAVING AVG(work_experience) > (SELECT AVG(work_experience) 
FROM HR.EMPLOYEES))