SQL exercises on hospital Database: Find out the floor where the minimum no of rooms are available
SQL hospital Database: Exercise-27 with Solution
27. Write a query in SQL to find out the floor where the minimum no of rooms are available.
Sample table: room
SELECT blockfloor as "Floor", count(*) AS "No of available rooms" FROM room WHERE unavailable='false' GROUP BY blockfloor HAVING count(*) = (SELECT min(zz) AS highest_total FROM ( SELECT blockfloor , count(*) AS zz FROM room WHERE unavailable='false' GROUP BY blockfloor ) AS t );
Floor | No of available rooms -------+----------------------- 3 | 7 4 | 7 2 | 7 (3 rows)
E R Diagram of Hospital Database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?