Generate oracle sql's for the following questions (based on this logical model :  

 

ANSI joins must be used where two or more tables are to be joined, under no circumstances can "implicit join notation" be used.

Where a question indicates 'Your output must have the form shown below'—this means the same appearance and alignment of columns/data as the sample output shows. Clearly, your actual data may be different.

(i) List the doctor title, first name, last name and contact phone number for all doctors who specialise in the area of "ORTHOPEDIC SURGERY" (this is the specialisation description). Order the list by the doctors' last name and within this, if two doctors have the same last name, order them by their respective first names. 

(ii) List the item code, item description, item stock and the cost centre title which provides these items for all items which have a stock greater than 50 items and include the word 'disposable' in their item description. Order the output by the item code. 

(iii) List the patient id, patient's full name as a single column, admission date and time and the supervising doctor's full name (including title) as a single column for all those patients admitted on the 1st June 2022. Your output must include at least two admissions on this date which occurred at different times. Order the output by the admission time with the earliest admission first, where several admissions occur at the same time order them by the patient id. Your output must have the form shown in the following image including using the indicated column headings: 

Patient ID | Patient name | Admission_Datetime | Doctor_Name

100128 | June Coskerry | 01-Jun-2022 | 11:00 | Dr Mary Wei

200000 | Peter Xuie | 01-Jun-2022 | 13:00 | Dr Sawyer Haisell

--

(iv) List the procedure code, name, description, and standard cost where the procedure standard cost is less expensive than the average procedure standard cost. The output must show the most expensive procedure first, where two procedures have the same standard cost order them by the procedure code. The procedure standard cost must be displayed with two decimal points and a leading $ symbol, for example as $120.66 and be right-aligned. 

(v) List the patient's id, last name, first name, date of birth and the number of times the patient has been admitted to the hospital where the number of admissions is greater than 2. The output should show patients with the most number of admissions first and for patients with the same number of admissions, show the patients in their date of birth order. Your output must include at least two patients with the same number of admissions. Your output must have the form shown in the following image including using the indicated column headings:

PATINT_ID | PATIENT_LNAME | _PATIENT_FNAME | DOB | NUMBERADMISSIONS

102345 Lee Wendy 25− May −1981

 100114 MacGillespie Florri 22−0????????−1992

100118 Edscer Carita 06− Dec −1994

 

(vi) List the admission number, patient id, first name, last name and the length of their stay in the hospital for all patients who have been discharged and who were in the hospital longer than the average stay for all discharged patients. The length of stay must be shown in the form 10 days 2.0 hrs where hours are rounded to one decimal digit, with the output right aligned. The output must be displayed in admission number order. Your output must have the form is shown in the following image (only some rows shown), including using the indicated column headings:

 

Given a doctor may charge more or less than the standard charge for a procedure carried out during an admission procedure, the hospital administration is interested in finding out what variations on the standard price have been charged. The hospital terms the difference between the procedure standard cost and the average actual charged procedure cost which has been charged to patients for all such procedures which have been carried out as the procedure "Price_Differential".

For example procedure 15509, X-ray Right knee has a standard procedure cost of $75. Assuming that the average actual charged cost for this procedure is $73.37, the price differential will be -1.63 (ie. the average cost is $1.63 below the standard cost).

For all procedures which have been carried out on an admission determine the procedure price differential. The list should show the procedure code, name, description, standard time and the procedure price differential in procedure code order. Your output must have the form shown in the following image including using the indicated column headings:

 

List for every procedure, the items which have been used and the maximum number of those items used when the procedure was carried on an admission. Your list must show the procedure code, procedure name, item code and item description and the maximum quantity of this item used for the given procedure.

For example, Vascular Surgery may require one standard anaesthetic pack, and then a number of Bupivacaine injections; sometimes one has been used sometimes two—the required listing will show:

43556	Vascular surgery	AN002	Std Anaesthetic Pack	1
43556	Vascular surgery	BI500	Bupivacaine Inj .5% 10ml Steriamp	2

If the procedure has not been carried out on any admission or has not used any items then the item code, item description and maximum quantity columns must show "---". The output must be in procedure name order and for a given procedure name in item code order. Your output must have the form shown in the following image (only some rows show) including using the indicated column headings.

 

PROCEDURE P * proc_code U * proc_name proc_description * proc_time proc_std_cost occurs_in 41- P DOCTOR * doctor_id * doctor_title * | performs doctor_fname HI- doctor_Iname * doctor_phone ADM_PRC P adprc_no U* adprc_date_time * adprc_pat_cost * requests adprc_items_cost UF* adm_no F* proc_code Frequest_dr_id F perform_dr_id requires ITEM_TREATMENT has |||| PF adprc_no * PF* item_code * it_qty_used *it_item_total_cost supervises requires FF spec_code OFF* doctor_id DOCTOR_SPECIALITY used_in -1H HH U ADMISSION Padm_no *adm_date_time adm_discharge UF* patient_id F * doctor_id ITEM Pitem_code item_description *item_stock item_cost F* cc_code for is HP -TH has spec_code U * spec_description * * -THR P patient_id SPECIALITY P* PATIENT patient_fname patient Iname * patient_address patient dob patient_contact_phn * COSTCENTRE cc_code cc_title cc_manager_name