SQL exercises on hospital Database: Find out the floor where the maximum no of rooms are available

Last update on May 30 2022 07:32:59 (UTC/GMT +8 hours)

SQL hospital Database: Exercise-26 with Solution

26. From the following table, write a SQL query to find the floor where the maximum numbers of rooms are available. Return floor ID as "Floor", count "Number of available rooms".

Sample table: room

Sample Solution:

SELECT blockfloor as "Floor",
count(*) AS  "Number of available rooms"
FROM room
WHERE unavailable='false'
GROUP BY blockfloor
HAVING count(*) =
  (SELECT max(zz) AS highest_total
   FROM
( SELECTblockfloor ,
count(*) AS zz
      FROM room
      WHERE unavailable='false'
      GROUP BY blockfloor ) AS t );

Sample Output:

 Floor | Number of available rooms
-------+-----------------------
     1 |                     8
(1 row)

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 count the number of unavailable rooms for each block in each floor. Sort the result-set on block floor, block code. Return the floor ID as "Floor", block ID as "Block", and number of unavailable as "Number of unavailable rooms".
Next: From the following table, write a SQL query to find the floor where the minimum numbers of rooms are available. Return floor ID as "Floor", Number of available rooms.

SQL: Tips of the Day

SQL Server SELECT into existing table.

INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3