Q:

Write a query in SQL to obtain the name and position of all physicians who completed a medical procedure with certification after the date of expiration of their certificate

hamza
Save to bookmark
|
2022-05-18
|
  175 
0

 Write a query in SQL to obtain the name and position of all physicians who completed a medical procedure with certification after the date of expiration of their certificate.

Sample table: physician

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
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
 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
Sql programming,
login and add your own answer to this question

All Answers

hamza
2022-05-18
need an explanation for this answer? contact us directly to get an explanation for this answer 

SELECT name AS "Physician",
       position AS "Position"
FROM physician
WHERE employeeid IN
    ( SELECT physician
     FROM undergoes u
     WHERE date >
         ( SELECT certificationexpires
          FROM trained_in t
          WHERE t.physician = u.physician
            AND t.treatment = u.procedure ) );

Sample Output:

  Physician   |           Position
--------------+------------------------------
 Todd Quinlan | Surgical Attending Physician
(1 row)

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions

need a help?

find thousands of online teachers now