w3resource
SQL exercises

SQL exercises on hospital Database: Find the names of all patients who has been prescribed some medication by his/her physician who has carried out primary care and the name of that physician

SQL hospital Database: Exercise-36 with Solution

36. Write a query in SQL to Obtain the names of all patients who has been prescribed some medication by his/her physician who has carried out primary care and the name of that physician.

Sample table: patient


Sample table: prescribes


Sample table: physician


Sample Solution:

SELECT pt.name AS "Ptient",
       p.name AS "Physician"
FROM patient pt
JOIN prescribes pr ON pr.patient=pt.ssn
JOIN physician p ON pt.pcp=p.employeeid
WHERE pt.pcp=pr.physician
  AND pt.pcp=p.employeeid;

Sample Output:

   Ptient   |  Physician
------------+-------------
 John Smith | John Dorian
(1 row) 

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.

What is the difficulty level of this exercise?



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