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".

 physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
         3 |         1 | 2008-01-01        | 2008-12-31
         3 |         2 | 2008-01-01        | 2008-12-31
         3 |         5 | 2008-01-01        | 2008-12-31
         3 |         6 | 2008-01-01        | 2008-12-31
         3 |         7 | 2008-01-01        | 2008-12-31
         6 |         2 | 2008-01-01        | 2008-12-31
         6 |         5 | 2007-01-01        | 2007-12-31
         6 |         6 | 2008-01-01        | 2008-12-31
         7 |         1 | 2008-01-01        | 2008-12-31
         7 |         2 | 2008-01-01        | 2008-12-31
         7 |         3 | 2008-01-01        | 2008-12-31
         7 |         4 | 2008-01-01        | 2008-12-31
         7 |         5 | 2008-01-01        | 2008-12-31
         7 |         6 | 2008-01-01        | 2008-12-31
         7 |         7 | 2008-01-01        | 2008-12-31

Sample Solution:

-- SELECTing physician name and position (Designation)
SELECT p.name AS "Physician",
       p.position AS "Designation"
-- FROM physician table aliased as p
FROM physician p
-- LEFT JOIN with trained_in table aliased as t based on physician's employeeid
LEFT JOIN trained_in t ON p.employeeid = t.physician
-- WHERE clause filters results where there is no corresponding treatment in trained_in
WHERE t.treatment IS NULL
-- ORDER BY employeeid
ORDER BY p.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.

Alternative Solutions:

Using NOT EXISTS Subquery:

-- SELECTing physician name and position (Designation)
SELECT p.name AS "Physician",
       p.position AS "Designation"
-- FROM physician table aliased as p
FROM physician p
-- WHERE clause using NOT EXISTS subquery to find physicians with no training
WHERE NOT EXISTS (
    SELECT 1
    FROM trained_in t
    WHERE p.employeeid = t.physician
      AND t.treatment IS NOT NULL
)
-- ORDER BY employeeid
ORDER BY p.employeeid;

Explanation:

This solution uses a NOT EXISTS subquery to find physicians with no training in the trained_in table. The main query selects physician names and positions based on the conditions in the subquery. Results are ordered by employeeid.

Using LEFT JOIN and GROUP BY:

-- SELECTing physician name and position (Designation)
SELECT p.name AS "Physician",
       p.position AS "Designation"
-- FROM physician table aliased as p
FROM physician p
-- LEFT JOIN with trained_in table aliased as t based on physician's employeeid
LEFT JOIN trained_in t ON p.employeeid = t.physician
-- GROUP BY physician details
GROUP BY p.name, p.position, p.employeeid
-- HAVING clause filters out physicians with any training
HAVING COUNT(t.treatment) = 0
-- ORDER BY employeeid
ORDER BY p.employeeid;

Explanation:

This solution uses a LEFT JOIN and GROUP BY to identify physicians with no training. The HAVING clause ensures that only physicians with a count of zero treatments are included. Results are ordered by employeeid.

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.