SQL Database Validation with AI: Check Data Like a Tester

A screen can say “Order saved” while the database holds the wrong data. Testers who can check the data find problems others miss. In this lesson you will practise SQL database validation with AI, write read-only checks and learn how to confirm that a query is really correct.

---

💡 Why Check the Database?

---

🔍 Describe the Tables, Not the Data

Our example uses two tables. Use made-up data only.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total DECIMAL(10, 2) NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO customers (id, email) VALUES
  (1, 'asha@example.com'),
  (2, 'ravi@example.com'),
  (3, 'asha@example.com');

INSERT INTO orders (id, customer_id, total, status) VALUES
  (101, 1, 500.00, 'PAID'),
  (102, 2, -50.00, 'PAID'),
  (103, 9, 300.00, 'PAID');

---

🧩 Three Useful Validation Queries

Orders that have no matching customer:

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

This returns order 103, because customer 9 does not exist.

Email addresses used more than once:

SELECT email, COUNT(*) AS total
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

This returns asha@example.com with a total of 2.

Paid orders with an invalid total:

SELECT id, total
FROM orders
WHERE status = 'PAID' AND total <= 0;

This returns order 102, with a total of -50.00.

A helpful pattern is to write checks so that zero rows means healthy. Any returned row is then something to investigate. Learn more in our SQL Tutorial and SQL Joins lessons.

---

🛠️ How to Do SQL Database Validation with AI

Give the AI the schema, the database type and the rule you want to check. Then verify its query using data where you already know the answer.

Check	What to do
Correctness	Create rows that should and should not match
Dialect	Confirm the syntax for your database
Joins	Check whether rows are lost or duplicated
NULL values	Test how missing values behave
Safety	Confirm that the query only reads data

---

🌍 Real-Life Example: A Join That Lies

A tester asks AI to count paid orders per customer. The query joins orders to payments, but an order with two payment attempts is counted twice. The number looks believable. Testing the query with one known order and two payment rows exposes the mistake. Always test a query with data where you know the right answer.

---

🧪 Try It Yourself

1. Create the tables above in a local test database.
2. Run the three queries, and compare the results.
3. Add one more bad row of your own.
4. Ask AI for a new check, using the prompt below.
5. Add rows that prove the query works.

---

📋 Ready-to-Copy Prompt

You are a senior QA engineer writing SQL data validation checks.

Database type: [MySQL, PostgreSQL or other]
Tables and columns:
[paste schema with no real data]

Business rule to validate:
[describe the rule]

Write one read-only SELECT query that returns only rows breaking the
rule, so that zero rows means healthy.

Rules:
- Do not use INSERT, UPDATE, DELETE, DROP or ALTER.
- Use only the tables and columns above.
- Explain the query in plain language.
- Give me 2 test rows that should be returned and 2 that should not.

---

⚠️ Common Mistakes

• Pasting real customer records into an AI tool
• Running AI-generated changes on shared databases
• Trusting a query because it runs without an error
• Forgetting how NULL values behave
• Ignoring duplicated rows caused by joins

---

🎯 Key Takeaways

• Database checks confirm what was really saved.
• Share the schema with AI, and keep real data out.
• Design validation queries so that zero rows means healthy.
• Prove each query using data with a known answer.

---

🎤 Interview Questions

1. Why would a tester validate data in the database?

Model answer: The UI can appear correct while the stored data is wrong or incomplete. Database checks confirm correct saving, relationships and calculations, and they are useful for investigating defects.

2. How would you find records with no matching parent?

Model answer: I would use a LEFT JOIN from the child table to the parent table and filter where the parent key is NULL. Those rows have no matching parent record.

3. How do you find duplicate values in a column?

Model answer: I group by the column and use HAVING with COUNT greater than one. Any returned value appears more than once.

4. How do you verify an AI-generated SQL query?

Model answer: I check the schema, read the logic and run it on small test data where I know the answer. I include rows that should and should not match, and I look carefully at joins and NULL handling.

5. How do you stay safe when using AI with databases?

Model answer: I share only the schema, use approved tools and run read-only queries through limited accounts on test environments. I would never run generated changes on shared data without review and approval.

---

📝 Assignment

Write five read-only validation checks for a small made-up shop database. For each one, create rows that prove it finds a problem and rows that prove it ignores healthy data. Try the QA Test Results Tracker project for more practice.

---

❓ FAQ

Do manual testers need SQL?

It is not always required, but it is a valuable skill. Even simple read-only queries can help you confirm results and investigate defects.

Can AI connect directly to my database?

Some tools can. Keep them away from production and use limited read-only access on approved test systems.

---

🚀 Next Steps

Well done on finishing this lesson. Complete the assignment, then continue with Performance Testing with AI: Write Scripts and Read Results.