673 views

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
1. Total number of employees in each department $D1$ and $D2$
2. Total number of employees of department $D1$ and $D2$ if their total is $>5$
3. Display total number of employees in both departments $D1$ and $D2$
4. The output of the query must have at least two rows

Option b
Attribute deptno: is not written in the select clause but used in the groupby part ... so does that not make it a wrong query?

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.

I guess the answer is none of the above.

The output of the query is the number of employees in each of departments D1 and D2 if number of employees in that department is more than 5.

1 comment

i think the best possible answer is B. but, the query has an error because it is not selecting from DEPARTMENT table

answer is none of these. It shows that if each department has count greater than 5, then display it else not. Hence none of these.
by

1 vote