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

Sample table: undergoes

Sample table: patient

Sample table: procedure

Sample table: trained_in

Sample Solution:

-- Selecting relevant columns and providing aliases for clarity
SELECT p.name AS "Physician", -- Selecting the name of the physician and aliasing it as "Physician"
       p.position AS "Position", -- Selecting the position of the physician and aliasing it as "Position"
       pr.name AS "Procedure", -- Selecting the name of the procedure and aliasing it as "Procedure"
       u.date AS "Date of Procedure", -- Selecting the date of the procedure and aliasing it as "Date of Procedure"
       pt.name AS "Patient", -- Selecting the name of the patient and aliasing it as "Patient"
       t.certificationexpires AS "Expiry Date of Certificate" -- Selecting the expiry date of the certificate from trained_in table and aliasing it
-- From tables physician, undergoes, patient, procedure, and trained_in
FROM physician p,
     undergoes u,
     patient pt,
     PROCEDURE pr,
     trained_in t
-- Conditions for joining tables
WHERE u.patient = pt.ssn -- Joining undergoes table with patient table based on patient's SSN
  AND u.procedure = pr.code -- Joining undergoes table with procedure table based on procedure code
  AND u.physician = p.employeeid -- Joining undergoes table with physician table based on physician's employee ID
  AND pr.code = t.treatment -- Joining procedure table with trained_in table based on procedure code
  AND p.employeeid = t.physician -- Joining physician table with trained_in table based on physician's employee ID
  AND u.date > t.certificationexpires; -- Filtering to include only procedures where the date is after the expiry date of the physician's certificate

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.