Question 8Raj, a Database Administrator, needs to display the average pay of workers from those departments which have more than five employees. He is experiencing a problem while running the following query :SELECT DEPT, AVG(SAL) FROM EMP WHERE COUNT(*) 5 GROUP BY DEPT;Which of the following is a correct query to perform the given task ?(i) SELECT DEPT, AVG(SAL) FROM EMP WHERE COUNT(*) 5 GROUP BY DEPT;(ii) SELECT DEPT, AVG(SAL) FROM EMP HAVING COUNT(*) 5 GROUP BY DEPT;(iii) SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT WHERE COUNT(*) 5;(iv) SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT HAVING COUNT(*) 5;

Question

Question 8Raj, a Database Administrator, needs to display the average pay of workers from those departments which have more than five employees. He is experiencing a problem while running the following query :SELECT DEPT, AVG(SAL) FROM EMP WHERE COUNT(*) > 5 GROUP BY DEPT;Which of the following is a correct query to perform the given task ?(i) SELECT DEPT, AVG(SAL) FROM EMP WHERE COUNT(*) > 5 GROUP BY DEPT;(ii) SELECT DEPT, AVG(SAL) FROM EMP HAVING COUNT(*) > 5 GROUP BY DEPT;(iii) SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT WHERE COUNT(*) > 5;(iv) SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT HAVING COUNT(*) > 5;

Accepted Answer

SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT HAVING COUNT(*) > 5;Reason — In the above query, the WHERE clause cannot be used with aggregate functions like COUNT(*) because it is meant to filter individual rows before the aggregation. On the other hand, the HAVING clause filters the groups created by the GROUP BY clause to include only those departments (DEPT) that have more than five employees.

Practice Paper — Question 8

Question 8