Please help me by answering questions f, g, h, i and j. Thank you! This question

Question

Please help me by answering questions f, g, h, i and j. Thank you!

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

And f)

select name, treatmentdate, fee from doctor, Account, item, accountline where account.providerNo=doctor.providerNO and account.accountNo=accountline.accountNo and accountline.itemno=item.itemno order by treatmentdate

Ans g)

SELECT DESCRIPTION, treatmentDate from Account,Item where AccountNo=(select AccountNo from Account where patientId=(select patientId from patient where GivenName='Betty' and FamilyName='Eggert'))
ANd
ItemNo=(Select Itemno from AccountLine where AccountNo=(select AccountNo from Account where patientId=(select patientId from patient where GivenName='Betty' and FamilyName='Eggert')))

Ans h

select name,count(*) count from doctor group by name having count(*)>(select avg(count(*)) from account group by providerno)

Ans i

select state, sum(fee), description from Item, patient, account, accountline where
patient.patientid = account.patientid and account.accountNo=accountline.accountNo and accountline.itemno=item.itemno group by description, state

Ans J

select patientId from patient where patient.patientID=(select patientID from account where accountno=
(select accountno from accountline group by accountno having count(distinct itemno)= (select count(ItemNo) from item)))

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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