Write SQL queries to perform the following operations. In each case show the SQL

Question

Write SQL queries to perform the following operations. In each case show the SQL statement you used and the output from MySQL.

The tables of Hospital database are as follows:

team (team_code, telephone_no, staff_no)

consists_of (staff_no, team_code)

doctor (staff_no, doctor_name, position)

specialist (staff_no, specialism)

patient (patient_id, patient_name, gender, height, weight, staff_no, ward_no)

ward (ward_no, ward_name, number_of_beds)

nurse (staff_no, nurse_name, ward_no)

supervises (staff_no, supervisor)

treatment (staff_no, patient_id, start_date, reason)

prescription (prescription_no, quantity, daily_dosage, staff_no, patient_id, start_date, drug_code)

small_occupied_by(patirnt_id, ward_no)

drug (drug_code, drug_name, type, price)

a) List prescription numbers and drugs whose codes are either P51 or X01 in these prescriptions.

b) Find the team codes along with staff numbers in each team.

c) Retrieve the staff numbers, patient identifiers and names along with starting date of all treatments that were between 2007-01-01 and 2007-02-01.

d) List the ward numbers and names along with patient identifiers and the number of patients in each ward. The output table should be sorted based on ward numbers in an ascending way.

e) Find drug codes and names for all drugs that were not given in any prescription.

f) Give the output table that has the staff numbers and names of all doctors who are not specialist and gave treatments to the patients.

g) Find the patient identifier and the average of drug prices for each patient that his drug price is greater than the average of drug prices of the same drug type.

h) Give the drug code along with the sum of quantities for each drug that its sum of quantities is greater than 100.

Explanation / Answer

a)select p.prescription_no,d.drug_code, d.drug_name from prescription p,drug d where p.drug_code=d.drug_code and p.drug_code='P51' or p.drug_code='X01';

The above query will retrieve the drug information along with prescription for the drug's P51 or X01;

b)select team_code,staff_no from team group by team_code order by team_code;

Above query will retrieve the team code and the respective staff no of each group in ascending order.

c)select t.staff_no,t.patient_id,p.patient_name from treatment t,patient p where t.patient_id=p.patient_id and start_date between '2007-01-01' and '2007-02-01';

This query will give you the results of staff and patient who got treated in the given dates

d) select w.ward_no, w.ward_name,p.patient_id,count(p.patient_id) from patient p,ward w where w.ward_no=p.ward_no group by w.ward_no order by w.ward_no asc;

The above query gives you the ward and patient information in the order of ascending order along with the count of patients in each ward

According to the chegg rules I can answer only 4 parts . Please understand and resubmit the remaining.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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