Aggregation & GROUP BY · SQL SQL
Consider a table named 'employees' with columns 'department' and 'salary'. Which of the following queries correctly returns the average salary for ea…
Practice question from Aggregation & GROUP BY (SQL SQL), with the correct answer and a step-by-step solution.
medium · MCQ · Q-5F2070
Consider a table named 'employees' with columns 'department' and 'salary'. Which of the following queries correctly returns the average salary for each department, but only for departments where the average salary is greater than 60000?
- A.SELECT department, AVG(salary) FROM employees WHERE AVG(salary) > 60000 GROUP BY department;
- B.SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;Correct answer
- C.SELECT department, AVG(salary) FROM employees GROUP BY department WHERE AVG(salary) > 60000;
- D.SELECT department, AVG(salary) FROM employees HAVING salary > 60000 GROUP BY department;
Solution
Option B is correct because aggregate functions like AVG() cannot be used inside a WHERE clause (as WHERE runs before aggregation). They must be evaluated in a HAVING clause that follows the GROUP BY clause.
More from Aggregation & GROUP BY
- What is the primary purpose of the SQL HAVING clause in an aggregation query?
- In standard SQL (ANSI SQL), if an aggregate function such as SUM() or COUNT() is used in a SELECT list, what rule applies to any other non-…
- How do standard SQL aggregate functions (such as COUNT, SUM, AVG) handle NULL values in their input columns?
- What will be the output of COUNT() versus COUNT(columnname) when the specified column contains several NULL values?
Practise more SQL
Sign up free for adaptive practice on this chapter, with instant feedback and hints on every question.
Start practising freeFree to start · No credit card needed