Data checks a tester writes

Much of a tester's SQL work is hunting for data that is wrong. A small set of query shapes finds most problems: duplicates, orphans (rows that point to something missing), missing values and numbers that do not add up. Interviewers for SDET roles (test engineers who write code) ask "how do you test the database?" and "write a query to find duplicates", and they want to see these shapes. This article uses the shop sample database, which holds a few planted mistakes, as you might find them after a buggy release.

Duplicates

Group by the columns that should be unique and keep the groups with more than one row:

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

email | customers
ravi@mail.in | 2

Duplicates across several columns use the same idea. A retry bug (the application sends the same request again, for example after a timeout) may create the same order twice: same customer, total, status and date, but a new id. To list every copy rather than only the repeated values, compare each row with the others:

SELECT o.id, o.customer_id, o.total, o.created_at
FROM orders o
WHERE EXISTS (SELECT 1 FROM orders x
              WHERE x.id <> o.id AND x.customer_id = o.customer_id AND x.total = o.total
                AND x.status = o.status AND x.created_at = o.created_at);

id | customer_id | total | created_at
1002 | 2 | 800 | 2026-03-02
1009 | 2 | 800 | 2026-03-02

Orphans

An orphan is a child row whose parent is missing. The child row holds a foreign key (a column with the id of another row), and the parent is the row it points to. Examples are an order for a customer who does not exist, or an item for an order that does not exist. Without enforced foreign keys they slip in easily. A left join with an IS NULL test finds them:

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

id | customer_id
1004 | 9

NOT EXISTS does the same job and reads like the question, "items where no order exists":

SELECT i.id, i.order_id FROM order_items i
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.id = i.order_id);

id | order_id
12 | 1012

Missing values in required columns

The product says every customer has an email. Check for NULL and for empty text, because an empty string is not NULL:

SELECT id, name, email FROM customers WHERE email IS NULL OR email = '';

id | name | email
4 | John Dsouza | NULL
8 | Nisha Paul | ''

Numbers that must agree

A reconciliation check compares two numbers that should agree, such as a stored number and the parts it is made of. An order's total should equal the value of its items. A left join keeps orders with no items, and COALESCE turns their missing sum into 0:

SELECT o.id, o.total, COALESCE(SUM(i.qty * i.price), 0) AS items_value
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.total
HAVING o.total <> COALESCE(SUM(i.qty * i.price), 0);

id | total | items_value
1003 | 2200 | 2000
1008 | 999 | 0

Payments get the same treatment. No order should have two successful payments (a double charge), and every PAID order needs one:

SELECT o.id, o.status,
       (SELECT COUNT(*) FROM payments p WHERE p.order_id = o.id AND p.status = 'SUCCESS') AS ok_payments
FROM orders o
WHERE o.status = 'PAID'
  AND (SELECT COUNT(*) FROM payments p WHERE p.order_id = o.id AND p.status = 'SUCCESS') <> 1;

id | status | ok_payments
1006 | PAID | 2
1007 | PAID | 0

Checking data after an API call

An API test (a test that sends requests to the application's back end) should not stop at the response. If the request POST /orders, which creates an order, says it created order 1006 with three lines worth 1250, the database should agree. One query returns everything to assert:

SELECT o.customer_id, o.total, o.status, COUNT(i.id) AS lines, SUM(i.qty * i.price) AS items_value
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.id = 1006
GROUP BY o.id, o.customer_id, o.total, o.status;

customer_id | total | status | lines | items_value
2 | 1250 | PAID | 3 | 1250

Also check what should not have changed. Count rows before and after the call: exactly one new order, three new items, and no other table touched. A test that only looks at the new row misses an API that also duplicated or deleted something.

Running checks from test code

In a framework, a check is a query plus assertions. Pass values as parameters (the value is sent separately and takes the place of a ? in the SQL), never by joining strings, which can let input change the meaning of the query. Close the connection when you finish. In Python, with the standard sqlite3 module (drivers for other databases work the same way, though some, such as psycopg for PostgreSQL, write the placeholder as %s):

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER, status TEXT);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER, qty INTEGER, price INTEGER);
INSERT INTO orders VALUES (1006, 2, 1250, 'PAID');
INSERT INTO order_items VALUES (7, 1006, 1, 1000), (8, 1006, 1, 150), (9, 1006, 1, 100);
""")

order_id = 1006  # the id the API returned
row = con.execute(
    "SELECT o.total, o.status, COUNT(i.id), SUM(i.qty * i.price) "
    "FROM orders o JOIN order_items i ON i.order_id = o.id "
    "WHERE o.id = ? GROUP BY o.id",
    (order_id,),
).fetchone()
total, status, lines, items_value = row
assert status == "PAID"
assert lines == 3
assert total == items_value, f"total {total} but items add up to {items_value}"
print("order", order_id, "ok:", total, status, lines)
con.close()

order 1006 ok: 1250 PAID 3

In Java the same check uses JDBC (the standard Java way to talk to a database) with a PreparedStatement, which is a query with ? placeholders. This fragment uses JUnit 5 assertions and needs your database's JDBC driver (a library for your database) on the classpath, the list of libraries your program can use:

String sql = "SELECT total, status FROM orders WHERE id = ?";
try (Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
     PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setLong(1, orderId);
    try (ResultSet rs = ps.executeQuery()) {
        assertTrue(rs.next(), "order not found");
        assertEquals(1250, rs.getInt("total"));
        assertEquals("PAID", rs.getString("status"));
    }
}

Keep connection details in configuration or environment variables. Run the broad checks (duplicates, orphans, totals) as a scheduled job against the test environment, and the targeted ones inside the tests that create the data.

How to answer in an interview

When asked "how do you test the database?", list the checks by risk: correct rows after each action, then duplicates, orphans, missing required values and totals that must agree. Write one of them on the spot; the duplicate query with GROUP BY and HAVING is the usual request.

Common mistakes

• Checking only for NULL when empty text also means "missing".
• Using an inner join in a reconciliation check, which hides orders that have no items.
• Building SQL by joining strings instead of passing parameters.

Recap

Duplicates: GROUP BY with HAVING COUNT(*) > 1. Orphans: LEFT JOIN with IS NULL, or NOT EXISTS. Missing values: IS NULL or empty text. Totals: compare the stored number with the sum of its parts.