Nested queries in SQL from hr.employees-CodePudding

I have a task like this: Using the HR.EMPLOYEES table, build a list of employees whose work experience in the company is below the average.

I tried to do this, but an error occurs, how can I rewrite the request correctly?

select first_name, last_name from hr.empolyees 
where MONTHS_BETWEEN(sysdate, hire_date) < (select avg(MONTHS_BETWEEN(sysdate, hire_date) from hr.employees)

CodePudding user response：

As mentioned in the comments, it looks like the AVG function is missing a closing parenthesis.

SELECT first_name, 
       last_name 
FROM hr.empolyees 
WHERE MONTHS_BETWEEN(sysdate, hire_date) < (SELECT AVG(MONTHS_BETWEEN(sysdate, hire_date)) FROM hr.employees)

CodePudding user response：

To rewrite the query correctly, you can use the AVG function in the SELECT clause to calculate the average work experience, and then use the HAVING clause to filter the result set to include only employees whose work experience is below the average.

The modified query would look like this:

SELECT first_name, last_name FROM hr.employees GROUP BY first_name, last_name HAVING MONTHS_BETWEEN(sysdate, hire_date) < AVG(MONTHS_BETWEEN(sysdate, hire_date))

In this query, the AVG function is used in the SELECT clause to calculate the average work experience of all employees in the HR.EMPLOYEES table. The HAVING clause is then used to filter the result set to include only those employees whose work experience is less than the average.

Note that the GROUP BY clause is used to group the rows by first_name and last_name, so that the AVG function calculates the average work experience for each employee. This is necessary because the HAVING clause operates on the grouped rows, not on the individual rows in the table.

If you want to include only employees whose work experience is strictly less than the average (not equal to the average), you can use the < operator in the HAVING clause instead of the <= operator. For example:

SELECT first_name, last_name FROM hr.employees GROUP BY first_name, last_name HAVING MONTHS_BETWEEN(sysdate, hire_date) < AVG(MONTHS_BETWEEN(sysdate, hire_date))

This query will return a list of employees whose work experience in the company is strictly below the average.

EDIT

SELECT first_name, last_name FROM hr.employees WHERE MONTHS_BETWEEN(sysdate, hire_date) < (SELECT AVG(MONTHS_BETWEEN(sysdate, hire_date)) FROM hr.employees)

In this query, the subquery in the SELECT clause calculates the average work experience for all employees in the HR.EMPLOYEES table. The outer query then uses the result of this subquery to filter the result set to include only those employees whose work experience is less than the average.

Note that the subquery must be enclosed in parentheses, and it must be followed by the < operator in the WHERE clause. This is necessary to ensure that the subquery is executed first, and the result is used to filter the result set of the outer query.

If you want to include only employees whose work experience is strictly less than the average (not equal to the average), you can use the < operator in the WHERE clause instead of the <= operator. For example:

SELECT first_name, last_name FROM hr.employees WHERE MONTHS_BETWEEN(sysdate, hire_date) < (SELECT AVG(MONTHS_BETWEEN(sysdate, hire_date)) FROM hr.employees)

This query will return a list of employees whose work experience in the company is strictly below the average.