SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need some help with sql urgently please

    hi i have an assignment dude tonight and as usual i have left it to the last minute >< . I am having trouble with the last 3 questions and if u are able to help i would really appreciate it i will donate $10 to a charity or your website

    the tables are

    BOOKING
    HOTEL_NO
    GUEST_NO
    DATE_FROM
    DATE_TO
    ROOM_NO

    GUEST
    GUEST_NO
    G_NAME
    H_ADDRESS

    HOTEL
    HOTEL_NO
    H_NAME
    H_ADDRESS

    ROOM
    ROOM_NO
    HOTEL_NO
    R_TYPE
    R_PRICE

    Question 8
    For the Sheraton hotel, list (in the ascending order) the total number of bookings for each room type.

    SELECT HOTEL_NO, COUNT(*) AS TOTALCOUNT
    FROM ROOM, BOOKING
    WHERE H.H_NAME = 'SHERATON'
    AND B.HOTEL_NO = R.HOTEL_NO
    GROUP BY HOTEL_NO;

    Question 9
    Find numbers and names of guests who booked rooms (at any hotel) in the price range between 230 and 350 dollars. Show also how many times they booked such rooms.

    SELECT GUEST_NO, G_NAME, R_PRICE
    FROM GUEST, ROOM
    WHERE R_PRICE >230 <350;
    (SELECT SUM GUEST_NO FROM BOOKING);

    Question 10
    Find the maximum average room price (regardless of room type) of all the hotels (Hint: Find the average of each hotel first).

    SELECT HOTEL_NO, AVG(R_PRICE) FROM ROOM
    GROUP BY HOTEL_NO

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, man, we won't do it for you

    post a serious attempt at each question, and we'll help you with advice and corrections
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    by the way, your copy/paste was incomplete, here's the entire question 10 --

    Find the maximum average room price (regardless of room type) of all the hotels (Hint: Find the average of each hotel first).
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, man, we won't do it for you

    post a serious attempt at each question, and we'll help you with advice and corrections
    hey thanx for replying man i just updated the original post with my attempts

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do you have a test database set up for this assignment?

    because you should really test your queries, to see the results (or error message) that they produce
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    do you have a test database set up for this assignment?

    because you should really test your queries, to see the results (or error message) that they produce
    hey yeh i have it set up

    question 8 the message that comes up is

    ORA-00918: column ambiguously defined
    00918. 00000 - "column ambiguously defined"
    *Cause:
    *Action:
    Error at Line: 5 Column: 10

    no idea what it means

    question 9 i tried

    SELECT GUEST_NO, G_NAME, R_PRICE
    FROM GUEST, ROOM
    WHERE R_PRICE >230 <350;
    (SELECT SUM(*) GUEST_NO FROM BOOKING);

    and the message i got was

    ORA-00936: missing expression
    00936. 00000 - "missing expression"
    *Cause:
    *Action:
    Error at Line: 10 Column: 13

    question 10

    i get this as the result but im not sure if its right or not

    H200 231
    H300 310
    H100 253.333333333333333333333333333333333333

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by wispr View Post
    question 8 the message that comes up is

    ORA-00918: column ambiguously defined
    00918. 00000 - "column ambiguously defined"
    *Cause:
    *Action:
    Error at Line: 5 Column: 10

    no idea what it means
    It means what it says. There is a column that is ambiguously defined, in other words you are using a column name in your query that exists in more than 1 table, and because you didn't specify from which column to take it (alias!) mysql doesn't know which column to use.
    Quote Originally Posted by wispr View Post
    question 9 i tried

    SELECT GUEST_NO, G_NAME, R_PRICE
    FROM GUEST, ROOM
    WHERE R_PRICE >230 <350;
    (SELECT SUM(*) GUEST_NO FROM BOOKING);

    and the message i got was

    ORA-00936: missing expression
    00936. 00000 - "missing expression"
    *Cause:
    *Action:
    Error at Line: 10 Column: 13
    See the line I indicated in red. You can't confront a column with two values like that. Do a reread of your reference manual and see how to write WHERE conditions.
    Quote Originally Posted by wispr View Post
    question 10

    i get this as the result but im not sure if its right or not

    H200 231
    H300 310
    H100 253.333333333333333333333333333333333333
    If you're not sure, how can we be? We don't even know the content of your test database.

  8. #8
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    due last night didnt do last 3 questions cause i couldnt figure it out hopefully the others were right and i will just pass

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by wispr View Post
    due last night didnt do last 3 questions cause i couldnt figure it out hopefully the others were right and i will just pass
    Well don't procrastinate You know for whatever reason, when I was in school and I had a DB class, I wasn't all that into it. 5 years later I make my living off it. I really wish I knew back then how important it was and I had pushed the limits of that class. I love to learn everything I can now and find the best methods for everything, I hope you can get to that point soon as well and enjoy this stuff as much as I do, before its too late.


Bookmarks

Posting Permissions

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