Answer: 2) Total number of employees in each department D1 and D2 if there total is >5
Explanation:
Breaking down the query, it groups the EMPLOYEE relation on distinct DEPTNO whose frequencies (row counts) are greater than 5 (i.e. no. of employees (hence no. of rows) for those dept. are greater than 5) and further filters only dept. D1 and D2 counts.
(DEPTNO in EMPLOYEE is not a key so multiple values will be present (Obviously))
Though this conclusion can be completely inferred from given option 2 only.
Still, upon critical evaluation of query,
- if no. of employees are less than 5 in dept. D1 or D2, the HAVING clause will reject them. Hence, the number of rows my be 2 (for both), 1 (if any one fails >5 cond.) or even no o/p of the query! (when both fail >5 cond.) This rejects option 4 and 1.
- Departments are grouped, hence result will contain counts corresponding to respective department. This won't be the sum of two departments hence option 3 is rejected.