create table Rooms

(Room_no char(4) primary key not null,

Room_Status varchar(20) not null,

Room_Type_Code char(4) foreign key references Room_Type(Room_Type_Code))

go

Data in the Table.

1011AvailSTD1

1012AvailSTD1

2011AvailDLX2

Create table Room_Bookings

(Booking_ID char(8) NOT NULL ,

Room_no char(4) NOT NULL Foreign KEY REFERENCES Rooms,

Adult_Num int ,

Child_Num int,

Chck_in_Date date,

Chck_out_Date date,

Rack_Rate decimal(7,2),

Guest_ID char(13)

)

go

Booking_ID R_No Arrv_Date Dep_Date

083044124012 2012-08-15 2012-08-25750.009210021234567

0837441240122012-08-20 2012-09-28750.009210021234567

0855412 10122012-08-15 2012-09-30450.009201112345678

1234567810122012-08-22 2012-08-23450.000987654321123

1234567810122012-08-25 2012-09-23450.000987654321123

The query does nt return correct room_Nos if the Arrv_Date and Dep_date are within the range of booked dates.

e.g Arrv_Date:2012-08-22 ,Dep_date:2012-09-20 ,it will return room 4012 and 1012 ofwhich they are booked by tht date.