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

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

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

 

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
appointmentid |  patient  | prepnurse | physician |    start_dt_time    |     end_dt_time     | examinationroom
---------------+-----------+-----------+-----------+---------------------+---------------------+-----------------
      13216584 | 100000001 |       101 |         1 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | A
      26548913 | 100000002 |       101 |         2 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | B
      36549879 | 100000001 |       102 |         1 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | A
      46846589 | 100000004 |       103 |         4 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | B
      59871321 | 100000004 |           |         4 | 2008-04-26 10:00:00 | 2008-04-26 11:00:00 | C
      69879231 | 100000003 |       103 |         2 | 2008-04-26 11:00:00 | 2008-04-26 12:00:00 | C
      76983231 | 100000001 |           |         3 | 2008-04-26 12:00:00 | 2008-04-26 13:00:00 | C
      86213939 | 100000004 |       102 |         9 | 2008-04-27 10:00:00 | 2008-04-21 11:00:00 | A
      93216548 | 100000002 |       101 |         2 | 2008-04-27 10:00:00 | 2008-04-27 11:00:00 | B
employeeid |      name       |  position  | registered |    ssn
------------+-----------------+------------+------------+-----------
        101 | Carla Espinosa  | Head Nurse | t          | 111111110
        102 | Laverne Roberts | Nurse      | t          | 222222220
        103 | Paul Flowers    | Nurse      | f          | 333333330
 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

الأجوبة

SELECT t.name AS "Name of the patient",
       n.name AS "Name of the Nurse assisting the physician",
       p.name AS "Name of the physician",
       a.examinationroom AS "Room No.",
       a.start_dt_time
FROM patient t
JOIN appointment a ON a.patient=t.ssn
JOIN nurse n ON a.prepnurse=n.employeeid
JOIN physician p ON a.physician=p.employeeid
WHERE start_dt_time='2008-04-25 10:00:00';

Sample Output:

 Name of the patient | Name of the Nurse assisting the physician | Name of the physician | Room No. |    start_dt_time
---------------------+-------------------------------------------+-----------------------+----------+---------------------
 John Smith          | Laverne Roberts                           | John Dorian           | A        | 2008-04-25 10:00:00
 Dennis Doe          | Paul Flowers                              | Percival Cox          | B        | 2008-04-25 10:00:00
(2 rows)
سجل الدخول لتستطيع الإجابة
هل كان المحتوى مفيد؟

0 نعم

0 لا

أسئلة مشابهة

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 patients and their physicians who does not require any assistance of a nurse
Write a query in SQL to find the name of the patients who taken an advanced appointment, and also display their physicians and medication
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

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

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

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

ابحث عن مدرسك
محتاج مساعدة باختيار المدرس الافضل؟ تواصل مع فريقنا الان لمساعدتك بتأمين افضل مدرس
ماهو التخصص الذي تبحث عنه؟
اكتب هنا...