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)

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

Solution:

e)

SELECT drug_code, drug_name FROM prescription INNER JOIN drug ON prescription .drug_code= drug.drug_code WHERE drug_code IS NULL

f)

SELECT staff_no, doctor_name FROM doctor INNER JOIN specialist ON doctor.staff_no= specialist.staff_no INNER JOIN treatment ON treatment.staff_no= specilist.staff_no EXISTS IN treatment AND NOT EXISTS IN specilist

g)

SELECT patient_id, AVG(price) FROM patient INNER JOIN prescription ON patient.patient_id= prescription.patient_id INNER JOIN drug ON prescription .drug_code= drug.drug_code

h)

SELECT drug_code, SUM(quantity) AS S FROM prescription INNER JOIN drug ON prescription .drug_code= drug.drug_code WHERE S>100

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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