--- Oracle code for the code fragments in the February, 1999 column in
--- DBPD Online 

--- Bob was assigned the position of Associate Director of the Computer
--- Center for 1997.

INSERT INTO INCUMBENTS
   ( SSN , PCN , START_DATE , END_DATE )
SELECT '111223333' SSN , '341288' , TO_DATE('1997-01-01','YYYY-MM-DD') , 
       TO_DATE('1998-01-01','YYYY-MM-DD')
FROM   DUAL
WHERE  NOT EXISTS( SELECT *
                   FROM   INCUMBENTS I2
                   WHERE  I2.SSN = SSN
                      AND I2.START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
                      AND I2.END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD'));

-- Bob was removed as Associate Director of the 
-- Computer Center for 1997

INSERT INTO INCUMBENTS
  (SSN , PCN , START_DATE , END_DATE)
SELECT SSN , PCN , TO_DATE('1998-01-01','YYYY-MM-DD') , END_DATE
FROM   INCUMBENTS
WHERE  SSN = '111223333' 
  AND  PCN = '341288'
  AND  START_DATE <= TO_DATE('1997-01-01','YYYY-MM-DD')
  AND  END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    END_DATE = TO_DATE('1997-01-01','YYYY-MM-DD')
WHERE  SSN = '111223333'
   AND PCN = '341288'
   AND START_DATE < TO_DATE('1997-01-01','YYYY-MM-DD')
   AND END_DATE >= TO_DATE('1997-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    START_DATE = TO_DATE('1998-01-01','YYYY-MM-DD')
WHERE  SSN = '111223333'
   AND PCN = '341288'
   AND START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
   AND END_DATE >= TO_DATE('1998-01-01','YYYY-MM-DD');

DELETE
  FROM  INCUMBENTS
  WHERE SSN = '111223333'
    AND PCN = '341288'
    AND START_DATE >= TO_DATE('1997-01-01','YYYY-MM-DD')
    AND END_DATE <= TO_DATE('1998-01-01','YYYY-MM-DD');

-- Bob was promoted to Director of the Computer
-- Center for 1997.

INSERT INTO INCUMBENTS
  (SSN , PCN , START_DATE , END_DATE)
SELECT '111223333' , PCN , START_DATE, TO_DATE('1997-01-01','YYYY-MM-DD')
FROM   INCUMBENTS
WHERE  SSN = '111223333' 
  AND  START_DATE < TO_DATE('1997-01-01','YYYY-MM-DD')
  AND  END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

INSERT INTO INCUMBENTS
  (SSN , PCN , START_DATE , END_DATE)
SELECT '111223333' , PCN , TO_DATE('1998-01-01','YYYY-MM-DD') , END_DATE
FROM   INCUMBENTS
WHERE  SSN = '111223333' 
  AND  START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
  AND  END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    PCN = '908654'
WHERE  SSN = '111223333'
   AND START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    START_DATE = TO_DATE('1997-01-01','YYYY-MM-DD')
WHERE  SSN = '111223333'
   AND START_DATE < TO_DATE('1997-01-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    END_DATE = TO_DATE('1998-01-01','YYYY-MM-DD')
WHERE  SSN = '111223333'
   AND START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD');