82 database testing interview questions with answers

What they are assessing

Whether you understand what the interface hides.

Model answer

Database testing verifies what is actually stored and how it behaves: that data is persisted correctly and completely, that constraints and relationships hold, that procedures and triggers do what they claim, and that nothing is silently lost or corrupted. It is needed because the UI shows you a rendering, not the record. A form can display a success message while the write partially failed, a value can be truncated on save, and a delete can leave orphan rows the interface never reveals.

Likely follow-up

Give an example of a defect only visible at the database layer.

What they are assessing

Basic SQL vocabulary.

Model answer

DDL, data definition language, defines structure: CREATE, ALTER, DROP, TRUNCATE. DML, data manipulation language, works with the rows: SELECT, INSERT, UPDATE, DELETE. DCL, data control language, handles permissions: GRANT and REVOKE. TCL, transaction control language, manages transactions: COMMIT, ROLLBACK and SAVEPOINT. The distinction that matters practically is that DDL statements are usually implicitly committed and cannot be rolled back on most platforms, which is why an accidental TRUNCATE is not recoverable the way a DELETE inside a transaction is.

What they are assessing

A classic, and the answer reveals care.

Model answer

DELETE removes rows, can have a WHERE clause, is logged per row so it can be rolled back inside a transaction, and fires triggers. TRUNCATE removes all rows, is minimally logged so it is much faster, usually cannot be rolled back, resets identity counters on most platforms and does not fire row triggers. DROP removes the table itself, structure included. For a tester the relevant point is that TRUNCATE in a shared environment is not undoable, which makes it a poor choice for test data cleanup.

Trap to avoid

Saying TRUNCATE can be rolled back. It can on SQL Server inside an explicit transaction, but not on most others, so an unqualified claim either way is wrong.

What they are assessing

Structured view of the work.

Model answer

Structural testing of schema, tables, columns, keys and indexes against the specification. Data integrity testing, covering CRUD operations and whether constraints and relationships hold. Business rule testing of procedures, functions, triggers and views. Data migration testing when moving or upgrading. Performance testing of queries and load. Security testing covering access control, injection and data protection. And data quality checks on nulls, duplicates and formats.

What they are assessing

Design literacy.

Model answer

Normalisation organises tables to reduce redundancy and avoid update anomalies. First normal form requires atomic values with no repeating groups, so no comma separated list in a column. Second normal form requires that every non key column depends on the whole primary key, which matters only with composite keys. Third normal form requires no transitive dependency, so a non key column must not depend on another non key column. Beyond that there is BCNF and higher forms, rarely discussed outside interviews.

Likely follow-up

When would you deliberately denormalise?

What they are assessing

Key semantics.

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; how many nulls are allowed varies by platform, which is a real portability trap. A foreign key references a key in another table and enforces referential integrity, so you cannot insert a child row pointing at a parent that does not exist, and the delete or update behaviour is governed by the cascade rule.

What they are assessing

Precision with terms that come up in schema review.

Model answer

A candidate key is any column or combination that could serve as the primary key; one is chosen and the rest become alternate keys. A composite key is a key made of more than one column, used when no single column is unique. A surrogate key is an artificial identifier with no business meaning, usually an auto increment integer or a UUID, used in place of a natural key because natural keys change and can be reused. Understanding surrogates matters because most application schemas use them.

What they are assessing

Structural testing method.

Model answer

Table and column names against the specification. Data types, lengths, precision and scale, since a decimal with the wrong scale silently rounds money. Nullability. Default values. Primary, unique and foreign keys, and whether they are actually enforced rather than merely documented. Check constraints. Indexes, because a missing one is a performance defect waiting to appear. And that the deployed structure matches across environments, since schema drift is a frequent cause of a release working in test and failing in production.

What they are assessing

Practical negative testing.

Model answer

NOT NULL, tested by attempting a null and expecting rejection. UNIQUE, by inserting a duplicate. PRIMARY KEY, which combines both. FOREIGN KEY, by inserting a child with a non existent parent and by deleting a referenced parent. CHECK, by supplying a value outside the permitted range or set. DEFAULT, by omitting the column and confirming the default applied rather than a null. The pattern is the same throughout: constraints are only proven by the negative case, since the positive case passes whether or not the constraint exists.

Trap to avoid

Testing only that valid data inserts successfully. That proves nothing about a constraint, and it is the most common gap in constraint testing.

What they are assessing

Understanding of deletion behaviour.

Model answer

ON DELETE and ON UPDATE can be NO ACTION or RESTRICT, which blocks the operation; CASCADE, which propagates it to the child rows; SET NULL, which nulls the reference; or SET DEFAULT. They matter enormously for testing because CASCADE deletes can remove far more than the user intended, and it is invisible from the UI. So deleting a customer should be tested for what happened to their orders: were they deleted, orphaned, or did the delete correctly fail. That is a business decision the schema encodes.

Likely follow-up

A customer delete silently removed three years of orders. Which cascade rule caused that?

What they are assessing

A small detail that produces real defects.

Model answer

CHAR is fixed length and pads with spaces to its defined size; VARCHAR is variable length and stores only what is supplied. A tester cares because padded values break comparisons: a CHAR column containing a code will not equal the same code supplied without padding unless the comparison trims, which produces failed lookups that look like missing data. It also affects storage and index size. The related point is NVARCHAR versus VARCHAR, where unicode support determines whether non Latin characters survive.

What they are assessing

Real world reasoning.

Model answer

It moves enforcement from the database to the application, so anything the constraint would have caught now has to be tested explicitly and can no longer be assumed. That means actively checking for orphan rows, duplicate business keys and invalid values, rather than trusting that they cannot exist. It also means a defect in one code path can corrupt data that every other path then trusts. I would ask why the constraints were disabled, because the usual reason is bulk load performance and the usual fix is enabling them after the load rather than permanently.

What they are assessing

The most common verification query.

Model answer

Group by the columns that should be unique and filter to groups larger than one: SELECT col1, col2, COUNT(*) FROM t GROUP BY col1, col2 HAVING COUNT(*) > 1. The judgement is in choosing the columns: duplicates on the business key are a defect, whereas several rows sharing a customer identifier may be entirely correct. So the first question is always what a single row is supposed to represent, and the query follows from that.

What they are assessing

A guaranteed question.

Model answer

WHERE filters rows before grouping and cannot reference aggregate functions. HAVING filters groups after aggregation and can. So filtering to orders placed this year uses WHERE, and filtering to customers with more than five orders uses HAVING. Putting an aggregate in WHERE is a syntax error on most platforms, and putting a simple row filter in HAVING works but is slower, because rows are aggregated before being discarded.

What they are assessing

A detail that trips people up.

Model answer

With IS NULL and IS NOT NULL, never with equals, because null is unknown rather than a value and any comparison with it evaluates to unknown rather than true. That is why WHERE col = NULL returns nothing even when nulls exist. The related trap is NOT IN with a subquery containing a null, which returns no rows at all rather than the expected set, because the comparison against null makes the whole predicate unknown. NOT EXISTS behaves correctly and is the safer choice.

Trap to avoid

Using NOT IN against a subquery that can contain nulls. It silently returns an empty result and looks like a data problem.

What they are assessing

The core reconciliation technique.

Model answer

With EXCEPT, or MINUS on Oracle, run in both directions: source EXCEPT target finds rows missing from the target, target EXCEPT source finds rows that should not be there. Both returning nothing means the sets are identical. Note that EXCEPT removes duplicates, so if duplicate detection matters, compare with a grouped count instead. Column order and data types must align or you get spurious differences, particularly with dates and decimals.

Likely follow-up

Why is running the comparison in only one direction insufficient?

What they are assessing

A small distinction with real consequences.

Model answer

UNION removes duplicates and therefore has to sort or hash, which costs time on large sets. UNION ALL returns everything including duplicates and is significantly faster. In verification work UNION ALL is usually the right default, because silently removing duplicate rows can hide exactly the problem you were looking for. Use UNION only when deduplication is genuinely intended.

What they are assessing

Referential integrity verification.

Model answer

A LEFT JOIN from the child to the parent, filtered where the parent key is null: SELECT c.* FROM child c LEFT JOIN parent p ON c.parent_id = p.id WHERE p.id IS NULL. NOT EXISTS is equivalent and often clearer. This matters specifically when foreign keys are not enforced, which is common in high volume systems, because nothing else prevents orphans from accumulating. It is one of the checks worth running routinely rather than once.

What they are assessing

SQL beyond the basics.

Model answer

ROW_NUMBER partitioned by a business key and ordered by a timestamp identifies which row is current and exposes duplicates at a defined grain in one pass. LAG and LEAD compare a row against its neighbours, which is how you detect gaps or overlaps in effective date ranges without a self join. Running totals verify cumulative balances. RANK identifies ties where a deduplication rule is ambiguous. Before window functions these checks required correlated subqueries that were slow and easy to get subtly wrong.

What they are assessing

Readability of verification SQL.

Model answer

A common table expression is a named temporary result defined with WITH, referenced in the statement that follows. Its value in test queries is readability: a multi step check reads as a sequence of named steps rather than nested subqueries. Recursive CTEs also handle hierarchies, such as verifying a category tree or an organisational structure. Performance is usually comparable to a subquery, though on some platforms a CTE is materialised and on others inlined, which occasionally matters on large data.

What they are assessing

A framework rather than ad hoc checks.

Model answer

Entity integrity, meaning every row is uniquely identifiable and the primary key is not null. Domain integrity, meaning values are valid for their column: correct type, within range, matching the permitted set. Referential integrity, meaning relationships between tables hold and no orphans exist. And user defined or business integrity, meaning application specific rules such as an order total matching the sum of its lines. Testing against these four covers most of what goes wrong.

What they are assessing

Basic verification method.

Model answer

Perform the action through the application, then verify in the database rather than trusting the confirmation message. For create, confirm the row exists with every field correct including defaults, audit columns and any derived values. For read, confirm what is displayed matches what is stored. For update, confirm only the intended fields changed and the audit timestamp moved. For delete, confirm whether it was a hard delete or a soft flag, and what happened to related rows.

Likely follow-up

An update shows success in the UI but the record is unchanged. Where do you look?

What they are assessing

A very common design choice.

Model answer

A hard delete removes the row; a soft delete sets a flag or a deleted timestamp and leaves it in place, which preserves history and avoids breaking foreign keys. Testing a soft delete requires more care: the record must disappear from every user facing query, which means checking every list, search, report and export, not just the screen it was deleted from. The classic defect is a soft deleted record still appearing in a report or counting toward a total, because one query forgot the filter.

What they are assessing

A silent corruption class.

Model answer

Insert a value at exactly the column length, one character over, and well over, and confirm the behaviour is rejection rather than silent shortening. Some platforms and some connection settings truncate without error, which means the save appears to succeed and the data is quietly wrong. Unicode makes it worse, because a multi byte character consumes more bytes than characters, so a field accepting fifty characters may reject a fifty character string containing emoji or non Latin script.

What they are assessing

Independent verification.

Model answer

Recompute it independently rather than reading the application logic and confirming it matches itself. So for an order total, write a query summing the line items with tax and discount applied, and compare against the stored total for every order rather than a sample. That comparison across the whole table frequently finds a small number of historical rows that are wrong, which is far more valuable than confirming the calculation works for a new order. Rounding is where the differences usually are.

What they are assessing

Fundamental transaction knowledge.

Model answer

Atomicity, meaning a transaction either completes entirely or not at all. Consistency, meaning it moves the database from one valid state to another, respecting all constraints. Isolation, meaning concurrent transactions do not interfere in ways that produce incorrect results. Durability, meaning once committed the change survives a crash. For testing, atomicity and isolation are the two that generate the most defects, because partial writes and concurrency bugs are both invisible in single user testing.

What they are assessing

Practical failure injection.

Model answer

By forcing a failure partway through a multi step operation and confirming nothing was left behind. For a transfer that debits one account and credits another, cause the second step to fail through a constraint violation, a killed connection or a service outage, and verify the debit was rolled back. The defect this finds is the common one where each step commits separately rather than sharing a transaction, which leaves money removed from one account and never added to the other.

Likely follow-up

How would you cause the second step to fail in a controlled way?

What they are assessing

A guaranteed senior question.

Model answer

Read uncommitted permits dirty reads, where you see another transaction's uncommitted changes. Read committed prevents dirty reads but allows non repeatable reads, where the same row read twice differs. Repeatable read prevents that but allows phantom reads, where a second identical query returns new rows. Serializable prevents all three by making concurrent execution equivalent to serial. Higher isolation costs concurrency, which is why read committed is the common default.

What they are assessing

Concurrency awareness.

Model answer

A deadlock occurs when two transactions each hold a lock the other needs, so neither can proceed and the database kills one as the victim. Testing for it means deliberately creating the condition: two sessions updating the same two rows in opposite order, which is the classic pattern. What you verify is that the application handles the victim gracefully, retrying or surfacing a sensible error, rather than showing a raw database exception or leaving the operation half done.

What they are assessing

Concurrency design knowledge.

Model answer

Pessimistic locking takes a lock when reading, so others wait; you test it by confirming the second session blocks and then proceeds, and that it does not block indefinitely. Optimistic locking takes no lock but checks a version column on write, rejecting the update if the row changed; you test it by having two sessions read the same row, both edit, and confirming the second gets a meaningful conflict error rather than silently overwriting. That silent overwrite, the lost update, is the defect the mechanism exists to prevent.

Trap to avoid

Testing concurrency with one session. Lost updates and deadlocks are invisible unless two sessions run simultaneously.

What they are assessing

Core SQL.

Model answer

INNER JOIN returns rows matching in both tables. LEFT JOIN returns all rows from the left plus matches from the right, with nulls where there is none, and RIGHT JOIN is the mirror. FULL OUTER JOIN returns everything from both with nulls where unmatched. CROSS JOIN returns the Cartesian product of every combination. A self join is a table joined to itself, used for hierarchies or comparing rows within a table. For verification, LEFT JOIN with a null check is the workhorse for finding what is missing.

What they are assessing

A very common query bug.

Model answer

Because the join is not at the grain you assumed: if the right table has several rows per key, each left row multiplies. That is how a query summing order values suddenly reports triple the real revenue, because a join to a table with multiple addresses per customer duplicated every order. The check is confirming the join key is unique in the table being joined to, and if it is not, aggregating first or using EXISTS instead of a join.

Likely follow-up

How would you confirm a join key is unique before relying on it?

What they are assessing

Query construction judgement.

Model answer

They often produce the same result, and modern optimisers frequently rewrite one into the other, so performance is usually comparable. Where it matters is semantics: a join can multiply rows if the joined table is not unique, whereas EXISTS returns each left row at most once regardless. So for checking existence, EXISTS is both clearer and safer. For retrieving columns from the other table, a join is the natural choice. Correlated subqueries evaluated per row are the case that genuinely performs badly.

What they are assessing

Whether you test the check itself.

Model answer

By recomputing it a different way and comparing, and by verifying the group count rather than only the totals, because a missing group is invisible in a grand total while being a real defect. Then check the null handling, since most aggregates ignore nulls while COUNT(*) does not, which makes AVG differ from the sum divided by the row count in a way that looks like a bug and is specification ambiguity. And check the grain, because a join before aggregation is the usual cause of inflated sums.

What they are assessing

Basic performance understanding.

Model answer

An index is a structure that lets the database find rows without scanning the whole table, dramatically speeding reads on the indexed columns. The trade-off is that every insert, update and delete must also maintain the index, so writes get slower and storage increases. That is why you index selectively: columns used in WHERE clauses, join conditions and ORDER BY, rather than everything. For a tester, both sides matter: a missing index is a slow query defect and an excessive set of indexes is a slow write defect.

What they are assessing

Index types.

Model answer

A clustered index determines the physical order of the rows, so there can be only one per table, and on SQL Server the table is the clustered index. A non clustered index is a separate structure holding the key values and pointers back to the rows, and a table can have many. A covering index includes every column a query needs so the lookup back to the table is avoided entirely. Terminology varies by platform, which is worth acknowledging rather than asserting one vendor's model as universal.

What they are assessing

Practical performance diagnosis.

Model answer

You are looking for a few specific things rather than reading it exhaustively. A full table scan on a large table where an index should apply. A nested loop join over a large row count. A sort or hash spilling to disk. And a large gap between estimated and actual row counts, which indicates stale statistics and usually means the optimiser chose a poor plan. Those four cover most of what a tester needs to raise, and the detailed tuning belongs with a DBA or developer.

Likely follow-up

The query has an index on the filtered column but still does a full scan. Why might that be?

What they are assessing

Deeper performance knowledge.

Model answer

A function applied to the indexed column in the WHERE clause, such as comparing on UPPER of a name, which prevents the index being used unless a functional index exists. Implicit type conversion, where a string is compared to a numeric column. Leading wildcard in a LIKE. Low selectivity, where the optimiser correctly decides a scan is cheaper because most rows match. Stale statistics. Or a composite index whose leading column is not in the predicate. The first two are the ones a tester can spot from the query alone.

What they are assessing

A very common application defect.

Model answer

It occurs when code fetches a list with one query, then issues a separate query per item, so displaying fifty rows executes fifty one queries. Response time looks acceptable in development with ten records and collapses in production with thousands. You detect it by watching the query log or an APM trace while performing a single user action and counting queries, which is a genuinely valuable test that almost nobody runs. The fix is eager loading or a join, and it is usually a small code change with a large effect.

Trap to avoid

Only testing with a handful of rows. N plus one is invisible at small data volumes, which is exactly why it reaches production.

What they are assessing

Unit testing at the database layer.

Model answer

Call it directly rather than through the application, so you can control inputs precisely. Test valid inputs producing the expected data changes and return values, boundary values, invalid inputs and nulls, and the error handling path. Verify the side effects, which is the part often missed: what rows changed, whether audit columns were set, and whether it committed or left an open transaction. And test it twice in a row, because a procedure that is not idempotent behaves differently on a retry.

What they are assessing

Awareness of hidden behaviour.

Model answer

A trigger is code that fires automatically on an insert, update or delete. They are difficult because the behaviour is invisible from the calling code: a developer inserting a row may have no idea three other tables changed. They can cascade, where one trigger fires another, and they can be recursive. For testing that means verifying not only the direct result but every side effect, and specifically testing bulk operations, because a trigger written assuming one row at a time misbehaves when ten thousand are inserted at once.

Likely follow-up

How would you find every trigger that fires on a given table?

What they are assessing

Precomputation awareness.

Model answer

A view is a stored query executed each time it is referenced, so it always reflects current data and costs the query each time. A materialised view stores the result physically and is refreshed on a schedule or on commit, so reads are fast but the data can be stale. For testing, the materialised version adds a consistency question: verify it matches the underlying data after a refresh, and establish how stale it can be, because a report reading a view refreshed nightly is showing yesterday regardless of what the user just did.

What they are assessing

Automation of database checks.

Model answer

With a framework that sets up known data, executes, asserts and rolls back, so tests are independent and leave nothing behind. tSQLt on SQL Server, utPLSQL on Oracle, pgTAP on Postgres, or DbUnit from Java. The important properties are a known starting state, which usually means either a transaction rolled back at the end or a deterministic seed, and the ability to fake dependent tables so a procedure can be tested without the whole schema. Running them in CI alongside application tests is what stops the logic decaying.

What they are assessing

Practical data handling.

Model answer

A combination. Scripted setup that creates exactly what a test needs and cleans it up, which gives independence and is what automated tests should use. A masked subset of production for realistic distribution and the awkward historical cases. And generated volume where performance is being tested. The mistake is relying on data someone created manually months ago, because it drifts, gets consumed, and nobody knows which rows matter, so tests start failing for reasons unrelated to the code.

What they are assessing

Data protection awareness.

Model answer

It is a data protection breach in most jurisdictions unless masked, since test environments have weaker access controls and more people with access. It also carries operational risk: real email addresses and phone numbers in a test system have caused real messages to be sent to real customers. So the requirement is masking or synthesis before it lands, preserving format and referential integrity so the data still works, and confirming no reversible mapping is retained. This comes up specifically in BFSI and healthcare interviews.

Trap to avoid

Saying you would restore a production backup to test. Without masking that is a compliance failure, and interviewers in regulated sectors are listening for it.

What they are assessing

Practical masking.

Model answer

Consistently and deterministically, so the same input always produces the same masked output, which preserves joins across tables without retaining the original value. Preserve format so validation still passes: a masked card number should still look like a card number and satisfy a checksum if the application checks one. Preserve distribution where it matters for performance testing. And mask everything identifying, not just the obvious fields, since free text notes and audit logs routinely contain names and account numbers.

What they are assessing

Isolation strategy.

Model answer

Each test creates the data it needs with unique identifiers generated at run time, and removes it afterwards, ideally by wrapping the test in a transaction that is rolled back. Where rollback is impractical because the code under test commits, clean up explicitly in teardown. Avoid shared fixture rows that tests modify, since that couples them and breaks parallelism. And avoid assertions on absolute counts of a shared table, because another test running concurrently changes the answer.

What they are assessing

Release safety.

Model answer

That the migration script runs cleanly on a copy of production sized data within the deployment window, which is where a long running ALTER on a large table causes an outage nobody predicted. That existing data is preserved and correctly transformed, verified by counts and by checking the transformed values. That the rollback script works, which is the part most often untested. That the application works against both the old and new schema if the deployment is not simultaneous. And that indexes and constraints survived.

Likely follow-up

How would you test a migration that takes four hours on production volume?

What they are assessing

Cross-platform reconciliation.

Model answer

Reconcile rather than assume. Row counts per table, aggregate sums on numeric columns, and checksums or hashes per partition to detect differences without transferring everything. Then investigate every difference rather than dismissing small ones. Expect and catalogue legitimate platform differences: numeric precision, date and timestamp handling including timezone, string collation affecting sort order and case sensitivity, and null ordering. The deliverable should be a signed list of accepted differences rather than silence.

What they are assessing

Deployment awareness.

Model answer

One where the new schema works with the old application code, so the database can be deployed before the application without downtime and rolled back independently. It usually means adding columns rather than renaming, keeping both old and new for a release, and removing the old only once nothing references it. It matters because a rename deployed simultaneously with code requires perfect synchronisation and cannot be rolled back cleanly. Testing it means running the old application against the new schema deliberately.

What they are assessing

The essential database security test.

Model answer

Supply input containing SQL syntax into every field that could reach a query, including ones that are not obviously text such as sort parameters, filters and identifiers in URLs. Classic payloads such as a quote, an OR condition that is always true, and a comment sequence will show whether input is being concatenated. Then check the error behaviour, since a database error message returned to the user is itself a finding. The underlying fix is parameterised queries, so the useful question to ask the developer is whether any query is built by concatenation.

What they are assessing

Least privilege thinking.

Model answer

That the application account has only the permissions it needs, which for most applications means data manipulation rather than schema modification, and certainly not administrative rights. That individual users cannot connect directly to production. That read only reporting accounts really are read only. That default and shared accounts are disabled or have changed credentials. And that credentials are not stored in configuration files in the repository, which is a finding I would expect to check for rather than assume.

What they are assessing

Data protection knowledge.

Model answer

At rest protects the stored data and backups, typically through transparent database encryption or disk encryption, so a stolen file or backup is unreadable. In transit protects the connection, through TLS between the application and the database. I would verify that the connection actually negotiates TLS rather than assuming it, since many drivers fall back silently; that backups are encrypted, because they are frequently the weak point; and that particularly sensitive fields have column level encryption or hashing where required.

What they are assessing

Multi-tenancy verification.

Model answer

By attempting it directly through the API rather than the UI, since the UI simply will not offer the option. Take a valid identifier belonging to another tenant and request it with a legitimate token for the first tenant, expecting a 403 or 404 rather than the record. Then check the database layer: whether the tenant filter is applied in every query or only in some, which is the usual defect, and whether row level security is enforced in the database rather than relying on every developer remembering the WHERE clause.

Trap to avoid

Testing only through the UI. Tenant isolation defects are almost always reachable through the API and invisible from the interface.

What they are assessing

Breadth beyond relational.

Model answer

There is usually no enforced schema, so structure validation moves into the application and into your tests: documents in the same collection can have different shapes, and schema drift is normal rather than exceptional. Referential integrity is not enforced, so orphan references must be checked explicitly. Many are eventually consistent, so a read immediately after a write may not reflect it, which changes how assertions are written. And queries are engine specific rather than standard SQL.

What they are assessing

A concept that breaks naive tests.

Model answer

It means a write will propagate to all replicas eventually but a read immediately afterwards may return stale data. Testing against it means not asserting instantly: poll for the expected state with a timeout rather than adding a fixed sleep, and be explicit about the window the business considers acceptable. It also means actively testing the stale window, since the interesting defects are what the user sees while it is inconsistent, such as an item they just created not appearing in their own list.

Likely follow-up

How would you decide what timeout is acceptable rather than guessing one?

What they are assessing

Distributed systems literacy.

Model answer

It states that a distributed data store can guarantee at most two of consistency, availability and partition tolerance, and since network partitions happen, the real choice is between consistency and availability during a partition. It matters because it tells you what behaviour to expect and therefore what to test: a system choosing availability will serve stale or conflicting data during a partition, which is a scenario that needs explicit test cases rather than being treated as a defect when it appears.

What they are assessing

Basic investigation.

Model answer

Whether you are looking at the right database and schema, which is more often the answer than people expect in an environment with several. Whether the transaction committed, since an uncommitted write is invisible to another session. Whether a trigger or the application moved or transformed it into a different table. Whether it was written and then deleted or soft deleted by a subsequent process. And whether the query you are running has a filter excluding it, such as a tenant or status condition.

What they are assessing

Performance triage.

Model answer

Compare the execution plan now against what it should be, since a plan change is the most common cause. Check whether data volume has grown past the point where the previous plan made sense. Check whether statistics are stale, which makes the optimiser estimate badly. Check whether an index was dropped or disabled by a recent release. Then check for blocking, since a query waiting on a lock looks slow without being slow. Those four cover the large majority.

What they are assessing

Practical tracing.

Model answer

Capture the queries issued during that single action, through the database's own trace or extended events, the application's SQL logging, or an APM trace. Then look at the count and the individual durations. Very often the answer is not one slow query but hundreds of fast ones, which is the N plus one pattern. If it is one query, take its plan. Doing this for a single user action is cheap and is one of the highest value things a tester can do that nobody asks for.

What they are assessing

Careful reasoning about a contradiction.

Model answer

Either the constraint does not cover what you assume or the duplicates are not duplicates by its definition. Common explanations are that the constraint is on a different column combination than the business key, case or whitespace differences making two values distinct to the database and identical to a human, nulls, since many platforms allow multiple nulls in a unique column, or the constraint being disabled or created as non enforced. I would check the actual constraint definition first rather than the assumption about it.

Likely follow-up

Two rows differ only by a trailing space. Is that a data defect or an application defect?

What they are assessing

Environment reasoning.

Model answer

Compare the schema first, since a missing constraint, a different default or an absent trigger explains most of these. Then compare configuration: collation, timezone, character set and any database level settings affecting comparison or rounding. Then the data itself, since production contains values test data does not. Then the code path, since environments can run different versions. Schema and collation differences account for the majority, and both are worth checking before anyone starts debugging application logic.

What they are assessing

A classic SQL puzzle.

Model answer

Several ways, and knowing more than one is the point. With a window function: SELECT value FROM (SELECT value, DENSE_RANK() OVER (ORDER BY value DESC) r FROM t) x WHERE r = 2. With a subquery: SELECT MAX(value) FROM t WHERE value < (SELECT MAX(value) FROM t). Or with OFFSET and FETCH on platforms that support it. The subtlety the question is usually testing is ties: DENSE_RANK treats equal values as the same rank, ROW_NUMBER does not, and the right choice depends on what second highest means to the business.

What they are assessing

Completeness verification.

Model answer

Define eligibility as a query, run it before the job to get the expected set, then after the job confirm every one of those records reached the expected state and that nothing outside the set was touched. Checking only the processed count is insufficient, because a job that processes the wrong hundred records has the right count. The other check worth running is whether any record became eligible during the run, since those are the ones that fall between the selection and the processing.

What they are assessing

Attention to metadata.

Model answer

Columns the system maintains rather than the user supplying: created and modified timestamps, created and modified by, and sometimes a version number or a row hash. Testing them means confirming created values are set on insert and never change afterwards, modified values change on every update and only on update, and the user recorded is the actual acting user rather than a service account, which is a frequent defect when an action passes through a background job.

What they are assessing

A hard real world scenario.

Model answer

By failing each side deliberately and verifying the outcome. Two phase commit is uncommon in modern architectures, so the usual pattern is eventual consistency with compensating actions: the first system commits, publishes an event, and the second acts on it. The cases to test are the second system being down when the event is published, the event being delivered twice, and the event arriving out of order. What you verify is that the compensation or retry leaves both systems consistent, and that duplicate delivery does not double the effect.

What they are assessing

Volume testing method.

Model answer

Load production-like volume rather than a sample, because behaviour is non linear: a query that is fine at a million rows can fall over at fifty million when a plan changes or a sort spills to disk. Then measure the slowest user journeys, the batch jobs against their windows, and index maintenance time. It also exposes storage growth and backup duration, which are operational rather than functional but real. Testing at ten per cent of volume and extrapolating is the mistake, because the failure is usually a cliff rather than a slope.

What they are assessing

Transaction interaction.

Model answer

On most platforms the trigger runs inside the transaction of the statement that fired it, so a failure rolls back both the trigger's work and the original operation. That is usually the desired behaviour, but it produces confusing symptoms: a user saves a record, gets an error referencing a table they have never heard of, and the save appears to do nothing. Testing it means deliberately causing the trigger to fail, confirming the original operation was rolled back, and checking the error surfaced to the user is comprehensible.

What they are assessing

Practical data generation.

Model answer

By generating rather than copying, using a tool or a script that respects the schema and the relationships, so foreign keys resolve and constraints pass. The important property is realistic distribution rather than uniform randomness: real data is skewed, with a small number of very active customers and many inactive ones, and a uniformly distributed data set produces query plans and performance that will not match production. Seeding directly into the database is far faster than creating through the API for volume.

What they are assessing

Practical drift detection.

Model answer

With a schema comparison tool where one is available, or by querying the information schema on both and diffing the results, which works anywhere. The things to compare are tables and columns with types and nullability, constraints, indexes, and routine definitions. The value is catching drift before a release rather than after: a constraint that exists in test and not in production, or an index present in one, explains a whole class of works here and fails there problems.

What they are assessing

The half of deployment testing usually skipped.

Model answer

By actually running it, on a copy with representative data, after running the forward migration. Confirm the schema returns to its previous shape, that no data added by the new version is lost in a way that matters, and that the previous application version works against the rolled back schema. The reason this matters is that rollbacks are executed under pressure during an incident, and discovering then that the script has never been run is a bad moment. It is also the check most often signed off untested.

What they are assessing

Precision with compliance terms.

Model answer

Masking replaces sensitive values with realistic substitutes while usually preserving the ability to link records, and it may be reversible depending on the technique. Anonymisation removes the ability to identify an individual irreversibly, which under data protection law means the data ceases to be personal data. Pseudonymisation sits between them: identifiers replaced but re-identification possible with a separate key, and it remains personal data. The distinction matters because only genuine anonymisation removes the compliance obligation.

What they are assessing

End to end reconciliation.

Model answer

Recompute the figure independently with a query written from the specification rather than from the report's own SQL, then compare. Where they differ, decompose: check each contributing table's count and sum separately to localise the difference. The frequent causes are a join inflating rows, a filter applied at the wrong point so it excludes more than intended, a date boundary using a different definition of the period, and null handling in an aggregate. Most reporting disagreements turn out to be definitional rather than defects.

What they are assessing

Whether you adjust your approach to the system.

Model answer

OLTP testing focuses on transactional correctness, concurrency, constraint enforcement and response time for small operations, so isolation levels and locking matter. OLAP testing focuses on aggregation correctness, data completeness against source, and query performance across large scans, so grain and reconciliation matter. Running a heavy analytical query against an OLTP system is itself a defect worth raising, because it locks tables and degrades the transactional workload.

What they are assessing

Investigative rigour.

Model answer

Through the audit trail: created and modified timestamps, the recorded user, and any application transaction identifier stored with the row. Correlate the timestamp against application logs to find the request that produced it. If audit columns show a database account rather than an application user, it was a direct change. Where no audit exists, database logs or change data capture may cover it. The broader point is that if this cannot be answered, that is itself a finding worth raising.

What they are assessing

Practical NoSQL testing.

Model answer

Validate document structure explicitly, since nothing enforces it: required fields present, types correct, and no unexpected variation across documents in the same collection, which a profiling query over a sample will reveal. Test the indexes, because query performance depends on them as much as in a relational store. Test referential consistency between collections manually. And test the schema evolution path, since documents written by an older application version coexist with newer ones and the code must handle both.

What they are assessing

Sustainability.

Model answer

Express each check as SQL returning zero rows when correct, then schedule them so they run continuously rather than at release time. Orphan checks, duplicate business keys, mandatory fields null, values outside permitted ranges, and cross table totals that must agree. Alert when a check returns rows, with a threshold so a single expected exception does not cause noise. The key property is that adding a new check is cheap, because the set needs to grow as new defect classes are found.

What they are assessing

Nuance about isolation.

Model answer

A dirty read is seeing another transaction's uncommitted change, which may then be rolled back, so you acted on data that never existed. You would allow it only where approximate results are acceptable and blocking is unacceptable, such as a rough count on a dashboard or a long analytical query where taking locks would harm the transactional workload. It is never acceptable for anything driving a decision or a financial figure, and seeing a read uncommitted hint on a report that feeds a business decision is worth raising.

What they are assessing

Change verification.

Model answer

Measure the intended improvement, then check the costs. Write performance on the affected table, since every insert and update now maintains another structure, which matters on a high volume table. Storage growth. And whether any other query's plan changed, because adding an index can cause the optimiser to choose differently elsewhere, occasionally for the worse. Also confirm a unique index addition did not fail silently or, worse, succeed and reject legitimate data the application previously permitted.

What they are assessing

Leadership scope.

Model answer

Start with the pain: usually tests failing because someone consumed the accounts, or an environment refresh wiping data people depended on. Fix that first by making tests create and clean their own data, which removes the dependency on a shared fixture. Then a masked refresh process from production on a schedule, so realism is maintained without manual copying. Then a provisioning mechanism for data that must be reserved, once parallelism makes collisions frequent. Tooling last, because the practice matters more than the product.

What they are assessing

A considered view on the skill.

Model answer

Enough to verify independently, which is more than most job descriptions imply. That means comfortable joins including outer joins, grouping and aggregation, subqueries and EXISTS, set operations for comparison, and ideally window functions, since they make historical and sequential checks far easier. Reading an execution plan well enough to spot a table scan is valuable. What is not needed is tuning, administration or writing production queries; the goal is being able to answer is the data right without asking a developer.

What they are assessing

Judgement about correction, not just detection.

Model answer

Establish the blast radius first: which rows, which period, and critically who consumed the data, since a figure used in an external report or a regulatory submission changes the response entirely. Stop the ongoing cause before correcting history, or you fix and re-break. Preserve the incorrect state before overwriting, because audit may need it. Then agree the correction approach with the business rather than deciding unilaterally, and communicate before changing numbers people have already reported on. Silently correcting is usually the bigger mistake.

Likely follow-up

The wrong figures were in a board pack. Does that change your approach?