SQL Exercise: Find all physicians who completed a medical procedure

SQL hospital Database: Exercise-33 with Solution

33. From the following table, write a SQL query to find all physicians who completed a medical procedure with certification after the expiration date of their license. Return Physician Name as "Physician", Position as "Position".

Sample table: physician

Sample table: undergoes

Sample table: trained_in

Sample Solution:

-- Selecting the name and position of physicians
SELECT name AS "Physician", -- Selecting the name column from the physician table and aliasing it as "Physician"
       position AS "Position" -- Selecting the position column from the physician table and aliasing it as "Position"
FROM physician -- Specifies the physician table
WHERE employeeid IN -- Filters the physicians to include only those whose employee ID is present in the subquery
    ( SELECT physician -- Subquery: Selects the physician column from the undergoes table
     FROM undergoes u -- Subquery: Specifies the undergoes table and aliases it as "u"
     WHERE date > -- Subquery: Filters the rows to include only those where the date of the procedure is greater than
         ( SELECT certificationexpires -- Subquery: Selects the certificationexpires column from the trained_in table
          FROM trained_in t -- Subquery: Specifies the trained_in table and aliases it as "t"
          WHERE t.physician = u.physician -- Subquery: Joins the trained_in table with the undergoes table based on physician
            AND t.treatment = u.procedure ) ); -- Subquery: Adds a condition for the procedure in the join

Sample Output:

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

Explanation:

This SQL query retrieves the names and positions of physicians whose certification has expired for a procedure they have performed.

The WHERE clause filters the results where the "employeeid" column is found in a subquery.

The WHERE clause of the subquery include only those records where the "date" column is greater than the certification expiration date for the procedure.

The certification expiration date is obtained from inner most subquery which selects the "certificationexpires" column from the 'trained_in' table where the physician and procedure match those in the outer subquery.

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: Doctors do the same procedure but are not certified.
Next SQL Exercise: Medical procedures done after certification expired.