
// CF 7-5

// 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 111223333,
         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 '12/05/1997');

