82 SQL interview questions for testers

What they are assessing

Whether the candidate sees the relevance.

Model answer

To verify what the application actually did rather than what the screen says it did. A transfer that shows a correct balance can have written the wrong ledger entry, and only a query reveals that. Beyond verification, SQL is how a tester sets up data states that would take hours to create through the interface, investigates a defect to find whether the data or the code is at fault, and reconciles two systems. It is probably the highest return technical skill a manual tester can acquire.

What they are assessing

Standard categorisation.

Model answer

DDL defines structure: CREATE, ALTER, DROP, TRUNCATE. DML manipulates data: SELECT, INSERT, UPDATE, DELETE, though some classifications treat SELECT separately as DQL. DCL controls access: GRANT and REVOKE. TCL controls transactions: COMMIT, ROLLBACK and SAVEPOINT. The practical relevance for a tester is that DDL statements usually commit implicitly in most databases, so a TRUNCATE cannot be rolled back the way a DELETE can.

Likely follow-up

Which of those would you expect to have permission for in a test environment?

What they are assessing

A question that explains most SQL confusion.

Model answer

FROM and its joins first, then WHERE, then GROUP BY, then HAVING, then SELECT including any aliases and window functions, then DISTINCT, then ORDER BY, then LIMIT. It is not the order you write it in, and knowing it explains several things that otherwise look arbitrary: why a column alias defined in SELECT cannot be used in WHERE, why aggregate functions cannot appear in WHERE, and why ORDER BY can use an alias when WHERE cannot.

What they are assessing

Basic schema vocabulary.

Model answer

A primary key uniquely identifies a row, cannot be null, and there is one per table. A unique key also enforces uniqueness but permits a null, and a table can have several. A foreign key references a key in another table and enforces referential integrity, so a child row cannot reference a parent that does not exist. For a tester these matter because they define what the database will prevent, and a missing foreign key is why orphan records appear.

What they are assessing

A distinction with practical consequences.

Model answer

DELETE removes rows, can take a WHERE clause, logs each row, fires triggers, and can be rolled back within a transaction. TRUNCATE removes all rows, is much faster because it deallocates pages rather than logging row deletions, does not fire row triggers, usually resets identity counters, and in most databases cannot be rolled back because it is DDL. DROP removes the table itself. The one that catches people out is TRUNCATE being unrecoverable, which matters a great deal when it was run against the wrong environment.

Trap to avoid

Assuming TRUNCATE can be rolled back like DELETE. In Oracle and several others it is DDL and commits implicitly.

What they are assessing

A very common question with a precise answer.

Model answer

WHERE filters rows before grouping, so it cannot reference aggregate functions. HAVING filters groups after aggregation, so it can. The practical rule is that any condition on an individual row belongs in WHERE and any condition on an aggregate belongs in HAVING. Putting a row condition in HAVING usually still works and is slower, because rows that could have been eliminated early are carried through the grouping.

Likely follow-up

Why can WHERE not reference an aggregate?

What they are assessing

A keyword that hides problems.

Model answer

It removes duplicate rows from the result, considering all selected columns together. The caution is that adding DISTINCT to fix unexpected duplicates usually masks a join problem rather than solving it: duplicates almost always mean the join multiplied rows, and suppressing them hides that the data or the query is wrong. So when DISTINCT appears to be necessary, the first question is why the duplicates exist, and only then whether removing them is correct.

Trap to avoid

Reaching for DISTINCT when a join produces unexpected rows. It makes the symptom disappear and leaves the cause, which then surfaces in aggregates that are quietly wrong.

What they are assessing

Pattern matching with a performance implication.

Model answer

LIKE matches a pattern, with percent for any sequence and underscore for a single character. A pattern anchored at the start can use an index on that column; a pattern beginning with a wildcard cannot, so it forces a full scan. On a large table that is the difference between instant and minutes. For case insensitive matching, the behaviour depends on the collation, so LIKE may or may not be case sensitive depending on the database and configuration, which is worth confirming rather than assuming.

What they are assessing

A boundary question with a classic defect.

Model answer

BETWEEN is inclusive of both endpoints. The pitfall with dates is that a column holding a date and time compared BETWEEN two dates excludes everything after midnight on the final day, because the upper bound is treated as midnight. So a query intended to cover a month silently omits most of the last day. The safe form is a greater-than-or-equal lower bound and a strictly-less-than upper bound set to the start of the next day, which works regardless of the time component and of precision.

Likely follow-up

Why is less-than the next day safer than less-than-or-equal the end of the day?

What they are assessing

Conditional logic in a query.

Model answer

A conditional expression returning a value based on conditions, usable almost anywhere an expression is allowed: in SELECT to derive a column, in WHERE as part of a condition, in ORDER BY for custom sorting, and inside an aggregate, which is how you produce conditional counts such as counting only rows meeting a condition within a single grouped query. That last use is particularly handy for testers building a summary of several categories in one result rather than running several queries.

What they are assessing

Core vocabulary.

Model answer

INNER returns rows matching in both tables. LEFT OUTER returns all rows from the left table with nulls where no match exists on the right, and RIGHT is the mirror. FULL OUTER returns all rows from both sides with nulls on either where unmatched. CROSS returns the Cartesian product of both tables with no join condition. A self join is a table joined to itself, which is how hierarchies and comparisons between rows of the same table are done.

What they are assessing

The anti-join pattern, which is the most useful join technique for testers.

Model answer

A LEFT JOIN from the first table to the second, with a WHERE clause requiring a column from the right side to be null, which keeps only the unmatched rows. The alternatives are NOT EXISTS with a correlated subquery, which is usually as fast and is safer than NOT IN, or EXCEPT. It is the query behind most useful tester checks: orphan records, source rows missing from a target, customers with no orders, and records that failed to process.

Likely follow-up

Which column should you test for null in the WHERE clause?

What they are assessing

Diagnosing a very common problem.

Model answer

Because the join is one to many or many to many on the join columns, so each left row matches several right rows and is duplicated accordingly. It usually means the join key is not as unique as assumed: joining on a customer identifier where the right table holds one row per order will multiply customers by their order count. The fix is either to aggregate the right side before joining, to add the missing condition that makes the match unique, or to accept the multiplication because it is actually what you wanted.

Trap to avoid

Adding DISTINCT to reduce the count back. Any aggregate computed over the multiplied rows is now wrong and nothing says so.

What they are assessing

A subtlety that distinguishes genuine fluency.

Model answer

For an INNER JOIN they are equivalent. For a LEFT JOIN they are not. A condition in ON filters which right-side rows are eligible to match, so unmatched left rows still appear with nulls. The same condition in WHERE is applied after the join, and because the unmatched rows have null in that column the condition excludes them, which silently converts the LEFT JOIN into an INNER JOIN. It is one of the most common causes of a query that appears to lose rows for no reason.

Likely follow-up

How would you tell from the results that this had happened?

What they are assessing

A technique with specific uses.

Model answer

Joining a table to itself with aliases, used when rows need comparing against other rows in the same table. Typical cases are hierarchies, such as an employee table where each row holds a manager identifier pointing at another row, and comparisons such as finding records that duplicate another on some column, or finding a row and its immediate predecessor. Window functions have replaced self joins for many of the sequential cases and are usually clearer when available.

What they are assessing

The reconciliation query, which is core tester work.

Model answer

With EXCEPT, or MINUS in Oracle, run in both directions, because one direction only finds rows present in the first and missing or different in the second. Running source minus target and then target minus source gives both missing and extra rows, and because the comparison is on all selected columns it also catches rows present in both with different values. A count comparison alone is not sufficient, since a pipeline that loses one row and duplicates another produces identical counts.

Trap to avoid

Comparing only row counts. Equal counts are entirely consistent with a loss and a duplication cancelling out, which is a real and common pipeline defect.

What they are assessing

A frequently asked question about null behaviour.

Model answer

COUNT with an asterisk counts rows regardless of content. COUNT of a column counts rows where that column is not null, so it can be smaller. COUNT DISTINCT counts distinct non-null values in that column. The difference between the first two is a quick and useful test in itself: comparing them tells you how many nulls a column holds without writing a second query.

Likely follow-up

How would you count rows where a column is null?

What they are assessing

A rule people learn by hitting the error.

Model answer

Every selected column must either appear in the GROUP BY clause or be inside an aggregate function, because any other column has no single value for the group. Most databases enforce this strictly; MySQL historically permitted it and returned an arbitrary value from the group, which produced quietly wrong results, and modern versions enforce it by default. If you need an additional column, either group by it as well, aggregate it with MIN or MAX, or use a window function instead.

What they are assessing

A subtlety with real consequences for verification.

Model answer

They ignore them. SUM, AVG, MIN and MAX skip null values rather than treating them as zero, which matters most for AVG: the average of a column with nulls is computed over the non-null rows only, so it is not the same as treating the nulls as zero. If nulls should count as zero, they must be converted explicitly with COALESCE. An aggregate over a group where every value is null returns null rather than zero, except COUNT which returns zero.

Trap to avoid

Assuming AVG treats null as zero. The two give different answers and the difference is invisible unless you know to look.

What they are assessing

A standard practical query.

Model answer

GROUP BY the columns that should together be unique, with HAVING COUNT greater than one, which gives the duplicated values and how many times each occurs. To see the full duplicate rows rather than just the keys, join that result back to the table, or use ROW_NUMBER partitioned by those columns and filter for a row number greater than one, which is usually cleaner and also gives you the rows to delete if that is the aim.

Likely follow-up

How would you delete the duplicates but keep one of each?

What they are assessing

A case the obvious query gets wrong.

Model answer

By starting from the table that holds all the categories and LEFT JOINing the data to it, then aggregating. Grouping the data table alone can only produce categories that have at least one row, so an empty category disappears entirely, which is frequently the thing you most wanted to know about. The second detail is to count a column from the joined side rather than using COUNT with an asterisk, because the latter counts the single null-filled row and returns one instead of zero.

What they are assessing

A distinction in subquery types.

Model answer

A subquery that references a column from the outer query, so it is conceptually evaluated once per outer row rather than once overall. A non-correlated subquery can be run independently and produces the same result every time. Correlated subqueries are what EXISTS and NOT EXISTS use, and they are the natural way to express conditions such as customers who have at least one order over a given value. Modern optimisers often rewrite them as joins, so the performance difference is smaller than it once was.

What they are assessing

The single most famous SQL trap.

Model answer

It returns no rows at all. NOT IN is evaluated as a series of not-equal comparisons, and comparing anything to null yields unknown rather than false, so the overall condition is never true and every row is excluded. The query runs without error and returns an empty result, which is easily mistaken for there being nothing to find. The fixes are NOT EXISTS, which handles nulls correctly, an anti-join with a null check, or filtering nulls out of the subquery explicitly.

Trap to avoid

Reading an empty NOT IN result as a passing check. It is the classic way a test reports no missing records when in fact it tested nothing.

What they are assessing

Two ways to express the same intent.

Model answer

IN compares a value against a set of values returned by the subquery. EXISTS tests whether the subquery returns any row at all, and does not care what it returns. They usually give the same result for the positive case, and differ in the negative: NOT EXISTS behaves correctly with nulls while NOT IN does not. Historically EXISTS was faster on large subqueries and IN on small ones; modern optimisers largely equalise this, so correctness with nulls is the better reason to prefer EXISTS.

What they are assessing

A readability feature with real value.

Model answer

A named temporary result set defined with WITH at the start of a query and referenced within it. The main value is readability: a query with three nested subqueries becomes a sequence of named steps that can be read top to bottom, which matters enormously for the kind of multi-step reconciliation queries testers write. They can also be recursive, which is how hierarchies of arbitrary depth are traversed. Whether they are materialised or inlined depends on the database, so they are not automatically a performance improvement.

Likely follow-up

When would you use a recursive CTE?

What they are assessing

A distinction with a performance consequence.

Model answer

UNION combines the results of two queries and removes duplicates, which requires a sort or hash and therefore costs time. UNION ALL concatenates them keeping duplicates, which is faster. The practical guidance is to use UNION ALL unless duplicates genuinely need removing, and for a tester there is an additional reason: if you expect no duplicates, using UNION ALL and then checking the count tells you whether that assumption held, which UNION would silently hide.

What they are assessing

Set operations in a testing context.

Model answer

INTERSECT returns rows present in both results, EXCEPT returns rows in the first and not the second, called MINUS in Oracle. For testers EXCEPT is the more useful by far, because running it in both directions is the standard way to compare a source against a target and find everything that differs. Both compare on all selected columns and both remove duplicates by default, which is worth remembering when the duplication itself is what you are looking for.

What they are assessing

Practical constraints.

Model answer

The two queries must return the same number of columns, in the same order, with compatible data types. The column names come from the first query. ORDER BY applies to the combined result and goes at the end rather than on either side. The requirement people trip over is the column order, since a set operation between two queries selecting the same columns in different orders will either fail on type mismatch or, worse, succeed and compare the wrong columns against each other.

Trap to avoid

Assuming columns are matched by name. They are matched by position, so two queries with the same columns in different orders compare the wrong things.

What they are assessing

A feature that distinguishes stronger candidates.

Model answer

A function computed over a set of rows related to the current row, defined by an OVER clause, which returns a value for every row rather than collapsing them into one. So where GROUP BY with SUM gives one row per group, SUM as a window function gives every row with its group total alongside. That is what makes it useful for comparisons: each row can be seen next to its group aggregate, its rank within the group, or the value from the previous row.

Likely follow-up

How would you show each row alongside the total for its group?

What they are assessing

A precise distinction.

Model answer

ROW_NUMBER assigns a unique sequential number with no ties, so two equal values get different numbers chosen arbitrarily unless the ordering is fully deterministic. RANK leaves gaps after ties, so two rows tied at position one are followed by position three. DENSE_RANK does not leave gaps, so the next value is two. Which one is correct depends entirely on what the business means by rank, and the question is a good one because candidates frequently know the names without knowing the tie behaviour.

What they are assessing

A classic interview exercise with several valid answers.

Model answer

DENSE_RANK over the column ordered descending, filtering for rank two, which handles ties the way most people intend. Alternatives are a subquery selecting the maximum value below the overall maximum, or an offset in the ordering, which varies by database. The part worth stating is the tie handling: if two rows share the highest value, whether the second highest means the second distinct value or the second row is an ambiguity in the question, and saying so is a better answer than picking one silently.

Likely follow-up

What changes if the top two values are equal?

What they are assessing

Row-to-row comparison.

Model answer

LAG returns a value from a previous row within the window and LEAD from a following one, both ordered by whatever you specify. They are how you compare a row to its predecessor without a self join: detecting a change in a status over time, computing the gap between consecutive timestamps, or finding where a sequence of identifiers skips a value. For a tester verifying a slowly changing dimension or an audit trail, LAG is the most direct way to check that each row correctly follows the one before it.

What they are assessing

A fundamental that catches beginners.

Model answer

With IS NULL or IS NOT NULL, never with an equals comparison. Null means unknown rather than a value, so comparing anything to null yields unknown rather than true or false, and a WHERE clause only keeps rows where the condition is true. A query filtering on a column equal to null therefore returns nothing and gives no error, which is the kind of silent failure that makes a verification query appear to pass.

What they are assessing

The concept underlying several SQL traps.

Model answer

SQL conditions evaluate to true, false or unknown, rather than only true or false, because any comparison involving null produces unknown. The consequences are that a WHERE clause keeps only true rows so unknown behaves like false, that NOT unknown is still unknown rather than true, and that a condition and its negation can both exclude the same row. That last point explains why a query for a column equal to a value and a query for it not equal to that value together do not return every row.

Likely follow-up

Why do those two queries together not cover the whole table?

What they are assessing

Null replacement.

Model answer

Returns the first non-null value from its arguments, so it is used to substitute a default where a column may be null. It is standard SQL and therefore portable, unlike the database-specific equivalents such as NVL in Oracle and ISNULL in SQL Server, which take only two arguments. For a tester it is most useful when comparing two data sets where nulls on both sides should be treated as equal, since a direct comparison of two nulls yields unknown rather than true.

What they are assessing

A real problem in reconciliation work.

Model answer

Either by coalescing both sides to a sentinel value that cannot occur in the data and comparing those, or by writing the condition explicitly as both being null or both being equal. Some databases offer a null-safe equality operator which does exactly this. It matters because comparing a source and target row by row with ordinary equality will report every row where both sides are legitimately null as a mismatch, which produces a reconciliation full of false positives.

What they are assessing

A source of both wrong results and poor performance.

Model answer

The database converting one type to another automatically to allow a comparison, such as comparing a character column to a number. It is a problem for two reasons. Correctness: the conversion rules may not be what you expect, and a comparison of a string column to a number can match rows you did not intend or fail on rows that cannot convert. Performance: converting the column rather than the literal prevents an index on that column being used, which turns a fast lookup into a full scan.

Likely follow-up

Which side of the comparison does the database usually convert?

What they are assessing

A frequent source of defects.

Model answer

That a date column may carry a time component, so equality against a date literal matches only rows at exactly midnight. That applying a function to truncate the time on the column prevents index use, so a range comparison is better. That time zones matter if the column stores one, and that comparing values stored in different zones without conversion gives wrong answers. And that the literal format is database-specific, so a query written for one database frequently fails or, worse, misinterprets the date on another.

What they are assessing

Numeric precision, which matters in financial testing.

Model answer

If the column is a floating point type rather than an exact decimal, values cannot be represented precisely, so an equality comparison against a literal can fail even when the value looks identical, and sums accumulate small errors. Financial data should use an exact numeric type for this reason, and finding a float column holding money is itself a defect worth raising. When comparing calculated values across systems, a tolerance comparison is safer than equality unless both sides are exact types.

What they are assessing

A common setup operation.

Model answer

The syntax differs by database, which is one of the less portable areas: SQL Server uses UPDATE with a FROM clause joining the other table, Oracle uses a correlated subquery in the SET clause with a matching condition in WHERE, and PostgreSQL uses UPDATE with a FROM clause of its own form. The detail that matters in all of them is that if the joined table returns more than one matching row, the behaviour is either an error or an arbitrary choice depending on the database, so the join must be unique.

What they are assessing

Upsert behaviour.

Model answer

A statement that inserts, updates or deletes rows in a target based on a match against a source, commonly called upsert. It is used heavily in data loading, so for a tester it is more often something to test than something to write. The behaviours worth testing are the matched and unmatched paths, what happens when the source contains duplicate keys, which most databases treat as an error, and whether the delete clause is present, since a MERGE that deletes unmatched target rows is far more destructive than one that does not.

What they are assessing

Professional practice that prevents incidents.

Model answer

Write the SELECT first with the same WHERE clause and check the rows and the count returned, then convert it to the modification. Run inside an explicit transaction so it can be rolled back if the affected count is wrong. Check the row count reported against what you expected before committing. And confirm which environment the connection points at before running anything, since the most expensive version of this mistake is a correct statement run against the wrong database. On a large change, taking a copy of the affected rows first is cheap insurance.

Trap to avoid

Writing the UPDATE first and the WHERE clause second. An UPDATE executed before the WHERE is typed updates every row, and in a shared environment that is a serious incident.

What they are assessing

Standard theory with practical relevance.

Model answer

Atomicity, meaning a transaction completes entirely or not at all. Consistency, meaning it moves the database from one valid state to another, respecting constraints. Isolation, meaning concurrent transactions do not interfere in ways that produce incorrect results. Durability, meaning committed changes survive a failure. For a tester the one most often violated in practice is atomicity across multiple systems, where a database transaction commits and a downstream call fails, leaving state that is internally consistent and globally wrong.

What they are assessing

Concurrency phenomena.

Model answer

A dirty read sees changes from another transaction that has not committed and may roll back. A non-repeatable read occurs when the same row is read twice in one transaction and has changed between reads. A phantom read occurs when the same query is run twice and returns a different set of rows because another transaction inserted or deleted matching rows. The isolation levels exist to prevent progressively more of these: read uncommitted allows all three, read committed prevents dirty reads, repeatable read prevents the first two, and serializable prevents all three.

Likely follow-up

Which isolation level do most databases use by default?

What they are assessing

Connecting theory to testing work.

Model answer

Because concurrency defects depend on them. An application that reads a balance, decides, and writes it back is correct under serializable and can lose updates under read committed, and testing serially will never reveal it. Isolation level also explains why a query in one session does not see data another session has inserted but not committed, which is a common confusion when verifying during a test. And deadlocks, which are a real production failure mode, are a product of locking behaviour that the isolation level governs.

What they are assessing

Partial rollback.

Model answer

A marker within a transaction that allows a rollback to that point rather than to the beginning, so part of the work is preserved. For testing it is occasionally useful when setting up a complex data state in stages, since a mistake in a later step can be undone without rebuilding everything. It is more commonly encountered in stored procedures handling errors partway through a batch, where the procedure rolls back to a savepoint and continues rather than abandoning everything.

What they are assessing

Basic performance understanding.

Model answer

A separate structure, usually a B-tree, that lets the database find rows matching a value without scanning the whole table, in the same way a book index avoids reading every page. It speeds up reads on the indexed columns and slows down writes, because every insert, update and delete must maintain it, and it consumes storage. For a tester the relevance is that a query that is slow in a volume test and fast on a small dataset usually indicates a missing index, which is a legitimate finding to raise.

What they are assessing

Practical diagnosis.

Model answer

When a function or a calculation is applied to the indexed column in the WHERE clause, since the index holds the raw values. When implicit conversion forces the column to be converted. When a LIKE pattern begins with a wildcard. When the query would return a large proportion of the table, where a scan is genuinely cheaper and the optimiser chooses it deliberately. When statistics are stale so the optimiser misjudges selectivity. And when the leading column of a composite index is not in the predicate.

Likely follow-up

How would you confirm which of those is happening?

What they are assessing

Reading a plan, which is a genuine skill boundary.

Model answer

The strategy the optimiser chose for a query, obtained with EXPLAIN or the database equivalent. The things to look for are full table scans on large tables where an index lookup was expected, the join algorithms chosen and whether a nested loop is being used over a large set, the estimated row counts compared against actual where available, since a large divergence means statistics are wrong and every decision downstream is suspect, and any sort or hash operation spilling to disk. A tester does not need to tune queries, but recognising a scan on a million-row table is worth having.

What they are assessing

A frequent and relevant question.

Model answer

Data volume, most often: a query with no suitable index is instant on ten thousand rows and unusable on ten million. Different statistics producing a different plan. Concurrency and locking, which a single-user test environment does not reproduce. Different hardware and memory allocation. And data distribution, since a test environment with evenly spread values behaves differently from production where one customer holds a third of the rows. It is the main argument for volume testing against production-scale data.

What they are assessing

The most basic tester query.

Model answer

By querying the row and checking every field that should have been set, not only the ones visible on screen, since fields such as the created timestamp, the user who created it, a status and any derived values are where defects hide. I would also check that related rows were created, such as an audit entry or a child record, and that nothing unintended was modified elsewhere. Checking the row exists is a weak verification; checking it holds exactly the expected values is the test.

What they are assessing

Referential integrity checking.

Model answer

With an anti-join: LEFT JOIN the child table to the parent on the foreign key column and keep rows where the parent side is null, which gives children referencing a parent that does not exist. The same pattern finds parents with no children when that is the question. Orphans should be impossible where a foreign key constraint exists, so finding any is a strong signal that the constraint is missing, was disabled during a load, or the data was inserted around the application.

Likely follow-up

What would finding orphans tell you about the schema?

What they are assessing

Verifying a bulk operation.

Model answer

By counting the rows that met the selection criteria before the run and comparing against the rows now in the expected state, and separately querying for rows that met the criteria and were not updated, which is the more useful of the two because it lists the failures rather than just a number. I would also check for rows updated that should not have been, by looking at modification timestamps outside the expected set. Counting alone can balance by coincidence, so identifying the specific exceptions is the stronger check.

What they are assessing

A routine query with a known pitfall.

Model answer

With a greater-than-or-equal comparison on the lower bound and a strictly-less-than comparison on the start of the next period, rather than BETWEEN, which excludes everything after midnight on the final day when the column holds a time component. I would also avoid wrapping the column in a date truncation function, since that prevents index use on a large table. And I would be explicit about the time zone if the column stores one, because an off-by-hours window is easy to produce and hard to notice.

What they are assessing

A specific and commonly tested data pattern.

Model answer

By checking the closure rather than the insertion, since everyone checks that the new row appeared. For each business key there should be exactly one current row, no overlapping date ranges, no gaps between the end of one version and the start of the next, and the end date on the superseded row should be set rather than left null. Window functions make this straightforward: LAG over the versions ordered by start date lets you compare each row start against the previous row end in one query. The ETL side of this is covered in the ETL testing bank.

What they are assessing

A useful pattern.

Model answer

With LAG over the ordered identifiers, keeping rows where the current value is more than one greater than the previous, which gives the start of each gap. Without window functions, a self join comparing each row to the next works but is clumsier. The reason it matters for a tester is that a gap in a sequence that should be contiguous usually means records were deleted, failed to load, or were rolled back, and finding the gaps points at where to investigate.

What they are assessing

Reconciliation at the coarse level, with its limits stated.

Model answer

A count on each, which is instant and worth doing first because a mismatch immediately tells you something is wrong. The limitation has to be stated though: equal counts prove very little, because a load that drops one row and duplicates another produces identical totals. So counts are a cheap first check and the real comparison is EXCEPT in both directions, or a checksum over the rows if the volume makes a full comparison impractical. Reporting a count match as a passing reconciliation is the mistake this question is usually probing for.

What they are assessing

Data quality checking.

Model answer

With a LIKE pattern or a regular expression function, which most databases provide under different names, filtering for rows that do not match. Typical checks are email addresses without an at sign, phone numbers containing letters, postcodes with the wrong shape, and identifiers of unexpected length. These queries are how a tester finds the data quality problems that cause downstream failures, and running them against a production copy before a migration is usually more informative than any amount of functional testing.

What they are assessing

Professional judgement about permissions.

Model answer

Read access everywhere they need to verify, which is the minimum to be effective, and write access only in environments where it is appropriate, which is not production. Separate credentials rather than a shared account so actions are attributable. No production write access as a default position, and where production read access is needed it should be restricted and logged, since production data is usually personal data and access to it carries obligations. Asking for less than you need slows you down; having production write access you do not need is a risk to everyone.

What they are assessing

Operational discipline.

Model answer

Confirm the connection, because the single most common serious mistake is running a correct statement against the wrong environment, and a colour-coded client or a separate profile helps. Read only unless there is an explicit approved reason otherwise. Consider the load, since an unindexed query on a large production table can affect live users, so adding a row limit and running at a quiet time is sensible. And be aware of what the data contains, since exporting a result set containing personal data to a local file is a disclosure even when the query was legitimate.

Trap to avoid

Running an exploratory query with no limit on a production table. It can lock or saturate the database and the fact that it was only a SELECT is no defence.

What they are assessing

A simple habit that prevents damage.

Model answer

Because it makes the change reversible until committed, so an incorrect WHERE clause can be undone after seeing the affected row count rather than discovered afterwards. The workflow is to begin the transaction, run the statement, check the count and the resulting rows, then commit or roll back. The caution is remembering to end the transaction, since an open transaction holds locks and can block other users of a shared environment, which is a different kind of incident.

What they are assessing

Schema object knowledge.

Model answer

A stored query presented as a table, used to simplify access, to encapsulate logic and to restrict which columns or rows a user sees. It is worth testing because the logic inside it is real logic that can be wrong, particularly where it joins and filters, and because views are often how reporting reaches the data, so a defect in one affects every report built on it. A materialised view adds a refresh schedule, which brings its own question of whether the data is current.

What they are assessing

Testing logic that lives in the database.

Model answer

A compiled routine stored in the database, containing procedural logic, often used for batch processing and complex operations. Testing one means treating it as a unit with inputs and outputs: calling it with valid and boundary parameters, verifying the data changes it makes rather than only its return value, checking the error paths and whether it leaves a transaction open or rolled back on failure, and checking behaviour with concurrent invocation if that is realistic. It is a frequently untested area because it sits outside the application code everyone reviews.

Likely follow-up

Why do stored procedures often escape testing entirely?

What they are assessing

Hidden behaviour.

Model answer

Code that executes automatically when a row is inserted, updated or deleted. It is relevant because it means a simple INSERT can have effects nobody reading the application code would expect: writing an audit row, updating a summary, or rejecting the operation. For a tester the practical consequences are that inserting data directly to set up a test state will fire triggers and may produce side effects, and that a defect appearing to come from nowhere is sometimes a trigger nobody knew about.

What they are assessing

A join type usually encountered accidentally.

Model answer

It returns every combination of rows from both tables, so a hundred rows joined to a hundred gives ten thousand. It is usually seen by accident when a join condition is omitted, which is why a query returning an enormous result is often a missing ON clause. It is deliberately useful for generating combinations, such as producing every product and region pair to check which have no sales, which is exactly the kind of completeness check a tester wants and which a straight join from the data cannot produce.

What they are assessing

Efficiency in building a verification query.

Model answer

With conditional aggregation: SUM or COUNT over a CASE expression that returns a value only for the rows meeting each condition, giving one column per category in a single pass. It is far better than running five separate queries, both for speed and because the figures are all from the same moment, which matters when the data is changing. It is probably the single most useful intermediate SQL technique for a tester building a data quality dashboard or a verification summary.

Likely follow-up

Why does taking all the counts in one query matter on a live system?

What they are assessing

A small distinction with practical effect.

Model answer

A column alias in SELECT renames the output column, and because SELECT is evaluated after WHERE and GROUP BY it cannot be referenced in those clauses, though most databases permit it in ORDER BY. A table alias in FROM renames the table for the duration of the query and can be used everywhere, which is what makes self joins possible and what keeps multi-table queries readable. Forgetting that a column alias is unavailable in WHERE is a routine source of confusion.

What they are assessing

Practical optimisation at a tester level.

Model answer

Look at the plan first rather than guessing. The usual causes are a scan where an index should be used, often because a function was applied to a column or a type is being converted implicitly, a join producing far more intermediate rows than expected, or a missing filter that could reduce the set early. Narrowing the date range or adding a row limit while developing the query makes iteration tolerable. If the query is genuinely needed often, raising it with a DBA for an index is reasonable, since the same query is probably slow for the application too.

What they are assessing

A small detail with a real consequence.

Model answer

CHAR is fixed length and pads with spaces to its defined size; VARCHAR stores only what is supplied. It matters because a value stored in a CHAR column comes back padded, so a comparison against an unpadded string can fail, and a join between a CHAR column and a VARCHAR column may not match. Some databases ignore trailing spaces in comparisons and some do not, so the behaviour is not portable. It is a classic cause of a reconciliation reporting mismatches that look identical on screen.

Trap to avoid

Reporting a mismatch between two values that print the same. Trailing whitespace from a CHAR column is one of the most common explanations.

What they are assessing

Recomputation rather than reading.

Model answer

By recomputing it independently from the underlying rows and comparing, rather than reading the stored value and confirming it looks plausible. So a stored order total is checked by summing the line items with their quantities, discounts and tax from the detail table and comparing against the header. Doing this across every order rather than one finds the cases where the calculation diverges under particular conditions, which is where the defect usually is. A tolerance is needed if the types are inexact.

What they are assessing

A subquery form with a constraint.

Model answer

A subquery returning exactly one row and one column, usable wherever a single value is expected, such as in a SELECT list or on one side of a comparison. The constraint is the important part: if it returns more than one row the statement errors, and if it returns none it yields null, which is silent and will propagate through whatever expression contains it. A scalar subquery in a SELECT list is also evaluated per row, which can be slow on a large result and is often better expressed as a join.

What they are assessing

Connecting two previous topics.

Model answer

Because of nulls in the subquery. NOT EXISTS evaluates whether any matching row exists and handles nulls correctly, returning the rows you intended. NOT IN compares against the full list of values, and if any is null the comparison yields unknown for every row, so nothing is returned. So the two give the same answer when the subquery column has no nulls, and NOT IN silently returns nothing when it does. That makes NOT EXISTS the safer default for anti-join conditions.

What they are assessing

Verifying a secondary effect.

Model answer

By querying the audit table for the operation just performed and checking it holds the actor, the action, the before and after values where applicable, and a timestamp. Then checking the cases that are routinely missing: failed and rejected attempts, which should also be recorded, and changes made through a different path such as a batch job or an API rather than the interface. And checking that nothing sensitive was written in clear, since audit tables frequently capture full values including ones that should be masked.

What they are assessing

A production failure mode.

Model answer

Two transactions each holding a lock the other needs, so neither can proceed, and the database resolves it by terminating one as the victim. Investigation starts with the database deadlock log or trace, which reports the statements and the resources involved. The usual cause is two code paths acquiring the same locks in different orders, and the usual fix is to make the ordering consistent, to shorten the transactions so locks are held briefly, or to reduce the isolation level where that is acceptable. It is reproducible in testing only under concurrency, which is why it reaches production.

What they are assessing

Schema design at an awareness level.

Model answer

Organising a schema to reduce redundancy, with each fact stored once and related through keys. A tester cares because denormalised data, where the same value is stored in several places, creates the possibility of it being updated in one place and not another, which is a real defect class worth testing for explicitly. Deliberate denormalisation for performance is common and legitimate, and when it exists the question is whether something keeps the copies consistent, which is exactly the kind of thing that works until it does not.

What they are assessing

Moving from ad hoc queries to something durable.

Model answer

By writing the checks as queries that return the exceptions rather than a pass or fail, so a non-empty result is a failure and the rows are the evidence. Then running them from a test framework or a scheduled job so they execute regularly rather than when someone remembers, with the count asserted as zero. The checks worth building are the invariants: no orphans, no duplicates on a business key, no negative amounts where impossible, no rows in suspense, and totals reconciling between related tables. That turns one-off investigation into a standing control.

Likely follow-up

Why return the exception rows rather than a count?

What they are assessing

Judgement about a common shortcut.

Model answer

That the data is inconsistent with what the application would have created: missing related rows, defaults not applied, derived values absent, and business rules bypassed entirely. A test running against such data can pass or fail for reasons unrelated to the behaviour being tested. Triggers may fire unexpectedly or not at all depending on the path. It is a legitimate technique for setting up states that cannot otherwise be reached, and the discipline is to create data through the API where possible and resort to direct insertion deliberately rather than by default.

What they are assessing

A detail that explains unexpected scans.

Model answer

An index across several columns in a defined order, which can be used for queries filtering on the leading column, or the leading columns in sequence, but not for one filtering only on a later column. So an index on two columns helps a query filtering on the first, and does not help one filtering only on the second. That is why adding an index does not always produce the expected improvement, and why a plan showing a scan despite an apparently relevant index is usually a column order issue rather than a missing index.

What they are assessing

Calibration of the skill in a role.

Model answer

Enough to verify independently and investigate, which is a defined and reachable set: SELECT with filtering, the join types including the anti-join pattern, aggregation with GROUP BY and HAVING, the null behaviours, and the ability to compare two sets with EXCEPT. Window functions are a genuine step up and worth having at senior level. Tuning and schema design are not the role. The practical test is whether the tester can answer did the system do the right thing without asking a developer, which the list above covers for most applications.

What they are assessing

Compliance awareness.

Model answer

By minimising it: selecting only the columns needed rather than everything, limiting rows, and avoiding exporting results containing personal data to local files or tickets, which is the most common accidental disclosure. In a defect report, referencing a record identifier rather than pasting a row of customer details achieves the same thing without the exposure. Where production access is needed it should be approved, restricted and logged. The relevant point is that a legitimate query can still produce an illegitimate copy of personal data.

What they are assessing

Self-checking, which separates careful testers.

Model answer

By proving the query would find something if it existed, since an empty result is ambiguous between nothing matches and the query is wrong. Practically that means relaxing the conditions one at a time until rows appear, which identifies which predicate eliminated everything. The specific causes to suspect are a NOT IN against a subquery containing nulls, a comparison against null using equals, a date range excluding the time component, a join condition that never matches due to type or whitespace differences, and a case sensitivity mismatch. Treating an empty result as a passing check without doing this is how verification queries silently stop verifying.

Trap to avoid

Reporting an empty result as evidence that there is no problem. It is equally consistent with a query that could never have returned anything.

What they are assessing

A closing question revealing depth of experience.

Model answer

Trusting a query that cannot fail. An empty result read as confirmation, a count match read as a reconciliation, a NOT IN against nullable data returning nothing and being recorded as no discrepancies found. All three produce a green verification from a query that tested nothing, which is worse than not checking at all because it creates confidence. The habit that prevents it is making the query prove itself first: deliberately break the condition and confirm it returns rows, so you know the check is capable of failing before you rely on it passing.