Question 1

What is database testing?

Accepted Answer

Database testing verifies that data is stored, updated and retrieved correctly, and that the schema, constraints, triggers, stored procedures and transactions behave as expected. It ensures what the application shows matches what is actually persisted.

Question 2

Why should QA test the database if the UI already works?

Accepted Answer

The UI can show success while the backend saves wrong, duplicate or partial data. Checking the database catches silent data corruption, wrong defaults and missing audit records that UI tests cannot see.

Question 3

What is data integrity and what types are there?

Accepted Answer

Data integrity means data stays accurate and consistent over its lifecycle. The main types are entity integrity via primary keys, referential integrity via foreign keys, and domain integrity via data types, CHECK, NOT NULL and default constraints.

Question 4

Which constraints do you validate and how?

Accepted Answer

I validate PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT constraints by trying to insert invalid data and confirming the database rejects it, and by inserting without optional values to confirm defaults apply.

Question 5

What is the difference between a primary key, foreign key and unique key?

Accepted Answer

A primary key uniquely identifies each row and cannot be null, with one per table. A unique key also prevents duplicates but a table can have many and most databases allow NULLs. A foreign key references a primary or unique key in another table to enforce relationships.

Question 6

How do you validate CRUD operations in the database?

Accepted Answer

For each operation performed in the UI or API, I query the relevant tables to confirm the row was created, updated with the right values, soft- or hard-deleted, and that related tables and audit columns like updated_at changed correctly.

Question 7

What is a transaction and what do COMMIT and ROLLBACK do?

Accepted Answer

A transaction is a group of operations treated as a single unit of work. COMMIT makes all changes permanent, and ROLLBACK undoes every change since the transaction began, so data never stays half-updated.

Question 8

Explain ACID properties with an example.

Accepted Answer

ACID stands for Atomicity, Consistency, Isolation and Durability. In a bank transfer, atomicity means both debit and credit happen or neither does, consistency keeps balances valid, isolation stops concurrent transfers from seeing partial state, and durability keeps the committed transfer after a crash.

Question 9

What are transaction isolation levels and the anomalies they prevent?

Accepted Answer

The levels are Read Uncommitted, Read Committed, Repeatable Read and Serializable. Higher levels prevent more anomalies: dirty reads, non-repeatable reads and phantom reads, at the cost of concurrency.

Question 10

Scenario: how do you validate that data shown on the UI matches the database?

Accepted Answer

I capture the values from the UI using the automation tool, run the equivalent SQL query through JDBC or a DB library, and compare them field by field, normalizing formats like dates, currency and rounding before asserting.

Question 11

How do you connect to a database in a Java automation framework?

Accepted Answer

I add the JDBC driver dependency, create a connection with DriverManager or a pooled DataSource such as HikariCP, use PreparedStatement for parameterized queries, and close resources with try-with-resources.

Question 12

How do you run DB validations in Python?

Accepted Answer

I use a DB-API driver such as psycopg for PostgreSQL or PyMySQL for MySQL, often wrapped in a pytest fixture, and run parameterized queries with placeholders instead of string formatting.

Question 13

How do you manage test data for database-heavy tests?

Accepted Answer

I create test data per test through APIs or seed scripts, use unique identifiers to avoid collisions, clean up in teardown or roll back transactions, and use masked or synthetic data instead of raw production data.

Question 14

How do you test stored procedures and triggers?

Accepted Answer

For stored procedures I call them with valid, invalid, null and boundary parameters and verify output parameters, result sets and table changes. For triggers I perform the triggering insert, update or delete and confirm the expected side effect, such as an audit row, happened exactly once.

Question 15

Scenario: a release includes a schema migration. How do you test it?

Accepted Answer

I run the migration on a copy of production-like data, verify the schema changes, compare row counts and checksums before and after, validate transformed values, confirm the application works on the new schema, and test the rollback script.

Question 16

How do you validate data moved between two systems (ETL or data migration)?

Accepted Answer

I reconcile source and target with row counts, aggregates and column-level checksums, then use set operations like EXCEPT to find rows that are missing or different, and spot-check transformation rules and duplicates.

Question 17

Scenario: two users book the last seat at the same time. How do you test it?

Accepted Answer

I fire concurrent booking requests in parallel and verify in the database that only one booking succeeded, the seat count never goes negative, and the other user gets a clean error. This checks locking such as SELECT ... FOR UPDATE or optimistic version columns.

Question 18

How do you identify database performance problems as a tester?

Accepted Answer

I look at slow query logs and use EXPLAIN or EXPLAIN ANALYZE to check for full table scans, missing indexes and bad join orders, and I test with production-sized data because small test datasets hide these issues.

Question 19

What database security and recovery checks would you include?

Accepted Answer

I verify least-privilege roles, that sensitive fields like passwords are hashed and card or PII data is encrypted or masked, that inputs are safe from SQL injection, and that backups can actually be restored within the agreed recovery time.

Question 20

What are the types of database testing?

Accepted Answer

Database testing is usually split into structural testing (schema, tables, columns, keys, indexes, procedures, triggers), functional testing (data stored and retrieved correctly through business operations), and non-functional testing (performance, load, security, backup and recovery).

Question 21

What is schema testing and what do you check?

Accepted Answer

Schema testing verifies the database structure matches the agreed design: table and column names, data types, lengths, nullability, defaults, keys, constraints and indexes. I usually query the information_schema or system catalog and compare it with the expected definition.

Question 22

What is an index and why does it matter for testing?

Accepted Answer

An index is a separate data structure, typically a B-tree, that lets the database find rows without scanning the whole table, like a book's index. It speeds up reads but adds storage and slows inserts and updates, so testers check that key queries use the right indexes.

Question 23

What is referential integrity and how do you test ON DELETE CASCADE?

Accepted Answer

Referential integrity means every foreign key value points to an existing parent row. To test cascade behaviour I delete a parent and confirm child rows are deleted, set to NULL or the delete is blocked, exactly as the design specifies.

Question 24

How do you test NULL handling and default values?

Accepted Answer

I insert records with optional fields omitted and confirm default values like status or created_at are applied, then try NULL in mandatory columns and expect a constraint error. I also check that the application displays NULLs correctly rather than showing 'null' text.

Question 25

How do you validate data types and field lengths at the database level?

Accepted Answer

I compare UI and API field limits with column definitions and push boundary values: maximum length, one character over, special characters, Unicode, large numbers and precision. The database should either store the value exactly or reject it, never silently truncate or round.

Question 26

What is ETL?

Accepted Answer

ETL stands for Extract, Transform, Load: data is pulled from source systems, transformed by cleaning, mapping and applying business rules, then loaded into a target such as a data warehouse. ETL testing ensures nothing is lost, duplicated or wrongly transformed on the way.

Question 27

What is data masking and why is it used in test environments?

Accepted Answer

Data masking replaces sensitive values like names, emails, phone numbers and card numbers with realistic fake values so production-like data can be used safely in lower environments. It keeps formats and relationships intact so tests still behave realistically.

Question 28

What is the difference between SQL and NoSQL databases?

Accepted Answer

SQL databases store data in tables with a fixed schema and support joins and ACID transactions, like PostgreSQL or MySQL. NoSQL databases use flexible models such as documents, key-value, wide-column or graph, like MongoDB, Redis, Cassandra and Neo4j, and often trade strict consistency for scale.

Question 29

How do you test a database view?

Accepted Answer

I run the view and compare its output against the same logic written directly on the base tables, checking columns, filters, joins and row counts. I also verify permissions, since views are often used to hide sensitive columns.

Question 30

What is normalization? Explain 1NF, 2NF and 3NF.

Accepted Answer

Normalization organises tables to reduce redundancy and update anomalies. 1NF requires atomic values with no repeating groups, 2NF removes partial dependency on part of a composite key, and 3NF removes transitive dependency where non-key columns depend on other non-key columns.

Question 31

What are dirty reads, non-repeatable reads and phantom reads?

Accepted Answer

A dirty read sees another transaction's uncommitted change, a non-repeatable read gets a different value when re-reading the same row because another transaction committed an update, and a phantom read sees new or missing rows when re-running the same range query.

Question 32

What is a deadlock and how would you test or handle it?

Accepted Answer

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. The database detects it and kills one as the victim, so I test that the application catches the deadlock error and retries safely rather than failing the user.

Question 33

What is the difference between clustered and non-clustered indexes?

Accepted Answer

A clustered index defines the physical order of rows, so the table data itself is stored in index order and there can be only one. A non-clustered index is a separate structure holding key values and pointers to rows, and a table can have many.

Question 34

How do you test a stored procedure?

Accepted Answer

I treat it like a function: set up known data, call it with valid, boundary, NULL and invalid parameters, then verify output parameters, result sets, table changes and error handling. I also check transaction behaviour, so a failure mid-way rolls everything back.

Question 35

How do you test a trigger?

Accepted Answer

I perform the DML event the trigger listens to, such as INSERT, UPDATE or DELETE, and verify its side effect, like an audit row or recalculated total. I also confirm it does not fire for other events and behaves correctly for multi-row statements.

Question 36

What is the difference between a stored procedure and a function?

Accepted Answer

A function must return a value and can be used inside a SELECT, but generally cannot change data or manage transactions. A stored procedure is called with CALL or EXEC, can return zero or many result sets and output parameters, and can perform DML and transaction control.

Question 37

What checks do you perform in ETL testing?

Accepted Answer

I validate source-to-target row counts, completeness of all expected records, transformation rules against the mapping document, data quality like NULLs, duplicates and formats, and incremental loads that only pick up new or changed records. I also check rejected-record handling and load performance.

Question 38

Which SQL queries do you use for data validation?

Accepted Answer

I use count comparisons, EXCEPT or MINUS to find rows in one set but not the other, GROUP BY HAVING for duplicates, LEFT JOIN IS NULL for orphans, and aggregate checks like SUM and MIN/MAX on key numeric columns.

Question 39

What is denormalization and what risks does it bring for testing?

Accepted Answer

Denormalization deliberately duplicates data, such as storing order_total or customer_name on the order row, to make reads faster. The risk is that copies drift out of sync, so I test that every update path keeps the duplicated values consistent.

Question 40

How do you test soft deletes and audit columns?

Accepted Answer

For soft delete, I delete through the app and check the row still exists with is_deleted or deleted_at set, and that it disappears from all user-facing queries and APIs. For audit columns I verify created_at, created_by, updated_at and updated_by are populated correctly and created values never change on update.

Question 41

What issues do you look for when testing date and time data?

Accepted Answer

I check timestamps are stored consistently, ideally in UTC with time zone info, and converted correctly for display. I test boundaries like midnight, month and year ends, leap years and daylight saving changes, plus date filters that might drop the last day.

Question 42

Scenario: after a user places an order via the API, what do you verify in the database?

Accepted Answer

I check a row exists in orders with the right customer, status and total, matching rows in order_items with correct quantities and prices, inventory reduced, a payment record linked, and audit timestamps set. If payment fails, nothing should be committed or the order should be marked failed as designed.

Question 43

How do you test schema migration scripts like Flyway or Liquibase changes?

Accepted Answer

I apply migrations on a fresh database and on a copy of production-like data, confirm the schema matches expectations, existing data is preserved or transformed correctly, and the application works. I also test the rollback or down migration and that the migration is safe to run on large tables.

Question 44

What is optimistic vs pessimistic locking and how do you test each?

Accepted Answer

Pessimistic locking locks rows up front, for example with SELECT ... FOR UPDATE, so other writers wait. Optimistic locking lets everyone read and checks a version column at update time, so the second writer's update affects zero rows and must be rejected or retried.

Question 45

What is MVCC?

Accepted Answer

Multi-Version Concurrency Control keeps multiple versions of a row so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL, Oracle and MySQL InnoDB use it to implement their isolation levels.

Question 46

What are Slowly Changing Dimensions and how do you test SCD Type 2?

Accepted Answer

SCDs describe how a warehouse handles changing attributes: Type 1 overwrites the old value, while Type 2 keeps history by closing the old row and inserting a new one. For Type 2 I change a source attribute and check the old row gets an end date and is_current = false, the new row starts with is_current = true, and only one current row exists per business key.

Question 47

How do you run database tests reliably in CI?

Accepted Answer

I spin up a real, disposable database per test run using Docker or Testcontainers, apply migrations, seed known data, and run the tests in isolation, either rolling back each test's transaction or truncating tables. This avoids shared-environment flakiness and in-memory databases behaving differently from production.

Question 48

Scenario: data written to the primary DB is missing on the read screen. What do you check?

Accepted Answer

I would suspect replication lag: writes go to the primary but reads hit a replica that has not caught up. I check replica lag metrics, whether the app reads its own writes from the primary, caching layers, and for CDC pipelines whether the event was captured and applied downstream.

Question 49

What are partitioning and sharding, and what do you test?

Accepted Answer

Partitioning splits a large table into pieces within one database, for example by month, while sharding spreads data across multiple database servers by a shard key. I test that rows land in the right partition or shard, queries prune to the right partitions, and cross-shard queries, joins and transactions behave correctly.

Question 50

How is testing a NoSQL database like MongoDB different?

Accepted Answer

Because the schema is flexible, I validate document structure and required fields explicitly, often with JSON Schema validators, and check embedded versus referenced data stays consistent. I also test indexes, eventual consistency across replicas, and write and read concern settings that affect durability.

Interview Questions

01What is database testing?BeginnerBasics

02Why should QA test the database if the UI already works?BeginnerBasics

03What is data integrity and what types are there?BeginnerIntegrity & Constraints

04Which constraints do you validate and how?BeginnerIntegrity & Constraints

05What is the difference between a primary key, foreign key and unique key?BeginnerIntegrity & Constraints

06How do you validate CRUD operations in the database?BeginnerUI/API vs DB Validation

07What is a transaction and what do COMMIT and ROLLBACK do?BeginnerTransactions & ACID

08Explain ACID properties with an example.IntermediateTransactions & ACID

09What are transaction isolation levels and the anomalies they prevent?IntermediateTransactions & ACID

10Scenario: how do you validate that data shown on the UI matches the database?IntermediateUI/API vs DB Validation

11How do you connect to a database in a Java automation framework?IntermediateAutomation: JDBC & Python

12How do you run DB validations in Python?IntermediateAutomation: JDBC & Python

13How do you manage test data for database-heavy tests?IntermediateTest Data Management

14How do you test stored procedures and triggers?IntermediateBasics

15Scenario: a release includes a schema migration. How do you test it?AdvancedMigrations & Performance