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

More IT Operating System Questions

What is a key characteristic of a "full binary tree"?
Consider the following Java code: public class SubstringDryRun { public static void main(String[] args) { String s = "Programming"; ...
If signal-to-interference ratio is 5 dB, signal power is 8 dB, then what is the interference power in dB?
Which of the following statements best describes the concept of a computing model in computer science?
Which statement is TRUE regarding Norton theorem?
The latency of a network is
What is the main purpose of disk scheduling in an operating system?
Which of the following is the primary function of an Operating System?
What is the time complexity for accessing an element at a specific index in an array?
Fill the blank In K-Means algorithm, we calculate the distance between each point of the dataset to every ________ initialized.

Hey! Ask a query

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?

Detailed Solution

More IT Operating System Questions

Hey! Ask a query

Please Register/Login