What is average quantity for each product ordered? (Database question)?

Question

You don't have to show me the solution. I just need help understanding what this question wants.

I think the question is referring to the PRODUCT_ORDER table and the PURCHASE_ORDER.

PRODUCT_ORDER
order_id order_date ship_date payment_type total customer_id

PURCHASE_ORDER
po_no po_date product_id quantity supplier_id

PRODUCT
product_id product_name quantity_in_stock reorder_point price supplier_id reorder_qty

I just don't understand what they want me to use the AVG function on.

I'm using Oracle as the dbms.

Product Order to Order Details 1 To Many with a cardinality of 1.

Product to Order Details 1 to Many with a cardinality of 1

Product to Order Details 1 to Many with a cardinality of 1.

Answer 1

What is the relation between PRODUCT_ORDER table and
PURCHASE_ORDER table ??? Does "po_no" means "product order no" ?? If so, the query should be like

SELECT AVG(quantity) FROM PURCHASE_ORDER
INNER JOIN PRODUCT_ORDER ON (PRODUCT_ORDER.po_no=PURCHASE_ORDER.order_id)

or something similar to this...

good luck

Answer 2

Select Avg(PO.quantity) as AVG_Quantity, P.product_name as Product, PRD.order_date as OrderDate from Purchase_Order PO
INNER JOIN Product P ON PO.product_id = P.product_id INNER Join PRODUCT_ORDER PRD ON PO.po_no = PRD.order_id
GROUP BY P.product_name, PRD.order_date

If you like not the grouping over the date just remove it from the query

Helmut