w3resource
SQL exercises

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


Sample Solution:

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;

Sample Output:

  Patient   |  Physician  |  Medication
------------+-------------+--------------
 John Smith | John Dorian | Procrastin-X
 Dennis Doe | Molly Clock | Thesisin
 Dennis Doe | Molly Clock | Thesisin
(3 rows)

Practice Online


E R Diagram of Hospital Database:

E R Diagram: SQL 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?



New Content: Composer: Dependency manager for PHP, R Programming