Purchase Report

SELECT
    p.purchase_id,
    c.first_name || ' ' || c.last_name AS customer,
    s.shop_name,
    i.item_name,
    p.quantity,
    p.unit_price,
    p.quantity * p.unit_price AS total,
    p.purchase_date
FROM purchase p
JOIN customer c ON c.customer_id = p.customer_id
JOIN item i ON i.item_id = p.item_id
JOIN shop s ON s.shop_id = i.shop_id
ORDER BY p.purchase_date DESC;