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:
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?
select location#, (select count(booking#)
where Booking.room# = Room.room#
group by location#) "Number of Bookings"
for the second query I have done this:
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.
where room# in (select room#
where from_date != '03-Mar-09' and to_date != '03-Mar-09');
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