SQL Exercise: Nurses and the room where they assist the physicians

SQL hospital Database: Exercise-16 with Solution

16. From the following tables, write a SQL query to identify the nurses and the room in which they will assist the physicians. Return Nurse Name as "Name of the Nurse" and examination room as "Room No.".

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

 appointmentid |  patient  | prepnurse | physician |    start_dt_time    |     end_dt_time     | examinationroom
---------------+-----------+-----------+-----------+---------------------+---------------------+-----------------
      13216584 | 100000001 |       101 |         1 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | A
      26548913 | 100000002 |       101 |         2 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | B
      36549879 | 100000001 |       102 |         1 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | A
      46846589 | 100000004 |       103 |         4 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | B
      59871321 | 100000004 |           |         4 | 2008-04-26 10:00:00 | 2008-04-26 11:00:00 | C
      69879231 | 100000003 |       103 |         2 | 2008-04-26 11:00:00 | 2008-04-26 12:00:00 | C
      76983231 | 100000001 |           |         3 | 2008-04-26 12:00:00 | 2008-04-26 13:00:00 | C
      86213939 | 100000004 |       102 |         9 | 2008-04-27 10:00:00 | 2008-04-21 11:00:00 | A
      93216548 | 100000002 |       101 |         2 | 2008-04-27 10:00:00 | 2008-04-27 11:00:00 | B

Sample Solution:

-- SELECTing nurse name and examination room based on appointment preparation nurse
SELECT n.name AS "Name of the Nurse",
       a.examinationroom AS "Room No."
-- FROM nurse table aliased as n
FROM nurse n
-- INNER JOIN with appointment table aliased as a based on preparation nurse's employeeid
JOIN appointment a ON a.prepnurse = n.employeeid;

Sample Output:

 Name of the Nurse | Room No.
-------------------+----------
 Carla Espinosa    | A
 Carla Espinosa    | B
 Laverne Roberts   | A
 Paul Flowers      | B
 Paul Flowers      | C
 Laverne Roberts   | A
 Carla Espinosa    | B
(7 rows)

Explanation:

The said query in SQL that returns information about the nurses who prepared for appointments, along with the examination room number for each appointment from the tables 'nurse' and 'appointment',

The JOIN keyword joins the "nurse" and "appointment" tables based on the employeeid and prepnurse columns.

Alternative Solution:

Using Implicit INNER JOIN:

-- SELECTing nurse name and examination room based on appointment preparation nurse
SELECT n.name AS "Name of the Nurse",
       a.examinationroom AS "Room No."
-- FROM nurse table aliased as n, appointment table aliased as a
FROM nurse n, appointment a
-- WHERE clause specifies the join condition based on preparation nurse's employeeid
WHERE a.prepnurse = n.employeeid;

Explanation:

This solution uses the traditional comma-separated table list with join conditions specified in the WHERE clause, creating an implicit INNER JOIN between the nurse and appointment tables.

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: Patients and the room number where they treated.
Next SQL Exercise: Patients with an appointment on the given date.