Data in sql_store::order_items table

The data in the order_items table in the sql_store schema (database) seems inconsistent. In the first record for order_id = 6, the product_id is 1.

In the products table, the record where product_id =1 is for item “Foam Dinner Plate”, with a unit_price of 1.21.

But in the order_items table, the unit_price is 8.65. It should be the same value as in the products table, I believe.

Additionally, the unit_price is an attribute of product and not an attribute of an order item, so it does not belong in the order_items table, I believe.

Below is the SQL statement I wrote to return records from order_items with the product name and the line total for each record where order_id = 6.

    p.unit_price * oi.quantity AS line_total
    order_items AS oi
        INNER JOIN
    products AS p ON oi.product_id = p.product_id
    oi.order_id = 6;

Can you advise what I am failing to understand in this exercise?

Thank you kindly.

It’s been awhile since I completed the course, but I believe the products table shows the current unit_price of the product, whereas the order_items table shows the unit_price of the product at the time the order was placed. A business may change the price of a product but the change wouldn’t reflect for orders already placed for that item.

1 Like