
// CF 7-22

// Incumbents table

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

// Positions table & data

CREATE TABLE Positions (
                        PCN             integer,
                        JobTitleCode1   integer,
                        StartDate       date,
                        EndDate         date,
                        PRIMARY KEY (PCN)
                       );
// JobTitles table & data

CREATE TABLE JobTitles (
                        JobTitleCode    integer,
                        JobTitle        char(66),
                        PRIMARY KEY (JobTitleCode)
                       );

// Actual modifications

UPDATE Incumbents
   SET PCN = (SELECT PCN
                FROM Positions, JobTitles
               WHERE Positions.JobTitleCode1 = JobTitleCode
                 AND JobTitle = 'Director, Computer Center')
 WHERE SSN = 111223333;

