SQL exercises on hospital Database: Find the names of all the physicians with their procedure but they are not cetified for that procedure

SQL hospital Database: Exercise-32 with Solution

32. 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”.

Sample table: physician

Sample table: undergoes

Sample table: patient

Sample table: procedure

Sample table: trained_in

Sample Solution:

SELECT p.name AS "Physician",
       pr.name AS "Procedure",
       u.date,
       pt.name AS "Patient"
FROM physician p,
     undergoes u,
     patient pt,
     PROCEDURE pr
WHERE u.patient = pt.SSN
  AND u.procedure = pr.Code
  AND u.physician = p.EmployeeID
  AND NOT EXISTS
    ( SELECT *
     FROM trained_in t
     WHERE t.treatment = u.procedure
       AND t.physician = u.physician );

Sample Output:

    Physician     |       Procedure       |        date         |  Patient
------------------+-----------------------+---------------------+------------
 Christopher Turk | Complete Walletectomy | 2008-05-13 00:00:00 | Dennis Doe
(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 those physicians who performed a medical procedure, but they are not certified to perform. Return Physician name as “Physician”.
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".