Practice & Assessment
Test your understanding of Transactions, Performance, and Production SQL
Multiple Choice Questions
5Account A has $200. A transaction debits $200 from A, then tries to credit an account B that doesn't exist. The credit fails. With proper transaction handling, what is Account A's balance?
EXPLAIN output shows 'Seq Scan on orders (cost=0.00..8500.00 rows=95000)' for a query with WHERE customer_id = 5. The orders table has 100,000 rows. What should you do?
A query filters on WHERE DATE(created_at) = '2024-01-15'. The created_at column is indexed. Will the index be used?
You insert 500 rows in a loop with 500 individual INSERT statements. How can this be improved?
An application runs: SELECT * FROM users LIMIT 20, then for each user runs SELECT * FROM posts WHERE user_id = ?. With 20 users, how many total database queries run?
Coding Challenges
1Transaction-Safe Inventory Deduction
Given tables: products (id, name, price), inventory (product_id, warehouse_id, quantity), orders (id, customer_id, status, total, created_at), order_items (id, order_id, product_id, quantity, unit_price): Write a transaction block that (1) inserts a new order with status 'pending', (2) inserts two order_items for that order, (3) deducts stock from inventory for each item (ensure quantity does not go below zero — rollback if it would), (4) updates order status to 'confirmed' after successful stock deduction, (5) inserts an audit_log row with order_id and timestamp. If any step fails, the entire transaction must roll back. Test with a case where one item has insufficient stock. Expected: order is fully created or fully absent. Time estimate: 25 minutes.
Mini Project
Query Performance Audit and Optimization
Given a realistic e-commerce schema with tables customers (500k rows), orders (2M rows), order_items (6M rows), products (50k rows): Part 1 — Run EXPLAIN ANALYZE on five provided slow queries and document the bottleneck for each (Seq Scan on large table, missing index, function on column, N+1 pattern, unnecessary DISTINCT). Part 2 — Rewrite each query to fix the bottleneck: add indexes where needed, rewrite function-on-column conditions as ranges, replace N+1 with a JOIN, replace unnecessary DISTINCT with GROUP BY. Part 3 — Implement three application patterns: (1) UPSERT for a user preferences table using ON CONFLICT, (2) soft delete implementation on customers with a deleted_at column and a partial index, (3) a bulk order status update wrapped in a transaction with RETURNING to confirm affected rows. Document before/after cost estimates from EXPLAIN for each optimization.