SQL exercises on hospital Database: Find the names of all patients whose primary care is taken by a physician who is not the head of any department and name of that physician

SQL hospital Database: Exercise-39 with Solution

39. From the following table, write a SQL query to find those patients whose primary care a physician who is not the head of any department takes. Return Patient name as “Patient”, Physician Name as “Primary care Physician”.

Sample table: patient

Sample table: department

Sample table: physician

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)	 

Practice Online

E R Diagram of Hospital Database:

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

Previous: From the following table, write a SQL query to find those patients who had at least two appointments where the nurse who prepped the appointment was a registered nurse and the physician who has carried out primary care. Return Patient name as “Patient”, Physician name as “Primary Physician”, and Nurse Name as “Nurse”.
Next: SQL Exercises, Practice, Solution - exercises on employee Database