﻿ SQL: Count the number available rooms

# SQL Exercise: Count the number available rooms

## SQL hospital Database: Exercise-6 with Solution

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿