w3resource
SQL exercises

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


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:

E R Diagram: SQL Hospital Database.

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

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming