Ilia,

>select count(distinct a.Room_id)
>from room a, reservation b, CheckIn c
>where
>a.room_type not in
>(select b.room_type from reservation b
>where b.Arrival_Date > :Arrival_Date and b.Departure_Date < :Departure_Date
>union
>select c.room_type from CheckIn c
>and c.Check_In_Date > :Arrival_Date and c.Departure_Date < :Departure_Date)

this will not do what he wanted for two reasons:

1) This query only counts the rooms of which no reservation or checkin
exists. He probably wants returned 1 if there's two rooms of that room_type
and one is reserved, but your query doesn't cover that.

2) The query only checks those records where the room_type is
reserved/checked in for parts of the stay. I think you can get away with
two checks, though:

a) When a reservation has arrival < :arrival and departure > :arrival
b) When a reservation has arrival > :arrival and arrival < :departure

Sorry,
Set