Consider the following EMP table and answer the question below: Which of the…
2013
Consider the following EMP table and answer the question below: Which of the following select statement should be executed if we need to display the average salary of employees who belong to grade "E4"?
- A.
select avg(salary) from EMP whose grade = "E4";
- B.
select avg(salary) from EMP having grade = "E4";
- C.
select avg(salary) from EMP group by grade where grade = "E4";
- D.
select avg(salary) from EMP group by grade having grade = "E4";
Attempted by 1216 students.
Show answer & explanation
Correct answer: D
Correct query (simplest and preferred):
select avg(salary) from EMP where grade = 'E4';
Why this works:
WHERE filters rows before aggregation. Here we want the average salary of only those rows whose grade is E4, so filtering first with WHERE and then computing AVG is straightforward and efficient.
HAVING filters groups after aggregation and is typically used together with GROUP BY. An alternative valid query is:
select avg(salary) from EMP group by grade having grade = 'E4';
Common errors in the other statements:
Using a non-SQL word such as "whose" is invalid; use WHERE instead.
Placing WHERE after GROUP BY is incorrect; the correct clause order is FROM, WHERE, GROUP BY, HAVING, ORDER BY.
Using HAVING without GROUP BY is misleading; HAVING is intended to filter aggregated groups.
Summary:
Best practice for this question: select avg(salary) from EMP where grade = 'E4';
Alternatively, grouping then filtering is valid: select avg(salary) from EMP group by grade having grade = 'E4'; but it is not necessary here.
A video solution is available for this question — log in and enroll to watch it.