AL-zami AL-zami - 6 months ago 18
MySQL Question

what should be the correct sql

I have the following schema:

employee(fname,lname,e_no,age,address,sex,slary,dept_number)

dpartment(dept_name,dept_number,dept_location)

project(pname,pnumber,plocation,dept_number)

works_on(e_no,pnumber,hours)


i have to find the name of the projects where all the employees work more than 3 hours.There might be some cases where an employee works less then 3 hours but others does more than three hours.
I need the project name where all the employee works more than three hours. Can't figure out how to take that into consideration.

Please help me out ! thanks

Answer

When you rephrase your question it's find the projects where no employee works less than three hours, i.e. the minimum hours is greater than three:

SELECT p.pname
FROM project AS p
JOIN works_on AS w ON p.pnumber = w.pnumber
GROUP BY p.pname
HAVING MIN(hours) > 3

Or using NOT EXISTS:

SELECT * 
FROM project AS p
WHERE NOT EXISTS
 ( SELECT *
   FROM works_on AS w
   WHERE p.pnumber = w.pnumber
     AND hours <= 3
 )

Caution, this is not exactly the same, as it will return projects without assigned employees.