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. Write a query in SQL to Obtain 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 along with their primary care physician.
Sample table: patient
Sample table: department
Sample table: physician
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);
Patient | Primary care Physician -------------------+------------------------ John Smith | John Dorian Grace Ritchie | Elliot Reid Random J. Patient | Elliot Reid Dennis Doe | Christopher Turk (4 rows)
E R Diagram of Hospital Database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?