SQL Exercise: Count the number of unavailable rooms
7. From the following table, write a SQL query to count the number of unavailable rooms. Return count as "Number of unavailable 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 unavailable rooms"
FROM room
WHERE unavailable='true';
Sample Output:
Number of unavailable rooms
---------------------------
7
(1 row)
Explanation:
The said query in SQL that counts the number of unavailable rooms in the room table.
The query selects all columns (*) from the room table where the unavailable column is set to 'true'. The resulting output will be a single row with a single column, showing the number of unavailable rooms in the room table. The column will be labeled "Number of unavailable 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: Count the number available rooms.
Next SQL Exercise: Find the name and department of the physician.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.