MySQL INSERT, UPDATE, DELETE and transactions (COMMIT and ROLLBACK)

Answers — INSERT, UPDATE, DELETE and transactions

1. Easy. Preview the exact row before changing it. Target: One Notebook row, id 1, price 100. Never run UPDATE without checking the predicate.

   SELECT * FROM products WHERE product_id=1;

2. Easy. Count how many rows an UPDATE would touch before running it: products in Stationery. Target: 1 row: rows_to_change = 3

   SELECT COUNT(*) AS rows_to_change FROM products WHERE category = 'Stationery';

3. Easy. Insert a new product 7, 'Eraser', Stationery, 10.00, then list all Stationery products. Target: 4 rows: Notebook, Pen, Planner, Eraser

   INSERT INTO products (product_id, product_name, category, price) VALUES (7, 'Eraser', 'Stationery', 10.00); SELECT product_id, product_name, price FROM products WHERE category = 'Stationery' ORDER BY product_id;

4. Easy. Insert customers 7 Gita (Ludhiana, no email) and 8 Harpreet (Ludhiana, harpreet@example.test) in one statement, then list Ludhiana customers. Target: 2 rows: Gita (email NULL), Harpreet

   INSERT INTO customers (customer_id, customer_name, city, email) VALUES (7, 'Gita', 'Ludhiana', NULL), (8, 'Harpreet', 'Ludhiana', 'harpreet@example.test'); SELECT customer_id, customer_name, email FROM customers WHERE city = 'Ludhiana' ORDER BY customer_id;

5. Easy. Inside a transaction, change Pen's price to 25, check it, then ROLLBACK. Target: 25.00 inside; 20.00 after ROLLBACK

   START TRANSACTION; UPDATE products SET price = 25 WHERE product_id = 2; SELECT price FROM products WHERE product_id = 2; ROLLBACK;

6. Easy. Insert a new customer 7 'Gita', Ludhiana, then roll it back. Target: 7 inside; 6 after ROLLBACK

   START TRANSACTION; INSERT INTO customers VALUES (7, 'Gita', 'Ludhiana', 'gita@example.test'); SELECT COUNT(*) FROM customers; ROLLBACK;

7. Medium. Record a new pending order 109 for Farah (customer 6) dated 2026-03-15, then show Farah's orders. Target: 1 row: 109, 2026-03-15, pending

   INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (109, 6, '2026-03-15', 'pending'); SELECT order_id, order_date, status FROM orders WHERE customer_id = 6;

8. Medium. Raise the Mug's price by 10% and show its new price. Target: 1 row: Mug 275.00

   UPDATE products SET price = price * 1.10 WHERE product_id = 4; SELECT product_name, price FROM products WHERE product_id = 4;

9. Medium. Cancel pending order 107, then show how many orders have each status. Target: 2 rows: cancelled 2, completed 6

   UPDATE orders SET status = 'cancelled' WHERE order_id = 107 AND status = 'pending'; SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY status;

10. Medium. Change an order status and roll it back. Target: Order 107 is completed inside the transaction and pending afterwards.

    START TRANSACTION;
    UPDATE orders SET status='completed' WHERE order_id=107;
    SELECT status FROM orders WHERE order_id=107;
    ROLLBACK;
    SELECT status FROM orders WHERE order_id=107;

11. Medium. Give Electronics a 10% price cut inside a transaction, show the new prices, roll back. Target: Desk lamp 720, Keyboard 1080 inside; 800 / 1200 after

    START TRANSACTION; UPDATE products SET price = price * 0.9 WHERE category = 'Electronics'; SELECT product_name, price FROM products WHERE category = 'Electronics' ORDER BY product_id; ROLLBACK;

12. Hard. Delete the order items that belong to cancelled orders (use a subquery), then count the remaining items. Target: 1 row: items_left = 13

    DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE status = 'cancelled'); SELECT COUNT(*) AS items_left FROM order_items;

13. Hard. Give every Stationery product 5% off, rounded to 2 decimals, and list the new Stationery prices. Target: 3 rows: Notebook 95.00, Pen 19.00, Planner 285.00

    UPDATE products SET price = ROUND(price * 0.95, 2) WHERE category = 'Stationery'; SELECT product_name, price FROM products WHERE category = 'Stationery' ORDER BY product_id;

14. Medium. Delete the cancelled order 104 inside a transaction. Why must its items go first? Target: 7 orders inside; 8 after ROLLBACK

    START TRANSACTION; DELETE FROM order_items WHERE order_id = 104; DELETE FROM orders WHERE order_id = 104; SELECT COUNT(*) FROM orders; ROLLBACK;

15. Professional. Remove cancelled order 104 for good: in one transaction delete its items and the order, verify the counts, and COMMIT. Target: 1 row: orders_left = 7, items_left = 13 (and the change is committed)

    START TRANSACTION; DELETE FROM order_items WHERE order_id = 104; DELETE FROM orders WHERE order_id = 104; SELECT (SELECT COUNT(*) FROM orders) AS orders_left, (SELECT COUNT(*) FROM order_items) AS items_left; COMMIT;

16. Professional. Safe price update: preview, START TRANSACTION, UPDATE Mug to 275, verify exactly 1 row changed, ROLLBACK. Target: changed = 1; Mug back to 250.00 after ROLLBACK

    SELECT * FROM products WHERE product_id = 4; START TRANSACTION; UPDATE products SET price = 275 WHERE product_id = 4; SELECT ROW_COUNT() AS changed; ROLLBACK;