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

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

    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
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Rachelk
    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...
    ...
    well to start with I'd suggest posting in the correct forum....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Posts
    22

    Which one should I post in?

    Thank you!

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Well I'll help you with the query.

    Code:
    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
    That was the hard part. Now you'll just need to declare it as a cursor and walk it.

  5. #5
    Join Date
    Sep 2009
    Posts
    22
    Thank you sooo sooo much for your help! You have no idea how much I appreciate it!

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I noticed that low demand is less then 2 and high demand is greater then two. Is 2 normal demand. Maybe something like.

    Code:
    select p.product_name,  CASE WHEN count(o.product_id) > 2 THEN 'High demand'
      WHEN count(o.product_id) < 2 THEN 'Low demand'
      when count(o.product_id) = 2 then null
      END AS demand
    from order_details o, product p
    where o.product_id = p.product_id
    group by p.product_id;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Sep 2009
    Posts
    22
    this is what i have... but its not taking the sum of quantity correctly...

    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);

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but its not taking the sum of quantity correctly...
    Because code does not SUM anything.
    I am unclear why you think SUM OF QUANTITY is involved.
    Problem requires to COUNT number of orders (records) on per product basis
    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
  •