SQL Exercise: Count the number available rooms

Last update on December 20 2024 12:01:13 (UTC/GMT +8 hours)

6. From the following table, write a SQL query to count the number available rooms. Return count as "Number of available rooms".

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 count(*) "Number of available rooms"
FROM room
WHERE unavailable='false';

Sample Output:

 Number of available rooms
---------------------------
                        29
(1 row)

Explanation:

The said query here in SQL that counts the number of available rooms in the room table.

The query selects all columns (*) from the room table where the unavailable column is set to 'false'.

The resulting output is a single row with a single column, showing the number of available rooms in the room table. The column will be labeled "Number of available rooms".

Pictorial presentation:

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 and block with a given room number.
Next SQL Exercise: Count the number of unavailable rooms.