SQL exercises on hospital Database: Find the name and position of all physicians who completed a medical procedure with certification after the date of expiration of their certificate

SQL hospital Database: Exercise-33 with Solution

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

Sample table: physician

Sample table: undergoes

Sample table: trained_in

Sample Solution:

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)

Practice Online

E R Diagram of Hospital Database:

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

Previous: From the following tables, write a SQL query to find all the physicians, their procedure, date when the procedure was carried out and name of the patient on which procedure have been carried out but those physicians are not certified for that procedure. Return Physician Name as "Physician", Procedure Name as "Procedure", date, and Patient. Name as "Patient".
Next: From the following table, write a SQL query to find all those physicians who completed a medical procedure with certification after the date of expiration of their certificate. 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”.