SQL exercises on hospital Database: Find the name of the patients, their treating physicians and medication
SQL hospital Database: Exercise-19 with Solution
19. Write a query in SQL to find the name of the patients, their treating physicians and medication.
Sample table: patient
Sample table: prescribes
Sample table: physician
Sample table: medication
SELECT t.name AS "Patient", p.name AS "Physician", m.name AS "Medication" FROM patient t JOIN prescribes s ON s.patient=t.ssn JOIN physician p ON s.physician=p.employeeid JOIN medication m ON s.medication=m.code;
Patient | Physician | Medication ------------+-------------+-------------- John Smith | John Dorian | Procrastin-X Dennis Doe | Molly Clock | Thesisin Dennis Doe | Molly Clock | Thesisin (3 rows)
E R Diagram of Hospital Database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query in SQL to find the name of patients and their physicians who does not require any assistance of a nurse.
Next: Write a query in SQL to find the name of the patients who taken an advanced appointment, and also display their physicians and medication.
What is the difficulty level of this exercise?