SQL exercises on hospital Database: Count the number of available rooms in each block

SQL hospital Database: Exercise-22 with Solution

22. From the following table, write a SQL query to count the number of available rooms in each block. Sort the result-set on ID of the block. Return ID of the block as "Block", count number of available rooms as "Number of available rooms".

Sample table: room

Sample Solution:

SELECT blockcode AS "Block",
       count(*) "Number of available rooms"
FROM room
WHERE unavailable='false'
GROUP BY blockcode
ORDER BY blockcode;

Sample Output:

 Block | Number of available rooms
-------+---------------------------
     1 |                         9
     2 |                        10
     3 |                        10
(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 those patients who did not take any appointment. Return Patient name as "Patient", Physician name as "Physician" and Medication name as "Medication".
Next: From the following table, write a SQL query to count the number of available rooms in each floor. Sort the result-set on block floor. Return floor ID as "Floor" and count the number of available rooms as "Number of available rooms".