Practice Exercises

Time to put everything together. Open the Banking Portal and work through these exercises. Each one targets a specific validation technique from this chapter. Do not just read — write the SQL yourself, run it, and see the results.

Exercise 1: Field-Level Validation

1. Query information_schema to list all columns in the accounts table with their data types and NULL rules.
2. Find any accounts where balance is NULL or negative.
3. Find any users where phone number length is not exactly 10 digits.
4. Check if the email column has a UNIQUE constraint by trying to insert a duplicate.
5. Verify that the balance column uses DECIMAL, not FLOAT.

Exercise 2: Record-Level Validation

1. Write a query to find any transactions where the amount is zero, negative, or NULL.
2. For any order, verify that the order total equals the sum of its line items.
3. Find insurance policies where end_date is before or equal to start_date.
4. Check that no discount exceeds the item price on any order.

Exercise 3: Referential Integrity

1. Write a LEFT JOIN query to find orphan transactions (transactions pointing to non-existent accounts).
2. Test cascade behavior: delete a test user and verify whether child records (orders, addresses) were handled.
3. Query information_schema to list all foreign keys in the database and their cascade rules.
4. Try inserting an order with a non-existent user_id — does the FK constraint block it?

Exercise 4: Duplicate Detection

1. Find exact duplicate emails in the users table using GROUP BY HAVING.
2. Find fuzzy duplicate names using LOWER() and TRIM().
3. Find transactions that look like double-submits (same user, same amount, within 60 seconds).
4. Write a query to show all duplicate rows side by side with their IDs and timestamps.

Exercise 5: Business Rule Validation

1. Perform a money transfer in the Banking Portal. Verify that the sender and receiver balances changed correctly.
2. Run a conservation check: verify the total money across all accounts has not changed.
3. Find any accounts where the balance does not match the calculated sum of all transactions.
4. Try transferring more than the available balance. Check what happens in the database — is the constraint enforced?

Exercise 6: Write a JDBC Validation Test

Write a Java class with TestNG tests that validates the following programmatically:

1. No NULL emails exist in the users table.
2. No orphan orders exist.
3. Every order total matches the sum of its line items.
4. Total system balance is positive and matches an expected value.