Multiple choice technology databases

The following statement will raise an exception on which line? select dept_name, avg(all salary) ,count() “number of employees” from emp , dept where deptno = dept_no and count() > 5 group by dept_name order by 2 desc;

  1. select dept_name, avg(all salary), count(*) “number of employees"

  2. where deptno = dept_no

  3. and count(*) > 5

  4. group by dept_name

  5. order by 2 desc;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Aggregate functions like COUNT() cannot be used in the WHERE clause - they must appear in HAVING after GROUP BY. The line 'and count() > 5' is in the WHERE clause, which causes an error. To filter groups based on aggregate values, use HAVING after GROUP BY. The correct query should move that condition to a HAVING clause. The alias in quotes and the AVG(ALL salary) syntax are valid.

Keep practicing — related questions