
// CF 6-4

// Employees table & data

CREATE TABLE Employees(
                        SSN             integer,
                        LastName        char(25),
                        FirstName       char(25),
                        AnnualSalary    integer,
                        BirthDate       date
                      );

INSERT INTO Employees VALUES (
                        111223333,
                        'Smith',
                        'Bob',
                        64000,
                        DATE '04/07/1932'
                             );

INSERT INTO Employees VALUES (
                        444332222,
                        'Brucks',
                        'Eric',
                        32768,
                        DATE '01/06/1969'
                             );


// Incumbents table & data

CREATE TABLE Incumbents (
                        SSN             integer,
                        PCN             integer,
                        StartDate       date,
                        EndDate         date
                        );

INSERT INTO Incumbents VALUES (
                        111223333,
                        120033,
                        DATE '01/01/1996',
                        DATE '06/01/1996'
                              );

INSERT INTO Incumbents VALUES (
                        111223333,
                        137112,
                        DATE '06/01/1996',
                        DATE '08/01/1996'
                              );

INSERT INTO Incumbents VALUES (
                        111223333,
                        341288,
                        DATE '08/01/1996',
                        DATE '10/01/1996'
                              );

INSERT INTO Incumbents VALUES (
                        111223333,
                        723401,
                        DATE '10/01/1996',
                        DATE '01/01/3000'
                              );

INSERT INTO Incumbents VALUES (
                        444332222,
                        120033,
                        DATE '01/01/1997',
                        DATE '01/01/3000'
                              );



// Actual query

SELECT FirstName
  FROM Employees
 WHERE NOT EXISTS (SELECT Incumbents.SSN
                     FROM Incumbents
                    WHERE Employees.SSN = Incumbents.SSN
                      AND StartDate <= DATE '12/05/1997'
                      AND DATE '12/05/1997' < EndDate)


