Answered>Order 12112

This question is based on the tables listed below, which describe a simple medical billing system.

PATIENT (PatientID, FamilyName, GivenName, Address, Suburb, State, PostCode)

DOCTOR (ProviderNo, Name)

ITEM (ItemNo, Description, Fee)

ACCOUNT (AccountNo, ProviderNo, PatientID, TreatmentDate)

ACCOUNTLINE (AccountNo, ItemNo)

(Primary key, foreign key)

The PATIENT table contains data about patients treated at a typical medical surgery. The details of the Doctors are contained in the DOCTOR table. ProviderNo is a unique code allocated to each registered medical practitioner in Australia. The ITEM table contains the details of treatment items, including the ItemNo which is a unique code allocated to each treatment item. When a patient visits the Doctor, an ACCOUNT is created. A Patient can only be treated by one Doctor on a particular visit. An Account can have several ACCOUNT LINEs, each of which lists the item number of the treatment provided. A Patient can have more than one account on a day.

c. Name and suburb of patients who live in the State named ‘WA’ and have been treated by Dr Ima or Dr Barbara.

d. Name and suburb of patients treated by Dr Brian but not Dr Barbara

e. Number of different suburbs covered by each doctor.

f. f. The name of each doctor, and the total fees collected from visit to each of them in each year. Your answer should be presented in order of doctor name followed by year.

g. Item Description and the treatment date of all treatments for any patients named Betty Eggert (i.e., Given name is Betty, family name is Eggert)

h. Doctors who have had more than the average number of consultations

i. Total amount of fees collected for each type of consultation in each state, in alphabetical order of state.

j. Patient ID and family name of patients who have had all types of treatments