Order 13 was paid from the customer's wallet. Cancel it completely, in one transaction:
the order's status becomes cancelled
its payment's status becomes refunded
the amount paid goes back into the wallet of the customer who placed the order
every product in the order gets its stock back: up by the quantity ordered
Commit, then finish with exactly this query:
Code
SELECT o.status AS order_status, pay.status AS payment_status, a.balance, p.product_name, p.stockFROM orders oJOIN payments pay ON pay.order_id = o.order_idJOIN accounts a ON a.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.order_id = 13ORDER BY p.product_name;
Tables
orders
order_id (PK)
customer_id (FK)
order_date
status
total_amount
shipping_city
payments
payment_id (PK)
order_id (FK)
customer_id (FK)
amount
method
status
paid_at
accounts
account_id (PK)
customer_id (FK)
account_no
balance
opened_at
order_items
item_id (PK)
order_id (FK)
product_id (FK)
quantity
unit_price
discount_pct
products
product_id (PK)
product_name
stock
Cancel an Order Completely
Hard
Description
Order 13 was paid from the customer's wallet. Cancel it completely, in one transaction:
the order's status becomes cancelled
its payment's status becomes refunded
the amount paid goes back into the wallet of the customer who placed the order
every product in the order gets its stock back: up by the quantity ordered
Commit, then finish with exactly this query:
Code
SELECT o.status AS order_status, pay.status AS payment_status, a.balance, p.product_name, p.stockFROM orders oJOIN payments pay ON pay.order_id = o.order_idJOIN accounts a ON a.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.order_id = 13ORDER BY p.product_name;