Results 1 to 3 of 3

Thread: set difference query

  1. #1
    Join Date
    Nov 2009
    Posts
    4

    set difference query

    Im working on some practice questions for my oracle class and one is asking me to list all the hotel rooms that are vacant on june 4th 2006. i know i need to get all rooms and then subtract all the rooms booked on that date to return the vacant ones however i am having trouble getting the SQL right. could someone please help me out.

    here is my table structrue

    Hotel(hotel_no, name, address)
    Room(room_no, hotel_no, type, price)
    booking(hotel_no, guest_no, room_no, date_from, date_to)

    i need to select hotel_no, room_no, date_from, and date_to

    again this is prob pretty simple but i just started learning this after switching from access.

    thanks
    alex

  2. #2
    Join Date
    Nov 2009
    Posts
    4

    almost solved

    so i got it figured out but my last question is how to i get the date_to and date_from to show up as blank. here is my code so far:

    select r.hotel_no, r.room_no, date_from, date_to
    from room r, booking b
    where r.hotel_no = b.hotel_no(+)
    minus
    select r.hotel_no, r.room_no, date_from, date_to
    from room r, booking b
    where r.hotel_no = b.hotel_no(+)
    and date_from<= '04-Jun-06'
    and date_to>= '04-Jun-06';

    it returns all the rooms that were not booked on june 4th. but when it displays the results it lists the date_from and date_to. i would like it to leave this information out but still have the columns. any suggestions?


    here is my output:

    HOTEL_NO ROOM_NO DATE_FROM DATE_TO
    10 101 02-MAY-06 20-MAY-06
    10 102 02-MAY-06 20-MAY-06
    10 103 02-MAY-06 20-MAY-06
    20 101 01-MAY-06 03-MAY-06
    20 101 20-MAY-06 31-MAY-06
    20 101 20-JUN-06 23-JUN-06
    20 102 01-MAY-06 03-MAY-06
    20 102 20-MAY-06 31-MAY-06
    20 102 20-JUN-06 23-JUN-06
    20 106 01-MAY-06 03-MAY-06
    20 106 20-MAY-06 31-MAY-06
    20 106 20-JUN-06 23-JUN-06
    30 201 10-JUN-06 15-JUN-06
    30 201 21-JUN-06 23-JUN-06
    30 211 10-JUN-06 15-JUN-06
    30 211 21-JUN-06 23-JUN-06
    40 120 25-JUN-06 26-JUN-06
    40 120 25-JUN-06 27-JUN-06
    40 121 25-JUN-06 26-JUN-06
    40 121 25-JUN-06 27-JUN-06
    40 122 25-JUN-06 26-JUN-06
    40 122 25-JUN-06 27-JUN-06

  3. #3
    Join Date
    Nov 2009
    Posts
    6
    I would think this would work. In pseudo code...

    (all rooms)
    minus
    (all rooms booked on 04-jun-06)

    so this is...

    (select hotel_no, room_no
    from room)
    minus
    (select hotel_no, room_no
    from booking
    where 04-Jun-2006 between date_from and date_to)

    The only question I have - is "between" inclusive or exclusive? I'm not sure but you should be able to test for it. Let me know how you get on.

Posting Permissions

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