w3resource

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

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: appointment
 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:

E R Diagram: SQL 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/hospital-database-exercise/sql-exercise-hospital-database-16.php