-- Standard SQL-92

populaterestricted

-- a logical current insertion

INSERT INTO LOT
VALUES (433, 'h', CURRENT_DATE, DATE '9999-12-31')

SELECT * FROM LOT

-- a logical current deletion

UPDATE LOT
SET TO_DATE = CURRENT_DATE
WHERE LOT_ID_NUM = 101
   AND TO_DATE = DATE '9999-12-31'

SELECT * FROM LOT

-- a logical current update

INSERT INTO LOT
SELECT DISTINCT 799, 's', CURRENT_DATE, DATE '9999-12-31'
FROM LOT
WHERE EXISTS (SELECT *
        FROM LOT
        WHERE LOT_ID_NUM = 799
           AND TO_DATE = DATE '9999-12-31')

UPDATE LOT
SET TO_DATE = CURRENT_DATE
WHERE LOT_ID_NUM = 799
   AND GNDR_CODE <> 's'
   AND TO_DATE = DATE '9999-12-31'

populategeneral

-- A logical current deletion, in the general scenario 

UPDATE LOT
SET TO_DATE = CURRENT_DATE
WHERE LOT_ID_NUM = 234
   AND TO_DATE >= CURRENT_DATE
   AND FROM_DATE < CURRENT_DATE

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
   AND FROM_DATE > CURRENT_DATE

SELECT * FROM LOT

-- a logical current update, in the general scenario

populategeneral

INSERT INTO LOT
SELECT LOT_ID_NUM, 's', CURRENT_DATE, TO_DATE
FROM LOT
WHERE LOT_ID_NUM = 799
   AND FROM_DATE <= CURRENT_DATE
   AND TO_DATE > CURRENT_DATE

UPDATE LOT
SET TO_DATE = CURRENT_DATE
WHERE LOT_ID_NUM = 799
   AND GNDR_CODE <> 's'
   AND FROM_DATE < CURRENT_DATE
   AND TO_DATE > CURRENT_DATE

UPDATE LOT
SET GNDR_CODE = 's'
WHERE LOT_ID_NUM = 799
   AND FROM_DATE >= CURRENT_DATE

SELECT * FROM LOT

-- sequenced insertion

populategeneral

INSERT INTO LOT
VALUES (426, 'h', DATE '1998-03-26', DATE '1998-04-14')

SELECT * FROM LOT

-- a sequenced deletion

INSERT INTO LOT
SELECT LOT_ID_NUM, GNDR_CODE, DATE '1998-10-22', TO_DATE
FROM LOT
WHERE LOT_ID_NUM = 234
   AND FROM_DATE <= DATE '1998-10-01'
   AND TO_DATE > DATE '1998-10-22'

UPDATE LOT
SET TO_DATE = DATE '1998-10-01'
WHERE LOT_ID_NUM = 234
   AND FROM_DATE < DATE '1998-10-01'
   AND TO_DATE >= DATE '1998-10-01'

UPDATE LOT
SET FROM_DATE = DATE '1998-10-22'
WHERE LOT_ID_NUM = 234
   AND FROM_DATE < DATE '1998-10-22'
   AND TO_DATE >= DATE '1998-10-22'

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
   AND FROM_DATE >= DATE '1998-10-01'
   AND TO_DATE <= DATE '1998-10-22'

SELECT * FROM LOT

-- a sequenced update

populategeneral

INSERT INTO LOT
SELECT LOT_ID_NUM, GNDR_CODE, FROM_DATE, DATE '1998-03-01'
FROM LOT
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < DATE '1998-03-01'
   AND TO_DATE > DATE '1998-03-01'

INSERT INTO LOT
SELECT LOT_ID_NUM, GNDR_CODE, DATE '1998-04-01', TO_DATE 
FROM LOT
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < DATE '1998-04-01'
   AND TO_DATE > DATE '1998-04-01'

UPDATE LOT
SET GNDR_CODE = 's'
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < DATE '1998-04-01'
   AND TO_DATE > DATE '1998-03-01'

UPDATE LOT
SET FROM_DATE = DATE '1998-03-01'
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < DATE '1998-03-01'
   AND TO_DATE > DATE '1998-03-01'

UPDATE LOT
SET TO_DATE = DATE '1998-04-01'
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < DATE '1998-04-01'
   AND TO_DATE > DATE '1998-04-01'

SELECT * FROM LOT

-- a nonsequenced modification

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
   AND (TO_DATE - FROM_DATE MONTH) > INTERVAL '3' MONTH

SELECT * FROM LOT