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
(A)
Total number of employees in each department D1 and D2
(B)
Total number of employees of department D1 and D2 if their total is >5
(C)
Display total number of employees in both departments D1 and D2
(D)
The output of the query must have at least two rows
Answer: (B)
Explanation:
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has same values in different rows then it will arrange these rows in a group.
The IN operator is used with Where Clause to test if the expression matches any value in the list of values. The advantage of using IN operator is that it avoids the use of multiple OR Operator.
So in this given query group by distinct DEPTNO on the EMPLOYEE relation having rows with same DEPTNO are greater than 5 and it also filters out only department D1 and D2 using IN(list…) query.
So, option 2 is correct answer.
Quiz of this Question
Please comment below if you find anything wrong in the above post