Data in sql_store::order_items table

SQL
1

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.

SELECT 
    oi.order_id,
    p.name,
    p.unit_price,
    oi.quantity,
    p.unit_price * oi.quantity AS line_total
FROM
    order_items AS oi
        INNER JOIN
    products AS p ON oi.product_id = p.product_id
WHERE
    oi.order_id = 6;

sql_store - order_items with line_total
sql_store - order_items with line_total731×325 27.6 KB

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

Thank you kindly.

2

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