Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Aggregate Queries HELP!!

    Hello, Im using db2 and am very new to it. Im trying to execute these queries but im having 0 luck. Im new to SQL and have never really worked with Aggregates.Here is my Library Table Schema

    Cardholder
    borrowerid b_name b_addr b_status

    Reserves
    borrowerid isbn r_date

    Status
    b_status loan_limit

    Book
    isbn author title pub_name pub_date c_price

    Copy
    accession_no isbn p_price

    Borrows
    borrowerid accession_no l_date

    Heres what Im trying to do:

    1) Find the cardholder (b_name, b_addr) who has borrowed the oldest book in the library.


    2) Did people of 'junior' status borrow more books than people of 'senior' status? In general, which of the various statuses has cardhodlers who have borrowed the most books? Answer this question by finding how many books the people of each status actually borrowed.


    3) For each cardholder (b_name, b_addr) find the average current price of all the books (not copies) they have either borrowed or reserved.

    I just dont even know where to start with these types of queries. Any help would be appreciated!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mnelson View Post
    Hello, Im using db2 and ...
    ... and you posted in the Microsoft SQL Server forum

    Quote Originally Posted by Mnelson View Post
    I just dont even know where to start with these types of queries.
    you weren't paying attention in class, holmes

    we don't do homework on this site, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll move your question to the DB2 forum, it should get better answers there than in the SQL Server forum!

    I usually recognize most assignments, but this one is new to me. Which class are you taking or which book are you using and what chapter is this assignment from?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    we don't do homework on this site, sorry
    That's true.
    But it doesn't mean we can't help you getting started and help you out if you get stuck. That is, after you give us proof that you are willing to work and learn.

    This is the SQL Server forum, but the SQL scripts for these questions is (almost) the same for SQL Server and DB2.

    1) Write the DDL scripts (CREATE TABLE) to create those tables. Post it on this forum.
    2) Write the DML scripts (INSERT INTO) to populate those tables with the sample data you received from your teacher. Post it on this forum.

    With this information we can compare our results with yours.

    Question 1) Find the cardholder (b_name, b_addr) who has borrowed the oldest book in the library.

    Tackle this problem one step at a time:
    3) Write a SELECT script to find all the books in the library, order by their age, the oldest first
    4) Write a SELECT script to find all the cardholder and the books they have ever borrowed
    5) Write a SELECT script that combines script 3) and 4)
    6) Adjust script 5) so it only shows the oldest book and its cardholder

    Post all your scripts on this forum.

    Don't worry that you get stuck somewhere. Show us what you got so far and we'll help you further.

    Once this question is answered we will continue with the second and third question.


    Oops, Pat's reply and mine crossed each other. If you don't get help on the DB2 forum, I don't mind helping you further, but I know there are also very skilled and helpful people on the DB2 forum (I used to be member on that forum).
    Last edited by Wim; 04-12-12 at 15:49. Reason: Oops, Pat's reply and mine crossed each other.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Let's start from here.
    1) Write the DDL scripts (CREATE TABLE) to create those tables. Post it on this forum.
    2) Write the DML scripts (INSERT INTO) to populate those tables with the sample data you received from your teacher. Post it on this forum.

Posting Permissions

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