SQL: Retrieve the names of employees who work on exactly one project that is con

Question

SQL: Retrieve the names of employees who work on exactly one project that is controlled by their department. (It does not matter how many non-departmental projects they work on.) Also show the department name, and the project name and location. Show results in ascending alpha order (by last name and then first name).
Column Headings: FNAME LNAME DNAME PNAME PLOCATION

I currently have a list of names who work on projects controlled by their departments. But I am unsure on how to trim down the table so that it shows the employees who work on exactly one project controlled by there department. (ie, james borg and jennifer wallace). Values are subject to change, so i can not just do a direct comparison to their last names or something.

SQL Query I produced:

select distinct fname, lname, dname, pname, plocation
from works_on, project, employee, department
where works_on.pno = project.pnumber
and works_on.essn = employee.ssn
and project.dnum = employee.dno
and department.dnumber = employee.dno
order by lname, fname;

Resulting table:

Tables and Column Headings:

works_on - essn, pno, hours,

project - pname, plocation, pnumber, dnum

employee - fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno

department - dname, dnumber, mgrssn, mgrstartdate

PNAME PLOCATION FNAME LNAME DNAME James Borg Headquarters Reorganization Houston Joyce English Research Product X Bellaire ProductY Sugarland Joyce English Research Ahmad Jabbar Administration Computerization Stafford Ahmad Jabbar Administration Newbenefits Bellaire Ramesh Narayan Research Product X Bellaire Ramesh Narayan Research Productz Houston ProductX John Smith Research Bellaire ProductY Sugarland John Smith Research Jennifer Wallace Administration Newbenefits Bellaire Franklin Wong Research ProductX Bellaire Franklin Wong Research ProductY Sugarland Franklin Wong Research ProductZ Houston Alicia Zelaya Administration Computerization Stafford Alicia Zelaya Administration Newbenefits Bellaire

Explanation / Answer

eclare @Emp as table(empID int, empname varchar(100), joindate date)
insert into @Emp values (1,'Arun','05-12-2008')
insert into @Emp values (2,'Balaji','2009-04-11')
insert into @Emp values (2,'Unknown','2010-05-12')
select *,
DATEPART(MONTH,joindate) MonthPart
from @emp
where DATEPART(MONTH,joindate)=5
SELECT S.*
FROM STAFF S
LEFT JOIN (
SELECT DEPTNO, COUNT(*) AS C
FROM STAFF
GROUP BY DEPTNO
) AS D_COUNTS ON S.DEPTNO = D_COUNT.DEPTNO
WHERE D_COUNTS.C >= 5
or as a CTE

WITH D_COUNTS AS
(
SELECT DEPTNO, COUNT(*) AS C
FROM STAFF
GROUP BY DEPTNO
)
SELECT S.*
FROM STAFF S
LEFT JOIN D_COUNTS ON S.DEPTNO = D_COUNT.DEPTNO
WHERE D_COUNTS.C >= 15

SELECT DEPTNO
FROM STAFF
GROUP BY DEPTNO
HAVING COUNT(*) >= 10
Now you join both result

SELECT S.*
FROM STAFF S
JOIN ( SELECT DEPTNO
FROM STAFF
GROUP BY DEPTNO
HAVING COUNT(*) >= 5 ) F
ON S.DEPTNO = F.DEPTNO
Subquery version:

SELECT S.*
FROM STAFF S
WHERE S.DEPTNO IN ( SELECT DEPTNO
FROM STAFF
GROUP BY DEPTNO
HAVING COUNT(*) >= 15 )

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.