Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: Need help with querying multiple tables

    Hello! I am going to thank everyone in advance for their help.

    I am having difficulty wrapping my brain around some queries that have to be performed on some tables I have.

    Here are my tables:
    Code:
    SQL> select *
      2  from item;
    
        ITEMNO ITEM_NAME                      CAT    QTY_OUT     QTY_IN    QTY_BAL    COST_PR    SALE_PR LAST_P_D LAS
    ---------- ------------------------------ --- ---------- ---------- ---------- ---------- ----------
           100 Corn, 1 doz                    VEG         20         20          0          2        1,5 15-07-09 09-
           101 Pepsi, 351ml                   DRK          4         12          8        1,5        1,3 17-07-09 09-
           102 Pencil, 12pk                   STA         15        115        100       4,99       2,99 20-07-09 10-
           103 Apples, 1lb                    FRT          1          4          3        5,5        4,5 21-07-09 21-
           104 Bug Spray                      OTH          2         12         10          6          3 15-07-09 15-
           105 Tilly Hat                      OTH          3         49         46      25,99      15,99 23-07-09 09-
           106 Grapes, 100g                   FRT          2         12         10          4          2 18-07-09 15-
           107 Eraser                         STA          5        103         98          3        2,5 20-07-09 09-
           108 Apple Juice, 500ml             DRK          2         32         30       4,29       2,29 17-07-09 15-
           109 Green Peppers                  VEG          7         15          8       1,99       3,99 15-07-09 09-
    
    10 rows selected.


    Code:
    SQL> select*
      2  from invoice;
    
         INVNO INVDATE      ITEMNO        QTY    SALE_PR   DISCOUNT   TOTAL_PR
    ---------- -------- ---------- ---------- ---------- ---------- ----------
             1 09-07-28        100         10        1,5         ,5       14,5
             2 09-07-28        107          2        2,5          2          3
             3 09-07-28        101          2        1,3         ,2        2,4
             4 09-07-28        105          2      15,99         10      21,98
             5 09-07-28        109          2       3,99          2       5,98
    And

    Code:
    SQL> select *
      2  from category;
    
    CAT CAT_NAME
    --- -------------------------
    VEG Vegetables
    DRK Drinks
    OTH Other
    FRT Fruit
    STA Stationary
    I need to perform the following queries:

    (1) Find the sum, minimum and maximum sales price for each cat_code
    (2) List the sales transactions showing the invoice number, date, item name, qty sold, price and total price for the items that are sold 2 months back.
    (3) List itemno, itemname, cost price and sales price for all the items which are more than 50 days in the stock.

    Any suggestions?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any suggestions?
    We don't do homework.
    You'll need to join 2 or more tables in a single SELECT statement.
    Hopefuly you were paying attention in class.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    I'm not asking you to do it for me.

    Everyone assumes that I am some punk kid who doesn't pay attention.

    I'm actually a working professional who has been taking an online course and I don't completely understand the concept of joining the two tables.

    I know that the two tables have itemno in common and I need to join them - just not completely sure which type of join I should be using.

    Unfortunately, my teacher doesn't write in English very well and I have had a hard time understanding her notes.

    Like I said, I'm simply looking for some guidance - not for someone to do it for me.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This forum contains many "solved" SQL problems.
    http://asktom.oracle.com contains many fine coding examples.

    Post DDL for tables.
    Post DML for test data.
    Post the SELECT statements you tried & show the results.
    using SQLPLUS use CUT & PASTE to post session back here using <code tags> as described in #1 STICKY post at top of this forum.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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