SQL Exercise: Medical procedures done after certification expired

Last update on December 20 2024 12:01:09 (UTC/GMT +8 hours)

34. From the following table, write a SQL query to find all physicians who have completed medical procedures with certification after their certificates expired. Return Physician Name as "Physician", Position as" Position", Procedure Name as "Procedure", Date of Procedure as "Date of Procedure", Patient Name as "Patient", and expiry date of certification as "Expiry Date of Certificate".

 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

    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

 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

 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

Sample Solution:

SELECT p.name AS "Physician",
       p.position AS "Position",
       pr.name AS "Procedure",
       u.date AS "Date of Procedure",
       pt.name AS "Patient",
       t.certificationexpires AS "Expiry Date of Certificate"
FROM physician p,
     undergoes u,
     patient pt,
     PROCEDURE pr,
               trained_in t
WHERE u.patient = pt.ssn
  AND u.procedure = pr.code
  AND u.physician = p.employeeid
  AND Pr.code = t.treatment
  AND P.employeeid = t.physician
  AND u.Date > t.certificationexpires;

Sample Output:

  Physician   |           Position           |         Procedure          |  Date of Procedure  |  Patient   | Expiry Date of Certificate
--------------+------------------------------+----------------------------+---------------------+------------+-------------------------
 Todd Quinlan | Surgical Attending Physician | Obfuscated Dermogastrotomy | 2008-05-09 00:00:00 | Dennis Doe | 2007-12-31
(1 row) 

Explanation:

The said query in SQL that selects the physician's name and position, the name of the procedure, the date of the procedure, the patient's name, and the expiry date of the physician's certificate for the procedure from the tables 'undergoes', 'patient', 'procedure', 'trained_in' and 'physician'.

The query joins the tables 'undergoes' and 'patient' based on the columns patient and ssn, the tables 'undergoes' and 'procedure' based on the columns procedure and code, the tables 'procedure' and 'trained_in' based on the columns code and treatment, and the tables 'physician' and 'trained_in' based on the columns employeeid and physician.

The WHERE clause at the end of the query filters the results to only include procedures performed after the physician's certification has expired.

Practice Online

E R Diagram of Hospital Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find all physicians who completed a medical procedure.
Next SQL Exercise: Find nurses who have ever been on call for room 122.