SQL Exercise: Find the nurses and the block where they are booked
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
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample table: on_call
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.
Go to:
PREV : Name of the patients, their block, floor, room number.
NEXT : Make a report of specified queries.
Practice Online
E R Diagram of Hospital Database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.