CREATE TABLE Schedule (
   ScheduleID int AUTO_INCREMENT,
   Date varchar(255),
   PRIMARY KEY (ScheduleID)
);

INSERT INTO Schedule (ScheduleID, Date)
VALUES (1, "MONDAY");

/*INSERT INTO Schedule (ScheduleID, Date)
VALUES (2, "MONDAY");

INSERT INTO Schedule (ScheduleID, Date)
VALUES (3, "MONDAY");

INSERT INTO Schedule (ScheduleID, Date)
VALUES (4, "MONDAY");*/

INSERT INTO Schedule (ScheduleID, Date)
VALUES (5, "TUESDAY");

INSERT INTO Schedule (Date)
VALUES ("FRIDAY");

INSERT INTO Schedule (Date)
VALUES ("SATURDAY");

INSERT INTO Schedule (Date)
VALUES ("SUNDAY");

CREATE TABLE ShiftInstance (
   ShiftInstanceID int AUTO_INCREMENT,
   ScheduleID int,
   DeptID int,
   NurseID int,
   Date varchar(255),
   StartTime int,
   EndTime int,
   PRIMARY KEY (ShiftInstanceID),
   FOREIGN KEY (ScheduleID) REFERENCES Schedule(ScheduleID)
);

INSERT INTO ShiftInstance (ShiftInstanceID, ScheduleID, DeptID, NurseID, Date, StartTime, EndTime)
VALUES (1, 1, 1, 1, "9/21/2017", 7, 3);

INSERT INTO ShiftInstance (ShiftInstanceID, ScheduleID, DeptID, NurseID, Date, StartTime, EndTime)
VALUES (2, 1, 1, 2, "9/21/2017", 3, 11);

INSERT INTO ShiftInstance (ShiftInstanceID, ScheduleID, DeptID, NurseID, Date, StartTime, EndTime)
VALUES (3, 1, 1, 3, "9/21/2017", 3, 11);

INSERT INTO ShiftInstance (ShiftInstanceID, ScheduleID, DeptID, NurseID, Date, StartTime, EndTime)
VALUES (4, 1, 1, 4, "9/21/2017", 11, 7);

INSERT INTO ShiftInstance (ShiftInstanceID, ScheduleID, DeptID, NurseID, Date, StartTime, EndTime)
VALUES (5, 5, 1, 4, "9/21/2017", 7, 3);

CREATE TABLE Department (
   DeptID int AUTO_INCREMENT,
   DeptName varchar(255),
   StaffSize int,
   PRIMARY KEY (DeptID)
);

INSERT INTO Department(DeptID, DeptName, StaffSize)
VALUES (1, "SURG", 25);

/*INSERT INTO Department(DeptID, DeptName, StaffSize)
VALUES (2, "SURG", 25);

INSERT INTO Department(DeptID, DeptName, StaffSize)
VALUES (3, "SURG", 25);

INSERT INTO Department(DeptID, DeptName, StaffSize)
VALUES (4, "SURG", 25);

INSERT INTO Department(DeptID, DeptName, StaffSize)
VALUES (5, "SURG", 25);*/

CREATE TABLE Nurses (
   NurseID int AUTO_INCREMENT,
   FirstName varchar(255),
   LastName varchar(255),
   Title varchar(255),
   IsSalaried int,
   StartDate varchar(255),
   PRIMARY KEY (NurseID)
);

INSERT INTO Nurses(NurseID, FirstName, LastName, Title, IsSalaried, StartDate)
VALUES (1, "Adam", "Apple", "The Big Cheese", 0, "1/1/1970");

INSERT INTO Nurses(NurseID,  FirstName, LastName, Title, IsSalaried, StartDate)
VALUES (2, "Betty", "Boop", "The Bigg Cheese", 0, "1/1/1970");

INSERT INTO Nurses(NurseID, FirstName, LastName, Title, IsSalaried, StartDate)
VALUES (3, "Charlie", "Chaplin", "The Biggg Cheese", 0, "1/1/1970");

INSERT INTO Nurses(NurseID, FirstName, LastName, Title, IsSalaried, StartDate)
VALUES (4, "Debbit", "Donsht", "The Bigggg Cheese", 0, "1/1/1970");

/* Display all the records from the table
SELECT N.FirstName, N.LastName, M.StartTime, M.EndTime, D.DeptName, S.Date
FROM Nurses N, ShiftInstance M, Schedule S, Department D
WHERE S.ScheduleID = M.ScheduleID AND S.Date = "MONDAY" AND M.NurseID = N.NurseID AND D.DeptID = M.DeptID
ORDER BY N.LastName;
*/

/* Display all the records from the table
SELECT N.FirstName, N.LastName, M.StartTime, M.EndTime, D.DeptName, S.Date
FROM Nurses N, ShiftInstance M, Schedule S, Department D
WHERE S.ScheduleID = M.ScheduleID AND M.NurseID = N.NurseID AND D.DeptID = M.DeptID AND N.FirstName =
ORDER BY N.LastName;


SELECT N.FirstName, N.LastName,

    (SELECT M.StartTime
    FROM Nurses N, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND S.Date = "MONDAY" AND M.NurseID = N.NurseID AND D.DeptID = M.DeptID
    ORDER BY N.LastName)

FROM Nurses N
ORDER BY N.LastName;
*/

/*SELECT M.StartTime
    FROM Nurses N, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND S.Date = "MONDAY" AND M.NurseID = N.NurseID AND D.DeptID = M.DeptID
    ORDER BY N.LastName;*/

/*SELECT N.FirstName, N.LastName,

    (SELECT M.StartTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "MONDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS MondayStart,

    (SELECT M.EndTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "MONDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS MondayEnd,

    (SELECT D.DeptName
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "MONDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS Department,

    (SELECT M.StartTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "TUESDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS TuesdayStart,

    (SELECT M.EndTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "TUESDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS TuesdayEnd,

    (SELECT D.DeptName
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "TUESDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS Department,

    (SELECT M.StartTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "WEDNESDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS WednesdayStart,

    (SELECT M.EndTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "WEDNESDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS WednesdayEnd,

    (SELECT D.DeptName
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "WEDNESDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS Department,

    (SELECT M.StartTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "THURSDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS ThursdayStart,

        (SELECT M.EndTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "THURSDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS ThursdayEnd,

    (SELECT D.DeptName
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "THURSDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS Department,

    (SELECT M.StartTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "FRIDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS FridayStart,

    (SELECT M.EndTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "FRIDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS FridayEnd,

    (SELECT D.DeptName
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "FRIDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS Department,

    (SELECT M.StartTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "SATURDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS SaturdayStart,

    (SELECT M.EndTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "SATURDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS SaturdayEnd,

    (SELECT D.DeptName
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "SATURDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS Department,

    (SELECT M.StartTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "SUNDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS SundayStart,

    (SELECT M.EndTime
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "SUNDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS SundayEnd,

    (SELECT D.DeptName
    FROM Nurses Nu, ShiftInstance M, Schedule S, Department D
    WHERE S.ScheduleID = M.ScheduleID AND Nu.NurseID = N.NurseID AND S.Date = "SUNDAY" AND M.NurseID = Nu.NurseID AND D.DeptID = M.DeptID
    ORDER BY Nu.LastName) AS Department

FROM Nurses N
ORDER BY N.LastName;*/