﻿ SQL: Physicians who are trained in a special treatment

# SQL Exercise: Physicians who are trained in a special treatment

## SQL hospital Database: Exercise-9 with Solution

9. From the following tables, write a SQL query to find those physicians who have received special training. Return Physician name as “Physician”, treatment procedure name as “Treatment".

Sample table: physician
``` employeeid |       name        |           position           |    ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian       | Staff Internist              | 111111111
2 | Elliot Reid       | Attending Physician          | 222222222
3 | Christopher Turk  | Surgical Attending Physician | 333333333
4 | Percival Cox      | Senior Attending Physician   | 444444444
5 | Bob Kelso         | Head Chief of Medicine       | 555555555
6 | Todd Quinlan      | Surgical Attending Physician | 666666666
7 | John Wen          | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident                  | 888888888
9 | Molly Clock       | Attending Psychiatrist       | 999999999
```
Sample table: procedure
``` code |              name              | cost
------+--------------------------------+-------
1 | Reverse Rhinopodoplasty        |  1500
2 | Obtuse Pyloric Recombobulation |  3750
3 | Folded Demiophtalmectomy       |  4500
4 | Complete Walletectomy          | 10000
5 | Obfuscated Dermogastrotomy     |  4899
6 | Reversible Pancreomyoplasty    |  5600
7 | Follicular Demiectomy          |    25
```
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:

``````SELECT p.name AS "Physician",
c.name AS "Treatment"
FROM physician p,
PROCEDURE c,
trained_in t
WHERE t.physician=p.employeeid
AND t.treatment=c.code;
``````

Sample Output:

```    Physician     |           Treatment
------------------+--------------------------------
Christopher Turk | Reverse Rhinopodoplasty
Christopher Turk | Obtuse Pyloric Recombobulation
Christopher Turk | Obfuscated Dermogastrotomy
Christopher Turk | Reversible Pancreomyoplasty
Christopher Turk | Follicular Demiectomy
Todd Quinlan     | Obtuse Pyloric Recombobulation
Todd Quinlan     | Obfuscated Dermogastrotomy
Todd Quinlan     | Reversible Pancreomyoplasty
John Wen         | Reverse Rhinopodoplasty
John Wen         | Obtuse Pyloric Recombobulation
John Wen         | Folded Demiophtalmectomy
John Wen         | Complete Walletectomy
John Wen         | Obfuscated Dermogastrotomy
John Wen         | Reversible Pancreomyoplasty
John Wen         | Follicular Demiectomy
(15 rows)
```

Explanation:

The given query in SQL that selects the name of physicians and the treatments they are trained in from the physician table, procedure table, and trained_in table.

The query combines data from the three tables. It selects the physician name , treatment name from the physician table and the procedure table respectively, and the trained_in table links the physician and treatment on their respective IDs.

The resulting output will be a table with the columns "Physician" and "Treatment".

Pictorial presentation:

Alternative Solution:

Using INNER JOIN Syntax:

``````
-- SELECTing physician name and corresponding treatment name
SELECT p.name AS "Physician",
c.name AS "Treatment"
-- FROM physician table aliased as p
FROM physician p
-- INNER JOIN with trained_in table aliased as t based on physician's employeeid
INNER JOIN trained_in t ON p.employeeid = t.physician
-- INNER JOIN with procedure table aliased as c based on treatment code
INNER JOIN procedure c ON t.treatment = c.code;

``````

Explanation:

This query uses INNER JOINs to connect the physician, trained_in, and procedure tables based on their respective keys. It explicitly defines the join conditions in the ON clause.

## 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 the name and department of the physician.
Next SQL Exercise: Find physicians who are yet to be affiliated.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿