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

Sample table: procedure

Sample table: trained_in

Sample Solution:

-- This SQL query retrieves the names of physicians and the treatments they are trained in from the physician, procedure, and trained_in tables.

SELECT p.name AS "Physician", -- Selects the name column from the physician table and aliases it as "Physician"
c.name AS "Treatment" -- Selects the name column from the procedure table and aliases it as "Treatment"
FROM physician p, -- Specifies the physician table with alias 'p'
     PROCEDURE c, -- Specifies the procedure table with alias 'c'
     trained_in t -- Specifies the trained_in table with alias 't'
WHERE t.physician=p.employeeid -- Joins the physician table with the trained_in table on the physician's employee ID
  AND t.treatment=c.code; -- Joins the trained_in table with the procedure table on the treatment 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:

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.