SQL Exercise: Physicians who are trained in a special treatment

Last update on December 20 2024 11:41:52 (UTC/GMT +8 hours)

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".

 employeeid |       name        |           position           |    ssn
------------+-------------------+------------------------------+-----------
          1 | John Dorian       | Staff Internist              | 111111111
          2 | Elliot Reid       | Attending Physician          | 222222222
          3 | Christopher Turk  | Surgical Attending Physician | 333333333
          4 | Percival Cox      | Senior Attending Physician   | 444444444
          5 | Bob Kelso         | Head Chief of Medicine       | 555555555
          6 | Todd Quinlan      | Surgical Attending Physician | 666666666
          7 | John Wen          | Surgical Attending Physician | 777777777
          8 | Keith Dudemeister | MD Resident                  | 888888888
          9 | Molly Clock       | Attending Psychiatrist       | 999999999

 code |              name              | cost
------+--------------------------------+-------
    1 | Reverse Rhinopodoplasty        |  1500
    2 | Obtuse Pyloric Recombobulation |  3750
    3 | Folded Demiophtalmectomy       |  4500
    4 | Complete Walletectomy          | 10000
    5 | Obfuscated Dermogastrotomy     |  4899
    6 | Reversible Pancreomyoplasty    |  5600
    7 | Follicular Demiectomy          |    25

 physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
         3 |         1 | 2008-01-01        | 2008-12-31
         3 |         2 | 2008-01-01        | 2008-12-31
         3 |         5 | 2008-01-01        | 2008-12-31
         3 |         6 | 2008-01-01        | 2008-12-31
         3 |         7 | 2008-01-01        | 2008-12-31
         6 |         2 | 2008-01-01        | 2008-12-31
         6 |         5 | 2007-01-01        | 2007-12-31
         6 |         6 | 2008-01-01        | 2008-12-31
         7 |         1 | 2008-01-01        | 2008-12-31
         7 |         2 | 2008-01-01        | 2008-12-31
         7 |         3 | 2008-01-01        | 2008-12-31
         7 |         4 | 2008-01-01        | 2008-12-31
         7 |         5 | 2008-01-01        | 2008-12-31
         7 |         6 | 2008-01-01        | 2008-12-31
         7 |         7 | 2008-01-01        | 2008-12-31

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)

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:

Alternative Solution:

Using INNER JOIN Syntax:

-- SELECTing physician name and corresponding treatment name
SELECT p.name AS "Physician",
       c.name AS "Treatment"
-- FROM physician table aliased as p
FROM physician p
-- INNER JOIN with trained_in table aliased as t based on physician's employeeid
INNER JOIN trained_in t ON p.employeeid = t.physician
-- INNER JOIN with procedure table aliased as c based on treatment code
INNER JOIN procedure c ON t.treatment = c.code;

Explanation:

This query uses INNER JOINs to connect the physician, trained_in, and procedure tables based on their respective keys. It explicitly defines the join conditions in the ON clause.

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.