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 that I want 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%';

And it gives all the 4 Project names

    PNAME
Qutantum Computing
Cosmic Rays
Artificial Intelligence
Cloud Computing

but according to my goal it should display:

    PNAME
Qutantum Computing
Cloud Computing

CodePudding user response：

If you think about how to explain the process. There are several methods to solve this including:

1. You would start with each of the Projects and find out if there does not exist anybody who Works on the project where the Employees name starts with S. You can do this using NOT EXISTS.

or

2. Again, start with a Project and find, if any, who Works on the project and their corresponding Employees details using LEFT OUTER JOINs (but starting from the Project) and filtering for employee names starting with S in the JOIN condition. Then GROUP BY the primary key for the project and find those projects HAVING a COUNT of zero matched employees.

Since this appears to be a homework question, I'll leave the rest for you to complete.