
// CF 7-3

// Incumbents table

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

// Positions table

CREATE TABLE Positions (
                        PCN             integer,
                        JobTitleCode1   integer,
                        StartDate       date,
                        EndDate         date,
                        PRIMARY KEY (PCN)
                       );

// Actual insert

INSERT INTO Incumbents 
  SELECT 11122333,
         341288,
         DATE '12/05/1997',
         DATE '01/01/3000'
    FROM Incumbents
   WHERE NOT EXISTS (SELECT *
                      FROM Incumbents AS I2
                     WHERE Incumbents.SSN = I2.SSN
                       AND I2.EndDate = DATE '01/01/3000')
     AND EXISTS (SELECT *
                   FROM Positions
                  WHERE Incumbents.PCN = Positions.PCN
                    AND Positions.EndDate = DATE '01/01/3000');

