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. From the following tables, write a SQL query to find those patients who have taken an advanced appointment. Return Patient name as "Patient", Physician name as "Physician" and Medication name as "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:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find the patients and their treating physicians and medication. Return Patient name as "Patient", Physician name as "Physician", Medication name as "Medication".
Next: From the following tables, write a SQL query to find those patients who did not take any appointment. Return Patient name as "Patient", Physician name as "Physician" and Medication name as "Medication".