For this assignment, use the following database schema: (please follow the schema exactly)

1) Patient—PatientID, Name, DOB

2) Doctor—DoctorID, Name, MedLicenseNumber, Phone

3) Medication—MedicationID, BrandName, GenericName

4) Prescription—PrescriptionID, Date, PatientID, PrescriberID, MedicationID

Specify the SQL queries to retrieve:

1) A list of all doctors.

2) The number of patients that were born before 01/01/1994.

3) The patient information along with the ID of any prescribing doctor for each patient.

4) The prescription information along with patient name, DOB, medication brand name, and prescribing doctor name sorted by most recent date.

5) The most prescribed generic medication name.

*********************************

SQLite statements for each step must be copied from the command prompt and pasted into MS Word file.

The final paper must look like this

Sqlite> CREATE TABLE PATIENT (PatientID varchar(10), Name varchar(100), DOB date, PRIMARY KEY (PatientID) ) ; (please check syntax thoroughly)

CREATE TABLE DOCTOR, MEDICATION, PRESCRIPTION ……. Etc.

Sqlite> .SCHEMA (as an evidence for the Professor)

 

Sqlite> INSERT INTO PATIENT ( PatientID, Name, DOB ) VALUES ( “1234567891”, “Michael Soros”, “12/25/1957” ) ;

Sqlite> SELECT * FROM Patient (for each table as an evidence for the Professor)

Sqlite> SELECT * FROM Doctor

Sqlite> SELECT * FROM Medication

Sqlite> SELECT * FROM Prescription

…. SQLite queries ……. Etc.

 

 

Here is prepared database content for each table and row. Each table must contain 4 rows.

For Table “Patient” (PatientID, Name, DOB)

Row 1 ( “1234567891”, “Michael Soros”, “12/25/1957” ) ;

Row 2 ( “7526523478”, “Anna Tores”, “05/11/1929” ) ;

Row 3 ( “5289764853”, “Ivan Kors”, “09/05/1998” ) ;

Row 4 ( “3892174568”, “Natasha Delores”, “02/07/1934” ) ;

 

For Table “Doctor” (DoctorID, Name, MedLicenseNumber, Phone)

Row 1 ( “4562”, “Diana Copas”, “252450”, “917-347-8579” ) ;

Row 2 ( “7890”, “Mark Gupta”, “447823”, “718-252-5228” ) ;

Row 3 ( “0287”, “Fernando Mitchel”, “874659”, “347-678-5262” ) ;

Row 4 ( “5477”, “Dereck Sanders”, “579301”, “646-252-7578” ) ;

 

For Table “Medication” (MedicationID, BrandName, GenericName)

Row 1 ( “732”, “Gralise 600 mg”, “Neurontin 600 mg” ) ;

Row 2 ( “457”, “Singulair 10 mg”, “Montelukast 10 mg” ) ;

Row 3 ( “358”, “Nexium 40 mg”, “Esomeprazole 40 mg” ) ;

Row 4 ( “292”, “Lyrica 75 mg”, “Pregabalin 75 mg” ) ;

 

For Table “Prescription” (PrescriptionID, Date, PatientID, PrescriberID, MedicationID)

Row 1 ( “MX5VGG97”, “03/05/2018”, “1234567891”, “4562”, “732” ) ;

Row 2 ( “YR8YRE63”, “04/14/2018”, “7526523478”, “7890”, “457” ) ;

Row 3 ( “NB7SWT71”, “02/18/2018”, “5289764853”, “0287”, “358” ) ;

Row 4 ( “LJ9SAL86”, “08/02/2018”, “3892174568”, “5477”, “292” ) ;

 

 

A minimum of two references is needed: One – is a just reference, another one – for SQLite syntax.