SQL exercises on hospital Database: Find the names of all the nurses who have ever been on call for room 122

SQL hospital Database: Exercise-35 with Solution

35. From the following table, write a SQL query to find those nurses who have ever been on call for room 122. Return name of the nurses.

Sample table: nurse

Sample table: on_call

Sample table: room

Sample Solution:

SELECT n.name
FROM nurse n
WHERE employeeid IN
    ( SELECT oc.nurse
     FROM on_call oc,
          room r
     WHERE oc.blockfloor = r.blockfloor
       AND oc.blockcode = r.blockcode
       AND r.roomnumber = 122 );

Sample Output:

      name
-----------------
 Laverne Roberts
 Paul Flowers
(2 rows)	

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 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".
Next: From the following table, write a SQL query to find those patients who have been prescribed by some medication by his/her physician who has carried out primary care. Return Patient name as “Patient”, and Physician Name as "Physician".