I am working on a sql query to do the following:
For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project.
So far my query looks like this:
, COUNT( DISTINCT w.ESSN) '# employees from Dept. 5'
FROM project p
JOIN department d
ON d.DNO = p.DNO
JOIN employee e
ON e.DNO = d.DNO
JOIN works_on w
ON w.ESSN = e.SSN
WHERE e.DNO LIKE '5'
AND p.PNO LIKE 10
Your joins are wrong.
You should be joining only projects, works_on and employee tables.
SELECT p.PNO, p.PNAME, COUNT( DISTINCT w.ESSN) '# employees from Dept. 5' FROM project p INNER JOIN works_on w ON p.pno = w.pno INNER JOIN employee e ON w.essn = e.essn WHERE e.DNO LIKE '5' AND p.PNO LIKE 10
And you are missing the group by at the end:
GROUP BY p.PNO, p.PNAME