Medical Center database
Non primary-foreign key JOINs
Non-equi JOINs
Non-equi self JOINs
Instruction
Well done! Here comes the next exercise on the orders table.
Exercise
For each order placed during the promotional period, show its ID, the ID of the ordered product, the date when the order was placed, and the total amount that was paid for the order (amount * price). Name the column total_amount.
Stuck? Here's a hint!
Type:
SELECT orders.id, orders.product_id, order_date, price * amount AS total_amount FROM orders JOIN product_price ON orders.product_id = product_price.product_id AND order_date BETWEEN start_date AND end_date
