Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Registered User
    Join Date
    Apr 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL and PL/SQL Queries

    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:
    Code:
    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:
    Code:
    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
    Last edited by modm; 04-04-2009 at 11:52 PM.

  • #2
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Answers to ur Queries, Just try it!!

    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •