Field-level checks verify each column in isolation. But some rules span multiple columns in the same row. An end_date must be after start_date. A total_amount must equal quantity times unit_price. A discount cannot exceed the item price. These are cross-field rules. If you only check one column at a time, you will miss them.
-- Rule 1: end_date must be AFTER start_date
SELECT policy_id, start_date, end_date
FROM insurance_policies
WHERE end_date <= start_date;
-- Any rows here = bug. A policy cannot end before it starts.
-- Rule 2: total_amount must equal quantity * unit_price
SELECT item_id, quantity, unit_price, total_amount,
(quantity * unit_price) AS expected_total
FROM order_items
WHERE total_amount != (quantity * unit_price);
-- Mismatch means the calculation logic is broken.
-- Rule 3: discount cannot exceed item price
SELECT item_id, unit_price, discount_amount
FROM order_items
WHERE discount_amount > unit_price;
-- A Rs 500 item with Rs 600 discount? That is free money.
-- Rule 4: min_balance must be less than max_balance
SELECT account_type, min_balance, max_balance
FROM account_types
WHERE min_balance >= max_balance;This is the single most common record-level validation in e-commerce and banking. The order header says total = Rs 1,500. But when you add up the line items, you get Rs 1,485. Where did the Rs 15 go? Or worse — the total is Rs 1,550 and the customer is overcharged.
-- Find orders where header total does not match sum of items
SELECT o.order_id,
o.total_amount AS header_total,
SUM(oi.quantity * oi.unit_price) AS calculated_total,
o.total_amount - SUM(oi.quantity * oi.unit_price) AS difference
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount != SUM(oi.quantity * oi.unit_price);
-- If discounts or taxes apply, account for them:
SELECT o.order_id,
o.total_amount AS header_total,
SUM(oi.quantity * oi.unit_price) - COALESCE(o.discount, 0)
+ COALESCE(o.tax_amount, 0) AS expected_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount, o.discount, o.tax_amount
HAVING o.total_amount != SUM(oi.quantity * oi.unit_price)
- COALESCE(o.discount, 0) + COALESCE(o.tax_amount, 0);In financial systems, even a Re 1 mismatch between the order total and line item sum is a critical bug. Auditors will flag it. Regulators will question it. Always validate totals down to the paisa.
Q: Give an example of a cross-field validation rule you have tested.
A: In an insurance application, I validated that the policy end_date was always after the start_date. I wrote a query: SELECT * FROM policies WHERE end_date <= start_date. I found 12 records where end_date equaled start_date — meaning zero-day policies. This was a bug in the bulk import script that was not applying the correct policy duration. I also validated order totals against line item sums in an e-commerce app and found a rounding error that caused a Re 1 mismatch on 3% of orders.
Key Point: Record-level validation checks cross-field rules within a single row — date ranges, calculated totals, and field dependencies.