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

# 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: trained_in
``` 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿