--- 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');