This question is based on the tables listed below, which describe a simple medic

Question

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 Provide SQL AND result tables for the following queries. Use only the information provided in the question in your solutions. Paste the queries and the result tables from either your SSH client or SQL Developer into your document. You can use a screen dump for the result tables, but NOT for the SQL. Family name and suburb of patients who live in the State named 'WA a. b. Family name and suburb of patients who live in the State named WA' or the State named 'SA', in alphabetical order of family name c. Name and suburb of patients who live in the State named 'WA' and have been treated by Dr Ima or Dr Barbara Name and suburb of patients treated by Dr Brian but not Dr Barbara Number of different suburbs covered by each doctor 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 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) Doctors who have had more than the average number of consultations Total amount of fees collected for each type of consultation in each state, in alphabetical order of state Patient ID and family name of patients who have had all types of treatments d. e. f. g. h. i. j.

Explanation / Answer

Accordind to CHEGG instructions, solved the first 4 suboptions of the main question.

Check out the SQL Queries and let me know if any queries through COMMENTS else THUMBS UP

----------------------------------------------------------------------------

a.
Select FamilyName,Subrub
From PATIENT
Where State = 'WA'
b.
Select FamilyName,Subrub
From PATIENT
Where State = 'WA' OR State = 'SA'
Order By FamilyName
c.
Select GivenName,Subrub
from PATIENT
Where State = 'WA' AND PatientID IN ( Select PatientID
From ACCOUNT
Where ProviderNo IN(Select ProviderNo
From DOCTOR
Where Name = 'Ima' OR Name = 'Barbara'))
d.
Select GivenName,Subrub
from PATIENT
Where PatientID IN (
(Select PatientID
From ACCOUNT
Where ProviderNo IN(Select ProviderNO
From DOCTOR
Where Name='Brian'))
INTERSECT
(Select PatientID
From ACCOUNT
Where ProviderNo NOT IN(Select ProviderNO
From DOCTOR
Where Name='Barbara'))
)

---------------------------------------------------------------------------------

Since the Data is not proper , couldnot able to get the snapshots of Output of the queries,

But I assure that , if you execute the above queries, then you will get the expected Output.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.