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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics