SQL exercises on hospital Database: Find out the name of the patients, their block, floor, and room number where they are admitted

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:

SELECT p.name AS "Patient",
       s.room AS "Room",
       r.blockfloor AS "Floor",
       r.blockcode AS "Block"
FROM stay s
JOIN patient p ON s.patient=p.ssn
JOIN room r ON s.room=r.roomnumber;

Sample Output:

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

Practice Online

E R Diagram of Hospital Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find the floor where the minimum numbers of rooms are available. Return floor ID as 'Floor', Number of available rooms.
Next: From the following tables, write a SQL query to find the nurses and the block where they are booked for attending the patients on call. Return Nurse Name as “Nurse”, Block code as “Block”.