
// CF 6-3

// Employees table & data

CREATE TABLE Employees(
                        SSN             integer,
                        LastName        char(25),
                        FirstName       char(25),
                        AnnualSalary    integer,
                        BirthDate       date
                      );

INSERT INTO Employees VALUES (
                        111223333,
                        'Smith',
                        'Bob',
                        64000,
                        DATE '04/07/1932'
                             );

INSERT INTO Employees VALUES (
                        444332222,
                        'Brucks',
                        'Eric',
                        32768,
                        DATE '01/06/1969'
                             );


// Incumbents table & data

CREATE TABLE Incumbents (
                        SSN             integer,
                        PCN             integer,
                        StartDate       date,
                        EndDate         date
                        );

INSERT INTO Incumbents VALUES (
                        111223333,
                        120033,
                        DATE '01/01/1996',
                        DATE '06/01/1996'
                              );

INSERT INTO Incumbents VALUES (
                        111223333,
                        137112,
                        DATE '06/01/1996',
                        DATE '08/01/1996'
                              );

INSERT INTO Incumbents VALUES (
                        111223333,
                        341288,
                        DATE '08/01/1996',
                        DATE '10/01/1996'
                              );

INSERT INTO Incumbents VALUES (
                        111223333,
                        723401,
                        DATE '10/01/1996',
                        DATE '01/01/3000'
                              );

INSERT INTO Incumbents VALUES (
                        444332222,
                        120033,
                        DATE '01/01/1997',
                        DATE '01/01/3000'
                              );


// Positions table & data

CREATE TABLE Positions (
                        PCN             integer,
                        JobTitleCode1   integer
                       );

INSERT INTO POSITIONS VALUES (
                        900225,
                        110689
                             );

INSERT INTO POSITIONS VALUES (
                        137112,
                        130891
                             );

INSERT INTO POSITIONS VALUES (
                        341288,
                        141955
                             );

INSERT INTO POSITIONS VALUES (
                        120033,
                        132955
                             );

INSERT INTO POSITIONS VALUES (
                        723401,
                        020730
                             );

INSERT INTO POSITIONS VALUES (
                        908654,
                        172734
                             );


// ZPOSCompHist (from UIS) table & data

CREATE TABLE ZPOSCompHist (
                        SSN             integer,
                        SalaryHourRate  integer,
                        ChronologyKey   integer,
                        EmployeeFTE     real,
                        HistStartDate   date,
                        HistEndDate     date
                          );

INSERT INTO ZPOSCompHist VALUES (
                        111223333,
                        52800,
                        1,
                        1.00,
                        DATE '07/01/1995',
                        DATE '03/01/1996'
                              );

INSERT INTO ZPOSCompHist VALUES (
                        111223333,
                        61300,
                        2,
                        1.00,
                        DATE '03/01/1996',
                        DATE '07/01/1996'
                              );

INSERT INTO ZPOSCompHist VALUES (
                        111223333,
                        72000,
                        3,
                        1.00,
                        DATE '07/01/1996',
                        DATE '07/15/1996'
                              );

INSERT INTO ZPOSCompHist VALUES (
                        111223333,
                        83000,
                        4,
                        1.00,
                        DATE '07/15/1996',
                        DATE '01/01/3000'
                              );

INSERT INTO ZPOSCompHist VALUES (
                        444332222,
                        40100,
                        1,
                        1.00,
                        DATE '01/01/1997',
                        DATE '01/01/3000'
                              );

// SalHistory view

 CREATE VIEW SalHistory AS
               SELECT SSN,
                      SalaryHourRate,
                      HistStartDate,
                      HistEndDate
                 FROM ZPOSCompHist AS Z     
                WHERE ChronologyKey = (SELECT MAX(ChronologyKey)
                                         FROM ZPOSCompHist AS Z2
                                        WHERE Z2.SSN = Z.SSN)
                  AND EmployeeFTE = 1.00;


// Actual query, note that UniSQL does not support renaming of columns

SELECT JobTitleCode1, SalaryHourRate
  FROM Employees, Incumbents, Positions, SalHistory
 WHERE FirstName = 'Bob'
   AND Employees.SSN = Incumbents.SSN
   AND Incumbents.PCN = Positions.PCN
   AND StartDate <= DATE '12/05/1997'
   AND DATE '12/05/1997' < EndDate
   AND HistStartDate <= DATE '12/05/1997'
   AND DATE '12/05/1997' < HistEndDate
   AND SalHistory.SSN = Employees.SSN;

