Role: QA — data validation (portfolio project)
Dataset: a small e-commerce schema I built myself (customers, products, orders, order_items) — same domain as the rest of this portfolio, seeded with realistic data that includes deliberate, labeled data-quality issues.
UI testing and API testing both stop at the response the app gives you. This project goes one layer deeper: I wrote queries that validate the data itself — duplicate records, orphaned foreign keys, totals that don't add up — the kind of check that catches a bug the UI never shows because it never renders the broken row.
Schema
customers (id, name, email, country) · products (id, name, category, price) · orders (id, customer_id, order_date, total_amount) · order_items (id, order_id, product_id, quantity, unit_price) — a standard e-commerce shape, small enough to seed by hand with known, labeled anomalies.
Queries (9)
| # | What it validates | Technique | Result |
|---|---|---|---|
| Q1 | Duplicate customers by email | GROUP BY … HAVING COUNT(*) > 1 | 1 found |
| Q2 | Customers missing a required field (email) | NULL / empty-string check | 1 found |
| Q3 | Order items referencing a non-existent product | LEFT JOIN … WHERE … IS NULL (anti-join) | 1 found |
| Q4 | Stored order total vs. sum of its line items | JOIN + GROUP BY + HAVING (business rule) | 1 found |
| Q5 | Orders with no linked customer | NULL foreign key check | 1 found |
| Q6 | Products with an invalid price (≤ 0) | WHERE filter | 1 found |
| Q7 | Full order detail across customer + product | 3-table JOIN | 17 rows |
| Q8 | Top customers by total spend | Aggregation (SUM, GROUP BY, ORDER BY) | Pass |
| Q9 | Products that were never ordered | Anti-join | 1 found |
↳ Full SQL, the pre-built database file and the raw captured output: sql/ on GitHub.
Data issues found (5)
Every result below is from actually running the queries against the seeded database — not hand-typed.
order_items.product_id should exist in productsproduct_id = 99, which doesn't exist — the kind of row that crashes an order-history screen the moment it tries to join in the product nametotal_amount should equal SUM(quantity × unit_price) for that order↳ Full detail and reasoning for each severity rating in sql/RESULTS.md.