SQL Exercise: Patients who have been prescribed some medication

SQL hospital Database: Exercise-36 with Solution

36. From the following table, write a SQL query to determine which patients have been prescribed medication by their primary care physician. Return Patient name as "Patient", and Physician Name as "Physician".

Sample table: patient

Sample table: prescribes

Sample table: physician

Sample Solution:

-- Selecting the name of the patient and physician
SELECT pt.name AS "Ptient", -- Selecting the name column from the patient table and aliasing it as "Ptient"
       p.name AS "Physician" -- Selecting the name column from the physician table and aliasing it as "Physician"
-- Joining patient table with prescribes table based on patient's SSN
FROM patient pt
JOIN prescribes pr ON pr.patient=pt.ssn
-- Joining patient table with physician table based on primary care physician's employee ID
JOIN physician p ON pt.pcp=p.employeeid
-- Filtering rows to include only those where the primary care physician is the same as the prescribing physician
WHERE pt.pcp=pr.physician
  AND pt.pcp=p.employeeid;

Sample Output:

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

Explanation:

This SQL query selects the names of patients and their primary care physicians (PCPs), along with the name of the physician who prescribed medication for the patient.

The first JOIN keyword links the 'patient' and 'prescribes' tables based on the patient and SSN columns. The second JOIN links the 'patient' and 'physician' tables based on the pcp and employeeid columns.

The WHERE clause includes two conditions to filter the results. The first condition ensures that the patient's PCP is also the physician who prescribed medication for the patient. The second condition ensures that the PCP employee ID matches the physician employee ID.

Practice Online

E R Diagram of Hospital Database:

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

Previous SQL Exercise: Find nurses who have ever been on call for room 122.
Next SQL Exercise: Patients who have had a procedure costing over $5,000.