1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

need a little help

Discussion in 'SQL PL/SQL' started by dan, Dec 7, 2010.

  1. dan

    dan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I have something I can't figure out.
    I had a lab that was due on Sunday and they were several I couldn't figure out.
    here is one.
    Code (SQL):
    SELECT oi.orderid, b.retail
    FROM Order_items oi
    INNER JOIN books b
    ON oi.bookid = b.bookid
    GROUP BY oi.bookid
    HAVING SUM(retail) =
    (
    SELECT MAX(SUM(retail))
    FROM books
    GROUP BY bookid
    );
    that was my starting point,but I had no luck.
    We were suppose to nest the function.
     
  2. dan

    dan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    This could give me a better understanding.
    When you are nesting queries or select statements, do you have to do all your joins in your first query or is it where ever the join is in the actual statement?
    Meaning
    *this would be my first statement from one table*
    (
    this would be my inner statement - this would requires two tables-
    does the join go here, or in the first statement?
    );

    Hope thatmakes sense.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What was the actual assignment for this? Please post the exact requirements you were given and the tables/data you were to work with.
     
  4. dan

    dan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Here's the tougher one:
    Using the BOOK_AUTHOR and AUTHOR tables for the upper most query, create a sub query that will return the BOOKID, and the first and last name of the author(s) who have written the book(s) that have been purchased the most. To successfully accomplish this, you will need to first find out the maximum total number of copies (based on quantity) sold of a book (based on bookid) in the ORDER_ITEMS table. This will be done in the bottom most query. The return from this query will be used to compare against the BOOKID in the ORDER_ITEMS table that occurs an equal number of times in the middle query. The return of this query will then be used to compare to a BOOKID related to an Author in the tables of the top most query. (HINT: Be careful, you will need both GROUP BY and HAVING clauses for your solution.)

    Here's my attempt which returned 0 rows:
    Code (SQL):

    SELECT bookid, fname, lname, quantity
    FROM book_author JOIN author USING (authorid)
    JOIN books USING(bookid)
    JOIN order_items USING (bookid)
    GROUP BY bookid, fname, lname, quantity
    HAVING (quantity) =
    (
    SELECT MAX(SUM(quantity))
    FROM order_items
    GROUP BY bookid
    );
     
     

    Attached Files:

    • erd.zip
      File size:
      145.3 KB
      Views:
      11
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Dan,
    How are you? I can't access your zip file. Kindly provide the table statistics and the required output here. so that i may help with your query.