MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


@@version


8.0.36


create table reservedRoom (
bookingID int ,
roomID int NOT NULL,
n_person int
)    
create table room (
roomID int NOT NULL PRIMARY KEY,
descriptions int NOT NULL,
rate int,
category varchar(30)
)
create table bookings (
bookingID int NOT NULL PRIMARY KEY,
clientID int NOT NULL,
checkIndate DATETIME,
checkOutDate DATETIME,
roomsCount int,
numNights int,
bookExpire DATETIME
)
insert into reservedRoom VALUES (2,2,2)
,(2,3,3)
,(2,4,3)
,(2,5,3)
,(2,1,2)

Records: 5  Duplicates: 0  Warnings: 0


insert
  into room
 values  
		 (1 ,2,2000,'standard')
		,(2 ,2,2000,'standard')
		,(3 ,2,2000,'standard')
		,(4 ,2,2000,'standard')
		,(5 ,2,2000,'standard')
		,(6 ,2,2000,'standard')
		,(7 ,2,2000,'standard')
		,(8 ,2,2000,'standard')
		,(9 ,2,2500,'quad'    )
		,(10,2,2500,'quad'    )
		,(11,2,2500,'quad'    )
		,(12,2,2500,'quad'    )
		,(13,2,2500,'quad'    )
		,(14,2,3000,'family'  )
		,(15,2,3000,'family'  )
		,(16,2,3000,'family'  )
		,(17,2,3000,'family'  )
		,(18,2,8000,'King'    )
		,(19,2,8000,'King'    )
		,(20,2,8000,'King'    )

Records: 20  Duplicates: 0  Warnings: 0


insert
  into bookings
VALUES  
			(1 ,1,'2018-02-08','2018-02-09',3,2,'2018-02-11') 
			,(2 ,2,'2018-02-08','2018-02-09',5,2,'2018-02-11') 
			,(3 ,3,'2018-02-08','2018-02-09',3,2,'2018-02-11') 
			,(4 ,4,'2018-02-08','2018-02-09',3,2,'2018-02-11') 
			,(5 ,5,'2018-02-08','2018-02-09',3,2,'2018-02-11') 
	 

Records: 5  Duplicates: 0  Warnings: 0


   select x.category
          ,count(x.roomId) - case when isnull(max(booked_rooms.cnt))=1 then 0 
                                  else max(booked_rooms.cnt)
                              end as available_rooms
     from room x
left join ( select c.category
                  ,count(c.roomID) as cnt
              from bookings a
              join reservedRoom b
                on a.bookingID=b.bookingID
              join room c
                on b.roomID=c.roomID
            where a.checkIndate between '2018-02-08' and '2018-02-09'
            group by c.category
           )booked_rooms
        on x.category=booked_rooms.category
 group by x.category       


category
available_rooms


standard
3

quad
5

family
4

King
3