Write SQL code to find the following details:
For each project: find the project number and the number of employees who do not work on the project but who work on a different project controlled by the same department.
Here is my solution so far.
In the relation PROJECT, observe that each dnum has multiple pnumber's but each pnumber can only have one dnum. i.e. there are several projects per department but only one department for a project.
For a fixed project, say pnumber = 1, we can find other pnumber's in the same department excluding the fixed pnumber.
SELECT pnumber FROM PROJECT WHERE dnum IN (SELECT dnum FROM PROJECT WHERE pnumber=1) and pnumber NOT IN (1);
Now, we can find the number of employees who work in the pnumbers listed in the query above using the relation WORKS_ON(essn, pno, hours). An essn is a foreign key for each employee.
SELECT count(*) from WORKS_ON WHERE pno in (...above query...);
This is where i'm stuck. In my example i can only find the project number and the number of employees who work in other projects in the same department for a given pnumber only. However, I can't think of a way to produce a table that runs the same algorithm for every single pnumber. Perhaps my approach might be wrong or I am unaware of an sql function that can do this.
1) How can I generalize my sql to generate the same result for ALL pnumbers, not just pnumber = 1?
2) For the first section of my code, is there a cleaner/faster way to find all other projects in the same department, given a fixed project? It seems a bit inefficient at the moment, having to nest 2 select queries and adding more select queries later on.