recategorized by
1,434 views
1 votes
1 votes

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
recategorized by

3 Answers

1 votes
1 votes

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.


 

0 votes
0 votes
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.
0 votes
0 votes
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.

Related questions

2 votes
2 votes
3 answers
1
soujanyareddy13 asked May 12, 2021
2,205 views
Find minimum number of tables required for converting the following entity relationship diagram into relational database?$2$$4$$3$$5$
1 votes
1 votes
2 answers
2
4 votes
4 votes
6 answers
4
soujanyareddy13 asked May 12, 2021
1,860 views
The Boolean expression $AB+A \overline{B}+\overline{A}C+AC$ is unaffected by the value of the Boolean variable _________.$A$$B$$C$$A, B$ and $C$