SQL for QA engineers: queries, database checks and interview tasks

1. Read the schema before writing SQL

A table name does not fully describe the data. Ask which column is the primary key, which foreign keys connect records, whether a column may be NULL, which values are allowed and whether uniqueness is enforced by the database. Without that information, a query can look plausible and still answer the wrong question.

For this guide, customers.id is the customer key; orders.customer_id and activities.customer_id refer to it; payments.order_id refers to orders.id. One customer may have many orders and activities, while one order may have several payment attempts. That cardinality explains why a JOIN may multiply rows.

• Write the relationship in words before joining tables.
• Distinguish a missing row from a row whose field is NULL.
• Confirm whether timestamps are UTC and whether deleted records remain as soft-deleted rows.

2. SELECT and WHERE: verify one operation precisely

After POST /orders returns 201, do not search by a vague status alone. Capture the returned order id and query that exact record. Compare customer_id, status, total and creation time with the request and contract. Selecting only needed columns makes the evidence easier to read and reduces accidental exposure of unrelated data.

Text, numbers and timestamps need explicit conditions. A time window helps when an id is unavailable, but it is weaker because another test may create a similar row. ORDER BY does not filter records; LIMIT without a deterministic order can return a different row on another run.

SELECT id, customer_id, status, total, created_at
FROM orders
WHERE id = 1842 AND customer_id = 77;

3. JOIN: compare related business objects

INNER JOIN returns only records with a match on both sides. LEFT JOIN keeps every row from the left table and fills the missing right side with NULL. That difference is central to negative checks such as customers without orders or orders without payments.

Place a filter carefully. A condition on the right table in WHERE can remove NULL rows and effectively turn a LEFT JOIN into an INNER JOIN. If the question is “customers without paid orders”, put the paid-status condition in ON and then check that the joined order id is NULL.

SELECT c.id, c.email
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'paid'
WHERE o.id IS NULL
ORDER BY c.id;

4. NULL, empty strings and missing data are not the same

SQL NULL means an unknown or absent value. It is not equal to an empty string and it cannot be tested with = NULL; use IS NULL or IS NOT NULL. In a product, NULL, empty and whitespace-only values can come from different validation paths and should not be merged without a requirement.

Three-valued logic matters: a comparison with NULL is unknown, so a WHERE condition does not pass. COALESCE can make reports convenient, but using it too early may hide a defect by converting unexpected NULL into a harmless-looking default.

SELECT id, email,
  CASE
    WHEN email IS NULL THEN 'null'
    WHEN TRIM(email) = '' THEN 'blank'
    ELSE 'present'
  END AS email_state
FROM customers;

5. COUNT, GROUP BY and HAVING: find duplicates and broken totals

Aggregation turns rows into evidence about quantities. GROUP BY defines the group; COUNT, SUM, MIN and MAX calculate within it; HAVING filters groups after aggregation. WHERE filters source rows before groups are built. Mixing these stages is a common interview mistake.

COUNT(*) counts rows, while COUNT(column) ignores NULL in that column. When several one-to-many tables are joined at once, rows can multiply and inflate totals. Aggregate each child table first or use a carefully justified DISTINCT instead of treating DISTINCT as a universal repair.

SELECT LOWER(TRIM(email)) AS normalized_email, COUNT(*) AS customer_count
FROM customers
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

SELECT external_id, COUNT(*) AS attempts, SUM(amount) AS charged
FROM payments
WHERE status = 'succeeded'
GROUP BY external_id
HAVING COUNT(*) > 1;

6. Verify integrity, not just the API response

A successful response does not prove that related data is consistent. An order may be stored without its lines, a payment may point to a missing order, or two active customers may share an email that should be unique. Foreign keys, UNIQUE and CHECK constraints prevent classes of defects, but QA still verifies that the application handles their failures correctly.

An orphan search is a useful diagnostic, not permission to read arbitrary production data. Work with synthetic records, minimize selected columns and attach identifiers rather than full personal data to a defect report.

SELECT p.id, p.order_id
FROM payments p
LEFT JOIN orders o ON o.id = p.order_id
WHERE o.id IS NULL;

SELECT id, order_id, amount
FROM payments
WHERE status = 'succeeded' AND amount <= 0;

7. Transactions, retries and concurrency

A transaction groups changes so they commit together or roll back together. Test the failure point between related writes: if payment succeeds but updating the order fails, what state remains and how is reconciliation performed? Do not claim atomicity from one successful run.

Concurrent requests expose defects that sequential checks miss. Two conversions of one lead, two uses of one coupon or a retry after timeout can both pass an application-level “does it exist?” check before either commits. A unique constraint, idempotency key or locking strategy must preserve the business invariant.

SELECT order_id, external_id, status, amount, created_at
FROM payments
WHERE order_id = 1842
ORDER BY created_at, id;

8. How to solve a SQL task in an interview

Do not start typing before defining the expected result. Repeat the task in business language, name the preserved table, sketch the relationships, predict one or two rows and only then write SQL. Afterward, test the query mentally on zero, one and many related rows.

A mid-level answer also names limitations: duplicated source data, timezone boundaries, soft deletion, isolation and whether direct database access is acceptable. It is fine to ask for the schema; inventing columns and silently assuming cardinality is weaker than one precise clarification.

• Explain why every JOIN condition is present.
• Say whether duplicate output rows are possible.
• Check empty input and multiple child rows.
• Separate a diagnostic query from a cleanup or migration script.
• Never run UPDATE or DELETE merely to demonstrate confidence.