SQL for Test Data Validation

What SQL queries have you used to validate data during testing?

Using the supplied tables, write one query that identifies orders whose stored total does not agree with their item total or payment total. The query must also identify orders with no items or no payments.

Output

1. One row per order that fails validation
2. Columns: order_id, order_total, item_total, payment_total, and validation_status
3. Include missing totals as zero in the output
4. Order by order_id ascending

Output details

• Use exact comparisons without rounding or tolerance between item_total, payment_total, and order_total.
• Set validation_status to MISSING_ITEMS, ITEM_TOTAL_MISMATCH, MISSING_PAYMENT, or PAYMENT_TOTAL_MISMATCH using that priority order, checking item issues first.