Question

Given tables Employees(emp_id, name, dept_id, salary) and Departments(dept_id, dept_name), which query correctly returns department names having an average salary greater than the overall average salary across all employees?

Q: Answer-Given tables Employees(emp_id, name, dept_id, salary) and Departments(dept_id, dept_name), which que...

Option A incorrectly tries to use AVG in a WHERE clause on a table with no salary column and without groupingjoining. Option C incorrectly places WHERE after GROUP BY should be HAVING, and appears after GROUP BY, not before. Correct syntax requires JOIN the two tables, GROUP BY department, then filter grouped results using HAVING with a subquery computing the overall average exactly what option E does.

A SELECT dept_name FROM Departments WHERE AVG(salary) > (SELECT AVG(salary) FROM Employees);

B SELECT dept_name FROM Departments HAVING salary > AVG(salary);

C SELECT dept_name FROM Departments GROUP BY dept_name WHERE AVG(salary) > (SELECT AVG(salary) FROM Employees);

D SELECT dept_name, AVG(salary) FROM Employees WHERE salary > AVG(salary);

E SELECT d.dept_name FROM Departments d JOIN Employees e ON d.dept_id=e.dept_id GROUP BY d.dept_name HAVING AVG(e.salary) > (SELECT AVG(salary) FROM Employees);

Practice Next