﻿ SQL: Medical procedures done after certification expired

# SQL Exercise: Medical procedures done after certification expired

## SQL hospital Database: Exercise-34 with Solution

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

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
```
Sample table: undergoes
```  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
```
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
```
Sample table: procedure
``` 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
```
Sample table: trained_in
``` 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿