Resource library

QA Interview

SQL Interview Questions for QA and Testers

SQL interview questions for QA and testers, with real queries for joins, aggregates, subqueries, second-highest salary, finding duplicates, and data validation.

2,555 words | Article schema | FAQ schema | Breadcrumb schema

Overview

SQL is the quiet skill that separates testers who take the UI at face value from those who can prove what the system actually stored. When a checkout says success, only a query against the orders table tells you whether the row, the amount, and the status are correct. That is why SQL rounds are standard in QA interviews, and why a tester fluent in joins and aggregates is worth more than one who tests through the screen alone.

This guide is for QA engineers and testers who use SQL to validate back-end data, set up test fixtures, and hunt for defects the UI hides. It walks from the fundamentals interviewers open with up to the joins, subqueries, and window functions that decide the round, with real queries you can read out loud. The focus is testing: every example is framed as something you would actually verify on the job.

You will get the classic puzzles (second-highest salary, finding duplicates, customers with no orders) with clear reasoning, plus the traps interviewers set, like the NULL behavior that quietly breaks a NOT IN. Learn the patterns, not just the answers, and you will handle whatever schema they put in front of you.

Why Testers Are Tested on SQL

Interviewers use SQL to find testers who verify the source of truth, not just the presentation. A UI can show the right number while the database stored the wrong one, and vice versa. A tester who queries the tables directly catches the class of defect that never appears on screen: a missing row, a wrong status, a total that does not reconcile. That instinct is what the SQL round is buying.

There is a practical angle too. Data setup and teardown, the plumbing of reliable automation, is SQL work. So is reproducing a customer bug from their exact data. Show that you use SQL both to assert results and to build clean, known test states, and you signal that your testing reaches all the way down to where the data lives, which is where the expensive bugs hide.

SQL lets you verify the data, not just what the UI renders.
It catches missing rows, wrong statuses, and totals that do not reconcile.
Test data setup and teardown is SQL work.
Querying the source of truth is where the expensive bugs surface.

SELECT, WHERE, and NULL Handling

Interviewers warm up with fundamentals to see if you speak SQL comfortably. Be ready to filter, sort, and limit: `SELECT id, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10;`. Know that `WHERE` filters rows before grouping, string literals use single quotes, and `LIKE 'a%'` does prefix matching. Small fluency here sets the tone for the whole round.

The trap they plant early is NULL. NULL means unknown, so it never equals anything, not even NULL. `WHERE bonus = NULL` returns nothing; you must write `WHERE bonus IS NULL`. Likewise `status != 'active'` silently excludes rows where status is NULL. Calling this out unprompted signals a tester instinct, because NULL handling is exactly the edge case that hides production bugs.

`WHERE` filters before grouping; string literals use single quotes.
NULL means unknown: use `IS NULL` or `IS NOT NULL`, never `= NULL`.
`col != 'x'` excludes NULL rows unless you add `OR col IS NULL`.
`ORDER BY ... DESC` and `LIMIT` control sorting and row count.

The JOIN Round

Joins are the heart of a QA SQL interview because real validation spans tables. Know the four cleanly: INNER JOIN returns only rows matching in both tables; LEFT JOIN returns all left rows plus matches (NULLs where none exist); RIGHT JOIN is the mirror; FULL OUTER JOIN returns everything from both sides. Say which you would use and why, because picking the wrong join produces confidently wrong test results.

The money question is finding rows with no match, for example customers who never ordered: `SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;`. Explain it: the LEFT JOIN keeps every customer, and `WHERE o.id IS NULL` keeps only those with no matching order. An INNER JOIN cannot answer this because it drops the unmatched customers entirely. This pattern (LEFT JOIN plus IS NULL) is the one interviewers most want to see you reach for.

INNER equals matches only; LEFT equals all left rows plus matches; FULL equals everything.
Find missing relationships with LEFT JOIN plus `WHERE right.id IS NULL`.
INNER JOIN cannot find unmatched rows; it drops them.
Always state the join type on purpose, not by habit.

Aggregates, GROUP BY, and HAVING

Aggregate questions test whether you can summarize data, which is most of back-end validation. Know `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, and that `GROUP BY` collapses rows into groups. To count orders per customer: `SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;`. Any non-aggregated column in the SELECT must appear in GROUP BY, a rule interviewers check.

The distinction they always probe is WHERE versus HAVING. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. To find customers with more than five orders: `SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 5;`. You cannot put that COUNT condition in WHERE because the count does not exist until rows are grouped. Nailing WHERE versus HAVING is a reliable way to show you understand the order of operations, not just the keywords.

`GROUP BY` collapses rows; non-aggregated SELECT columns must be grouped.
`WHERE` filters rows before grouping; `HAVING` filters groups after.
Aggregate with `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.
A condition on an aggregate belongs in HAVING, never WHERE.

Subqueries and the NOT IN Trap

Subqueries nest one query inside another and come up constantly. A subquery in WHERE filters against another result: `SELECT name FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = true);`. Know correlated subqueries too, where the inner query references the outer row and runs per row, useful but slower than an equivalent join.

The trap interviewers love is NOT IN with NULLs. If the subquery returns any NULL, `NOT IN` yields no rows at all, because a comparison against unknown is never true. `SELECT id FROM orders WHERE customer_id NOT IN (SELECT id FROM banned_customers)` silently returns nothing if `banned_customers.id` contains a NULL. The safe pattern is `NOT EXISTS`, which handles NULLs correctly. Flagging this marks you as someone who has been burned by it, which is exactly the point.

Subqueries nest a query in WHERE, FROM, or SELECT.
Correlated subqueries reference the outer row and run per row (slower).
`NOT IN` with a NULL in the list returns zero rows: a real bug source.
Prefer `NOT EXISTS` over `NOT IN` when NULLs are possible.

The Classic: Nth Highest Value

The second-highest-salary question is a rite of passage, so expect a variant. The modern, interviewer-preferred approach uses a window function: `SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) ranked WHERE rnk = 2;`. It generalizes cleanly to the Nth highest by changing the number.

Explain your choice: `DENSE_RANK` handles ties correctly (two people tied for top still make the next distinct salary rank 2), whereas `ROW_NUMBER` would not, and `LIMIT 1 OFFSET 1` breaks with duplicate salaries. Mention the classic subquery alternative, `WHERE salary < (SELECT MAX(salary) FROM employees)`, and note that generalizing to the Nth highest is why window functions win. Discussing ties unprompted is what separates a memorized answer from real understanding.

`DENSE_RANK() OVER (ORDER BY ... DESC)` generalizes to the Nth highest.
`DENSE_RANK` handles ties; `ROW_NUMBER` and `LIMIT/OFFSET` may not.
Window functions (`RANK`, `ROW_NUMBER`, `LEAD`, `LAG`) are worth knowing.
Always mention how your query handles duplicate values.

Finding Duplicates and Data-Quality Checks

Duplicate detection is pure QA work, so it is a common ask. To find duplicate emails, group by the column and keep groups larger than one: `SELECT email, COUNT(*) AS copies FROM users GROUP BY email HAVING COUNT(*) > 1;`. This is the same GROUP BY plus HAVING pattern, now aimed at data quality rather than reporting.

Extend it to full-row duplicates by grouping on every meaningful column, and mention that finding the actual duplicate row ids (to delete all but one) uses `ROW_NUMBER() OVER (PARTITION BY email ORDER BY id)` and deletes where the row number is greater than one. Framing duplicates as a data-integrity defect, and knowing how to both detect and de-duplicate, shows the testing mindset behind the SQL rather than rote query recall.

Detect duplicates with `GROUP BY col HAVING COUNT(*) > 1`.
Group on every meaningful column to find full-row duplicates.
Use `ROW_NUMBER() OVER (PARTITION BY ...)` to keep one and delete the rest.
Treat duplicates as a data-integrity defect, not just a query exercise.

Data Validation Queries Testers Actually Run

Interviewers may ask what you actually query to validate a feature. Give concrete examples: after a UI action, confirm the row exists with the right values (`SELECT status, amount FROM orders WHERE id = 1001;`), check referential integrity by hunting orphans (an order whose customer_id has no matching customer), and reconcile counts between a source and a destination table after a sync.

Add cross-field consistency checks, the ones bugs hide in: `SELECT * FROM orders WHERE total_amount != quantity * unit_price;` finds rows where a computed field drifted from its inputs. This is the tester edge; you are not just running queries, you are encoding business rules as SQL assertions the application should have upheld. That framing is what interviewers reward, because it shows you think in invariants.

Verify a UI action landed with the right values via a targeted SELECT.
Hunt orphan rows to check referential integrity.
Reconcile row counts between source and destination after a sync.
Encode business rules as queries: `WHERE total != quantity * unit_price`.

Setting Up and Cleaning Test Data

Testers write data as well as read it, so expect DML and DDL questions. Know `INSERT` to seed a fixture, `UPDATE ... WHERE` to set a known state, and `DELETE ... WHERE` to clean up, with the loud warning that an UPDATE or DELETE without a WHERE clause hits every row. Wrapping setup in a transaction (`BEGIN; ... ROLLBACK;`) lets you test against changes and then undo them, which interviewers like to hear.

Touch on DDL lightly: `CREATE TABLE`, `ALTER TABLE`, and the difference between `DELETE` (row by row, can be rolled back, keeps the table), `TRUNCATE` (fast, empties the table, minimal logging), and `DROP` (removes the table itself). Knowing that TRUNCATE and DROP are far harder to undo than DELETE is the practical distinction that keeps you from wiping a shared test database by accident.

`INSERT` seeds fixtures; `UPDATE ... WHERE` sets state; `DELETE ... WHERE` cleans up.
An UPDATE or DELETE with no WHERE clause changes every row: a classic disaster.
Wrap setup in a transaction and `ROLLBACK` to undo test changes.
DELETE (recoverable) versus TRUNCATE (empties table) versus DROP (removes table).

Scenario: Verifying an Order End to End

A favorite end-to-end prompt: a user places an order in the UI; how do you verify it in the database? Walk the data trail. First confirm the order row: `SELECT id, status, total_amount FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 1;`. Then confirm the line items match the cart by joining `order_items` to the order, and that the totals reconcile with quantity times price.

Finish by checking side effects the UI does not show: inventory decremented, a payment row created with a matching amount and status, and no orphaned rows if the transaction half-failed. Narrating this trail (order, then items, then totals, then inventory, then payment) shows you test the system, not the screen, which is exactly the instinct a SQL round is designed to surface.

Confirm the order row exists with correct status and amount.
Join `order_items` and reconcile totals against quantity times price.
Check side effects: inventory decrement and a matching payment row.
Look for orphans left by a half-committed transaction.

SQL Traps and Behavioral Notes

A few traps are worth pre-loading. NULL in aggregates: `COUNT(column)` ignores NULLs while `COUNT(*)` counts all rows, which changes your numbers. Integer division in some databases makes `1/2` return 0. And a JOIN condition placed in the WHERE clause versus the ON clause behaves differently for outer joins. Mentioning even one of these unprompted signals depth beyond happy-path SQL.

On the behavioral side, you may be asked how you validate data without breaking production. The safe answer: read-only access on production, run destructive queries only against test databases, always preview a DELETE or UPDATE with a SELECT of the same WHERE clause first, and use transactions. Demonstrating that you treat data as a shared, fragile resource is as important as writing the clever query.

`COUNT(col)` skips NULLs; `COUNT(*)` counts every row.
Watch integer division and ON-versus-WHERE differences in outer joins.
Preview a DELETE or UPDATE by running its WHERE clause as a SELECT first.
Use read-only access on production; run destructive queries only on test data.

Frequently Asked Questions

Do QA testers need to know SQL for interviews?

Yes. Back-end and API-heavy roles expect you to validate data directly, set up test fixtures, and find defects the UI hides. You do not need to be a database administrator, but you should be comfortable with joins, aggregates, subqueries, and the common NULL traps.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. A condition on an aggregate like `COUNT(*) > 5` must go in HAVING because the count does not exist until rows are grouped. You can use both in the same query.

How do you find the second highest salary in SQL?

Use a window function: rank salaries with `DENSE_RANK() OVER (ORDER BY salary DESC)` and select where the rank equals 2. `DENSE_RANK` handles ties correctly, whereas `LIMIT 1 OFFSET 1` breaks when the top salary is duplicated across employees.

How do you find duplicate records in a table?

Group by the columns that define a duplicate and keep groups with more than one row: `GROUP BY email HAVING COUNT(*) > 1`. To delete all but one copy, use `ROW_NUMBER() OVER (PARTITION BY email ORDER BY id)` and delete rows where the number is greater than one.

Why does NOT IN sometimes return no rows?

If the subquery inside NOT IN returns even one NULL, the whole expression evaluates to unknown and returns zero rows, because comparing against unknown is never true. Use `NOT EXISTS` instead, which handles NULLs correctly and is usually the safer choice.

What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes rows one at a time with a WHERE clause and can be rolled back. TRUNCATE quickly empties an entire table with minimal logging. DROP removes the table structure itself. TRUNCATE and DROP are much harder to undo, so use them carefully on shared databases.