edited by
275 views
1 votes
1 votes

Consider the set of relations for the given $SQL$ query:

EMP (eno, ename)
DEPT (dno,dname)
WORKS_IN (eno,dno)

Where primary keys of the relations are eno, dno in $EMP$ table and $DEPT$ table respectively and $\left ( eno,dno \right )$ of WORKS_IN are foreign keys referring to $EMP$$\left ( eno \right )$ and $DEPT$$\left ( dno \right )$

SELECT eno FROM WORKS_IN GROUP BY eno HAVING count (*) = (SELECT count(dno) FROM dept);

The above SQL query returns

  1. The eno’s of employees working in at least one department
  2. The eno’s of employees working in all departments
  3. The eno’s of employees working in 2 (or) more departments
  4. The eno’s of employees not working in any department
edited by

2 Answers

2 votes
2 votes
SELECT count(dno) FROM dept) this will give total number of (distinct because dno is Key in DEPT) departments.

SELECT eno FROM WORKS_IN GROUP BY eno HAVING count (*) = (SELECT count(dno) FROM dept);
It returns : The eno’s of employees working in all departments
1 votes
1 votes
Inner query returns total number of departments and outer query groups WORKS_IN table by the attribute eno. And it filters the groups based on number of tuples in each group(this number should be equal to total number of departments returned by outer query then only that respective eno of the group will be selected)
Answer:

Related questions