Dark Mode

673 views

1 vote

Given two tables

EMPLOYEE (EID, ENAME, DEPTNO) DEPARTMENT (DEPTNO, DEPTNAME)

Find the most appropriate statement of the given query:

Select count (*) ‘total’ from EMPLOYEE where DEPTNO IN (D1,D2) group by DEPTNO having count (*) >5

- Total number of employees in each department $D1$ and $D2$
- Total number of employees of department $D1$ and $D2$ if their total is $>5$
- Display total number of employees in both departments $D1$ and $D2$
- The output of the query must have at least two rows

0

1 vote

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.