I'm having the schemas like:
Employee (Empno, Empname, City)
Project (Pno, Pname)
Part (Partno, Partname, Color)
Use (Pno, Partno)
Works (Empno, Pno)
From these schemas I had created a sample tables:
The goal is to find the names of the projects where no employees are working whose name starts with 'S'
I'm using ORACLE 11g Express Edition. Here I used this query : For Names:
Select DISTINCT Pname FROM
(
SELECT w.Empno, p.Pno, p.Pname, e.Empname
FROM Works w
LEFT JOIN Project p ON w.Pno=p.Pno
LEFT JOIN Employee e ON e.Empno=w.Empno
)
WHERE Empname not like 'S%';
CodePudding user response:
If you think about how to explain the process. There are several methods to solve this including:
- You would start with each of the
Projects
and find out if there does not exist anybody whoWorks
on the project where theEmployees
name starts withS
. You can do this usingNOT EXISTS
.
or
- Again, start with a
Project
and find, if any, whoWorks
on the project and their correspondingEmployees
details usingLEFT OUTER JOIN
s (but starting from theProject
) and filtering for employee names starting withS
in theJOIN
condition. ThenGROUP BY
the primary key for the project and find those projectsHAVING
aCOUNT
of zero matched employees.
Since this appears to be a homework question, I'll leave the rest for you to complete.