SQL Exercise: Name of the physicians who are not a specialized

SQL hospital Database: Exercise-11 with Solution

11. From the following tables, write a SQL query to identify physicians who are not specialists. 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)

Explanation:

The said query in SQL that selects the name and designation of physicians who are not trained in any treatment from the physician table and a trained_in table.

The left join combines the physician and trained_in tables based on the employeeid and physician columns. It checks whether he physician is trained in any treatment other wise treatment, the trained_in table will have NULL values for that physician.

The WHERE clause is used to filter the results and return only those physicians who are not trained in any treatment.

The resulting output will be a table with two columns labeled "Physician" and "Designation", respectively.

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: Find physicians who are yet to be affiliated.
Next SQL Exercise: Find the patients and the physicians who treated them.