DATABASE

CREATE TABLE StudentInfo (
    StudentID VARCHAR(3) PRIMARY KEY,
    LastName VARCHAR(45),
    FirstName VARCHAR(45),
    ProgramCode VARCHAR(45),
    ProgramDesc VARCHAR(255)
);

CREATE TABLE EmployeeInfo (
    LogID INT PRIMARY KEY,
    EmployeeID VARCHAR(6),
    LogDate VARCHAR(8),
    EmpName VARCHAR(255)
);

CREATE TABLE EmployeeDtr (
    EmployeeID VARCHAR(6),
    EmpName VARCHAR(255),
    LogID INT,
    LogDate VARCHAR(8),
    TimeRecID INT,
    TIn VARCHAR(5),
    TOut VARCHAR(5),
    PRIMARY KEY (EmployeeID, LogID)
);

INSERT INTO StudentInfo (StudentID, LastName, FirstName, ProgramCode, ProgramDesc)
VALUES
('001', 'Cruz', 'Juan', 'BSCS', 'BS in Computer Science'),
('002', 'Perez', 'Henry', 'BSBA', 'BS in Business Administration'),
('003', 'Albuena', 'Andy', 'BSHRM', 'BS in Hotel and Restaurant Management'),
('004', 'Peralta', 'Vincent', 'BSED', 'BS in Education');

INSERT INTO EmployeeInfo (LogID, EmployeeID, LogDate, EmpName)
VALUES
(1, '01-054', '11-09-11', 'Jerry Mendoza'),
(2, '01-056', '11-10-11', 'Dane Sevilla'),
(3, '01-054', '11-11-11', 'Jerry Mendoza');

INSERT INTO EmployeeDtr (EmployeeID, EmpName, LogID, LogDate, TimeRecID, TIn, TOut)
VALUES
('01-054', 'Jerry Mendoza', 1, '11-09-11', 1, '09:00', '18:03'),
('01-054', 'Jerry Mendoza', 3, '11-10-11', 1, '09:00', '18:03'),
('01-055', 'Dane Sevilla', 0, NULL, NULL, NULL, NULL),
('01-056', 'Mylene Antonia', 2, '11-09-11', 2, '7:30', '16:55');


-- a. View Employee Time Record as inner join
CREATE VIEW EmployeeTimeRecord AS
SELECT
    e.LogID,
    e.EmployeeID,
    e.LogDate,
    e.EmpName,
    d.TimeRecID,
    d.TIn,
    d.TOut
FROM
    EmployeeInfo e
INNER JOIN
    EmployeeDtr d ON e.EmployeeID = d.EmployeeID AND e.LogID = d.LogID;
SELECT * FROM EmployeeTimeRecord;

-- b. View Employee Record as left join
CREATE VIEW EmployeeRecord AS
SELECT
    e.LogID,
    e.EmployeeID,
    e.LogDate,
    e.EmpName,
    d.TimeRecID,
    d.TIn,
    d.TOut
FROM
    EmployeeInfo e
LEFT JOIN
    EmployeeDtr d ON e.EmployeeID = d.EmployeeID AND e.LogID = d.LogID;

-- c. View Log and employee Table as right join
CREATE VIEW LogEmployeeTable AS
SELECT
    e.LogID,
    e.EmployeeID,
    e.LogDate,
    e.EmpName,
    d.TimeRecID,
    d.TIn,
    d.TOut
FROM
    EmployeeInfo e
RIGHT JOIN
    EmployeeDtr d ON e.EmployeeID = d.EmployeeID AND e.LogID = d.LogID;