Phase2.sql

/* Group 10: Paul Ruggiero, Karinne Aiello, Catherine Foley */


/*  Part 1 */

-- DROP TABLE StayIn;
-- DROP TABLE Examine;
-- DROP TABLE Admission;
-- DROP TABLE Patient;
-- DROP TABLE RoomAccess;
-- DROP TABLE RoomService;
-- DROP TABLE Equipment;
-- DROP TABLE Room;
-- DROP TABLE CanRepairEquipment;
-- DROP TABLE EquipmentType;
-- DROP TABLE EquipmentTechnician;
-- DROP TABLE Doctor;
-- DROP TABLE Employee;


CREATE TABLE Employee(
    ID VARCHAR2(10) PRIMARY KEY,
    FName VARCHAR2(25) NOT NULL,
    Lname VARCHAR2(25) NOT NULL,
    Salary INTEGER NOT NULL,
    jobTitle VARCHAR2(30) NOT NULL,
    OfficeNum VARCHAR2(30),
    empRank INTEGER NOT NULL,
    supervisorID VARCHAR2(30),
    AddressStreet VARCHAR2(10) NOT NULL,
    AddressCity VARCHAR2(20) NOT NULL,
    CONSTRAINT empRankVal check(empRank in (0,1,2))
);

CREATE TABLE Doctor(
    EmployeeID VARCHAR2(10) PRIMARY KEY,
    Gender CHAR(1) NOT NULL,
    Specialty VARCHAR2(20) NOT NULL,
    GraduatedFrom VARCHAR2(20) NOT NULL,
    FOREIGN KEY (EmployeeID) REFERENCES Employee(ID),
    CONSTRAINT GenderCheck check(Gender in ('M', 'F', 'X'))
);

CREATE TABLE EquipmentTechnician(
    EmployeeID VARCHAR2(11) PRIMARY KEY,
    FOREIGN KEY (EmployeeID) REFERENCES Employee(ID)
);

CREATE TABLE EquipmentType(
    ID VARCHAR2(11) PRIMARY KEY,
    Description VARCHAR2(20) NOT NULL,
    Model VARCHAR2(20) NOT NULL,
    Instructions VARCHAR2(1000) NOT NULL,
    NumberOfUnits INTEGER NOT NULL
);

CREATE TABLE Room(
    Num INTEGER PRIMARY KEY,
    Occupied INTEGER,
    CONSTRAINT occupiedCheck check(Occupied in (0,1))
);

CREATE TABLE Equipment(
    SerialNumber VARCHAR2(11) PRIMARY KEY,
    TypeID VARCHAR2(20) NOT NULL,
    PurchaseYear INTEGER NOT NULL,
    LastInspection VARCHAR2(20) NOT NULL,
    roomNum INTEGER NOT NULL,
    FOREIGN KEY (TypeID) REFERENCES EquipmentType(ID),
    FOREIGN KEY (roomNum) REFERENCES Room(Num)
);

CREATE TABLE CanRepairEquipment(
    EmployeeID VARCHAR2(11),
    FOREIGN KEY(EmployeeID) REFERENCES EquipmentTechnician(EmployeeID),
    EquipmentType VARCHAR2(20),
    FOREIGN KEY (EquipmentType) REFERENCES EquipmentType(ID),
    PRIMARY KEY (EmployeeID, EquipmentType)
);

CREATE TABLE RoomService(
    RoomNum INTEGER,
    Service VARCHAR2(10),
    FOREIGN KEY(RoomNum) REFERENCES Room(Num),
    CONSTRAINT PK_Service PRIMARY KEY (RoomNum, Service)
);

CREATE TABLE RoomAccess(
    RoomNum INTEGER,
    EmployeeID VARCHAR2(11),
    FOREIGN KEY (RoomNum) REFERENCES Room(Num),
    FOREIGN KEY (EmployeeID) REFERENCES Employee(ID),
    CONSTRAINT PK_RoomAccess PRIMARY KEY (RoomNum, EmployeeID)
);

CREATE TABLE Patient(
    SSN VARCHAR2(11) PRIMARY KEY,
    FirstName VARCHAR2(25) NOT NULL,
    LastName VARCHAR2(25) NOT NULL,
    Address VARCHAR2(50),
    TelNum VARCHAR2(30)
);

CREATE TABLE Admission(
    Num INTEGER PRIMARY KEY,
    AdmissionDate DATE NOT NULL,
    LeaveDate DATE,
    TotalPayment DECIMAL NOT NULL,
    InsurancePayment NUMBER(5,4) NOT NULL,
    Patient_SSN VARCHAR2(11) NOT NULL,
    FOREIGN KEY(Patient_SSN) REFERENCES Patient(SSN),
    FutureVisitDate DATE,
    CONSTRAINT InsureAmount check(InsurancePayment BETWEEN 0 AND 1)
);

CREATE TABLE Examine(
    DoctorId VARCHAR2(10) NOT NULL,
    FOREIGN KEY (DoctorID) REFERENCES Doctor(EmployeeID),
    AdmissionNum INTEGER NOT NULL,
    FOREIGN KEY (AdmissionNum) REFERENCES Admission(Num),
    DoctorComment VARCHAR(500) NOT NULL
);

CREATE TABLE StayIn(
    AdmissionNum INTEGER,
    FOREIGN KEY (AdmissionNum) REFERENCES Admission(Num),
    RoomNum INTEGER,
    FOREIGN KEY (RoomNum) REFERENCES Room(Num),
    StartDate DATE,
    endDate DATE,
    CONSTRAINT Pk_StayIn PRIMARY KEY (AdmissionNum, RoomNum, StartDate)
);


/* Phase 3 */

INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('987-65-4321', 'John', 'Oracle', '42 Wallaby Way', '123-456-789');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('987-65-4322', 'Jane', 'Doe', '100 Institute', '123-456-790');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('987-65-3423', 'Joe', 'Smith', '123 West st', '123-456-791');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('000-00-0003', 'Alexander', 'Hamilton', '456 East St', '123-456-792');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('000-00-0001', 'Thing', '1', '789 Pennsylvania Ave', '123-456-793');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('000-00-0002', 'Thing', '2', '789 Pennsylvania Ave', '123-456-794');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('987-65-4324', 'Edward', 'Teach', 'High Seas', '123-456-795');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('987-65-4325', 'Adam', 'Smasher', '152 Main St', '123-456-796');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('987-65-4326', 'Elon', 'Musk', '600 White House', '123-456-797');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('987-65-4327', 'Spongebob', 'Squarepants', 'Pineapple Under the Sea', '123-456-798');
INSERT INTO Patient (SSN, FirstName, LastName, Address, TelNum) values('111-22-3333', 'Dexter', 'Morgan', '1155 103rd St', '123-456-789');


INSERT INTO Room(Num, Occupied) values(123456789, 1);
INSERT INTO RoomService(roomnum, service) values(123456789, 'MRI');
INSERT INTO RoomService(roomnum, service) values(123456789, 'X-ray');

INSERT INTO Room(Num, Occupied) values(7351038498, 0);
INSERT INTO RoomService(roomnum, service) values(7351038498, 'ICU');
INSERT INTO RoomService(roomnum, service) values(7351038498, 'X-ray');

INSERT INTO Room(Num, Occupied) Values(451038498, 1);
INSERT INTO RoomService(roomnum, service) values(451038498, 'Lobby');
INSERT INTO RoomService(roomnum, service) values(451038498, 'Operating');

INSERT INTO Room(Num, Occupied) values(123456781, 1);
INSERT INTO RoomService(roomnum, service) values(123456781, 'MRI');

INSERT INTO Room(Num, Occupied) values(123856781, 0);
INSERT INTO RoomService(roomnum, service) values(123856781, 'Operating');

INSERT INTO Room(Num, Occupied) values(123856771, 1);
INSERT INTO RoomService(roomnum, service) values(123856771, 'CAT scan');

INSERT INTO Room(Num, Occupied) values(123858771, 0);
INSERT INTO RoomService(roomnum, service) values(123858771, 'Nursery');

INSERT INTO Room(Num, Occupied) values(123858271, 1);
INSERT INTO RoomService(roomnum, service) values(123858271, 'Delivery');

INSERT INTO Room(Num, Occupied) values(123818271, 0);
INSERT INTO RoomService(roomnum, service) values(123818271, 'Bathroom');

INSERT INTO Room(Num, Occupied) values(923818271, 0);
INSERT INTO RoomService(roomnum, service) values(923818271, 'Meeting');


INSERT INTO EquipmentType(ID, Description, Model, Instructions, NumberOfUnits) values('123-abc-456', 'MRI Machine', 'Mk1', 'handle with care', 10);
INSERT INTO EquipmentType(ID, Description, Model, Instructions, NumberOfUnits) values('123-abc-789', 'CAT Scanner', 'MkII', 'Empty litter box frequently', 5);
INSERT INTO EquipmentType(ID, Description, Model, Instructions, NumberOfUnits) values('789-abc-456', 'Operating table', 'Mk3', 'Patient use only', 100);
INSERT INTO EquipmentType(ID, Description, Model, Instructions, NumberOfUnits) values('123-abc-780', 'q9 machine', 'q9 machine model', 'q9 instructions', 8);


INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('123-456-789', '123-abc-456', 2002, '02/03/2004', 123456781);
INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('123-456-790', '123-abc-456', 2002, '02/03/2004', 123456781);
INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('123-456-791', '123-abc-456', 2002, '02/03/2004', 123456781);

INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('923-456-792', '123-abc-789', 2013, '10/10/2020', 123856771);
INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('923-456-793', '123-abc-789', 2013, '10/10/2020', 123856771);
INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('923-456-794', '123-abc-789', 2013, '10/10/2020', 123856771);

INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('A01-02X', '789-abc-456', 2010, '5/22/2024', 123858771);
INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('A01-03X', '789-abc-456', 2010, '5/22/2024', 123858771);
INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('A01-04X', '789-abc-456', 2011, '5/22/2024', 123858771);
INSERT INTO Equipment(SerialNumber, TypeID, PurchaseYear, LastInspection, roomNum) values('987-654-320', '123-abc-780', 2011, '5/22/2023', 123858771);


INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(987654321, '10-APR-20', '15-APR-20', 10000.0, 0, '000-00-0002', '20-FEB-22');
INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(987654322, '20-FEB-22', '15-APR-22', 10000.0, 0.5, '000-00-0002', NULL);

INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(321069840, '02-MAY-77', '01-JUN-78', 100000.0, 0, '987-65-4325', '02-SEP-78');
INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(321069841, '02-MAY-77', '02-SEP-78', 100000.0, 0, '987-65-4325', NULL);

INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(814620749, '08-FEB-12', '01-JUN-23', 100000.0, 0, '987-65-4327', '20-MAR-24');
INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(162944387, '20-MAR-24', '21-MAR-24', 10.0, 0.30, '987-65-4327', NULL);

INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(162944388, '02-JAN-03', '03-JAN-03', 10000.0, 0.25, '987-65-4326', '20-MAR-04');
INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(162944389, '20-MAR-04', '03-JAN-05', 10000.0, 0.10, '987-65-4326', NULL);

INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(926148723, '02-JAN-10', '05-JAN-10', 5000.0, 0.31, '987-65-4324', '20-MAR-12');
INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(162944390, '20-MAR-12', '22-MAR-12', 10000.0, 1.00, '987-65-4324', NULL);

INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(162944391, '22-JUL-16', '25-JUL-16', 1000.0, 0.50, '111-22-3333', '22-AUG-17');

INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(162944354, '19-JUL-16', '19-JUL-16', 1000.0, 0.50, '111-22-3333', '25-AUG-17');
INSERT INTO Admission(num, admissiondate, leavedate, totalpayment, insurancepayment, patient_ssn, futurevisitdate) values(162944355, '20-JUL-16', '21-JUL-16', 1000.0, 0.50, '111-22-3333', NULL);


INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(12345678, 'John', 'Doctor', 100000, 'General Manager', 1, 2,'12345678', 'Main St', 'Worcester');
INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(92347104, 'John', 'Doctor', 100000, 'General Manager', 4, 2,'92347104', 'Main St', 'Worcester');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(12445678, 'John', 'Doctor', 100000, 'Division Manager', 5, 1,'12345678', 'Main St', 'Worcester');
INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(12455678, 'John', 'Doctor', 100000, 'Division Manager', 6, 1,'12345678', 'Main St', 'Worcester');
INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(12455679, 'John', 'Doctor', 100000, 'Division Manager', 7, 1,'12345678', 'Main St', 'Worcester');
INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(12455699, 'John', 'Doctor', 100000, 'Division Manager', 8, 1,'12345678', 'Main St', 'Worcester');


INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(98765432, 'Gregory', 'House', 100000, 'Regular Employee', 9, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (98765432, 'M', 'Diagnostics', 'John Hopkins');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(18765433, 'Gregory', 'Home', 100000, 'Regular Employee', 10, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (18765433, 'M', 'Diagnostics', 'John Hopkins');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(13765432, 'John', 'Hospital', 100000, 'Regular Employee', 11, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (13765432, 'M', 'Surgery', 'John Hopkins');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(13765440, 'Jane', 'Hospital', 100000, 'Regular Employee', 12, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (13765440, 'F', 'Surgery', 'John Hopkins');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(13765450, 'Jane', 'Building', 100000, 'Regular Employee', 13, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (13765450, 'F', 'Surgery', 'John Hopkins');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(11122233, 'Gregory', 'House', 100000, 'Regular Employee', 20, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (11122233, 'M', 'Diagnostics', 'John Hopkins');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(11122234, 'Gregory', 'Home', 100000, 'Regular Employee', 21, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (11122234, 'M', 'Diagnostics', 'John Hopkins');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(11122235, 'John', 'Hospital', 100000, 'Regular Employee', 22, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (11122235, 'M', 'Surgery', 'WPI');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(11122236, 'Jane', 'Hospital', 100000, 'Regular Employee', 23, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (11122236, 'F', 'Surgery', 'John Hopkins');

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(11122237, 'Jane', 'Building', 100000, 'Regular Employee', 24, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO Doctor(EmployeeID, Gender, Specialty, GraduatedFrom) values (11122237, 'F', 'Surgery', 'WPI');


INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(13765441, 'Joe', 'Tech', 100000, 'Regular Employee', 14, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO EquipmentTechnician(EmployeeID) values (13765441);

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(13765442, 'John', 'Tech', 100000, 'Regular Employee', 15, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO EquipmentTechnician(EmployeeID) values (13765442);

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(13765443, 'John', 'Lastname', 100000, 'Regular Employee', 16, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO EquipmentTechnician(EmployeeID) values (13765443);

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(13765444, 'John', 'Database', 100000, 'Regular Employee', 17, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO EquipmentTechnician(EmployeeID) values (13765444);

INSERT INTO Employee(ID, FName, Lname, Salary, jobTitle, OfficeNum, empRank, supervisorID, AddressStreet, AddressCity) values(13765445, 'Ryan', 'Oracle', 100000, 'Regular Employee', 18, 0,'12345678', 'Main St', 'Worcester');
INSERT INTO EquipmentTechnician(EmployeeID) values (13765445);


INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765441,'123-abc-456' );
INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765442,'123-abc-456' );
INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765445,'123-abc-789' );
INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765441,'789-abc-456' );
INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765442,'123-abc-789' );
INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765443,'123-abc-456' );
INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765444,'789-abc-456' );
INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765444,'123-abc-789' );
INSERT INTO CanRepairEquipment(EmployeeID, EquipmentType) values(13765444,'123-abc-780' );


INSERT INTO Examine(DoctorId, AdmissionNum, DoctorComment) values(11122237, 162944391, 'please free me');
INSERT INTO Examine(DoctorId, AdmissionNum, DoctorComment) values(11122237, 162944354, 'pretty please free me');
INSERT INTO Examine(DoctorId, AdmissionNum, DoctorComment) values(11122237, 162944354, 'pretty pretty please free me');


INSERT INTO RoomAccess(RoomNum, EmployeeID) values(123456781, 12345678);
INSERT INTO RoomAccess(RoomNum, EmployeeID) values(123856771, 92347104);
INSERT INTO RoomAccess(RoomNum, EmployeeID) values(123818271, 12455699);


/* Phase 2 */

/* Q1: This query is to report each employeeID, specialty, gender, and school of graduation for doctors that have graduated WPI */
SELECT D.employeeId, D.specialty, D.Gender, D.GraduatedFrom
FROM Doctor D
WHERE D.GraduatedFrom IN ('WPI');

/* Q2: This query is to select each employeeID, first name, last name, and salary for all regular employees supervised by a given division manager (ID = 12345678, in this case) */
SELECT E.ID, E.Fname,E.Lname, E.salary
FROM Employee E
WHERE E.supervisorID = 12345678
  AND E.empRank = 0;

/* Q3: This query is to select each patients SSN and the sum total of the payment made by their insurance company from all of that patients admissions */
SELECT A.Patient_SSN, SUM(A.InsurancePayment * A.TotalPayment) AS TotalInsurancePayment
FROM Admission A
GROUP BY A.Patient_SSN;

/* Q4: This query is to select each patient's SSN, first name, last name, and the total number of admissions they have had */
SELECT P.SSN, P.FirstName, P.LastName, COUNT(A.Num) AS AdminCount
FROM Admission A, Patient P
WHERE P.SSN = A.Patient_SSN
GROUP BY P.SSN, P.FirstName, P.LastName;

/* Q5: This query is to select the room number of a unit of equipment with a serial number equal to 'A01-02X' */
SELECT E.roomNum
FROM Equipment E
WHERE E.SerialNumber = 'A01-02X';

/* Q6: This query is to select the employeeID(s) and number of rooms they can access from all employees with access to the highest amount of rooms */
SELECT EmployeeID, MAX(Rooms)
FROM (SELECT EmployeeID, COUNT(RoomNum) AS Rooms
      FROM RoomAccess
      GROUP BY EmployeeID)
GROUP BY EmployeeID;

/* Q7: This query is to select the number of regular employees (empRank=0), division managers (empRank=1), and general managers (empRank=2) */
SELECT S.jobTitle AS Type, COUNT(S.ID) AS Count
FROM Employee S
GROUP BY S.jobTitle
ORDER BY Count DESC;

/* Q8: This query is to select the SSN, first name, last name, and the visit date from all patients who scheduled a future appointment at their last visit */
SELECT SSN, FirstName, LastName, FutureVisitDate
FROM Patient, Admission
WHERE SSN = Patient_SSN
  AND FutureVisitDate IS NOT NULL;

/*Q9: This query is to select all of the equipmentType that can be repaired by less than two equipment technicians */
SELECT EquipmentType
FROM CanRepairEquipment
GROUP BY EquipmentType
HAVING COUNT(EmployeeID) < 2;

/* Q10: This query is to select the date of the future visit that a patient made at their last appointment given their SSN, 111-22-3333 in this case */
SELECT MAX(A.FutureVisitDate) AS FutureVisit
FROM Admission A
WHERE A.Patient_SSN = '111-22-3333';

/* Q11: This query is to select the ID of the doctors who have examined a patient more than twice given their SSN, 111-22-3333 in this case */
SELECT E.DoctorId
FROM Examine E, Admission A
WHERE E.AdmissionNum = A.Num
  AND A.Patient_SSN = '111-22-3333'
GROUP BY E.DoctorId
HAVING COUNT(E.AdmissionNum) > 2;

/* Q12: This query is to select the IDs of the equipment types for which the hospital purchased equipment (units) of in both 2010 and 2011 */
SELECT C.TypeID
FROM Equipment C
WHERE C.PurchaseYear = 2010
INTERSECT
SELECT C.TypeID
FROM Equipment C
WHERE C.PurchaseYear = 2011;