Prove with SQL that a bulk price update did exactly what was intended
A release script raised prices by 10% for a single category:
UPDATE products SET price = price * 1.10 WHERE category_id = 7;
The table products_before is a snapshot of products taken immediately before the run (same id values). The script reported that it updated 1 240 rows.
Write the SQL that proves the update did exactly what was intended: the right number of rows changed, every new value is correct, and nothing outside category 7 was touched.
-- 1) how many rows are in the target category?
-- your query here
-- 2) any row in category 7 whose new price is not old price * 1.10?
-- your query here
-- 3) any row OUTSIDE category 7 whose price changed?
-- your query hereWrite the queries.
Three queries, not one. Count the rows in the target category and compare with the reported count; join the table to the pre-run snapshot and assert no row inside the category holds a price other than old times 1.10; then assert that no row outside the category changed at all.
- ✗Trusting the affected-row count as the only evidence
- ✗Checking only the rows in scope and never that nothing else changed
- ✗Deriving the expected values with the same statement that made the change
- →How would you verify the update if no snapshot table had been taken?
- →Why wrap the whole verification in a transaction you roll back?
The check is three independent assertions — volume, values, and blast radius.
-- 1) volume: how many rows are in the target category (compare with 1 240)
SELECT count(*) FROM products WHERE category_id = 7;
-- 2) values: no row with a wrong new price
SELECT p.id, b.price AS old_price, p.price AS new_price
FROM products p
JOIN products_before b ON b.id = p.id
WHERE p.category_id = 7
AND p.price <> round(b.price * 1.10, 2);
-- 3) blast radius: nothing outside category 7 changed
SELECT p.id
FROM products p
JOIN products_before b ON b.id = p.id
WHERE p.category_id <> 7
AND p.price <> b.price;Queries 2 and 3 must both return zero rows. Separately, confirm the total row count is unchanged (an UPDATE must neither delete nor create rows) and that the rounding matches the rule in the requirements rather than whatever the database happened to do.
