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

برمجة سي كيو ال sql

2021-09-06
mhanasmh00489829403

. 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

ssn    |       name        |      address       |  phone   | insuranceid | pcp
-----------+-------------------+--------------------+----------+-------------+-----
 100000001 | John Smith        | 42 Foobar Lane     | 555-0256 |    68476213 |   1
 100000002 | Grace Ritchie     | 37 Snafu Drive     | 555-0512 |    36546321 |   2
 100000003 | Random J. Patient | 101 Omgbbq Street  | 555-1204 |    65465421 |   2
 100000004 | Dennis Doe        | 1100 Foobaz Avenue | 555-2048 |    68421879 |   3

patient  | procedure | stay |        date         | physician | assistingnurse
-----------+-----------+------+---------------------+-----------+----------------
 100000001 |         6 | 3215 | 2008-05-02 00:00:00 |         3 |            101
 100000001 |         2 | 3215 | 2008-05-03 00:00:00 |         7 |            101
 100000004 |         1 | 3217 | 2008-05-07 00:00:00 |         3 |            102
 100000004 |         5 | 3217 | 2008-05-09 00:00:00 |         6 |
 100000001 |         7 | 3217 | 2008-05-10 00:00:00 |         7 |            101
 100000004 |         4 | 3217 | 2008-05-13 00:00:00 |         3 |            103

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

الأجوبة

SELECT pt.name AS "Patient",
       p.name AS "Primary Physician",
       pd.cost AS "Procedure 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:

   Patient   | Primary Physician | Procedure Cost
------------+-------------------+----------------
 John Smith | John Dorian       |           5600
 Dennis Doe | Christopher Turk  |          10000
(2 rows)

سجل الدخول لتستطيع الإجابة

هل كان المحتوى مفيد؟

0 نعم

0 لا

أسئلة مشابهة

Write a query in SQL to Obtain the names of all patients who has been prescribed some medication by his/her physician who has carried out primary care and the name of that physician

Write a query in SQL to Obtain the names of all patients whose primary care is taken by a physician who is not the head of any department and name of that physician along with their primary care physician

Write a query in SQL to Obtain the names of all patients who had at least two appointment where the nurse who prepped the appointment

Write a query in SQL to find the name of the patients who taken the appointment on the 25th of April at 10 am, and also display their physician, assisting nurses and room no

القوائم الدراسية التي ينتمي لها السؤال

لايوجد قوائم حاليا

معلومات ذات صلة

تبحث عن مدرس اونلاين؟

ابحث عن مدرسك