SQL Exercise: Find the nurses and the block where they are booked

SQL hospital Database: Exercise-29 with Solution

29. From the following tables, write a SQL query to locate the nurses and the block where they are scheduled to attend the on-call patients.Return Nurse Name as "Nurse", Block code as "Block".

 employeeid |      name       |  position  | registered |    ssn
------------+-----------------+------------+------------+-----------
        101 | Carla Espinosa  | Head Nurse | t          | 111111110
        102 | Laverne Roberts | Nurse      | t          | 222222220
        103 | Paul Flowers    | Nurse      | f          | 333333330

 nurse | blockfloor | blockcode |     oncallstart     |      oncallend
-------+------------+-----------+---------------------+---------------------
   101 |          1 |         1 | 2008-11-04 11:00:00 | 2008-11-04 19:00:00
   101 |          1 |         2 | 2008-11-04 11:00:00 | 2008-11-04 19:00:00
   102 |          1 |         3 | 2008-11-04 11:00:00 | 2008-11-04 19:00:00
   103 |          1 |         1 | 2008-11-04 19:00:00 | 2008-11-05 03:00:00
   103 |          1 |         2 | 2008-11-04 19:00:00 | 2008-11-05 03:00:00
   103 |          1 |         3 | 2008-11-04 19:00:00 | 2008-11-05 03:00:00

Sample Solution:

SELECT n.name AS "Nurse",
       o.blockcode AS "Block"
FROM nurse n
JOIN on_call o ON o.nurse=n.employeeid;

Sample Output:

      Nurse      | Block
-----------------+-------
 Carla Espinosa  |     1
 Carla Espinosa  |     2
 Laverne Roberts |     3
 Paul Flowers    |     1
 Paul Flowers    |     2
 Paul Flowers    |     3
(6 rows)

Explanation:

The said query in SQL that retrieves the name of the nurse and the block they are on call for.

The JOIN clause joins the 'nurse' and 'on_call' tables based on the employeeid and nurse columns.

Practice Online

E R Diagram of Hospital Database:

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

Previous SQL Exercise: Name of the patients, their block, floor, room number.
Next SQL Exercise: Make a report of specified queries.