REM   Script: v3

REM   Ascendia/Centine 

REM Kevin Abel

REM Student

REM 646 498 3081

create table ENROLLMENT_MASTER ( 
    Member_ID varchar2(4) constraint enrollment_master_pk primary key, 
    Member_Name varchar2(40), 
    Enrollment_Start_Date date, 
    Enrollment_End_Date date 
)

INSERT INTO ENROLLMENT_MASTER  
VALUES(123,'John Doe', TO_DATE ('1/1/2020','MM/DD/YYYY'), TO_DATE('12/31/2020','MM/DD/YYYY'))

INSERT INTO ENROLLMENT_MASTER   
VALUES(124,'Sally Smith', TO_DATE ('1/1/2022','MM/DD/YYYY'), TO_DATE('12/31/2024','MM/DD/YYYY'))

INSERT INTO ENROLLMENT_MASTER  
VALUES(126,'Clark Kent', TO_DATE ('1/1/2023','MM/DD/YYYY'), TO_DATE('12/31/2025','MM/DD/YYYY'))

INSERT INTO ENROLLMENT_MASTER  
VALUES(125,'Bruce Wayne', TO_DATE ('1/1/2022','MM/DD/YYYY'), TO_DATE('12/31/2024','MM/DD/YYYY'))

DESCRIBE ENROLLMENT_MASTER

create table PROVIDER_CONTRACT ( 
    Provider_ID varchar2(4), 
    Specialty_Description varchar2(30), 
    Contract_Start_Date date, 
    Contract_End_Date date 
)

INSERT INTO PROVIDER_CONTRACT   
VALUES(223,'Family Medicine', TO_DATE ('1/1/2020','MM/DD/YYYY'), TO_DATE('12/31/2020','MM/DD/YYYY'))

INSERT INTO PROVIDER_CONTRACT  
VALUES(224,'Family Medicine', TO_DATE ('1/1/2022','MM/DD/YYYY'), TO_DATE('12/31/2024','MM/DD/YYYY'))

INSERT INTO PROVIDER_CONTRACT    
VALUES(225,'Cardiology', TO_DATE ('1/1/2022','MM/DD/YYYY'), TO_DATE('12/31/2024','MM/DD/YYYY'))

INSERT INTO PROVIDER_CONTRACT   
VALUES(226,'Chiropractor', TO_DATE ('1/1/2023','MM/DD/YYYY'), TO_DATE('12/31/2025','MM/DD/YYYY'))

INSERT INTO PROVIDER_CONTRACT   
VALUES(223,'Family Medicine', TO_DATE ('1/1/2023','MM/DD/YYYY'), TO_DATE('12/31/2025','MM/DD/YYYY'))

DESCRIBE PROVIDER_CONTRACT

create table OHIO_MEMBERS (  
    Member_ID varchar2(4)  
)

alter table OHIO_MEMBERS add constraint ohio_members_member_id_uq unique (Member_ID)

INSERT INTO OHIO_MEMBERS
VALUES(225)

INSERT INTO OHIO_MEMBERS
VALUES(226)

DESCRIBE OHIO_MEMBERS

create table MEMBER_VISIT ( 
    Member_ID varchar2(4), 
    Provider_ID varchar2(4), 
    Service_Date date 
)

INSERT INTO MEMBER_VISIT
VALUES(123,225, TO_DATE ('1/1/2020','MM/DD/YYYY'))

INSERT INTO MEMBER_VISIT
VALUES(124,225, TO_DATE ('1/1/2022','MM/DD/YYYY'))

INSERT INTO MEMBER_VISIT
VALUES(125,226, TO_DATE ('1/1/2022','MM/DD/YYYY'))

INSERT INTO MEMBER_VISIT
VALUES(126,223, TO_DATE ('1/1/2023','MM/DD/YYYY'))

Describe MEMBER_VISIT