Need some help with my SQL homework

Discussion in 'OT Technology' started by r1dd1ck, Mar 14, 2008.

  1. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    0
    I have a database built in Oracle and need some help with 2 queries on my homework assignments that I can not figure out.

    There are more tables in the database but these questions only involve these two:

    INVENTORY
    PK,FK book_code
    PK,FK branch_num
    on_hand

    BOOK
    PK book_code
    title
    FK publisher_code
    type
    price
    paperback

    The 2 questions I can not figure out are as follows:

    1.Find the book code and book title for each book whose price is greater than the price of at least one book that has the type HOR.

    This one I am not too sure what to do. I started by creating a mirror table thing (not sure what it is called) and beyond that I am lost.

    SELECT b.book_code, b.title
    FROM book b, book bb
    WHERE b.book_code = bb.book_code
    AND b.title = bb.title
    AND b.publisher_code = bb.publisher_code
    AND b.type = bb.type
    AND b.price = bb.price
    AND b.paperback = bb.paperback
    AND b.price > bb.price (where b.type = (‘hor’))




    2. List the book code, book title and units on hand for each book in branch number 3. Be sure each book is included, regardless of whether there are any copies of the book currently on hand in branch 3. Order the output by book code.

    For this one I don't know what I am doing wrong. I think my join statement is right, but I should be getting tons of evtra rows in the output with null in the on_hand colum, but I get none.

    SELECT b.book_code, b.title, i.on_hand
    FROM book b
    FULL OUTER JOIN inventory i
    ON b.book_code = i.book_code
    WHERE i.branch_num = 3
    ORDER BY b.book_code


    Any help would be much appreciated.
     
  2. Bruticus

    Bruticus half dead OT Supporter

    Joined:
    Apr 10, 2004
    Messages:
    4,608
    Likes Received:
    0
    Location:
    Melbourne
    I'm not going to give you the answers but this would be how I would do them, after looking at them for a couple of seconds.

    1.Find the book code and book title for each book whose price is greater than the price of at least one book that has the type HOR.

    I would do a nested query such as:

    select ...
    from ...
    where price >
    (select ...
    from ...
    where ... )

    In the nested query I would select the minimum price of a book with the type HOR and in the outer query I would just select all the books with a price above that.

    2. List the book code, book title and units on hand for each book in branch number 3. Be sure each book is included, regardless of whether there are any copies of the book currently on hand in branch 3. Order the output by book code.

    Why are you doing a full outer join? Shouldn't on_hand be 0 and not null if there are none?
     
  3. Doomsday

    Doomsday XXX

    Joined:
    Mar 14, 2000
    Messages:
    14,902
    Likes Received:
    0
    Location:
    Minnesota
    read the sticky

    :lockd:
     
  4. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    0
    I am not asking for someone to do it for me. That would not help me at all to understand the material. I am just asking for someone to point me in the right direction, and possibly tell me what I am doing wrong with my join statement. Just because I only have 26 posts doesn't me that I don't know how to read the rules. Obviously you didn't read my post.

    The information in the inventory table only lists the books they have stock in i.e if they have 5 copies of a book. It does not list books they have no copies of. Hence the need for an outer join, so that I have the complete list of books from the book table and the ones in stock from the inventory table. For some reason the join statement does not give me this result, it only gives me the books that are in stock.
     
    Last edited: Mar 15, 2008
  5. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    0
    Bruticus thank you for the help on the first one. I figured it out.

    SELECT book_code, title
    FROM book
    WHERE price > (SELECT MIN(price) FROM book WHERE type = 'HOR')

    I was trying to do a nested query but I don't know where my head was at.
    I just don't understand what it wrong with my join statement in the second one. I should be getting around 40 rows returned, not 12.
     
  6. Frequency

    Frequency New Member

    Joined:
    Dec 30, 2004
    Messages:
    7,504
    Likes Received:
    0
    Location:
    PA
    wtf are you the next jolly?
     
  7. Doomsday

    Doomsday XXX

    Joined:
    Mar 14, 2000
    Messages:
    14,902
    Likes Received:
    0
    Location:
    Minnesota
    if it were like P07, I would have called him an idiot or something.
     
  8. Frequency

    Frequency New Member

    Joined:
    Dec 30, 2004
    Messages:
    7,504
    Likes Received:
    0
    Location:
    PA
    I'll give ya that one
     

Share This Page