SQL Exercise: Providers of primary care who are not department heads

SQL hospital Database: Exercise-39 with Solution

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".

    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

 departmentid |       name       | head
--------------+------------------+------
            1 | General Medicine |    4
            2 | Surgery          |    7
            3 | Psychiatry       |    9

 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