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

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

SQL hospital Database: Exercise-27 with Solution

27. 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.

Sample table: room

Sample Solution:

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 );

Sample Output:

 Floor | No of available rooms
-------+-----------------------
     3 |                     7
     4 |                     7
     2 |                     7
(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 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".
Next: From the following tables, write a SQL query to find the name of the patients, their block, floor, and room number where they admitted.

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