w3resource
SQL exercises

SQL exercises on hospital Database: Find the name of the patients who taken an advanced appointment, and also display their physicians and medication

SQL hospital Database: Exercise-20 with Solution

20. Write a query in SQL to find the name of the patients who taken an advanced appointment, and also display their 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
WHERE s.appointment IS NOT NULL;

Sample Output:

  Patient   |  Physician  |  Medication
------------+-------------+--------------
 John Smith | John Dorian | Procrastin-X
 Dennis Doe | Molly Clock | Thesisin
(2 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 the patients, their treating physicians and medication.
Next: Write a query in SQL to find the name and medication for those patients who did not take any appointment.

What is the difficulty level of this exercise?



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