SQL Exercise: Providers of primary care who are not department heads
39. From the following table, write a SQL query to identify those patients whose primary care is provided by a physician who is not the head of any department. Return Patient name as "Patient", Physician Name as "Primary care 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: department
departmentid | name | head
--------------+------------------+------
1 | General Medicine | 4
2 | Surgery | 7
3 | Psychiatry | 9
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 "Patient",
p.name AS "Primary care Physician"
FROM patient pt
JOIN physician p ON pt.pcp=p.employeeid
WHERE pt.pcp NOT IN
(SELECT head
FROM department);
Sample Output:
Patient | Primary care Physician
-------------------+------------------------
John Smith | John Dorian
Grace Ritchie | Elliot Reid
Random J. Patient | Elliot Reid
Dennis Doe | Christopher Turk
(4 rows)
Explanation: The said query in SQL that selects the names of patients and their physicians who are not heads of any department.
The JOIN statement combines the 'patient' and 'physician' tables based on the pcp and employeeid columns.
The WHERE clause filters the results to ensure that only pcp's who are not department heads and this is accomplished using a subquery. The subquery that selects the "head" column from the 'department' table and filters out any pcp's who match this value using the NOT IN operator.
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: Names of all patients who had at least 2 appointments.
Next SQL Exercise: SQL Exercises, Practice, Solution - exercises on employee Database
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.