/*==========================================================================*/
/* Period Examples for the Oracle DBMS                                      */
/*==========================================================================*/

/*--------------------------------------------------------------------------*/
/* Periods:                                                                 */
/*      This example consists of a table containing people's names, their   */
/*      pay rate, and the period for which the pay rate was valid.  Several */
/*      queries on the table are implemented to show how periods can be     */
/*      used in the Oracle DBMS.                                            */
/*--------------------------------------------------------------------------*/

/* Create a table to contain a list of people's names, their */
/* pay rate, and the period for which the pay rate was valid */
CREATE TABLE Employee (Name VARCHAR(20), PayRate NUMBER(5,2),
	StartDate DATE, EndDate DATE);

/* Insert some sample data into the Employee table */
INSERT INTO Employee (Name, PayRate, StartDate, EndDate)
	VALUES ('Chris', 6.00, TO_DATE('1994-05-15', 'YYYY-MM-DD'),
    TO_DATE('1996-06-04', 'YYYY-MM-DD'));
INSERT INTO Employee (Name, PayRate, StartDate, EndDate)
    VALUES ('Chris', 18.00, TO_DATE('1996-06-04', 'YYYY-MM-DD'),
    TO_DATE('3000-01-01', 'YYYY-MM-DD'));
INSERT INTO Employee (Name, PayRate, StartDate, EndDate)
    VALUES ('John', 28.00, TO_DATE('1991-03-18', 'YYYY-MM-DD'),
    TO_DATE('1993-03-25', 'YYYY-MM-DD'));
INSERT INTO Employee (Name, PayRate, StartDate, EndDate)
    VALUES ('John', 32.00, TO_DATE('1993-03-25', 'YYYY-MM-DD'),
    TO_DATE('1995-04-10', 'YYYY-MM-DD'));
INSERT INTO Employee (Name, PayRate, StartDate, EndDate)
    VALUES ('John', 35.00, TO_DATE('1995-04-10', 'YYYY-MM-DD'),
    TO_DATE('3000-01-01', 'YYYY-MM-DD'));

/* List everything in the Employee table */
SELECT Name, PayRate, StartDate, EndDate FROM Employee;

/* List everyone's pay rate as of 6-1-95 in the Employee table */
SELECT Name, PayRate FROM Employee 
    WHERE TO_DATE('1995-06-01', 'YYYY-MM-DD')
    BETWEEN StartDate AND EndDate;

/* List everyone whose pay rate changed between */
/* 1-1-95 and 12-31-95 in the Employee table */
SELECT DISTINCT Name FROM Employee 
	WHERE TO_DATE('1995-01-01', 'YYYY-MM-DD') < StartDate
    AND StartDate < TO_DATE('1995-12-31', 'YYYY-MM-DD');

/* Drop the example Employee table */
DROP TABLE Employee;

/*==========================================================================*/
