SQL Exercise: Patients who have been prescribed some medication
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
ssn | name | address | phone | insuranceid | pcp
-----------+-------------------+--------------------+----------+-------------+-----
100000001 | John Smith | 42 Foobar Lane | 555-0256 | 68476213 | 1
100000002 | Grace Ritchie | 37 Snafu Drive | 555-0512 | 36546321 | 2
100000003 | Random J. Patient | 101 Omgbbq Street | 555-1204 | 65465421 | 2
100000004 | Dennis Doe | 1100 Foobaz Avenue | 555-2048 | 68421879 | 3
Sample table: prescribes
physician | patient | medication | date | appointment | dose
-----------+-----------+------------+---------------------+-------------+------
1 | 100000001 | 1 | 2008-04-24 10:47:00 | 13216584 | 5
9 | 100000004 | 2 | 2008-04-27 10:53:00 | 86213939 | 10
9 | 100000004 | 2 | 2008-04-30 16:53:00 | | 5
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
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)
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.