#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    2
    Rep Power
    0

    Please help me with this!


    I need help with 2 queries I've been stuck on for a long time. There are 4 tables in a database, hotel,room,booking and guest. I desperately need help with these 2 queries, please take a look, thank you.

    1) Find the guest no. and guest names of guests who booked rooms (at any hotel) in the price range between 130 and 150 dollars. Show also how many times they booked such rooms.

    2)Find the highest average room price (regardless of room type) out of all the hotels (find average room price of each hotel first,then show the highest average among them,using MAX function).

    p.s Im using Oracle SQL developer

    HOTEL
    Code:
     
    HOTEL_NO  H_NAME   H_ADDRESS
    --------  ------   ----------
        H100  Windsor  Melbourne
        H200  ANA      Sydney
        H300  Hilton   Adelaide
    ROOM
    Code:
    ROOM_NO  HOTEL_NO  R_TYPE  R_PRICE 
    -------  --------  ------  -------- 
          1  H100      Single  120 
          2  H100      Double  130 
          3  H100      Family  110  
          1  H200      Single  160 
          2  H200      Double  170 
          3  H200      Single  150 
          4  H200      Family  180 
          5  H200      Family  195 
          1  H300      Double  200 
          2  H300      Single  220
    GUEST
    Code:
    GUEST_NO  G_NAME           G_ADDRESS 
    --------  ------           ---------
        G001  Anita Doherty    Sydney 
        G002  Paul Cross       Sydney 
        G003  Jenny Freestone  Canberra 
        G009  Dianne Kelly     Adelaide 
        G005  Steve Kirkwood   Hobart
        G006  Martin Ferguson  Darwin 
        G007  Vanessa Horton   Hobart 
        Goo8  Bianca Begg      Sydney
    BOOKING
    Code:
    HOTEL_NO  GUEST_NO  DATE_FROM  DATE_TO    ROOM_NO 
    --------  --------  ---------  -------    -------
        H100      G001  15-AUG-09  17-AUG-09  1 
        H100      G002  30-AUG-09  15-SEP-09  2 
        H100      G002  01-AUG-09  02-AUG-09  2 
        H100      G005  10-AUG-09  11-AUG-09  2
        H100      G003  01-SEP-09  15-SEP-09  3 
        H100      G006  31-JUL-09  02-AUG-09  1 
        H200      G009  11-JUL-09  01-AUG-09  1 
        H200      G005  12-JUL-09  13-JUL-09  1 
        H200      G006  14-JUL-09  15-JUL-09  1 
        H200      G001  17-AUG-09  28-AUG-09  2
        H200      G003  31-JUL-09  01-SEP-09  1
        H200      G006  31-AUG-09  01-SEP-09  2
        H200      G005  31-AUG-09  15-SEP-09  4 
        H300      G007  01-SEP-09  15-SEP-09  1
    The relationships should be pretty obvious but here they are if you need them:

    HOTEL.HOTEL_NO = ROOM.HOTEL_NO = BOOKING.HOTEL_NO

    GUEST.GUEST_NO= BOOKING.GUEST_NO
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    You know this is a MySQL forum, right?
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    no problem, i will boot this homework question over to the oracle forum, where i am sure they will be less kind to this person than we would hvae been...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    844
    Rep Power
    387

    Cool


    Originally Posted by abirz
    I need help with 2 queries I've been stuck on for a long time. ... Etc ...
    R937 is right, here at the Oracle forum we are not so kind.

    We expect you to do your work and we will provide tips, some assistance and other insight so you can learn.

    What we do not provide is the code for a solution.

    Let's see what you got.

IMN logo majestic logo threadwatch logo seochat tools logo