First, you need to add some unique primary keys. That will make joining much easier and the code more readable. Look up "normalization" in google and try to get to 3NF. Second, use "not exists" instead of "not in". They both work, but "not in" does a table scan. If your database gets large, that isn't going to work quickly. Not a big deal for lookup tables and small databases, just FYI.

Now for the join:

You tried this:

Select *
From room R, booking B
Where R.room_no = B.room_no
And R.room_type = ‘single’
And B.arrivel_date = ‘2005-04-23’
And B.departure_date=’2005-04-30’ ;

Try something like this:

Select * From Room R
Join Booking B
On R.room_no = B.room_no
Where R.room_type = ‘single’
And B.arrivel_date = ‘2005-04-23’
And B.departure_date=’2005-04-30’

Also, look up on Google the different types of joins and use what you want. Inner is the default in MS SQL; I should have been explicit in my code but now you know. Good Luck.