w3resource

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: 

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:

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



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

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

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook