text: -- Name: Project No. 4: SQL script with the advanced database objects
  -- Subject: Database Systems 2017/2018
  -- Institute: Faculty of Information Technology, Brno University of Technology
  --
  -- Author: xstupi00 AT stud.fit.vutbr.cz
  -- Author: xzubri00 AT stud.fit.vutbr.cz
  --
  -- Date: 01-05-2018
  
  SET serveroutput ON;
  
  -- drop TABLE
  DROP TABLE PERSON CASCADE CONSTRAINTS;
  DROP TABLE DOCTOR CASCADE CONSTRAINTS;
  DROP TABLE NURSE CASCADE CONSTRAINTS;
  DROP TABLE PATIENT CASCADE CONSTRAINTS;
  DROP TABLE OBLIGATION CASCADE CONSTRAINTS;
  DROP TABLE DEPARTMENT CASCADE CONSTRAINTS;
  DROP TABLE EXAMINATION CASCADE CONSTRAINTS;
  DROP TABLE HOSPITALIZATION CASCADE CONSTRAINTS;
  DROP TABLE MEDICATION CASCADE CONSTRAINTS;
  DROP TABLE ADMINISTRATION_MEDICATION CASCADE CONSTRAINTS;
  
  DROP SEQUENCE DEPARTMENT_ID;
  --DROP TRIGGER AUTO_INCREMENTATION_ID;
  --DROP TRIGGER CHECK_ID_NUMBER;
  --DROP TRIGGER CHECK_IBAN;
  --DROP TRIGGER SET_TURNDATE;
  --DROP PROCEDURE DETAILS_EXAMINATION;
  --DROP PROCEDURE MEDICAMENTS_COUNT;
  --DROP PROCEDURE REMOVE_OLD_EXAMINATION;
  --DROP INDEX INFO_INDEX;
  --DROP INDEX ID_INDEX;
  
  CONNECT xzubri00/KiRkPrDC;
    DROP MATERIALIZED VIEW nurse_view;
  CONNECT xstupi00/j06nyINz;
  
  -- create TABLE
  CREATE TABLE PERSON (
      PERSON_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
      "NAME" VARCHAR2(42) NOT NULL,
      SURNAME VARCHAR2(42) NOT NULL,
      BIRTHDATE DATE NOT NULL,
      ID_NUMBER INTEGER NOT NULL,
      TOWN VARCHAR2(42) NOT NULL,
      STREET VARCHAR2(42),
      HOUSE_NUMBER INTEGER NOT NULL,
      COUNTRY VARCHAR2(42) NOT NULL,
      POSTCODE INTEGER NOT NULL,
      SEX CHAR(1) CHECK (SEX = 'M' OR SEX = 'F') NOT NULL
  );
  
  CREATE TABLE DOCTOR (
      DOCTOR_ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
      SPECIALIZATION VARCHAR2(42) NOT NULL,
      "DEGREE" VARCHAR(42) NOT NULL,
      POSITION VARCHAR2(42) NOT NULL,
      ABBREVIATION VARCHAR2(42) NOT NULL,
      "STATE" VARCHAR2(10) DEFAULT 'ACTIVE',
      TURNDATE DATE
  );
  
  CREATE TABLE NURSE (
      NURSE_ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
      "DEGREE" VARCHAR2(42) NOT NULL,
      TURNDATE DATE,
      DEPARTMENT_ID INTEGER NOT NULL,
      COMPETENCE VARCHAR2(42) NOT NULL
  );
  
  CREATE TABLE PATIENT (
      PATIENT_ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
      WEIGHT INTEGER NOT NULL,
      HEIGHT INTEGER NOT NULL,
      HEALTH_CONDITION VARCHAR2(42) NOT NULL,
      ACCOUNT_NUMBER VARCHAR(42),
      REGISTRATION_DATE TIMESTAMP NOT NULL,
      DEATHDATE TIMESTAMP
  );
  
  CREATE TABLE OBLIGATION (
      OBLIGATION_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
      DOCTOR_ID INTEGER NOT NULL,
      DEPARTMENT_ID INTEGER NOT NULL,
      "TYPE" VARCHAR2(42) NOT NULL,
      PHONE_NUMBER varchar2(13) NOT NULL
  );
  
  CREATE TABLE DEPARTMENT (
      DEPARTMENT_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
      "NAME" VARCHAR2(42) NOT NULL,
      COUNT_OF_BEDS INTEGER NOT NULL,
      COUNT_OF_ROOMS INTEGER NOT NULL,
      START_VISITING_HOURS VARCHAR2(5) NOT NULL,
      END_VISITING_HOURS VARCHAR2(5) NOT NULL,
      FLOOR INTEGER NOT NULL
  );
  
  CREATE TABLE EXAMINATION (
      EXAMINATION_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
      DOCTOR_ID INTEGER NOT NULL,
      DEPARTMENT_ID INTEGER NOT NULL,
      HOSPITALIZATION_ID INTEGER NOT NULL,
      SPECIALIZATION VARCHAR2(40) NOT NULL,
      DATE_AND_TIME TIMESTAMP NOT NULL,        
      MEDICALRECORD VARCHAR2(1000) NOT NULL
  );
  
  CREATE TABLE HOSPITALIZATION (
      HOSPITALIZATION_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
      DOCTOR_ID INTEGER NOT NULL,
      DEPARTMENT_ID INTEGER NOT NULL,
      PATIENT_ID INTEGER NOT NULL,
      DATE_AND_TIME TIMESTAMP NOT NULL,
      HOSPITAL_RECORD VARCHAR2(1000) NOT NULL
  );
  
  CREATE TABLE MEDICATION ( 
      MEDICATION_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
      "NAME" VARCHAR2(42) NOT NULL,
      MAX_DOSE INTEGER NOT NULL,
      "FORM" VARCHAR2(42) NOT NULL,
      ACTIVE_SUBSTANCE VARCHAR2(42) NOT NULL,
      CONTRAINDICTION VARCHAR2(42) NOT NULL,
      SIDE_EFFECTS VARCHAR2(42)
  );
  
  CREATE TABLE ADMINISTRATION_MEDICATION (
      ADMINISTRATION_MEDICATION_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
      DOCTOR_ID INTEGER NOT NULL,
      MEDICATION_ID INTEGER NOT NULL,
      HOSPITALIZATION_ID INTEGER NOT NULL,
      "PROCEDURE" VARCHAR2(42),
      DATE_AND_TIME TIMESTAMP NOT NULL,
      FREQUENCY_AT_DAY INTEGER NOT NULL,
      WAY VARCHAR2(42) NOT NULL
  );
  
  -- set PRIMARY KEYS
  ALTER TABLE PERSON  ADD CONSTRAINT PK_PERSON_ID  PRIMARY KEY(PERSON_ID);
  ALTER TABLE DOCTOR  ADD CONSTRAINT PK_DOCTOR_ID  PRIMARY KEY(DOCTOR_ID);
  ALTER TABLE NURSE   ADD CONSTRAINT PK_NURSE_ID   PRIMARY KEY(NURSE_ID);
  ALTER TABLE PATIENT ADD CONSTRAINT PK_PATIENT_ID PRIMARY KEY(PATIENT_ID);
  
  ALTER TABLE OBLIGATION ADD CONSTRAINT PK_OBLIGATION_ID PRIMARY KEY(OBLIGATION_ID);
  ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPARTMENT_ID PRIMARY KEY(DEPARTMENT_ID);
  ALTER TABLE EXAMINATION ADD CONSTRAINT PK_EXAMINATION_ID PRIMARY KEY(EXAMINATION_ID);
  ALTER TABLE HOSPITALIZATION ADD CONSTRAINT PK_HOSPITALIZATION_ID PRIMARY KEY(HOSPITALIZATION_ID);
  ALTER TABLE MEDICATION ADD CONSTRAINT PK_MEDICATION_ID PRIMARY KEY(MEDICATION_ID);
  ALTER TABLE ADMINISTRATION_MEDICATION ADD CONSTRAINT PK_ADMINISTRATION_MEDICATION_ID PRIMARY KEY(ADMINISTRATION_MEDICATION_ID);
  
  -- set FOREIGN KEYS
  ALTER TABLE DOCTOR ADD CONSTRAINT FK_PERSON_ID_D FOREIGN KEY (DOCTOR_ID) REFERENCES PERSON(PERSON_ID);
  ALTER TABLE NURSE ADD CONSTRAINT FK_PERSON_ID_N FOREIGN KEY (NURSE_ID) REFERENCES PERSON(PERSON_ID);
  ALTER TABLE PATIENT ADD CONSTRAINT FK_PERSON_ID_P FOREIGN KEY (PATIENT_ID) REFERENCES PERSON(PERSON_ID);
  
  ALTER TABLE ADMINISTRATION_MEDICATION ADD CONSTRAINT FK_DOCTOR_ID_A FOREIGN KEY (DOCTOR_ID) REFERENCES DOCTOR(DOCTOR_ID);
  ALTER TABLE ADMINISTRATION_MEDICATION ADD CONSTRAINT FK_MEDICATION_ID_A FOREIGN KEY (MEDICATION_ID) REFERENCES MEDICATION(MEDICATION_ID);
  ALTER TABLE ADMINISTRATION_MEDICATION ADD CONSTRAINT FK_HOSPITALIZATION_ID_A FOREIGN KEY (HOSPITALIZATION_ID) REFERENCES HOSPITALIZATION(HOSPITALIZATION_ID);
  
  ALTER TABLE HOSPITALIZATION ADD CONSTRAINT FK_DOCTOR_ID_H FOREIGN KEY (DOCTOR_ID) REFERENCES DOCTOR(DOCTOR_ID);
  ALTER TABLE HOSPITALIZATION ADD CONSTRAINT FK_DEPARTMENT_ID_H FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID);
  ALTER TABLE HOSPITALIZATION ADD CONSTRAINT FK_PATIENT_ID_H FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT(PATIENT_ID);
  
  ALTER TABLE EXAMINATION ADD CONSTRAINT FK_DOCTOR_ID_E FOREIGN KEY (DOCTOR_ID) REFERENCES DOCTOR(DOCTOR_ID);
  ALTER TABLE EXAMINATION ADD CONSTRAINT FK_DEPARTMENT_ID_E FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID);
  ALTER TABLE EXAMINATION ADD CONSTRAINT FK_HOSPITALIZATION_ID_E FOREIGN KEY (HOSPITALIZATION_ID) REFERENCES HOSPITALIZATION(HOSPITALIZATION_ID);
  
  ALTER TABLE OBLIGATION ADD CONSTRAINT FK_DOCTOR_ID_O FOREIGN KEY (DOCTOR_ID) REFERENCES DOCTOR(DOCTOR_ID);
  ALTER TABLE OBLIGATION ADD CONSTRAINT FK_DEPARTMENT_ID_O FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID);
  
  ALTER TABLE NURSE ADD CONSTRAINT FK_DEPARTMENT_ID_N FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID);
  
  -- set CONSTRAINTS 
  ALTER TABLE OBLIGATION ADD CONSTRAINT LENGTH_PHONE_NUMBER CHECK (LENGTH(PHONE_NUMBER) = 13);
  ALTER TABLE DEPARTMENT ADD CONSTRAINT CNT_COUNT_OF_BEDS CHECK (COUNT_OF_BEDS > 0);
  ALTER TABLE DEPARTMENT ADD CONSTRAINT CNT_COUNT_OF_ROOMS CHECK (COUNT_OF_ROOMS > 0);
  ALTER TABLE DEPARTMENT ADD CONSTRAINT HOUR_START_VISITING_HOURS CHECK (START_VISITING_HOURS <= '15:00' AND START_VISITING_HOURS >= '12:30'); 
  ALTER TABLE DEPARTMENT ADD CONSTRAINT HOUR_END_VISITING_HOURS CHECK (END_VISITING_HOURS <= '17:30'); 
  ALTER TABLE ADMINISTRATION_MEDICATION ADD CONSTRAINT CNT_FREQUENCY CHECK (FREQUENCY_AT_DAY > 0);
  ALTER TABLE PATIENT ADD CONSTRAINT CHECK_WEIGHT CHECK (WEIGHT > 0); 
  ALTER TABLE PATIENT ADD CONSTRAINT CHECK_HEIGHT CHECK (HEIGHT > 0); 
  
  ---
  CREATE OR REPLACE TRIGGER CHECK_ID_NUMBER
    BEFORE INSERT OR UPDATE OF ID_NUMBER ON PERSON
    FOR EACH ROW
    DECLARE
      ID_NUMBER PERSON.ID_NUMBER%TYPE;
      "DAY" VARCHAR2(2);
      "MONTH" VARCHAR2(2);
      "YEAR" VARCHAR2(2);
      LEN INTEGER;
      FORMAT_ERROR INTEGER;
      DAY_ERROR INTEGER;
      MONTH_ERROR INTEGER;
      YEAR_ERROR INTEGER;
      DIVIDE_ERROR INTEGER;
    BEGIN
      ID_NUMBER := :NEW.ID_NUMBER;
      LEN := LENGTH(ID_NUMBER);
      "YEAR" := SUBSTR(ID_NUMBER, 1, 2);
      "MONTH" := SUBSTR(ID_NUMBER, 3, 2);
      "DAY" := SUBSTR(ID_NUMBER, 5, 2);
    
      FORMAT_ERROR := -20100;
      DAY_ERROR := -20200;
      MONTH_ERROR := -20300;
      YEAR_ERROR := -20400;
      DIVIDE_ERROR := -20500;
    
      IF ("MONTH" > 50) THEN
        "MONTH" := "MONTH" - 50;
      ELSE
        IF ("MONTH" > 20) THEN
          "MONTH" := "MONTH" - 20;
        END IF;
      END IF;
  
      IF ("DAY" > 40) THEN
        "DAY" := "DAY" - 40;
      END IF;
  
       IF (LEN = 9 OR LEN = 10) THEN 
         IF (LEN != 9 OR SUBSTR(ID_NUMBER, 7, 3) != '000') THEN 
           IF (MOD(ID_NUMBER, 11) = 0) THEN
              IF ("YEAR" >= 0 AND "YEAR" <= 99) THEN
                 IF ("MONTH" > 0 AND "MONTH" <= 12) THEN
                    IF ("DAY" > 0) THEN
                      IF (("MONTH" = 2 AND MOD("YEAR", 4) = 0 AND ("YEAR" <= 29)) OR "MONTH" > 0 AND "MONTH" <= 12) THEN
                         IF (("MONTH" = 2 AND MOD("YEAR", 4) != 0 AND ("DAY" > 28)) OR "MONTH" > 0 AND "MONTH" <= 12) THEN
                            IF (("MONTH" = 1 OR "MONTH" = 3 OR "MONTH" = 5 OR "MONTH" = 7 OR "MONTH" = 8 OR "MONTH" = 10 OR "MONTH" = 12) AND "DAY" > 31) THEN
                              raise_application_error(DAY_ERROR, 'The given DAY is not in the correct format, you check it!');
                            ELSE  
                               IF (("MONTH" = 4 OR "MONTH" = 6 OR "MONTH" = 9 OR "MONTH" = 11) AND "DAY" > 30) THEN 
                                 raise_application_error(DAY_ERROR, 'The given DAY is not in the correct format, you check it!'); END IF;
                            END IF;
                         ELSE 
                           raise_application_error(DAY_ERROR, 'The given DAY is not in the correct format, you check it!'); END IF;
                      ELSE 
                        raise_application_error(DAY_ERROR, 'The given DAY is not in the correct format, you check it!'); END IF;
                    ELSE
                      raise_application_error(DAY_ERROR, 'The given DAY is not in the correct format, you check it!'); END IF;
                  ELSE  
                    raise_application_error(MONTH_ERROR, 'The given MONTH is not in the correct format, you check it!'); END IF;
              ELSE 
                raise_application_error(YEAR_ERROR, 'The given YEAR is not in the correct format, you check it!'); END IF;
           ELSE 
             raise_application_error(DIVIDE_ERROR, 'The identification number must be divided by number eleven!'); END IF;
         ELSE 
           raise_application_error(FORMAT_ERROR, 'The bad format of the identification number (bad end parts (000)!'); END IF;
       ELSE
         raise_application_error(FORMAT_ERROR, 'The bad format of the identification number (bad count of numbers) !'); END IF;
    END;
    /
    
  CREATE SEQUENCE DEPARTMENT_ID START WITH 1000 INCREMENT BY 100;
  
  CREATE OR REPLACE TRIGGER AUTO_INCREMENTATION_ID
    BEFORE INSERT ON DEPARTMENT
    FOR EACH ROW
    BEGIN
      :NEW.DEPARTMENT_ID := DEPARTMENT_ID.nextval;
    END;
    /
    
  CREATE OR REPLACE TRIGGER CHECK_IBAN
    BEFORE INSERT OR UPDATE
    OF ACCOUNT_NUMBER ON PATIENT
    FOR EACH ROW
    DECLARE
      PATIENT_ACCOUNT PATIENT.ACCOUNT_NUMBER%TYPE;
      CHECK_ACCOUNT VARCHAR(24);
      CHECK_ACCOUNT_TRANSFER VARCHAR(50);
      ACCOUNT_NUMBER VARCHAR(20);
      COUNTRY  VARCHAR(2);
      SUM_CODE VARCHAR(2);
      INDEX_STR INT;
      CHAR_STR CHAR;
      LENGTH_ERROR INTEGER;
      COUNTRY_ERROR INTEGER;
      FORMAT_ERROR INTEGER;
  	BEGIN
        PATIENT_ACCOUNT := :NEW.ACCOUNT_NUMBER;
        INDEX_STR := 1;
        COUNTRY   := SUBSTR(PATIENT_ACCOUNT, 1, 2);
        SUM_CODE  := SUBSTR(PATIENT_ACCOUNT, 3, 2);
        
        ACCOUNT_NUMBER := SUBSTR(PATIENT_ACCOUNT, 5, 20);
        CHECK_ACCOUNT  := CONCAT(CONCAT(ACCOUNT_NUMBER, COUNTRY), SUM_CODE);
  
        LENGTH_ERROR  := -20900;
        COUNTRY_ERROR := -20800;
        FORMAT_ERROR  := -20700;
        
        IF (LENGTH(PATIENT_ACCOUNT) != 24) THEN
  			RAISE_APPLICATION_ERROR(LENGTH_ERROR, 'The length of the account number must be 24!');
        END IF;
        
        IF (COUNTRY != 'SK' AND COUNTRY != 'CZ') THEN
  			RAISE_APPLICATION_ERROR(COUNTRY_ERROR, 'Uncorrect format of the account number!');
        END IF;
        
        WHILE (INDEX_STR <= LENGTH(CHECK_ACCOUNT)) LOOP
          CHAR_STR := SUBSTR(CHECK_ACCOUNT, INDEX_STR, 1);
          IF (ASCII(CHAR_STR) >= 65 AND ASCII(CHAR_STR) <= 90) THEN
            CHECK_ACCOUNT_TRANSFER := CONCAT(CHECK_ACCOUNT_TRANSFER, TO_CHAR((ASCII(CHAR_STR)-55)));
          ELSE
            CHECK_ACCOUNT_TRANSFER := CONCAT(CHECK_ACCOUNT_TRANSFER, CHAR_STR);
          END IF;
          INDEX_STR := INDEX_STR + 1;
        END LOOP;
        IF (MOD(TO_NUMBER(CHECK_ACCOUNT_TRANSFER), 97) != 1) THEN
          RAISE_APPLICATION_ERROR(FORMAT_ERROR, 'The check sum of the account number is not valid!');
        END IF;
  	END;
      /
  
  -- insert VALUES to TABLES
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Dewayne', 'Duff', TO_DATE('25-12-1950', 'DD-MM-YYYY'), 'Melbourne', '6th St.', 123, 'Australia', 32904, 'M', 9901047574);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Carmelita', 'Clukey', TO_DATE('17-06-1953', 'DD-MM-YYYY'), 'Honolulu', 'Goldfield Rd. ', 4, 'USA', 96815, 'F', 9712080642);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Erica', 'Ernert', TO_DATE('28-04-1963', 'DD-MM-YYYY'), 'Chevy Case', 'Pilgrim Avenue', 71, 'USA', 20815, 'F', 9609279130);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Terica', 'Tague', TO_DATE('24-01-1969', 'DD-MM-YYYY'), 'Chicago', 'Shirley Ave', 44, 'USA', 60185, 'F', 9210056559);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Magda', 'Montoro', TO_DATE('16-02-1976', 'DD-MM-YYYY'), 'South Windsor', 'Bownma St.', 70, 'USA', 06074, 'F', 9111118115);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Charlyn', 'Cissell', TO_DATE('04-12-1985', 'DD-MM-YYYY'), 'Orlando', 'S. Mangnolia St.', 514, 'USA', 32806, 'F', 9558238525);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Rufina', 'Ruocco', TO_DATE('14-05-1950', 'DD-MM-YYYY'), 'Waxhaw', 'Stillwater Drive Apt', 3, 'USA', 28173, 'F', 9606086622);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Milagros', 'Musto', TO_DATE('18-07-1960', 'DD-MM-YYYY'), 'Hialeah', 'Branch Street', 54, 'USA', 33010, 'F', 9258245975);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Alyson', 'Amore', TO_DATE('03-12-1970', 'DD-MM-YYYY'), 'Winona', 'Hartford Ave', 9542, 'USA', 55987, 'F', 9660239754);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Raisa', 'Reeser', TO_DATE('18-08-1973', 'DD-MM-YYYY'), 'Lawrence Township', 'Pineknool Ave', 622, 'USA', 08648, 'F', 9362168233);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Rodolfo', 'Rae', TO_DATE('16-07-1941', 'DD-MM-YYYY'), 'London', 'Naylo Rd.', 107, 'Great Britain', 58369, 'M', 9201181000);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Zachary', 'Zarrella', TO_DATE('15-05-1948', 'DD-MM-YYYY'), 'Newport', 'Aberthaw Rd.', 136, 'Great Britain', 69742, 'M', 9053176407);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Randell', 'Rosenburg', TO_DATE('09-12-1953', 'DD-MM-YYYY'), 'Barnsley', 'Cailhead Drive', 3, 'Great Britain', 361819, 'M', 9753253224);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Del', 'Dlneen', TO_DATE('06-02-1955', 'DD-MM-YYYY'), 'Minnesota', 'Orchard Street', 3957, 'USA', 55437, 'M', 9853215042);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Ernle', 'Engebretson', TO_DATE('19-04-1966', 'DD-MM-YYYY'), 'Ciney', 'Lodewijk De Raetlaan', 310, 'Belgium', 5590, 'M', 9805072134);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Conrad', 'Cypert', TO_DATE('24-09-1947', 'DD-MM-YYYY'), 'Strijpem', 'Rue des Campanules', 187, 'Belgium', 9620, 'M', 9312130355);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Quitin', 'Quesada', TO_DATE('09-06-1954', 'DD-MM-YYYY'), 'Orchimont', 'Rue de Boneffe', 442, 'Belgium', 5550, 'M', 9902175877);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Garrett', 'Grieves', TO_DATE('27-02-1958', 'DD-MM-YYYY'), 'Haine-Saint-Paul', 'Rue Fouramont', 303, 'Belgium', 7001, 'M', 9853113941);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Herschel', 'Henningsen', TO_DATE('16-11-1984', 'DD-MM-YYYY'), 'South Windsor', 'Bowman St.', 70, 'USA', 06074, 'M', 9911211706);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Shaun', 'Spires', TO_DATE('08-06-1986', 'DD-MM-YYYY'), 'Ladson', 'Shore Ave', 9449, 'USA', 07470, 'M', 9757254408);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Thi', 'Tilghman', TO_DATE('19-09-1990', 'DD-MM-YYYY'), 'Norristown', 'Old York Street', 9491, 'Australia', 07061, 'M', 9304229539);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Ernelda', 'Esterline', TO_DATE('22-10-1969', 'DD-MM-YYYY'), 'Stockbridge', 'Woodsman St.', 17, 'USA', 06016, 'F', 9051266510);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Junior', 'Janz', TO_DATE('13-03-1974', 'DD-MM-YYYY'), 'Warner Robins', 'Rock Creek Street', 7163, 'USA', 02127, 'M', 9712074383);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Rudolph', 'Raymer', TO_DATE('28-11-1982', 'DD-MM-YYYY'), 'Painesville', 'Greystone Ave.', 335, 'USA', 60453, 'M', 9711139658);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Milda', 'Metzinger', TO_DATE('28-09-1971', 'DD-MM-YYYY'), 'Halethorpe', 'Glenwood Drive', 167, 'USA', 54880, 'F', 9901253131);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Gayle', 'Gorski', TO_DATE('09-03-1943', 'DD-MM-YYYY'), 'Hackensack', 'Addison Drive', 3, 'USA', 21227, 'M', 9153076636);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Marilu', 'Miesner', TO_DATE('10-01-1977', 'DD-MM-YYYY'), 'Hartford', '4th St.', 568, 'USA', 44077, 'F', 9554159978);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Paz', 'Pegues', TO_DATE('29-05-1970', 'DD-MM-YYYY'), 'Boston', 'Prince Court', 602, 'USA', 31088, 'F', 9011144439);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Ressie', 'Roma', TO_DATE('31-07-1964', 'DD-MM-YYYY'), 'Oak Lawn', 'Glen Eagles St.', 457, 'USA', 30281, 'F', 9511106693);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Cathi', 'Cousar', TO_DATE('20-05-1980', 'DD-MM-YYYY'), 'Superior', 'Bayport Drive', 716, 'USA', 19401, 'F', 9410088314);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Maria', 'Curry', TO_DATE('12-02-1917', 'DD-MM-YYYY'), 'Warsaw', 'Jana Pavla II', 2, 'Poland', 02485, 'F', 9912198989);
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('John', 'Friedrich', TO_DATE('15-09-1987', 'DD-MM-YYYY'), 'Prague', 'Hlavni', 1, 'Czech Republic', 08601, 'M', 9255117091);
  
  
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(12, 'Anestesiology', 'MD, DA. FFA', 'MUDr., prof.', 'Head doctor');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION, TURNDATE) VALUES(1, 'Dermatology', 'FACD', 'MUDr.,', 'consultant', TO_DATE('13-04-2018', 'DD-MM-YYYY'));
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION, TURNDATE) VALUES(6, 'Neurology', 'MMed, MD', 'MUDr., Ph.D.', 'ordinary doctor', TO_DATE('25-02-2018', 'DD-MM-YYYY'));
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(10, 'Neurosurgery', 'SpR, MS, MRCS', 'MUDr.,', 'extern doctor');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(23, 'Pediatrics', 'DCH, CPH', 'PaedDr.', 'Head doctor');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(13, 'Psychiatry', 'FRCPsych, MClinPscychol', 'MUDr.', 'doctor specialist');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION, TURNDATE) VALUES(22, 'Surgery', 'BS, DS', 'MUDr., DrSc.', 'Head doctor', TO_DATE('01-04-2018', 'DD-MM-YYYY'));
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(28, 'Surgery', 'FRCS', 'Dr. h. c.', 'ordinary doctor');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(11, 'Urology', 'FFR, GP', 'MUDr.,', 'consultant');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(21, 'Radiology', 'DR, FRCR', 'MUDr.,', 'extern doctor');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(14, 'Orthopaedic Surgery', 'DCh, LMS, MChOrth', 'MUDr.,, doc.', 'Head doctor');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(15, 'Allergy and Immunology', 'FFPHM. DPH', 'MUDr.', 'ordinary doctor');
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(32, 'Gynecology', 'MCM, MMedSc', 'MUDr.', 'Head doctor');
  
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('Neurology',  15, 7, '13:00', '15:00', 2);
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('Urology', 20, 10, '12:30', '15;30', 3);
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('Radiology', 10, 5, '14:00', '15:00', 4);
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('Cardiology', 15, 8, '13:00', '15:00', 5);
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('Orthopaedics', 6, 3, '13:00', '15:00', 5);
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('General Surgery', 50, 25, '13:00', '16:00', 1);
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('Gynecology', 10, 5, '14:30', '16:00', 2);
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('Pediatrics', 30, 15, '15:00', '17:30', 4);
  INSERT INTO DEPARTMENT ("NAME", COUNT_OF_BEDS, COUNT_OF_ROOMS, START_VISITING_HOURS, END_VISITING_HOURS, FLOOR) VALUES('Psychiatry', 30, 30, '15:00', '15:30', 6);
  
  INSERT INTO NURSE (NURSE_ID, "DEGREE", DEPARTMENT_ID, COMPETENCE) VALUES (2, 'Mgr.', 1100, 'FULL');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", TURNDATE, DEPARTMENT_ID, COMPETENCE) VALUES (3, 'Bc.', TO_DATE('02-01-2018', 'DD-MM-YYYY'), 1000, 'LIMITED');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", DEPARTMENT_ID, COMPETENCE) VALUES (4, 'Mgr.', 1000, 'FULL');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", DEPARTMENT_ID, COMPETENCE) VALUES (5, 'PharmDr.', 1200, 'FULL');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", DEPARTMENT_ID, COMPETENCE) VALUES (6, 'Mgr.',  1200, 'LIMITED');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", DEPARTMENT_ID, COMPETENCE) VALUES (7, 'RNDr.', 1300, 'FULL');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", TURNDATE, DEPARTMENT_ID, COMPETENCE) VALUES (8, 'Bc.', TO_DATE('01-04-2018', 'DD-MM-YYYY'), 1400, 'LIMITED');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", DEPARTMENT_ID, COMPETENCE) VALUES (9, 'Mgr.', 1400, 'FULL');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", DEPARTMENT_ID, COMPETENCE) VALUES (10, 'Ing.', 1500, 'FULL');
  INSERT INTO NURSE (NURSE_ID, "DEGREE", DEPARTMENT_ID, COMPETENCE) VALUES (11, 'Mgr. ', 1500, 'LIMITED');
  
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE, DEATHDATE) VALUES (31, 82, 175, 'Allergic person', TO_DATE('23-11-2016', 'DD-MM-YYYY'), TO_DATE('01-03-2018', 'DD-MM-YYYY'));
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE, DEATHDATE) VALUES (30, 72, 180, 'Valetudinarian', TO_DATE('02-08-2014', 'DD-MM-YYYY'), TO_DATE('15-05-2017', 'DD-MM-YYYY'));
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE, DEATHDATE) VALUES (29, 100, 190, 'Suscetible to tonsillitis', TO_DATE('17-10-2017', 'DD-MM-YYYY'), TO_DATE('23-10-2017', 'DD-MM-YYYY'));
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE, ACCOUNT_NUMBER) VALUES (27, 62, 153, 'Asthmatic', TO_DATE('29-12-2017', 'DD-MM-YYYY'), 'CZ6907101781240000004159'); 
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE, ACCOUNT_NUMBER) VALUES (26, 93, 179, 'Diabetic', TO_DATE('29-12-2017', 'DD-MM-YYYY'), 'CZ5508000000001234567899');
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE, ACCOUNT_NUMBER) VALUES (25, 90, 183, 'Disabled person', TO_DATE('03-01-2018', 'DD-MM-YYYY'), 'SK8975000000000012345671');
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE, ACCOUNT_NUMBER) VALUES (24, 87, 192, 'Nothing special', TO_DATE('28-02-2018', 'DD-MM-YYYY'), 'SK3112000000198742637541');
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE) VALUES (20, 98, 201, 'Susceptible to flu', TO_DATE('30-03-2018', 'DD-MM-YYYY'));
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE) VALUES (19, 79, 175, 'Diabetic', TO_DATE('12-12-2017', 'DD-MM-YYYY'));
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE) VALUES (16, 82, 187, 'Allergic person', TO_DATE('30-11-2017', 'DD-MM-YYYY'));
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE) VALUES (13, 68, 157, 'Psychically labile', TO_DATE('11-01-2018', 'DD-MM-YYYY'));
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE) VALUES (17, 115, 200,'Disabled person', TO_DATE('22-09-2017', 'DD-MM-YYYY'));
  INSERT INTO PATIENT (PATIENT_ID, WEIGHT, HEIGHT, HEALTH_CONDITION, REGISTRATION_DATE) VALUES (18, 84, 190, 'Asthmatic', TO_DATE('19-10-2017', 'DD-MM-YYYY'));
  
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(12, 1100, 'Operations specialist', '(513)863-2200');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(12, 1500, 'Operations specialist', '(513)863-2600');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(01, 1400, 'Dermatology specialist', '(513)863-2500');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(06, 1000, 'Chief expert', '(513)863-2100');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(06, 1300, 'Consultant',  '(513)863-2400');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(06, 1500, 'Assistant doctor', '(513)863-2601');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(10, 1000, 'Assistant doctor', '(513)863-2101');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(10, 1500, 'Neurosurgery specialist', '(513)863-2602');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(23, 1700, 'Chief expert', '(513)863-2800');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(13, 1800, 'Psychotherapist', '(513)863-2900');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(13, 1000, 'Consultant', '(513)863-2103');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(22, 1500, 'Intervention specialist', '(513)863-2603');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(28, 1500, 'Ordaining doctor', '(513)863-2604');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(22, 1400, 'Assistant doctor', '(513)863-2501');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(11, 1100, 'Chief expert', '(513)863-2201');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(21, 1200, 'Chief expert', '(513)863-2300');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(14, 1500, 'Chief expert', '(513)863-2605');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(14, 1400, 'Orthopaedic surgery specialist', '(513)863-2502');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(14, 1700, 'Consultant', '(513)863-2801');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(15, 1600, 'Immunology specialist', '(513)863-2700');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(32, 1600, 'Chief expert', '(513)863-2701');
  INSERT INTO OBLIGATION (DOCTOR_ID, DEPARTMENT_ID, "TYPE", PHONE_NUMBER) VALUES(15, 1200, 'Assistant doctor', '(513)863-2301');
  
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(06, 1000, 31, TO_TIMESTAMP('23-11-2016 12:18:32', 'DD-MM-YYYY HH24:MI:SS'), 'The patient is a 35-year-old female who complains of a cough for one week. She reports a fever last Saturday that is now resolved. She has a slightly hoarse voice. She is a nonsmoker. She takes Prozac 20 mg daily for depression.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(06, 1000, 30, TO_TIMESTAMP('02-08-2014 15:15:58', 'DD-MM-YYYY HH24:MI:SS'), 'The patient was administered 3 mCi of Thallium 201 Chloride and a routine SPECT rest study was performed. The patient was subsequently exercised using the standard Bruce protocol until at least 85% of MPHR was achieved. The patient was then administered 30mCi of Tc99m Myoview at peak stress.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(06, 1000, 13, TO_TIMESTAMP('11-01-2018 03:25:47', 'DD-MM-YYYY HH24:MI:SS'), 'The patient is a 52-year-old gentleman with multiple medical problems and a symptomatic left inguinal hernia. He was chronically constipated and this caused that hernia to pop out. He also had urinary frequency. In the operating room, he had a left indirect inguinal hernia sac, but also was catheterized before the surgery and 700 cc was removed.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(06, 1500, 16, TO_TIMESTAMP('30-11-2017 09:28:17', 'DD-MM-YYYY HH24:MI:SS'), 'The patient comes in for follow up of his lupus. He is tolerating the Plaquenil without any difficulties. He had a right knee arthroscopy done and repair of a meniscal tear. He also underwent viscosupplementation, but he is not sure it made much of a difference. His left knee is bothering him and he was told that he needs viscosupplementation there, so he is going to be going back next week for that.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(10, 1000, 17, TO_TIMESTAMP('25-09-2017 11:59:02', 'DD-MM-YYYY HH24:MI:SS'), 'The patient presents to the office today for a follow-up evaluation of chronic pain in the low back. The patient initially injured his back at work lifting a sign that he states was frozen to the ground. In May of 2008, the patient had epidural steroid injections x 2. He had a reaction that was attributed to the corticosteroids. The patient states that he had relief with the injections. He continued to have pain.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(10, 1500, 18, TO_TIMESTAMP('19-10-2017 23:12:11', 'DD-MM-YYYY HH24:MI:SS'), 'The patient was last evaluated in our office on May 18th. She has a history of lumbar post laminectomy syndrome and lumbar disc herniation. The pain in the lower back is radiating down the side of the right leg. Pain intensity is rated as a 10/10 and it has been worse over the last few weeks. Her migraines have also become more intense. Her sleeping pattern has been disturbed, sleeping only about two hours in the evening.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(10, 1500, 25, TO_TIMESTAMP('03-01-2018 13:45:13', 'DD-MM-YYYY HH24:MI:SS'), 'This is a 59-year-old, right-handed woman with a history of hypertension, schizophrenia, and a fallopian ovarian tumor resection surgically and with radiotherapy treatment, who presented to the emergency room with a four-hour history of difficulty talking, and numbness and weakness on the right side. She was in her usual state of health until early the morning of admission when she woke up and noted numbness on her right side.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(23, 1700, 29, TO_TIMESTAMP('18-10-2017 00:03:25', 'DD-MM-YYYY HH24:MI:SS'), 'This 52-year-old male with a work related injury to his left knee and failure to improve on conservative therapy with MRI scan indicating oblique tearing of the posterior horn of the medial meniscus of the left knee was admitted at this time for diagnostic operative arthroscopic surgery. The patient underwent routine preadmission testing and was cleared for surgery. He was taken to the Operating Room on the date of admission and, under general anesthesia underwent resection of a torn posterior horn of his medial meniscus with chondroplasty of the anterior surface of the medial femoral condyle for grade III chondromalacia and three compartmental synovectomy for hypertrophic synovitis.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(23, 1700, 26, TO_TIMESTAMP('29-12-2017 10:32:47', 'DD-MM-YYYY HH24:MI:SS'), 'This is a 32-year-old gentleman who came in for reevaluation of diabetes mellitus. He was started on Metformin 500 mg t.i.d last week and so far he has tolerated the medication well. He stated he is feeling a lot better. He has no more polyuria, no polydipsia, and he has more energy.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(28, 1500, 20, TO_TIMESTAMP('30-03-2018 14:48:59', 'DD-MM-YYYY HH24:MI:SS'), 'She has a pulse of 84. Blood pressure is 120/80. She is here for an acute visit today complaining about a recurrent rash on her right buttock. A few days ago Heidi felt a tingling sensation in her upper right buttock and today noticed that she had developed some small vesicles. It is in a well-circumscribed area, in an area where she had lesions previously. She had cultures done back in May, on May 10th. It occurred first in January of this year. She had light growth of coagulase-negative staphylococcus and that was all on her last bacterial culture.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(28, 1500, 19, TO_TIMESTAMP('12-12-2017 12:58:01', 'DD-MM-YYYY HH24:MI:SS'), 'She is here for a re-check of weight. She is being seen on a chronic basis for her failure to thrive. Her normal weight is around 112. She is down to 95. Her pulse is 80. Blood pressure is 110/68. She has met a new friend and is enjoying his company. He is also helping out with things around the house and she appreciates that very much. She has gone to the graduation of her daughter from high school and her son from middle school.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(28, 1500, 24, TO_TIMESTAMP('28-02-2018 11:25:58', 'DD-MM-YYYY HH24:MI:SS'), 'The patient presents complaining of bilateral knee pain. She is known to have degenerative disc disease with osteoarthritis of the knees. On examination, both knees are tender, minimally swollen and warm. Straight leg raising test is negative. The sensory examination of the back is unremarkable and the back is nontender.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(28, 1400, 30, TO_TIMESTAMP('15-12-2015 21:53:24', 'DD-MM-YYYY HH24:MI:SS'), 'The patient presents for a physical. The back pain has been present for just a few days and is slightly worse with movement. He has had pains in his elbows for approximately four months. It is relatively constant, no extreme. It does tend to hurt when he supinates his forearm fully. He has also had some mild pain in the back.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(22, 1400, 18, TO_TIMESTAMP('25-01-2018 14:59:21', 'DD-MM-YYYY HH24:MI:SS'), 'Patient states that she has always been overweight. She is very frustrated with trying diet. Her 20 year class reunion is next year and she would like to begin working toward a weight loss goal that is realistic.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(11, 1100, 19, TO_TIMESTAMP('29-12-2017 17:00:36', 'DD-MM-YYYY HH24:MI:SS'), 'Patient has pain in the left hip for 3 months. It is worse when walking or doing exercise. She has no known drug allergies.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(11, 1100, 29, TO_TIMESTAMP('08-03-2018 08:01:52', 'DD-MM-YYYY HH24:MI:SS'), 'Patient reported difficulties in home care, in particular cleaning and shopping. She expressed concern of putting strain on her son who is her primary caregiver. She is keen to get back to previous roles within home (mother/housewife) and visit friends.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(11, 1100, 27, TO_TIMESTAMP('28.01.2018 17:58:07', 'DD-MM-YYYY HH24:MI:SS'), 'This is a 55-year-old white female referred today for chest pain. She was recently evaluated for a typical chest pain and had exercise stress testing. She exercised for 9 minutes and 26 seconds and the test was clinically negative. She continues to have atypical chest pain complaints. She has no orthopnea, no lower extremity edema, no palpations, or dyspnea on exertion.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(21, 1200, 17, TO_TIMESTAMP('14-11-2017 17:45:21', 'DD-MM-YYYY HH24:MI:SS'), 'This is a 70-year-old male with chief complaint of low back pain and bilateral lower extremity pain. The patient denies any recent trauma to the low back. He states that he has been a body builder for the past 30 years and has worked out at the gym on a regular basis. He has entered in one multiple body building contests. He states that approximately 3 years ago, he was a using a machine at the gym doing crunches and felt a pop in his low back. He states that his back has not been the same since then.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(21, 1200, 19, TO_TIMESTAMP('15-03-2018 21:13:42', 'DD-MM-YYYY HH24:MI:SS'), 'This is a 43-year-old black man with no apparent past medical history who presented to the emergency room with the chief complaint of weakness, malaise and dyspnea on exertion for approximately one month. The patient also reports a 15-pound weight loss. He denies fever, chills, and sweats. He denies cough and diarrhea. He has mild anorexia.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(14, 1500, 13, TO_TIMESTAMP('15-03-2018 14:25:01', 'DD-MM-YYYY HH24:MI:SS'), 'Upper respiratory infection, probably viral. Phenergan with Codeine 150 mL, 2 tsp q.4-6h. p.r.n.. She is warned about the drowsy side effects of this. Follow up with her primary doctor next week if not improved.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(14, 1500, 16, TO_TIMESTAMP('08-03-2018 06:25:15', 'DD-MM-YYYY HH24:MI:SS'), 'He feels well and should continue on Prilosec 40 mg a day as part of a chronic antireflux regimen, particularly because of his cardiac history.  This is safe to take long term.  Biopsies of the stomach are negative for Helicobacter. He is scheduled for endoscopy on Thursday, June 14, at 7 a.m. It is my pleasure to help in the care of this very pleasant gentleman.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(14, 1400, 30, TO_TIMESTAMP('21-03-2017 12:53:48', 'DD-MM-YYYY HH24:MI:SS'), 'Likely herpetic rash. Cultures have been taken. We will also do a check for HSV-1 and HSV-2. I have given her Valtrex 500 mg twice daily for the next three days with p.r.n. refills. We will await the culture results to go further and, in the meantime, we talked about its spread and how to protect her partner. She was agreeable with the above- instructed plan. Risks, benefits and side effects of medications and treatments were discussed, and she will follow up as suggested.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(32, 1600, 31, TO_TIMESTAMP('02-08-2017 08:05:23', 'DD-MM-YYYY HH24:MI:SS'), 'There is mild decreased perfusion identified at the anteroseptal wall at stress which demonstrates normal perfusion at rest. In addition, there is mild to moderate decreased perfusion along inferolateral wall at stress which demonstrates normal perfusion at rest. These findings are likely related to ischemia. Remainder of the myocardia is well perfused with no transient dilatation.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(32, 1600, 24, TO_TIMESTAMP('01-04-2018 16:35:21', 'DD-MM-YYYY HH24:MI:SS'), 'After adequate general endotracheal anesthesia was introduced using a bronchoscopic incubation, a full catheter was placed and an impression device replaced on lower extremity. The patient was then prepped and draped in the usual sterile manner. I did a left parallel angle incision. The fascia was exposed leaving point coagulated a tie with 3-0 Vicryl. The fascia was lifted through external ring and the cord mobilized with a Penrose drain.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(32, 1600, 30, TO_TIMESTAMP('08-05-2017 17:15:32', 'DD-MM-YYYY HH24:MI:SS'), 'The floor was completely intact and the creamasteric fibers and internal spermatic fascia opened. It was immediately evident there was indirect sac. This was dissected down to the internal ring. It was reduced and the hemostasis was excellent. The hernia was pushed back and an extra large plug with a central pedal removed was placed and secured with a 2-0 Prolene anteriorally and medially.');
  INSERT INTO HOSPITALIZATION (DOCTOR_ID, DEPARTMENT_ID, PATIENT_ID, DATE_AND_TIME, HOSPITAL_RECORD) VALUES(15, 1200, 27, TO_TIMESTAMP('15-03-2018 22:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'THIS PATIENT FALLS WITHIN THE RANGE OF OSTEOPENIA. IN COMPARISON TO THE PRIOR EXAMINATION OF 10/25/08, THERE HAS BEEN AN INCREASE IN BONE MINERAL DENSITY OF L1 THROUGH L4 OF 1.3% AND AN INCREASE IN BONE MINERAL DENSITY OF THE TOTAL HIPS OF 0.3%');
  
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(06, 1000, 01, 'CT', TO_TIMESTAMP('24-11-2016 14:54:14', 'DD-MM-YYYY HH24:MI:SS'), 'CT was performed of the upper abdomen following the administration of IV contrast utilizing standard protocol. The current study is compared to a prior CT examination of the abdomen from 03/08/05 and a prior MRI of the upper abdomen from 09/07/05.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(06, 1000, 01, 'UltraSound', TO_TIMESTAMP('30-11-2016 07:18:58', 'DD-MM-YYYY HH24:MI:SS'), 'The obvious structures are intact. There is no dislocation. The infraspinatus and teres minor tendons are maintained. There is a focal vertical tear of the supraspinatus tendon at the insertion, which appears full thickness. There is no tendon retraction. The subscapularis tendon is intact.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(06, 1000, 02, 'MRI', TO_TIMESTAMP('04-08-2014 09:36:01', 'DD-MM-YYYY HH24:MI:SS'), 'There are five vertebrae with lumbar configuration. There is a 4.5 x 2.7 cm cystic structure noted on the lowest cut of the examination to the right of midline. This may represent a right adnexal cyst. Correlation with pelvic sonography is recommended.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(10, 1000, 02, 'Done Density', TO_TIMESTAMP('14-08-2014 11:11:11', 'DD-MM-YYYY HH24:MI:SS'), 'There is slight rightward deviation of the anterior aspect of the nasal septum. The nasal turbinates are within normal limits. There is normal aeration of the paranasal sinuses, without mucosal thickening or opacification. There is no air-fluid level identified. The ostiomeatal units appear patent.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(10, 1000, 03, 'Operation', TO_TIMESTAMP('13-01-2018 10:34:07', 'DD-MM-YYYY HH24:MI:SS'), 'He had laboratory workup done. It showed glucose of 241, BUN and creatinine is in good range, total bilirubin is 1.2, AST is 49, ALT 135, total cholesterol is 216, triglyceride 180, and hemoglobin A1c is 11.4. He does not have any microalbuminuria.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(12, 1500, 04, 'Operation', TO_TIMESTAMP('06-12-2017 08:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'She has a pulse of 84. Blood pressure is 120/80. She is here for an acute visit today complaining about a recurrent rash on her right buttock. A few days ago Heidi felt a tingling sensation in her upper right buttock and today noticed that she had developed some small vesicles. It is in a well-circumscribed area, in an area where she had lesions previously.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(28, 1500, 04, 'Postoperative Control', TO_TIMESTAMP('08-12-2017 12:24:57', 'DD-MM-YYYY HH24:MI:SS'), 'She had cultures done back in May, on May 10th. It occurred first in January of this year. She had light growth of coagulase-negative staphylococcus and that was all on her last bacterial culture. She has had multiple sex partners in her life. She has, she believes, been tested for herpes in the past and was negative. She has been monogamous with her current partner for quite some time now. She is trying to conceive a child and has been sexually active lately and is worried about giving this to her husband.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(10, 1000, 05, 'Preventive Control', TO_TIMESTAMP('29-09-2017 06:32:48', 'DD-MM-YYYY HH24:MI:SS'), 'She has a 2-3 cm well-circumscribed hyperemic area with small clear vesicles. Bacterial culture is taken. A viral culture is taken. She has no lymph nodes either in her anterior cervical chain, supra or subclavicular or inguinal area.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(06, 1500, 06, 'Preoperative Examination', TO_TIMESTAMP('20-10-2017 11:28:47', 'DD-MM-YYYY HH24:MI:SS'), 'She is here for a re-check of weight. She is being seen on a chronic basis for her failure to thrive. Her normal weight is around 112. She is down to 95. Her pulse is 80. Blood pressure is 110/68. She has met a new friend and is enjoying his company. He is also helping out with things around the house and she appreciates that very much. She has gone to the graduation of her daughter from high school and her son from middle school. Their father came to the son’s ceremony but not his daughter’s, which was her graduation from high school.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(14, 1500, 06, 'Operation', TO_TIMESTAMP('21-10-2017 09:30:00', 'DD-MM-YYYY HH24:MI:SS'), ' This had a huge impact on the family. Her son has actually bitten Daniel again and fortunately did not draw blood this time, but they are definitely having difficulties. She is trying to eat more but finding it difficult. Her back is really bothering her. She has had physical therapy but really has not noted any real improvement thus far. She was suggested to try Pilates, which she is doing daily. The pain is worse after working for several days and, although she had the injury on the left, it radiates all the way through to the right. At the end of the week, she has difficulty even walking around in the mall.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(28, 1500, 06, 'Postoperative Control', TO_TIMESTAMP('24-10-2017 06:57:32', 'DD-MM-YYYY HH24:MI:SS'), '	This is a well-appearing thin middle-aged female. She has slight exophthalmus, anicteric and pupils are equal round and reactive to light. She is very thin appearing with very little musculature. Her veins are clearly visible. She has no thyromegaly. Chest is clear to auscultation bilaterally. Cardiovascular is regular rate and rhythm. Her abdomen is scaphoid but soft with audible bowel sounds, is nontender and without organomegaly. Her extremities are warm and well perfused. There is no edema.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(22, 1500, 07, 'Foot amputation', TO_TIMESTAMP('05-01-2018 13:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'Osteopenia. She continues to take her vitamin D for her vitamin deficiency. She is also taking Actonel. She has no esophageal problems, but she does need lab work done in August. She was agreeable with the above-instructed plan. Risks, benefits and side effects of medication and treatments were discussed, and she will follow up as suggested.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(10, 1500, 07, 'Neurosurgery Control', TO_TIMESTAMP('05-01-2018 15:30:00', 'DD-MM-YYYY HH24:MI:SS'), 'Hands and wrists, two views of the right and left hand and wrist were obtained. There is generalized osteopenia. There are OA changes seen at the first CMC joint with subchondral sclerosis and joint space narrowing. Ulnar styloids appear intact. There is no chondrocalcinosis. There are some degenerative changes seen at the carpus but without any obvious erosive changes. There are no erosions seen at any of the MCP or PIP joints. There are scattered areas of joint space narrowing at the PIP and DIP joints.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(23, 1700, 08, 'Select of almonds', TO_TIMESTAMP('20-10-2017 10:24:47', 'DD-MM-YYYY HH24:MI:SS'), 'Shoulders, two views of the right and left shoulder were obtained. Bony mineralization appears within normal limits. There are no lytic or blastic lesions. There is mild degenerative change at the AC joint with some joint space narrowing. A pacemaker is seen in the left hemithorax. There is minimal periarticular calcification at the left shoulder near the supraspinatus insertion. There is mild elevation in the humeral head at the left and perhaps a minimal amount on the right.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(14, 1700, 08, 'Preventive Control', TO_TIMESTAMP('23-10-2017 07:30:52', 'DD-MM-YYYY HH24:MI:SS'), 'The lungs are well aerated. There is no evidence of any focal area of consolidation. A faint rounded density is seen in the base of the left lower hemithorax probably representing a nipple shadow. The hilar and pulmonary vasculature is normal. The heart size is within normal limits. The costophrenic angles are clear.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(23, 1700, 09, 'CT', TO_TIMESTAMP('30-12-2017 09:17:58', 'DD-MM-YYYY HH24:MI:SS'), 'Lumbosacral spine, multiple views of the lumbar spine were obtained. There appears to be a fracture at the coccyx which is age indeterminate. There is straightening of normal lumbar lordosis. Intervertebral disc spaces are maintained. Facet joints appear within normal limits bilaterally. There is a little bit of sacroiliac sclerosis bilaterally, more so on the right. The SI joints do appear patent.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(28, 1500, 10, 'Operation', TO_TIMESTAMP('01-04-2018 07:30:00', 'DD-MM-YYYY HH24:MI:SS'), 'Hips, two views of the right and left hip were obtained. Bony mineralization is normal. There is no acute fracture seen. There is no visible enthesopathy in the limited views of the pelvis. There is a little bit of superior sclerosis at the acetabula bilaterally. The hip joint spaces appear preserved.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(10, 1500, 10, 'Postoperation Control', TO_TIMESTAMP('01-04-2018 17:32:03', 'DD-MM-YYYY HH24:MI:SS'), 'No fractures, subluxations or other acute bony abnormalities are identified. There is mild degenerative change with mild endplate spur formation and mild facet sclerosis at all levels. No erosive or destructive changes are seen.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(12, 1500, 11, 'Sewing', TO_TIMESTAMP('14-12-2017 10:12:15', 'DD-MM-YYYY HH24:MI:SS'), 'he basilar cisterns, ventricular system and cortical sulci appear unremarkable for a 67-year-old patient. The visualized mastoid air cells and paranasal sinuses appear clear. There are multiple small foci of increased signal on T2 weighted and FLAIR images in the subcortical white matter, predominately in the frontal regions of both hemispheres. There is no evidence of mass effect, midline shift, extra-axial fluid collection or hemorrhage. There are no abnormal areas of contrast enhancement identified within the brain.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(28, 1500, 11, 'Postintervention Control', TO_TIMESTAMP('15-12-2017 07:14:47', 'DD-MM-YYYY HH24:MI:SS'), ' Focal abnormalities in the white matter as described above are generally associated with ischemic microvascular disease. A demyelinating process can have a similar appearance. Clinical correlation is suggested. See complete discussion above.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(06, 1500, 11, 'Preoperative Examination', TO_TIMESTAMP('18-12-2017 14:17:33', 'DD-MM-YYYY HH24:MI:SS'), 'MRI examination of the brain is performed using the following protocol: T1 weighted sagittal and coronal, T2 weighted coronal, FLAIR and T1 weighted axial sequences. The patient refused contrast enhancement so post-contrast scans have not been done. Also, please note that diffusion scans have not been done. The brain parenchyma shows normal signal intensity on the various pulse sequences. There is no intracranial mass, mass effect or area of abnormal signal intensity. The right temporal horn is slightly prominent and asymmetric. Flow voids are appreciated in the major intracranial vessels.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(22, 1500, 12, 'Foot amputation', TO_TIMESTAMP('21-03-2018 11:00:10', 'DD-MM-YYYY HH24:MI:SS'), 'The brain parenchyma shows normal signal intensity on the various pulse sequences. There is no intracranial mass, mass effect or area of abnormal signal intensity. The right temporal horn is slightly prominent and asymmetric. Flow voids are appreciated in the major intracranial vessels.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(10, 1500, 13, 'UltraSound', TO_TIMESTAMP('17-12-2015 09:44:11', 'DD-MM-YYYY HH24:MI:SS'), 'The brain parenchyma shows normal signal intensity. There is no confluent edema or mass effect. There is no extra-axial collection identified. The corpus callosum is well formed and it is in good position. The cerebellar tonsils are unremarkable. The pituitary gland is normal in size. The cavernous sinuses are symmetric. The basal cisterns are symmetric in appearance.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(12, 1500, 13, 'Operation', TO_TIMESTAMP('25-12-2015 00:53:24', 'DD-MM-YYYY HH24:MI:SS'), 'The cerebellopontine angle cisterns are normal in appearance, without mass or mass effect. The VII and VIII nerve complexes are unremarkable. There is no evidence for vestibular schwannoma or other mass. Trigeminal nerves are symmetric in caliber and in normal position. There are normal flow voids in the basilar artery and along the circle of Willis.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(14, 1400, 14, 'Done Density', TO_TIMESTAMP('27-01-2018 08:17:22', 'DD-MM-YYYY HH24:MI:SS'), 'ere are moderate intrameniscal myxoid changes in the medial meniscus. The intrameniscal myxoid changes are more pronounced in the posterior horn. There is no evidence of discrete meniscal tear extending to an articular surface in a linear fashion of either the medial or lateral meniscus. The anterior and posterior cruciate ligaments are intact. There is some edema medial to the MCL consistent with a grade I MCL sprain. There is also some fluid deep to the MCL below the joint line consistent with some TCL bursitis.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(22, 1500, 14, 'Postoperative Control', TO_TIMESTAMP('01-02-2018 07:16:46', 'DD-MM-YYYY HH24:MI:SS'), 'There is a fairly large broad band of edema in the medial tibial plateau extending to the medial aspect of the lateral tibial plateau compatible with an area of edema, or contusion. There is some subchondral degenerative changes in the femoral condyle. A discrete fracture line is not identified. The lateral collateral ligament appears intact. The extensor mechanism appears intact. There is chondromalacia patella most pronounced along the lateral facet as well as the lateral aspect of the medial facet near the patellar apex. The chondromalacia patella is most pronounced at the level of the upper pole of the patella. A few small subchondral cysts are seen within the patella. There is a small joint effusion.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(12, 1100, 15, 'X-Ray', TO_TIMESTAMP('05-01-2018 11:47:01', 'DD-MM-YYYY HH24:MI:SS'), 'MRI examination of the lumbar spine is performed using the following protocol: T1 weighted and T2 weighted axial and sagittal sequences. The lumbar vertebral alignment is well maintained. The vertebral body heights and disc spaces are well maintained. Normal T2 hyperintensities are seen within all the discs in the lumbar spine. Axial scans are done from disc space level L2-L3 through L5-S1. At disc space levels L4-L5 and L5-S1, there are very minimal diffuse disc bulges. There is no focal disc herniation, canal stenosis or foraminal narrowing seen at any of the levels in the lumbar spine.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(11, 1100, 15, 'Operation', TO_TIMESTAMP('08-01-2018 09:45:00', 'DD-MM-YYYY HH24:MI:SS'), 'The infraspinatus and teres minor tendons are intact. There is mild tendinosis of the supraspinatus tendon without tear. The subscapularis tendon is intact. The long head of the biceps tendon is in anatomic location and the biceps anchor is maintained.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(11, 1100, 15, 'Postoperative Control', TO_TIMESTAMP('09-01-2018 07:12:30', 'DD-MM-YYYY HH24:MI:SS'), 'Close attention was paid to insure that no lagophthalmos was created. At this point, lidocaine 2.0% with epinephrine was then injected locally under the premarked skin area, on the left upper lid as well as the right upper lid. Cool compresses were placed on the lids. Attention was first paid to the left side where a #15 blade was then used to incise the skin along the area that was premarked. The skin was then initially removed using Westcott, and 0.12 and then with a Bovie. Hemostasis was achieved using the Bovie cautery as well. Then 6-0 Prolene suture was then used to close the incision with a running subcuticular stitch pattern. The ends were bolstered with small pieces of rubber band and tied to the Prolene suture. The wound was noted to be well approximated.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(12, 1100, 16, 'X-Ray', TO_TIMESTAMP('10-03-2018 13:18:14', 'DD-MM-YYYY HH24:MI:SS'), 'No lagophthalmos was appreciated. The same technique was performed on the right upper lid with no lagophthalmos appreciated as well. Maxitrol and cold compresses were placed. Patient tolerated the procedure without any complications. Patient was instructed to take oral antibiotics and Topical TobraDex and follow up in one week with Dr. Richard in the eye clinic.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(12, 1100, 17, 'Preventive Control', TO_TIMESTAMP('04.02.2018 14:24:47', 'DD-MM-YYYY HH24:MI:SS'), 'The patient was taken to the Operating Room and was placed in a recumbent position. A time out was conducted in which the patient’s identity, surgical eyes and procedure was verified by the surgeon, circulating nurse and scrub nurse. Using a Colorado needle, an initial incision in the lower eyelid margin on the premarked skin area was performed. The skin was undermined and bleeding vessels were carefully cauterized with a held cautery. The spectrum was opened and excess fat was removed and a lateral tarsorrhaphy was performed as well.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(11, 1100, 17, 'MRI', TO_TIMESTAMP('05.02.2018 08:42:29', 'DD-MM-YYYY HH24:MI:SS'), 'The skin was closed with 6-0 plain suture. On the left upper eyelid, we also used a Colorado needle to make an initial elliptical incision on the premarked area of the skin. The skin was removed and it was closed with 6-0 Prolene suture. At the endo f the procedure, we gave 1 gm of Ancef and 8 mg of Decadron intravenously. The patient was sent to the Recovery Room without any complications where he received postoperative instructions and follow up appointments.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(21, 1200, 18, 'X-Ray', TO_TIMESTAMP('17-11-2017 09:27:53', 'DD-MM-YYYY HH24:MI:SS'), 'The patient was prepped and draped in the standard fashion with a tourniquet on the leg after preoperative antibiotics. The tourniquet was set 300 mm of pressure. Inferomedial and inferolateral portals were established. The scope was passed through an inferolateral portal. Evaluation of the suprapatellar pouch demonstrated no major abnormalities although some synovitis. The patella, however, demonstrated grade III chondromalacia especially along the lateral facet and over to the middle portion of the patella. There was no significant chondromalacia in the femoral groove.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(21, 1200, 18, 'Operation', TO_TIMESTAMP('19-11-2017 10:30:00', 'DD-MM-YYYY HH24:MI:SS'), 'The anterior cruciate ligament was intact. The medial compartment demonstrated no significant tears and no significant areas of chondromalacia. The lateral meniscus, however, demonstrated a large bucket-handle tear of the posterior horn and a flap tear in the body and anterior part of the meniscus. Initial attention was directed to the lateral meniscus where the flap tear and the bucket-handle tear were removed. This did not appear to be repairable. It was too damaged for an effective repair. The patient then underwent a trimming of it to a stable margin and once this had been established attention was directed to the patella.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(15, 1200, 18, 'CT', TO_TIMESTAMP('21-11-2017 07:24:51', 'DD-MM-YYYY HH24:MI:SS'), 'Initially, a mechanical debridement was done followed by a smoothing and rounding of the shoulders and edges with an ArthroCare on a setting of 2. This having been completed this, the knee was injected with platelet rich plasma to help healing, help hemostasis and prevent infection. She then was placed in a compressive dressing from toes to midthigh. She tolerated the procedure well and will be discharged home to be followed up in my office on Monday.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(21, 1200, 19, 'X-Ray', TO_TIMESTAMP('18-03-2018 11:47:23', 'DD-MM-YYYY HH24:MI:SS'), ' The patient was identified prior to operative area and holding area the correct eye was identified with a YES. A peribulbar block was given in the reoperative area under normal conditions. The patient was brought into the operative suite were leads and monitors were placed. It was confirmed that the left eye was the operative eye. A lid spectrum was spectrum was fashioned in the operative eye and the area was approached from the temporal area. A small groove was made at the two o’clock position using a corneal scleral blade. An incision was made at the six o’clock position with a 1 mm keratome. The anterior chamber was filled with Tryphan blue to stain the capsule. Viscoelastic was then inserted into the eye. The two o’clock position was then entered with a 2.7 mm keratome and using the cystotome and Utrata complete capsulorrhexis was performed.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(28, 1500, 20, 'Operation', TO_TIMESTAMP('16-03-2018 04:29:00', 'DD-MM-YYYY HH24:MI:SS'), 'The water tightness was complete. Miochol was inserted in the eye thus constricting the pupil and it appeared to be complete. The speculum was removed. Antibiotic medication was placed on the eye. A shield and a patch were placed on the eye and the drapes were removed. The reminder procedure without complications and there was no vitreous loss. The patient was taken to the Recovery Room where he recovered without any problems.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(10, 1500, 20, 'Neurosurgery Control', TO_TIMESTAMP('16-03-2018 15:39:41', 'DD-MM-YYYY HH24:MI:SS'), 'The procedure, risks, and benefits were explained to the patient, all questions were answered and consent was obtained. Patient was taken to the Operating Room and place in a decubitus-prone position on the operating table. Monitors were applied and sedation started by the anesthesiologist.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(01, 1400, 22, 'Flat-feet intervention', TO_TIMESTAMP('10-03-2018 08:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'Following the induction of IV Diprovan and inhalation of nitrous oxide, the gentle manipulation of the left total knee replacement was undertaken with audible lysis of adhesions. The manipulation was carried out to 115° of flexion, measured with a goniometer and minus 4° of full extension. Excellent stability of the knee stressed in full extension. Surgical wound well healed. The patient, following the manipulation, she was allowed to recover from her anesthesia and transported by a stretcher from the Operating Room to the Recovery Room in good condition.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(22, 1400, 22, 'Postintervention Conrol', TO_TIMESTAMP('11-03-2018 07:18:48', 'DD-MM-YYYY HH24:MI:SS'), 'he patient is a 32-year-old female, right hand dominant with a previous left carpal tunnel release, cubital tunnel release. The patient has had an increase in symptoms including muscle wasting and constant numbness. The release was done by another physician. A re-release will be performed with a transposition of the nerve.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(01, 1400, 22, 'Preventive Control', TO_TIMESTAMP('21-03-2018 09:41:53', 'DD-MM-YYYY HH24:MI:SS'), 'The patient was taken to the Operating Suite, placed in the supine position. A tourniquet was applied to the left upper extremity and set at 250 mm Hg. The arm was prepped and draped in the usual sterile fashion. The area in the palm and the elbow were infiltrated with 2% lidocaine and 0.25% Marcaine by me as well as IV sedation. The arm was maximally elevated, exsanguinated and the tourniquet was inflated to its preset pressures.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(32, 1600, 23, 'UltraSound', TO_TIMESTAMP('22-03-2017 04:22:00', 'DD-MM-YYYY HH24:MI:SS'), 'The first aspect of the procedure was to go ahead and make an incision in the palm. This was extended down to the transverse carpal ligament, which had reformed with scar tissue and an open release of the carpal tunnel was done, both the palm and volar forearm through the limited incision. A synovectomy was also performed due to the heavy reactive synovial tissue encountered. The synovial tissue was submitted to Pathology.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(15, 1600, 23, 'Immunology Control', TO_TIMESTAMP('22-03-2017 12:53:48', 'DD-MM-YYYY HH24:MI:SS'), 'The area was irrigated with antibiotic solution. All bleeding was meticulously controlled. After complete release of the carpal tunnel and transverse carpal ligament release the incision was closed using 4-0 nylon interrupted sutures.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(32, 1600, 24, 'UltraSound', TO_TIMESTAMP('14-08-2017 14:47:22', 'DD-MM-YYYY HH24:MI:SS'), 'Following this, my attention was then directed at the elbow. The medial epicondyle region, from the previous scar, was then incised carefully removing thick hard scar tissue. After considerable amount of dissection the ulnar nerve was identified and completely released at this time. The nerve appeared hyperemic and compressed. A minor transposition of the nerve and partial submuscular was performed and the area was irrigated with antibiotic solution.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(15, 1600, 25, 'UltraSound', TO_TIMESTAMP('01-04-2018 19:11:11', 'DD-MM-YYYY HH24:MI:SS'), 'The patient had incision lines dressed with Xeroform gauge, fluff and 4-inch Kling. The patient had the hand maximally elevated in a stockinettesling.The patient tolerated the procedure well and all sponge counts, needle counts and instrument counts were correct postoperatively. The patient is aware of possible post surgical sequelae, post traumatic complications including but not limited to hypertrophic scar, wound dehiscence, wound infection, retracted scar, contracted scar, nerve compression, nerve injury, tendon injury, further wasting and numbness of the hand and the possible need for further revisional surgery and/or occupation therapy.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(15, 1600, 25, 'Immnulogy Control', TO_TIMESTAMP('02-04-2018 00:02:57', 'DD-MM-YYYY HH24:MI:SS'), ':Anesthesia was administered without complication. An oculopress was placed over the eye for an appropriate duration. Prior to the actual surgical procedure a time out was undertaken with the patient present on the Operating Room table. The surgeon, anesthesia provider and circulating nurse confirmed the patient’s name, operative side, operative site and the exact procedure to be performed. The patient was prepped and draped in the usual sterile fashion.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(21, 1200, 26, 'X-Ray', TO_TIMESTAMP('14-05-2017 05:55:44', 'DD-MM-YYYY HH24:MI:SS'), 'Subjective growth of corneal lesion and decreased vision in the above noted eye with chronic inflammation subjectively noted. Preoperatively all risks, benefits, general considerations and alternatives regarding the above noted surgery have been explained by myself to the patient including possible loss of vision, infection, double vision, recurrence, the need for additional surgery and the potential loss of the eye. The patient has acknowledged understanding and has preoperatively signed the informed operative consent. Advised of all potential ramifications of surgery the patient desires to undergo the above noted surgery.');
  INSERT INTO EXAMINATION (DOCTOR_ID, DEPARTMENT_ID, HOSPITALIZATION_ID, SPECIALIZATION, DATE_AND_TIME, MEDICALRECORD) VALUES(15, 1200, 26, 'X-Ray', TO_TIMESTAMP('17-05-2017 11:11:11', 'DD-MM-YYYY HH24:MI:SS'), 'A wire lid speculum was used to open the eyelid. Two drops of Betadine 5.0% was placed on the eye for 1 minute. The ocular surface reconstruction was begun by removing the pterygium. A 57 blade was used to excise the pterygium in a lamellar fashion. The base of the pterygium was removed using a Westcott scissor. Cautery was used to obtain hemostasis. It was difficult to dissect the plane of the pterygium as there was diffuse corneal scarring');
  
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Codeine', 360, 'syrup', '3-Methylmorphine', 'opioids', 'dizziness');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Acetaminophen', 4000, 'liqiud, surup', 'Acetaminophen', 'severe renal impairment', 'yellow skin');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Lexapro', 10, 'tablet', 'Escitalopram', 'heart disease' ,'ejaculatory disorder');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Viagra', 100, 'tablet','SILDENAFIL CITRATE', 'heart attack', 'dyspepsia');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Ibuprofen', 3200, 'tablet', 'Genpril', 'pregnancy', 'decreased hemoglobin');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Xanax', 2, 'tablet', 'Niravam', 'pregnancy', 'ataxia');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Amoxicillin', 500, 'capsule', 'Moxatag', 'drug abuse', 'bleeding gums');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Lyrica', 100, 'capsule', 'Pregabalin', 'depresion', 'headache');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Lisinopril', 80, 'tablet', 'Prinivil', 'pregnancy', 'dizziness');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Metformin', 2500, 'tablet', 'Fortamet', 'heart attack', 'diarrhea');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Cymbalta', 30, 'capsule', 'Irenka', 'seizures', 'dizziness');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Naproxen', 450, 'capsule', 'Naprosyn', 'heart attack', 'stomach pain');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Tramadol', 400, 'suspension', 'Ultram', 'asthma', 'insomnia');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Oxycodone', 5, 'tablet', 'Oxaydo', 'seizures', 'faintness');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Trazodone', 375, 'tablet', 'Oleptro', 'heart disease', 'headache');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Gabapentin', 3600, 'capsule', 'Neurontin', 'pregnancy', 'dizziness');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Lorazepam', 1, 'tablet', 'Ativan', 'drug abuse', 'sedation');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Ciprofloxaxin', 500, 'syrup', 'Proquin', 'seizures', 'myalgia');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Zoloft', 125, 'capsule', 'Sertaline', 'weight loss', 'diarrhea');
  INSERT INTO MEDICATION ("NAME", MAX_DOSE, "FORM", ACTIVE_SUBSTANCE, CONTRAINDICTION, SIDE_EFFECTS) VALUES('Citalopram', 40, 'tablet', 'CeleXA', 'heart disease', 'impotence');
  
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(10, 05, 05, 'buccal', TO_TIMESTAMP('29-09-2017 06:34:12', 'DD-MM-YYYY HH24:MI:SS'), 3, 'injection');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(28, 10, 04, 'rectal', TO_TIMESTAMP('08-12-2017 12:27:02', 'DD-MM-YYYY HH24:MI:SS'), 1, 'infusion');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(28, 12, 06, 'oral', TO_TIMESTAMP('24-10-2017 06:58:02', 'DD-MM-YYYY HH24:MI:SS'), 5, 'solid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(10, 07, 07, 'sublingual', TO_TIMESTAMP('05-01-2018 15:32:14', 'DD-MM-YYYY HH24:MI:SS'), 2, 'solid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(14, 04, 08, 'intrathecal', TO_TIMESTAMP('23-10-2017 07:37:19', 'DD-MM-YYYY HH24:MI:SS'), 3, 'injection');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(10, 20, 10, 'infused', TO_TIMESTAMP('01-04-2018 17:42:47', 'DD-MM-YYYY HH24:MI:SS'), 10, 'infusion');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(28, 17, 11, 'rectal', TO_TIMESTAMP('15-12-2017 07:17:45', 'DD-MM-YYYY HH24:MI:SS'), 3, 'liquid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(06, 15, 11, 'buccal', TO_TIMESTAMP('18-12-2017 14:24:23', 'DD-MM-YYYY HH24:MI:SS'), 1, 'injection');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(22, 14, 14, 'intrathecal', TO_TIMESTAMP('01-02-2018 07:30:48', 'DD-MM-YYYY HH24:MI:SS'), 2, 'solid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(11, 01, 15, 'infused', TO_TIMESTAMP('09-01-2018 07:19:15', 'DD-MM-YYYY HH24:MI:SS'), 5, 'infusion');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(12, 03, 17, 'oral', TO_TIMESTAMP('04.02.2018 14:35:12', 'DD-MM-YYYY HH24:MI:SS'), 3, 'solid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(10, 16, 20, 'buccal', TO_TIMESTAMP('16-03-2018 15:47:10', 'DD-MM-YYYY HH24:MI:SS'), 2, 'liquid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(22, 11, 22, 'rectal', TO_TIMESTAMP('11-03-2018 07:24:01', 'DD-MM-YYYY HH24:MI:SS'), 3, 'liquid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(01, 12, 22, 'sublingual', TO_TIMESTAMP('21-03-2018 09:47:17', 'DD-MM-YYYY HH24:MI:SS'), 4, 'solid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(15, 02, 23, 'infused', TO_TIMESTAMP('22-03-2017 12:54:12', 'DD-MM-YYYY HH24:MI:SS'), 1, 'infusion');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(15, 05, 25, 'oral', TO_TIMESTAMP('02-04-2018 00:12:17', 'DD-MM-YYYY HH24:MI:SS'), 1, 'injection');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(23, 06, 09, 'buccal', TO_TIMESTAMP('17-12-2015 09:50:09', 'DD-MM-YYYY HH24:MI:SS'), 3, 'solid state');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(10, 09, 02, 'intrathecal', TO_TIMESTAMP('14-08-2014 11:25:48', 'DD-MM-YYYY HH24:MI:SS'), 2, 'injection');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(06, 13, 01, 'infused', TO_TIMESTAMP('24-11-2016 15:02:01', 'DD-MM-YYYY HH24:MI:SS'), 1, 'infusion');
  INSERT INTO ADMINISTRATION_MEDICATION (DOCTOR_ID, MEDICATION_ID, HOSPITALIZATION_ID, "PROCEDURE", DATE_AND_TIME, FREQUENCY_AT_DAY, WAY) VALUES(28, 07, 26, 'buccal', TO_TIMESTAMP('08-01-2018 09:52:41', 'DD-MM-YYYY HH24:MI:SS'), 3, 'solid state');
  
  -- Listing of clients that originate from the US
  SELECT P.PERSON_ID, P.NAME, P.SURNAME
  FROM PERSON P
  WHERE P.COUNTRY = 'USA';
  
  -- Listing of names of drugs which were administered to frequency once a day
  SELECT M.NAME
  FROM MEDICATION M NATURAL JOIN ADMINISTRATION_MEDICATION A
  WHERE A.FREQUENCY_AT_DAY = 1
  ORDER BY M.NAME;
  
  -- Listing of nurse that have limited competence
  SELECT P.NAME, P.SURNAME, N.NURSE_ID
  FROM PERSON P INNER JOIN NURSE N ON P.PERSON_ID = N.NURSE_ID AND N.COMPETENCE = 'LIMITED'
  ORDER BY P.NAME, P.SURNAME;
  
  -- Listing of doctors ID that described medication in injections way
  SELECT DISTINCT D.DOCTOR_ID
  FROM DOCTOR D, ADMINISTRATION_MEDICATION A
  WHERE D.DOCTOR_ID = A.DOCTOR_ID AND A.WAY = 'injection';
  
  -- Listing of doctors that do not work anymore 
  -- in the hospital and date of their dismissal
  SELECT P.NAME, P.SURNAME, D.TURNDATE 
  FROM DOCTOR D, PERSON P 
  WHERE D.DOCTOR_ID = P.PERSON_ID AND D.TURNDATE IS NOT NULL
  ORDER BY P.NAME, P.SURNAME;
  
  -- Listing of patients that have ever undergone surgery and its date
  SELECT DISTINCT P.NAME, P.SURNAME, E.DATE_AND_TIME 
  FROM PERSON P INNER JOIN PATIENT PT on P.PERSON_ID = PT.PATIENT_ID INNER JOIN HOSPITALIZATION H ON H.PATIENT_ID = PT.PATIENT_ID INNER JOIN EXAMINATION E ON E.HOSPITALIZATION_ID = H.HOSPITALIZATION_ID
  WHERE E.SPECIALIZATION = 'Operation'
  ORDER BY P.NAME, P.SURNAME;
  
  -- Listing of count of obligations of Head Doctors
  SELECT DISTINCT P.NAME, P.SURNAME, COUNT(*) AS HEAD_DOCTORS_COUNT_OBLIGATIONS
  FROM PERSON P INNER JOIN DOCTOR D on P.PERSON_ID = D.DOCTOR_ID INNER JOIN OBLIGATION O ON O.DOCTOR_ID = D.DOCTOR_ID
  WHERE D.POSITION = 'Head doctor'
  GROUP BY P.NAME, P.SURNAME
  ORDER BY COUNT(*) DESC;
  
  -- Listing of count of obligations all doctors
  SELECT D.DOCTOR_ID, P.NAME, P.SURNAME, COUNT(*) AS OBLIGATION_COUNT
  FROM PERSON P, DOCTOR D, OBLIGATION O 
  WHERE D.DOCTOR_ID = O.DOCTOR_ID AND O.DOCTOR_ID = P.PERSON_ID
  GROUP BY D.DOCTOR_ID, P.NAME, P.SURNAME
  ORDER BY COUNT(*) DESC;
  
  -- Listing of count of doctors on each department
  SELECT D.DEPARTMENT_ID, COUNT(*) AS DOCTORS_COUNT
  FROM DEPARTMENT D, OBLIGATION O 
  WHERE D.DEPARTMENT_ID = O.DEPARTMENT_ID
  GROUP BY D.DEPARTMENT_ID;
  
  -- Listing of doctors that execution some examination
  SELECT P.NAME, P.SURNAME, P.PERSON_ID
  FROM PERSON P 
  WHERE EXISTS (
      SELECT E.DOCTOR_ID 
      FROM EXAMINATION E 
      WHERE E.DOCTOR_ID = P.PERSON_ID
  );
  
  -- Listing of count of hospitalization of indiviudual patient
  SELECT P.NAME, P.SURNAME, COUNT(*) AS HOSPITALIZATION_COUNT  
  FROM PERSON P, PATIENT PT, HOSPITALIZATION H 
  WHERE P.PERSON_ID = PT.PATIENT_ID AND PT.PATIENT_ID = H.PATIENT_ID
  GROUP BY P.NAME, P.SURNAME
  ORDER BY COUNT(*) DESC;
  
  -- Listing of count of types of examination on the each department
  SELECT D.NAME, E.SPECIALIZATION, COUNT(*) AS DEPARTMENT_EXAM_COUNT  
  FROM DEPARTMENT D, EXAMINATION E
  WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
  GROUP BY D.NAME, E.SPECIALIZATION
  ORDER BY D.NAME, E.SPECIALIZATION;
  
  -- Maximal count of executed intervetion in the hospital and 
  -- department on wich was executed
  SELECT D.NAME, E.SPECIALIZATION, COUNT(*) AS DEPARTMENT_EXAM_COUNT  
  FROM DEPARTMENT D, EXAMINATION E
  WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
  GROUP BY D.NAME, E.SPECIALIZATION
  HAVING COUNT(*) >= ALL (
      SELECT COUNT(*)
      FROM DEPARTMENT D, EXAMINATION E
      WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
      GROUP BY D.NAME, E.SPECIALIZATION
  );
  
  -- Listing of patient that was hospitalized in year 2018
  SELECT NAME, SURNAME, PERSON_ID
  FROM PERSON
  WHERE PERSON_ID IN (
      SELECT PATIENT_ID 
      FROM PATIENT
      WHERE PATIENT_ID IN (
          SELECT PATIENT_ID  
          FROM HOSPITALIZATION
          WHERE DATE_AND_TIME BETWEEN TO_TIMESTAMP('01-01-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
                        AND TO_TIMESTAMP('15-04-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
                  )
          );
      
  CREATE OR REPLACE TRIGGER SET_TURNDATE
    BEFORE UPDATE OF "STATE" ON DOCTOR
    REFERENCING OLD AS MYOLD NEW AS MYNEW
    FOR EACH ROW
    BEGIN
      SELECT SYSDATE
      INTO : MYNEW.TURNDATE
      FROM DUAL;
    END SET_TURNDATE;
    /
    
  -- VIEW OF TRIGGER
  SELECT * FROM DOCTOR WHERE DOCTOR_ID = 12;
  UPDATE DOCTOR SET "STATE" = 'UNACTIVE' WHERE DOCTOR_ID = 12;
  SELECT * FROM DOCTOR WHERE DOCTOR_ID = 12;
  
  CREATE OR REPLACE PROCEDURE MEDICAMENTS_COUNT(EFFECT IN VARCHAR2)
    IS
      CURSOR MEDICAMENTS IS SELECT * FROM MEDICATION;
      "RECORD" MEDICAMENTS%ROWTYPE;
      COUNT_ALL NUMBER;
      COUNT_WITH_EFFECT NUMBER;
    BEGIN
      COUNT_ALL := 0;
      COUNT_WITH_EFFECT := 0;
      OPEN MEDICAMENTS;
        LOOP
          FETCH MEDICAMENTS INTO "RECORD";
          EXIT WHEN MEDICAMENTS%NOTFOUND;
        
          IF (TRIM("RECORD".SIDE_EFFECTS) = EFFECT) THEN
          COUNT_WITH_EFFECT := COUNT_WITH_EFFECT + 1;
          END IF;
        
          IF ("RECORD".SIDE_EFFECTS IS NOT NULL) THEN
            COUNT_ALL := COUNT_ALL + 1;
          END IF;
        END LOOP;
      CLOSE MEDICAMENTS;
      DBMS_OUTPUT.PUT_LINE('The database of the Hospital contains the: ' || COUNT_ALL || ' MEDICAMENTS.');
      DBMS_OUTPUT.PUT_LINE('The count of medicaments, which have the side effect ' || EFFECT || ' is: ' || COUNT_WITH_EFFECT);
      DBMS_OUTPUT.PUT_LINE('The percentage rate of medicaments with side effect ' || EFFECT || ' to all medicaments in the databse of hospital is ' || COUNT_WITH_EFFECT * 100 / COUNT_ALL || ' %');
      EXCEPTION
        WHEN ZERO_DIVIDE THEN
          DBMS_OUTPUT.PUT_LINE('The database of hospital, which contains the medicaments is empty.');
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20987, 'Some error was occured.');
    END MEDICAMENTS_COUNT;
    /
  
  BEGIN
    MEDICAMENTS_COUNT('headache');
    MEDICAMENTS_COUNT('dizziness');
  END;
  /
  
  CREATE OR REPLACE PROCEDURE REMOVE_OLD_EXAMINATION("DATE" EXAMINATION.DATE_AND_TIME%TYPE) AS
    CURSOR EXAMINATION_CURSOR IS
    SELECT EXAMINATION_ID, DATE_AND_TIME
    FROM EXAMINATION;
    "ROW" EXAMINATION_CURSOR%ROWTYPE;
    BEGIN
      OPEN EXAMINATION_CURSOR;
      FETCH EXAMINATION_CURSOR INTO "ROW";  
      WHILE EXAMINATION_CURSOR%found LOOP
        IF ("ROW".DATE_AND_TIME < "DATE") THEN
          DELETE FROM EXAMINATION WHERE "ROW".EXAMINATION_ID = EXAMINATION.EXAMINATION_ID;
        END IF;
        FETCH EXAMINATION_CURSOR INTO "ROW";
      END LOOP; 
      CLOSE EXAMINATION_CURSOR;
    END REMOVE_OLD_EXAMINATION;
    /
  
  CREATE OR REPLACE PROCEDURE DETAILS_EXAMINATION(SEARCH_EXAMINATION EXAMINATION.EXAMINATION_ID%TYPE) AS
    EXAM_SPECIALIZATION EXAMINATION.SPECIALIZATION%TYPE;
    ID_DOCTOR EXAMINATION.DOCTOR_ID%TYPE;
    SEARCH_HOSPITALIZATION EXAMINATION.HOSPITALIZATION_ID%TYPE;
    DATE_EXAMINATION EXAMINATION.DATE_AND_TIME%TYPE;
    PATIENT_NAME VARCHAR(42);
    PATIENT_SURNAME VARCHAR(42);
    DOCTOR_NAME VARCHAR(42);
    DOCTOR_SURNAME VARCHAR(42);
    BEGIN
      SELECT DOCTOR_ID, HOSPITALIZATION_ID, DATE_AND_TIME
      INTO ID_DOCTOR, SEARCH_HOSPITALIZATION, DATE_EXAMINATION
      FROM EXAMINATION
      WHERE SEARCH_EXAMINATION = EXAMINATION.EXAMINATION_ID;
    
      SELECT PERSON."NAME", PERSON.SURNAME
      INTO DOCTOR_NAME, DOCTOR_SURNAME
      FROM DOCTOR NATURAL JOIN PERSON
      WHERE ID_DOCTOR = PERSON.PERSON_ID AND ID_DOCTOR = DOCTOR_ID;
      
      SELECT SPECIALIZATION, "NAME", SURNAME
      INTO EXAM_SPECIALIZATION, PATIENT_NAME, PATIENT_SURNAME
      FROM EXAMINATION, HOSPITALIZATION, PERSON
      WHERE SEARCH_EXAMINATION = EXAMINATION.EXAMINATION_ID AND SEARCH_HOSPITALIZATION = HOSPITALIZATION.HOSPITALIZATION_ID AND HOSPITALIZATION.PATIENT_ID = PERSON.PERSON_ID;
      
      DBMS_OUTPUT.PUT_LINE('EXAMINATION with number ID ' || SEARCH_EXAMINATION || ', which was executed in the frame of HOSPITALIZATION with number ID ' || SEARCH_HOSPITALIZATION || ', has these details:');
      DBMS_OUTPUT.PUT_LINE('TYPE OF EXAMINATION: ' || EXAM_SPECIALIZATION);
      DBMS_OUTPUT.PUT_LINE('HOSPITALIZATION: ' || SEARCH_HOSPITALIZATION);
      DBMS_OUTPUT.PUT_LINE('DOCTOR : ' || DOCTOR_NAME  || ' '  || DOCTOR_SURNAME);
      DBMS_OUTPUT.PUT_LINE('PATIENT: ' || PATIENT_NAME || ' ' || PATIENT_SURNAME);
      DBMS_OUTPUT.PUT_LINE('DATE: ' || DATE_EXAMINATION);
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('THE EXAMINATION WITH NUMBER ID ' || SEARCH_EXAMINATION || ' IS NOT IN THE DATABASE OF THE HOSPITAL.');
    END DETAILS_EXAMINATION;
    /
    
  BEGIN
    DETAILS_EXAMINATION(42);
  END;
  /
  
  SELECT * FROM EXAMINATION WHERE DATE_AND_TIME <  TO_TIMESTAMP('01-01-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS');
  BEGIN
    REMOVE_OLD_EXAMINATION(TO_TIMESTAMP('01-01-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS'));
  END;
  /
  SELECT * FROM EXAMINATION WHERE DATE_AND_TIME <  TO_TIMESTAMP('01-01-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS');
  
  GRANT ALL ON PERSON TO xzubri00;
  GRANT ALL ON DOCTOR TO xzubri00;
  GRANT ALL ON NURSE TO xzubri00;
  GRANT ALL ON PATIENT TO xzubri00;
  GRANT ALL ON OBLIGATION TO xzubri00;
  GRANT ALL ON DEPARTMENT TO xzubri00;
  GRANT ALL ON EXAMINATION TO xzubri00;
  GRANT ALL ON HOSPITALIZATION TO xzubri00;
  GRANT ALL ON MEDICATION TO xzubri00;
  GRANT ALL ON ADMINISTRATION_MEDICATION TO xzubri00;
  
  -- materialized view
  CONNECT xzubri00/KiRkPrDC;
  CREATE MATERIALIZED VIEW NURSE_VIEW
    CACHE
    BUILD IMMEDIATE
    REFRESH COMPLETE
    AS
      SELECT DISTINCT SURNAME, "NAME", SPECIALIZATION, POSITION
      FROM xstupi00.DOCTOR NATURAL JOIN xstupi00.PERSON
      WHERE xstupi00.DOCTOR.DOCTOR_ID = xstupi00.PERSON.PERSON_ID;
    
  GRANT ALL ON NURSE_VIEW to xstupi00;
  
  CONNECT xstupi00/j06nyINz;
  
  SELECT *
  FROM xzubri00.NURSE_VIEW;
  
  INSERT INTO PERSON ("NAME", SURNAME, BIRTHDATE, TOWN, STREET, HOUSE_NUMBER, COUNTRY, POSTCODE, SEX, ID_NUMBER) VALUES('Peter', 'Panelak', TO_DATE('25-04-1977', 'DD-MM-YYYY'), 'Zvolen', 'Listova', 3, 'Slovakia', 08688, 'M', 9059040892);
  INSERT INTO DOCTOR (DOCTOR_ID, SPECIALIZATION, ABBREVIATION, "DEGREE", POSITION) VALUES(33, 'Anestesiology', 'MD, FFA', 'prof.', 'Doctor');
  
  SELECT *
  FROM xzubri00.NURSE_VIEW;
  
  CONNECT xzubri00/KiRkPrDC;
    EXECUTE DBMS_SNAPSHOT.REFRESH('nurse_view', 'COMPLETE');
  CONNECT xstupi00/j06nyINz;
  
  SELECT *
  FROM xzubri00.NURSE_VIEW;
  -- end materialized view
  
  EXPLAIN PLAN FOR 
    SELECT D.DOCTOR_ID, P.NAME, P.SURNAME, COUNT(*) AS OBLIGATION_COUNT
    FROM PERSON P, DOCTOR D, OBLIGATION O 
    WHERE D.DOCTOR_ID = O.DOCTOR_ID AND O.DOCTOR_ID = P.PERSON_ID
    GROUP BY D.DOCTOR_ID, P.NAME, P.SURNAME;
  SELECT PLAN_TABLE_OUTPUT
  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null, 'advanced'));
  
  CREATE INDEX INFO_INDEX ON PERSON("NAME", SURNAME);
  CREATE INDEX ID_INDEX ON OBLIGATION(DOCTOR_ID);
  
  EXPLAIN PLAN FOR 
    SELECT D.DOCTOR_ID, P.NAME, P.SURNAME, COUNT(*) AS OBLIGATION_COUNT
    FROM PERSON P, DOCTOR D, OBLIGATION O 
    WHERE D.DOCTOR_ID = O.DOCTOR_ID AND O.DOCTOR_ID = P.PERSON_ID
    GROUP BY D.DOCTOR_ID, P.NAME, P.SURNAME;
  SELECT PLAN_TABLE_OUTPUT
  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null, 'advanced'));
  
  DROP INDEX INFO_INDEX;
  DROP INDEX ID_INDEX;
  
  EXPLAIN PLAN FOR 
    SELECT DISTINCT P.NAME, P.SURNAME, COUNT(*) AS HEAD_DOCTORS_COUNT_OBLIGATIONS
    FROM PERSON P INNER JOIN DOCTOR D on P.PERSON_ID = D.DOCTOR_ID INNER JOIN OBLIGATION O ON O.DOCTOR_ID = D.DOCTOR_ID
    WHERE D.POSITION = 'Head doctor'
    GROUP BY P.NAME, P.SURNAME;
  SELECT PLAN_TABLE_OUTPUT
  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null, 'advanced'));
  
  CREATE INDEX INFO_INDEX ON PERSON("NAME", SURNAME);
  CREATE INDEX ID_INDEX ON OBLIGATION(DOCTOR_ID);
  
  EXPLAIN PLAN FOR 
    SELECT DISTINCT P.NAME, P.SURNAME, COUNT(*) AS HEAD_DOCTORS_COUNT_OBLIGATIONS
    FROM PERSON P INNER JOIN DOCTOR D on P.PERSON_ID = D.DOCTOR_ID INNER JOIN OBLIGATION O ON O.DOCTOR_ID = D.DOCTOR_ID
    WHERE D.POSITION = 'Head doctor'
    GROUP BY P.NAME, P.SURNAME;
  SELECT PLAN_TABLE_OUTPUT
  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null, 'advanced'));
  
  CONNECT xzubri00/KiRkPrDC;
    DROP MATERIALIZED VIEW nurse_view_1;
  CONNECT xstupi00/j06nyINz;
  
  DROP MATERIALIZED VIEW LOG ON DOCTOR;
  DROP MATERIALIZED VIEW LOG ON EXAMINATION;
  CREATE MATERIALIZED VIEW LOG ON DOCTOR WITH PRIMARY KEY, ROWID;
  CREATE MATERIALIZED VIEW LOG ON EXAMINATION WITH PRIMARY KEY, ROWID;
  GRANT ALL ON MLOG$_DOCTOR to xzubri00;
  GRANT ALL ON MLOG$_EXAMINATION to xzubri00;
  
  CONNECT xzubri00/KiRkPrDC;
  CREATE MATERIALIZED VIEW nurse_view_1
    CACHE
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    ENABLE QUERY REWRITE
    AS
      SELECT DISTINCT POSITION, D.ROWID docrowid, P.ROWID perrowid
      FROM xstupi00.DOCTOR D INNER JOIN xstupi00.EXAMINATION P
      ON D.DOCTOR_ID = P.DOCTOR_ID;
      
  GRANT ALL ON nurse_view_1 TO xstupi00;
  
  EXPLAIN PLAN FOR 
   SELECT *
      FROM xstupi00.DOCTOR D INNER JOIN xstupi00.EXAMINATION P
      ON D.DOCTOR_ID = P.DOCTOR_ID;
  SELECT plan_table_output FROM TABLE(dbms_xplan.display(NULL, NULL,'basic'));
  
  ALTER SESSION SET query_rewrite_enabled = TRUE;
  
  EXPLAIN PLAN FOR 
   SELECT DISTINCT POSITION, D.ROWID DOCROWID, P.ROWID PERROWID
      FROM xstupi00.DOCTOR D INNER JOIN xstupi00.EXAMINATION P
      ON D.DOCTOR_ID = P.DOCTOR_ID;
  SELECT plan_table_output FROM TABLE(dbms_xplan.display(NULL, NULL,'basic'));