0
$\begingroup$

Schema:
http://comet.lehman.cuny.edu/jung/cmp420758/DBMidexamFa14-SelectedSolution.pdf

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.

Edit:
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.

Questions:
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.

$\endgroup$
  • $\begingroup$ What have you tried? Where did you get stuck? We do not want to just hand you the solution; we want you to gain understanding. However, as it is we do not know what your underlying problem is, so we can not begin to help. See here for tips on asking questions about exercise problems. $\endgroup$ – Discrete lizard Apr 17 '18 at 12:46
  • 1
    $\begingroup$ But, more to the point, "Write code in language X to perform task Y" is always off-topic, here. $\endgroup$ – David Richerby Apr 17 '18 at 13:20
  • $\begingroup$ Thank you for the feedback, the links were very useful. I'm new here so apologies for the misinterpretation! I edited my post to explain my working so far. $\endgroup$ – Protein Apr 17 '18 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.