SQL for Test Data Validation | Dataford Interview Questions

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

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

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
order_total	NUMERIC(10,2)	Stored total expected for the order

order_items

Column	Type	Description
item_idPK	INT	Unique order item identifier
order_id	INT	Order associated with the item
line_total	NUMERIC(10,2)	Total value of the item line

payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
order_id	INT	Order associated with the payment
payment_amount	NUMERIC(10,2)	Amount applied by the payment

Your question is SQL for Test Data Validation. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem