w3resource
SQL exercises

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


Sample Solution:

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;

Sample Output:

    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)

Practice Online


E R Diagram of Hospital Database:

E R Diagram: SQL 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?



New Content: Composer: Dependency manager for PHP, R Programming