Question

Given a table employees(id, dept_id, salary), which query returns departments with average salary greater than overall average across the company?

Use GROUP BY and HAVING to compare group avg to overall avg computed via subquery.

A SELECT dept_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

B SELECT dept_id FROM employees GROUP BY dept_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

C SELECT dept_id FROM employees GROUP BY dept_id WHERE AVG(salary) > (SELECT AVG(salary) FROM employees);

D SELECT dept_id FROM employees WHERE AVG(salary) > (SELECT AVG(salary) FROM employees) GROUP BY dept_id;

E SELECT dept_id FROM employees GROUP BY dept_id HAVING AVG(salary) > AVG(salary);

More SQL Questions