Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

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. From the following tables, write a SQL query to find those physicians who have trained for special treatment. Return Physician name as "Physician", treatment procedure name as "Treatment".

Sample table: physician


Sample table: procedure


Sample table: trained_in


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)

Pictorial presentation:

Find the name of the physicians who are trained for a special treatement

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: From the following tables, write a SQL query to find the physician and the departments they are affiliated with. Return Physician name as "Physician", and department name as "Department".
Next: From the following tables, write a SQL query to find those physicians who are yet to be affiliated. Return Physician name as "Physician", Position, and department as "Department".

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3