SQL Exercise: Name of the patients, their block, floor, room number

SQL hospital Database: Exercise-28 with Solution

28. From the following tables, write a SQL query to find the name of the patients, their block, floor, and room number where they admitted.

Sample table: stay

Sample table: patient

Sample table: room

Sample Solution:

-- This SQL query retrieves the names of patients along with the room, blockfloor, and blockcode of the rooms they are staying in from the stay, patient, and room tables.

SELECT p.name AS "Patient", -- Selects the name column from the patient table and aliases it as "Patient"
       s.room AS "Room", -- Selects the room column from the stay table and aliases it as "Room"
       r.blockfloor AS "Floor", -- Selects the blockfloor column from the room table and aliases it as "Floor"
       r.blockcode AS "Block" -- Selects the blockcode column from the room table and aliases it as "Block"
FROM stay s -- Specifies the stay table
JOIN patient p ON s.patient=p.ssn -- Joins the stay table with the patient table on the patient's SSN
JOIN room r ON s.room=r.roomnumber; -- Joins the stay table with the room table on the room number

Sample Output:

      Patient      | Room | Floor | Block
-------------------+------+-------+-------
 John Smith        |  111 |     1 |     2
 Random J. Patient |  123 |     1 |     3
 Dennis Doe        |  112 |     1 |     2
(3 rows)

Explanation:

The said query in SQL that retrieves the name of the patient, the room number they are staying in, and the floor and block of that room.

The JOIN keyword joins the patient table to the stay table based on the ssn and patient columns and the room table to the stay table based on the roomnumber and room 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: Find the floor with the minimum available rooms.
Next SQL Exercise: Find the nurses and the block where they are booked.