SQL and PL/SQL Queries

[modm]

I have the following tables implemented and filled with data in Oracle 10g:

Venue_site (location#*, loc_name, address, tel_no)
Room (room#, location#*, room_name, cost_per_day, capacity)
Booking (booking#, customer#*, room#*, from_date, to_date, n_people)
Customer (customer#, cust_name, address, tel_no, discount)

and I am looking to do 3 SQL queries on them as follows:
1. Display the total number of bookings in each location
2. Display the details of rooms available on 03-Mar-09
3. Display the names of pairs of customers who have booked the same room together with the details of the location and room

I am having some difficulty in producing these queries which have to be implemented using subqueries rather than joins

for the first one I have this:
select location#, (select count(booking#)
from Booking
where Booking.room# = Room.room#
group by location#) "Number of Bookings"
from Room;

which produces a semi correct answer. this lists the location# and number of bookings but wil list location numbers more than once, the values on the count are correct but it seems to be the grouping of location# that is wrong. I have been able to do this in a separate query for each location# but this way should work as well? any ideas?

for the second query I have done this:

select *
from Room
where room# in (select room#
from Booking
where from_date != '03-Mar-09' and to_date != '03-Mar-09');
however this doesnt take into consideration a room that has from_date 02-mar-09 and to_date 04-mar-09 and if one location is booked on mutiple days and one is the 3rd march it wil stilll say it is free as it pulls the other date.

any suggestions on any of these 3 queries would be great. I am sure i am trying to make it more difficult than it is.
Will move on to the PL/SQL after I have these working

[vinothtr]

:)1. Total booking in locations

SELECT location#,
(SELECT COUNT(booking#)
FROM booking
WHERE room# IN (SELECT room# from room WHERE location# = v.location#))
FROM venue_site v

(or)

SELECT v.location#, COUNT(booking#)
FROM venue_site v, room r, booking b
WHERE v.location# = r.location# AND b.room# = r.room#
GROUP BY v.location#

2. Rooms available on 03-Mar-09

SELECT *
FROM room
WHERE room# NOT IN
(SELECT room#
FROM booking
WHERE '03-Mar-09' BETWEEN from_date AND to_date)


3. More than one Bookings on Same room for same day.

SELECT customer#,
room#,
(SELECT location# FROM room WHERE room.room# = bookings.room#) location#
FROM bookings
WHERE '03-Mar-09' BETWEEN from_date AND to_date AND
room# IN (SELECT room#
FROM booking
WHERE '03-Mar-09' BETWEEN from_date AND to_date)
GROUP BY room#
HAVING COUNT(room#) > 1