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".

Sample table: nurse

Sample table: on_call

Sample Solution:

-- This SQL query retrieves the names of nurses along with the blockcode of the blocks they are on call for from the nurse and on_call tables.

SELECT n.name AS "Nurse", -- Selects the name column from the nurse table and aliases it as "Nurse"
       o.blockcode AS "Block" -- Selects the blockcode column from the on_call table and aliases it as "Block"
FROM nurse n -- Specifies the nurse table
JOIN on_call o ON o.nurse=n.employeeid; -- Joins the on_call table with the nurse table on the nurse's employee ID

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.