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",
       pt.name AS "Patient"
FROM physician p,
     undergoes u,
     patient pt,
WHERE u.patient = pt.SSN
  AND u.procedure = pr.Code
  AND u.physician = p.EmployeeID
    ( 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