Write SQL queries in Mysql to perform the following operations. In each case sho

Question

Write SQL queries in Mysql 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.

Explanation / Answer

Solution:

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';

b)

SELECT team_code,staff_no FROM team GROUP BY team_code ORDER BY team_code;

c)

SELECT staff_no, patient_id, patient_name FROM treatment, patient WHERE treatment.patient_id=patient .patient_id AND start_date BETWEEN '2007-01-01' AND '2007-02-01';

d)

SELECT ward_no, ward_name, patient_id, COUNT(patient_id) FROM patient ,ward WHERE ward_no=ward_no GROUP BY ward_no ORDER BY ward_no ASC;

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.