SQL exercises on hospital Database: Find out the floor where the maximum no of rooms are available
SQL hospital Database: Exercise-26 with Solution
26. Write a query in SQL to find out the floor where the maximum 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 max(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 -------+----------------------- 1 | 8 (1 row)
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?