w3resource
SQL exercises

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. Write a query in SQL to obtain the names of 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 cetified for that procedure.

Sample table: physician


Sample table: undergoes


Sample table: patient


Sample table: procedure


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:

E R Diagram: SQL Hospital Database.

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

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming