need sql to represent relation negation and date compare problem I have the following tables:


Employee(eid,ename,salary,did,classification)
Project(pid,pname,did,budget,ddate)
Department(did,dname,floor,head) 
dbudget(did,byear,budget)
Project(pid,pname,did,budget,ddate)
Onproject(pid,eid,fdate)


did department identifier, so we need something that is in employee.did but non on project.did

and I want to retrieve all of the projects whose duedate is not passed, and that all of its workers belong to a department  that is not responsible for the project.

now, I don't know how to check if the ddate is over(ddate is due date, and I don't know if it's passed or not), and how to represent it in a query.

I'll show the query I constructed so far:

SELECT   Project.pname 
from         Project 
FULL OUTER JOIN Employee 
on   Project.did &lt;&gt;  Employee.did 
AND .... 


(don't know how to check if ddate is passed) is there a function to show date in SQL to compare (so the difference will be positive) or any trick to work with the given info?
The problem is not simple as it involves 2 entities/tables (Employee and Project) that have 2 relationships between them. One is through the Department (a project belongs to a department, an employee belongs to a department) and the other through Onproject (which shows that an employee has started working on a project).

Moreover, the answer needs a relational division query ("all is workers belong ..."). There are many ways to do relational division - and all are in the range of not simple to very complicated. 

One way to express the problem is with a (double) negative. Instead of finding projects where all workers do not do something, find projects where none of the workers do something. This can be expressed with a NOT EXISTS subquery:

SELECT p.* 
FROM Project AS p
WHERE p.ddate &gt; CURRENT_DATE 
  AND NOT EXISTS
      ( SELECT * 
        FROM Employee AS e
            JOIN Onproject AS w
            ON w.eid = e.eid
        WHERE w.pid = p.pid
          AND e.did = p.did
      ) ;


Or with EXCEPT:

SELECT p.* 
FROM Project AS p
    JOIN
      ( SELECT p.pid, p.did
        FROM Project AS p
        EXCEPT
        SELECT w.pid, e.did
        FROM Employee AS e
            JOIN Onproject AS w
            ON w.eid = e.eid
      ) AS pw
      ON pw.pid = p.pid
WHERE p.ddate &gt; CURRENT_DATE ;


Given the tables definitions, the latter can be (simplified?) to use NATURAL JOIN:

SELECT * 
FROM Project
    NATURAL JOIN
      ( SELECT pid, did FROM Project 
        EXCEPT
        SELECT pid, did FROM Employee NATURAL JOIN Onproject
      ) AS pw
WHERE ddate &gt; CURRENT_DATE ;