SQL exercises on hospital Database: Find the name of the physicians who are trained for a special treatement
SQL hospital Database: Exercise-9 with Solution
9. Write a query in SQL to obtain the name of the physicians who are trained for a special treatement.
Sample table: physician
Sample table: procedure
Sample table: trained_in
SELECT p.name AS "Physician", c.name AS "Treatement" FROM physician p, PROCEDURE c, trained_in t WHERE t.physician=p.employeeid AND t.treatment=c.code;
Physician | Treatement ------------------+-------------------------------- 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)
E R Diagram of Hospital Database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query in SQL to obtain the name of the physician and the departments they are affiliated with.
Next: Write a query in SQL to obtain the name of the physicians with department who are yet to be affiliated.
What is the difficulty level of this exercise?