SQL exercises on hospital Database: Find the name of the physicians who are not a specialized physician

SQL hospital Database: Exercise-11 with Solution

11. From the following tables, write a SQL query to find those physicians who are not a specialized physician. Return Physician name as "Physician", position as "Designation".

Sample table: physician

Sample table: trained_in

Sample Solution:

SELECT p.name AS "Physician",
       p.position "Designation"
FROM physician p
LEFT JOIN trained_in t ON p.employeeid=t.physician
WHERE t.treatment IS NULL
ORDER BY employeeid;

Sample Output:

     Physician     |        Designation
-------------------+----------------------------
 John Dorian       | Staff Internist
 Elliot Reid       | Attending Physician
 Percival Cox      | Senior Attending Physician
 Bob Kelso         | Head Chief of Medicine
 Keith Dudemeister | MD Resident
 Molly Clock       | Attending Psychiatrist
(6 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 tables, write a SQL query to find those physicians who are yet to be affiliated. Return Physician name as "Physician", Position, and department as "Department".
Next: From the following tables, write a SQL query to find the patients with their physicians by whom they got their preliminary treatment. Return Patient name as "Patient", address as "Address" and Physician name as "Physician".