SQL Exercise: Find the floor and block with a given room number

Last update on October 08 2025 12:35:57 (UTC/GMT +8 hours)

5. From the following table, write a SQL query to locate the floor and block where room number 212 is located. Return block floor as "Floor" and block code as "Block".

Sample table: room

roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
       101 | Single   |          1 |         1 | f
       102 | Single   |          1 |         1 | f
       103 | Single   |          1 |         1 | f
       111 | Single   |          1 |         2 | f
       112 | Single   |          1 |         2 | t
       113 | Single   |          1 |         2 | f
       121 | Single   |          1 |         3 | f
       122 | Single   |          1 |         3 | f
       123 | Single   |          1 |         3 | f
       201 | Single   |          2 |         1 | t
       202 | Single   |          2 |         1 | f
       203 | Single   |          2 |         1 | f
       211 | Single   |          2 |         2 | f
       212 | Single   |          2 |         2 | f
       213 | Single   |          2 |         2 | t
       221 | Single   |          2 |         3 | f
       222 | Single   |          2 |         3 | f
       223 | Single   |          2 |         3 | f
       301 | Single   |          3 |         1 | f
       302 | Single   |          3 |         1 | t
       303 | Single   |          3 |         1 | f
       311 | Single   |          3 |         2 | f
       312 | Single   |          3 |         2 | f
       313 | Single   |          3 |         2 | f
       321 | Single   |          3 |         3 | t
       322 | Single   |          3 |         3 | f
       323 | Single   |          3 |         3 | f
       401 | Single   |          4 |         1 | f
       402 | Single   |          4 |         1 | t
       403 | Single   |          4 |         1 | f
       411 | Single   |          4 |         2 | f
       412 | Single   |          4 |         2 | f
       413 | Single   |          4 |         2 | f
       421 | Single   |          4 |         3 | t
       422 | Single   |          4 |         3 | f
       423 | Single   |          4 |         3 | f

Sample Solution:

SELECT blockfloor AS "Floor",
       blockcode AS "Block"
FROM room
WHERE roomnumber=212;

Sample Output:

 Floor | Block
-------+-------
     2 |     2
(1 row)

Explanation:

The said query in SQL that selects the "blockfloor" and "blockcode" columns from the 'room' table where the "roomnumber" is equal to 212.

The "blockfloor" column aliased as "Floor" and the "blockcode" column aliased as "Block".

Pictorial presentation:

Find the floor and block where the room number 212 belongs to

Go to:

Previous SQL Exercise: Patients with at least one physician appointment.
NEXT : Count the number available rooms.

Practice Online

E R Diagram of Hospital Database:

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