w3resource
SQL exercises

SQL exercises on hospital Database: Find the name of all those physicians who completed a medical procedure with certification after the date of expiration of their certificate, with some other information

SQL hospital Database: Exercise-34 with Solution

34. Write a query in SQL to obtain the name of all those physicians who completed a medical procedure with certification after the date of expiration of their certificate, their position, procedure they have done, date of procedure, name of the patient on which the procedure had been applied and the date when the certification expired.

Sample table: physician


Sample table: undergoes


Sample table: patient


Sample table: procedure


Sample table: trained_in


Sample Solution:

SELECT p.name AS "Physician",
       p.position AS "Position",
       pr.name AS "Procedure",
       u.date AS "Date of Procedure",
       pt.name AS "Patient",
       t.certificationexpires AS "Expiry Date of Certificate"
FROM physician p,
     undergoes u,
     patient pt,
     PROCEDURE pr,
               trained_in t
WHERE u.patient = pt.ssn
  AND u.procedure = pr.code
  AND u.physician = p.employeeid
  AND Pr.code = t.treatment
  AND P.employeeid = t.physician
  AND u.Date > t.certificationexpires;

Sample Output:

  Physician   |           Position           |         Procedure          |  Date of Procedure  |  Patient   | Expiry Date of Certificate
--------------+------------------------------+----------------------------+---------------------+------------+-------------------------
 Todd Quinlan | Surgical Attending Physician | Obfuscated Dermogastrotomy | 2008-05-09 00:00:00 | Dennis Doe | 2007-12-31
(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.

Previous: Write a query in SQL to obtain the name and position of all physicians who completed a medical procedure with certification after the date of expiration of their certificate.
Next: Write a query in SQL to obtain the names of all the nurses who have ever been on call for room 122.

What is the difficulty level of this exercise?



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