-- Oracle

-- Populate Restricted

DROP TABLE LOT;

CREATE TABLE LOT(
   LOT_ID_NUM INT,
   GNDR_CODE CHAR(1),
   FROM_DATE DATE,
   END_DATE DATE
);

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 'c', TO_DATE('1998-01-01','YYYY-MM-DD'), TO_DATE('1998-03-23','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 's', TO_DATE('1998-03-23','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-02-17','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-03-12','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

SELECT * FROM LOT;

-- a logical current insertion

INSERT INTO LOT
VALUES (433, 'h', TRUNC(SYSDATE), TO_DATE('9999-12-31','YYYY-MM-DD'));

SELECT * FROM LOT;

-- a logical current deletion

UPDATE LOT
SET END_DATE = TRUNC(SYSDATE)
WHERE LOT_ID_NUM = 101
   AND END_DATE = TO_DATE('9999-12-31','YYYY-MM-DD');

SELECT * FROM LOT;

-- a logical current update

INSERT INTO LOT
SELECT DISTINCT 799, 's', TRUNC(SYSDATE), TO_DATE('9999-12-31','YYYY-MM-DD')
FROM LOT
WHERE EXISTS (SELECT *
        FROM LOT
        WHERE LOT_ID_NUM = 799
           AND END_DATE = TO_DATE('9999-12-31','YYYY-MM-DD'));

UPDATE LOT
SET END_DATE = TRUNC(SYSDATE)
WHERE LOT_ID_NUM = 799
   AND GNDR_CODE <> 's'
   AND END_DATE = TO_DATE('9999-12-31','YYYY-MM-DD');

SELECT * FROM LOT;

-- Populate General

DROP TABLE LOT;

CREATE TABLE LOT(
   LOT_ID_NUM INT, 
   GNDR_CODE CHAR(1),
   FROM_DATE DATE,
   END_DATE DATE
);

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 'c', TO_DATE('1998-01-01','YYYY-MM-DD'), TO_DATE('1998-03-23','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 's', TO_DATE('1998-03-23','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-02-17','YYYY-MM-DD'), TO_DATE('1998-03-22','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-03-22','YYYY-MM-DD'), TO_DATE('1998-04-01','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-04-01','YYYY-MM-DD'), TO_DATE('1998-05-10','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-05-10','YYYY-MM-DD'), TO_DATE('1998-06-29','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-06-29','YYYY-MM-DD'), TO_DATE('1998-10-17','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-10-17','YYYY-MM-DD'), TO_DATE('1998-10-19','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-10-19','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-02-27','YYYY-MM-DD'), TO_DATE('1998-03-12','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-03-12','YYYY-MM-DD'), TO_DATE('1998-04-09','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-04-09','YYYY-MM-DD'), TO_DATE('1998-05-21','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-05-21','YYYY-MM-DD'), TO_DATE('1998-09-13','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-09-13','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

SELECT * FROM LOT;

-- A logical current deletion, in the general scenario 

UPDATE LOT
SET END_DATE = TRUNC(SYSDATE)
WHERE LOT_ID_NUM = 234
   AND END_DATE >= TRUNC(SYSDATE)
   AND FROM_DATE < TRUNC(SYSDATE);

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
   AND FROM_DATE > TRUNC(SYSDATE);

SELECT * FROM LOT;

-- a logical current update, in the general scenario

-- Populate General

DROP TABLE LOT;

CREATE TABLE LOT(
   LOT_ID_NUM INT, 
   GNDR_CODE CHAR(1),
   FROM_DATE DATE,
   END_DATE DATE
);

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 'c', TO_DATE('1998-01-01','YYYY-MM-DD'), TO_DATE('1998-03-23','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 's', TO_DATE('1998-03-23','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-02-17','YYYY-MM-DD'), TO_DATE('1998-03-22','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-03-22','YYYY-MM-DD'), TO_DATE('1998-04-01','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-04-01','YYYY-MM-DD'), TO_DATE('1998-05-10','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-05-10','YYYY-MM-DD'), TO_DATE('1998-06-29','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-06-29','YYYY-MM-DD'), TO_DATE('1998-10-17','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-10-17','YYYY-MM-DD'), TO_DATE('1998-10-19','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-10-19','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-02-27','YYYY-MM-DD'), TO_DATE('1998-03-12','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-03-12','YYYY-MM-DD'), TO_DATE('1998-04-09','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-04-09','YYYY-MM-DD'), TO_DATE('1998-05-21','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-05-21','YYYY-MM-DD'), TO_DATE('1998-09-13','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-09-13','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

SELECT * FROM LOT;

INSERT INTO LOT
SELECT LOT_ID_NUM, 's', TRUNC(SYSDATE), END_DATE
FROM LOT
WHERE LOT_ID_NUM = 799
   AND FROM_DATE <= TRUNC(SYSDATE)
   AND END_DATE > TRUNC(SYSDATE);

UPDATE LOT
SET END_DATE = TRUNC(SYSDATE)
WHERE LOT_ID_NUM = 799
   AND GNDR_CODE <> 's'
   AND FROM_DATE < TRUNC(SYSDATE)
   AND END_DATE > TRUNC(SYSDATE);

UPDATE LOT
SET GNDR_CODE = 's'
WHERE LOT_ID_NUM = 799
   AND FROM_DATE >= TRUNC(SYSDATE);

SELECT * FROM LOT;

-- sequenced insertion

-- Populate General

DROP TABLE LOT;

CREATE TABLE LOT(
   LOT_ID_NUM INT, 
   GNDR_CODE CHAR(1),
   FROM_DATE DATE,
   END_DATE DATE
);

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 'c', TO_DATE('1998-01-01','YYYY-MM-DD'), TO_DATE('1998-03-23','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 's', TO_DATE('1998-03-23','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-02-17','YYYY-MM-DD'), TO_DATE('1998-03-22','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-03-22','YYYY-MM-DD'), TO_DATE('1998-04-01','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-04-01','YYYY-MM-DD'), TO_DATE('1998-05-10','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-05-10','YYYY-MM-DD'), TO_DATE('1998-06-29','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-06-29','YYYY-MM-DD'), TO_DATE('1998-10-17','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-10-17','YYYY-MM-DD'), TO_DATE('1998-10-19','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-10-19','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-02-27','YYYY-MM-DD'), TO_DATE('1998-03-12','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-03-12','YYYY-MM-DD'), TO_DATE('1998-04-09','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-04-09','YYYY-MM-DD'), TO_DATE('1998-05-21','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-05-21','YYYY-MM-DD'), TO_DATE('1998-09-13','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-09-13','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

SELECT * FROM LOT;

INSERT INTO LOT
VALUES (426, 'h', TO_DATE('1998-03-26','YYYY-MM-DD'), TO_DATE('1998-04-14','YYYY-MM-DD'));

SELECT * FROM LOT;

-- a sequenced deletion

INSERT INTO LOT
SELECT LOT_ID_NUM, GNDR_CODE, TO_DATE('1998-10-22','YYYY-MM-DD'), END_DATE
FROM LOT
WHERE LOT_ID_NUM = 234
   AND FROM_DATE <= TO_DATE('1998-10-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1998-10-22','YYYY-MM-DD');

UPDATE LOT
SET END_DATE = TO_DATE('1998-10-01','YYYY-MM-DD')
WHERE LOT_ID_NUM = 234
   AND FROM_DATE < TO_DATE('1998-10-01','YYYY-MM-DD')
   AND END_DATE >= TO_DATE('1998-10-01','YYYY-MM-DD');

UPDATE LOT
SET FROM_DATE = TO_DATE('1998-10-22','YYYY-MM-DD')
WHERE LOT_ID_NUM = 234
   AND FROM_DATE < TO_DATE('1998-10-22','YYYY-MM-DD')
   AND END_DATE >= TO_DATE('1998-10-22','YYYY-MM-DD');

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
   AND FROM_DATE >= TO_DATE('1998-10-01','YYYY-MM-DD')
   AND END_DATE <= TO_DATE('1998-10-22','YYYY-MM-DD');

SELECT * FROM LOT;

-- a sequenced update

-- Populate General

DROP TABLE LOT;

CREATE TABLE LOT(
   LOT_ID_NUM INT, 
   GNDR_CODE CHAR(1),
   FROM_DATE DATE,
   END_DATE DATE
);

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 'c', TO_DATE('1998-01-01','YYYY-MM-DD'), TO_DATE('1998-03-23','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (101, 's', TO_DATE('1998-03-23','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-02-17','YYYY-MM-DD'), TO_DATE('1998-03-22','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-03-22','YYYY-MM-DD'), TO_DATE('1998-04-01','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-04-01','YYYY-MM-DD'), TO_DATE('1998-05-10','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-05-10','YYYY-MM-DD'), TO_DATE('1998-06-29','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-06-29','YYYY-MM-DD'), TO_DATE('1998-10-17','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-10-17','YYYY-MM-DD'), TO_DATE('1998-10-19','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (234, 'c', TO_DATE('1998-10-19','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-02-27','YYYY-MM-DD'), TO_DATE('1998-03-12','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-03-12','YYYY-MM-DD'), TO_DATE('1998-04-09','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-04-09','YYYY-MM-DD'), TO_DATE('1998-05-21','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-05-21','YYYY-MM-DD'), TO_DATE('1998-09-13','YYYY-MM-DD'));

INSERT INTO LOT(LOT_ID_NUM, GNDR_CODE, FROM_DATE, END_DATE)
VALUES (799, 'c', TO_DATE('1998-09-13','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

SELECT * FROM LOT;

INSERT INTO LOT
SELECT LOT_ID_NUM, GNDR_CODE, FROM_DATE, TO_DATE('1998-03-01','YYYY-MM-DD')
FROM LOT
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < TO_DATE('1998-03-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1998-03-01','YYYY-MM-DD');

INSERT INTO LOT
SELECT LOT_ID_NUM, GNDR_CODE, TO_DATE('1998-04-01','YYYY-MM-DD'), END_DATE 
FROM LOT
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < TO_DATE('1998-04-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1998-04-01','YYYY-MM-DD');

UPDATE LOT
SET GNDR_CODE = 's'
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < TO_DATE('1998-04-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1998-03-01','YYYY-MM-DD');

UPDATE LOT
SET FROM_DATE = TO_DATE('1998-03-01','YYYY-MM-DD')
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < TO_DATE('1998-03-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1998-03-01','YYYY-MM-DD');

UPDATE LOT
SET END_DATE = TO_DATE('1998-04-01','YYYY-MM-DD')
WHERE LOT_ID_NUM = 799
   AND FROM_DATE < TO_DATE('1998-04-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1998-04-01','YYYY-MM-DD');

SELECT * FROM LOT;

-- a nonsequenced modification

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
   AND MONTHS_BETWEEN(END_DATE, FROM_DATE) > 3;

SELECT * FROM LOT;