w3resource
SQL exercises

SQL exercises on hospital Database: Find the names of all patients who has been undergone a procedure costing more than $5,000 and the name of that physician who has carried out primary care

SQL hospital Database: Exercise-37 with Solution

37. Write a query in SQL to obtain the names of all patients who has been undergone a procedure costing more than $5,000 and the name of that physician who has carried out primary care.

Sample table: patient


Sample table: undergoes


Sample table: physician


Sample table: procedure


Sample Solution:

SELECT pt.name AS "Ptient",
       p.name AS "Primary Physician",
       pd.cost AS "Porcedure Cost"
FROM patient pt
JOIN undergoes u ON u.patient=pt.ssn
JOIN physician p ON pt.pcp=p.employeeid
JOIN PROCEDURE pd ON u.procedure=pd.code
WHERE pd.cost>5000;

Sample Output:

   Ptient   | Primary Physician | Porcedure Cost
------------+-------------------+----------------
 John Smith | John Dorian       |           5600
 Dennis Doe | Christopher Turk  |          10000
(2 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.

What is the difficulty level of this exercise?



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