Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2009
    Posts
    22

    Question Unanswered: Need help with my homework....

    I was not sure which thread to post in....

    Below is the problem for my homework... I am stuck on it. This is the first time my class is starting pl/sql... Unfortunately I had no choice but to take this database class online, it was not offered in a normal classroom soooo... I need a lot of help with it...

    1.

    Create a PL/SQL anonymous block program unit that lists the product_name attribute value and a demand status text display. The demand status text display is determined by counting the number of times a product has been ordered so far. The demand status text display will be either "low demand" or "high demand." Low demand is displayed if the product has been ordered less than 2 times. High Demand is displayed if the product has been ordered more than 2 times.

    Where should I begin? Do I need to declare 'low demand' and 'high demand'??

    This is just a rough outline of what I have so far...

    This info is coming out of two tables, PRODUCT table, and ORDER_DETAILS table. I need to get the number of times a product has been ordered from ORDER_DETAILS, and the names of the product from the table PRODUCT.


    DECLARE
    Demand_status VARCHAR2(10);
    SELECT product_name
    FROM product

    BEGIN
    IF product_name.product < 2
    THEN dbms_output.put_line('Low Demand');

  2. #2
    Join Date
    Aug 2003
    Posts
    13
    First, PL/SQL is Oracle, but your problem can be solved by standard SQL by inner joining the product table to a sub-select wherein you would select orders, counting and grouping by product type, joining on product type. For Low vs High, simply use a case statement to check order count.

    If you need to output one at a time, you could use a Cursor I suppose, or if it's available in Oracle, a While (generally faster).

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Join the 2 via whatever key, do a count/sum on the second, whether via derived table or join with group by, and case the sum(orders) to 'low demand' or 'high demand'.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Rfh

    Quote Originally Posted by Rachelk
    I was not sure which thread to post in....

    Create a PL/SQL anonymous block program unit
    That always cracks me up

    that lists the product_name attribute value and a demand status text display. The demand status text display is determined by counting the number of times a product has been ordered so far. The demand status text display will be either "low demand" or "high demand." Low demand is displayed if the product has been ordered less than 2 times. High Demand is displayed if the product has been ordered more than 2 times.
    OK


    Where should I begin? Do I need to declare 'low demand' and 'high demand'??

    This is just a rough outline of what I have so far...

    This info is coming out of two tables, PRODUCT table, and ORDER_DETAILS table. I need to get the number of times a product has been ordered from ORDER_DETAILS, and the names of the product from the table PRODUCT.

    Post the DDL of the Table

    Also what version of Oracle are you using?

    Do you have the CASE Function or are you stuck with DECODE?

    In any case...this is SQL Server...I know some Oracle, but you want to look in this forum for more help

    Oracle - dBforums

    Good Luck

    However...something like

    SELECT Product, CASE WHEN SUM(Orders) < 2 THEN 'LOW' ELSE 'HIGH'
    FROM PRODUCT a INNER JOIN ORDER_DETAILS b ON a.ID = b.ID
    GROUP BY Product


    Less that 2 btw is 1 or 0....2 or greater is high demand?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2009
    Posts
    22
    Thank you guys for all your help!! I hope I can take it from here...

    Would I need a cursor and if so why? What exactly does a cursor do?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    Above contains the whole Oracle Documentation set.
    I hope you are ready & willing to Read The Fine Manual.
    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.

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    * Slaps RachelK for double posting *

    Now that I got that out of the way... In the real world, you wouldn't use a cursor (hopefully). But since this is homework and not the real world, you'll need to use a cursor to do this in a plsql block.

    What exactly does a cursor do?
    You need to do your homework

  8. #8
    Join Date
    Sep 2009
    Posts
    22
    Sorry for double posting....

  9. #9
    Join Date
    Sep 2009
    Posts
    22
    Someone Fix this....

    This is what I have...


    SET SERVEROUTPUT ON

    DECLARE
    CURSOR prod_cursor IS
    SELECT DISTINCT product_id, quantity
    FROM order_details;
    prod_row prod_cursor%rowtype;

    CURSOR name_cursor IS
    SELECT product_name, product_id
    FROM product;
    name_row name_cursor%rowtype;

    prod_name product.product_name%type;
    demand_status VARCHAR2(15);

    BEGIN
    OPEN name_cursor;
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;

    OPEN prod_cursor;
    LOOP
    FETCH prod_cursor INTO prod_row;
    EXIT WHEN prod_cursor%notfound;
    IF (prod_row.quantity > '2' and prod_row.product_id=name_row.product_id) THEN
    demand_status := 'High Demand';
    ELSE demand_status := 'Low Demand';
    END IF;

    END LOOP;

    dbms_output.put_line (name_row.product_name||' '||demand_status);

    CLOSE prod_cursor;
    END LOOP;
    CLOSE name_cursor;

    END;


    This is what it's suppose to look like....

    Product Name Demand Status
    ------------ -------------
    Beginner's Ski Boot Low Demand
    Intermediate Ski Boot High Demand
    Pro Ski Boot Low Demand
    Beginner's Ski Pole Low Demand
    Intermediate Ski Pole Low Demand
    Pro Ski Pole High Demand
    Road Bicycle Low Demand
    Mountain Bicycle Low Demand
    Tire Pump Low Demand
    Water Bottle High Demand
    Bicycle Tires Low Demand
    Bicycle Helmet Low Demand


    BUT mine is only showing 'low demand', can someone help me figure this out??

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I find formatted code easier to read & debug

    Code:
    SET SERVEROUTPUT ON 
    
    DECLARE 
      CURSOR prod_cursor IS 
        SELECT DISTINCT product_id, 
                        quantity 
        FROM   order_details; 
      prod_row       prod_cursor%ROWTYPE; 
      CURSOR name_cursor IS 
        SELECT product_name, 
               product_id 
        FROM   product; 
      name_row       name_cursor%ROWTYPE; 
      prod_name      product.product_name%TYPE; 
      demand_status  VARCHAR2(15); 
    BEGIN 
      OPEN name_cursor; 
       
      LOOP 
        FETCH name_cursor INTO name_row; 
         
        EXIT WHEN name_cursor%NOTFOUND; 
         
        OPEN prod_cursor; 
         
        LOOP 
          FETCH prod_cursor INTO prod_row; 
           
          EXIT WHEN prod_cursor%NOTFOUND; 
           
          IF (prod_row.quantity > '2' 
              AND prod_row.product_id = name_row.product_id) THEN 
            demand_status := 'High Demand'; 
          ELSE 
            demand_status := 'Low Demand'; 
          END IF; 
        END LOOP; 
         
        dbms_output.Put_line(name_row.product_name 
                             ||' ' 
                             ||demand_status); 
         
        CLOSE prod_cursor; 
      END LOOP; 
       
      CLOSE name_cursor; 
    END;
    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.

  11. #11
    Join Date
    Sep 2009
    Posts
    22
    I am still having problems. Can someone help me see if the loop is missing something or if I need to declare quantity on it's own?? I would really appreciate it...

  12. #12
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Good, now that you've done something, I can finish
    You don't need two cursors

    Code:
    SET SERVEROUTPUT ON 
    DECLARE 
      CURSOR artacus_rocks IS
        SELECT p.product_name, 
          CASE WHEN o.cnt > 2 THEN 'High demand'
          WHEN o.cnt <= 2 OR o.cnt IS NULL THEN 'Low demand'
          END AS demand
        FROM product p
        LEFT JOIN (
          SELECT product_id, COUNT(1) AS cnt
          FROM order_details
          GROUP BY product_id
        ) o ON p.product_id = o.product_id;
    
    BEGIN 
      FOR v_row IN artacus_rocks LOOP
        dbms_output.put_line(v_row.product_name || ' - ' || v_row.demand);
      END LOOP;
    END;
    Thanks again for formatting Ana. You should come help my coworkers

  13. #13
    Join Date
    Sep 2009
    Posts
    22
    artacus72 - Thank you so much for solving my issue... You are the best as well as everyone else who has helped me!

    what does o.cnt mean? why not count? is there another way to put this? we have no learned this o.cnt in my class yet and teacher might think it's weird that I'm using it? unless I'm just being ignorant?

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >we have no learned this o.cnt in my class yet and teacher might think it's weird that I'm using it? unless I'm just being ignorant?
    yes
    With regard to "o.cnt", what is "o" & what is "cnt" based upon posted SQL?
    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.

  15. #15
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    They are just aliases. Like Ana said, dig into the query and find out where they originated.

Posting Permissions

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