Mockpreps
🧑💼
🪴
Login now and master your exams with Mockpreps’ mock tests!
Top Interview Questions
Database Testing is a type of software testing that focuses on verifying the integrity, consistency, reliability, and performance of databases. It ensures that the backend database of an application functions correctly and efficiently while maintaining the accuracy of stored data. Unlike functional testing, which primarily validates the user interface (UI) and business logic, database testing focuses on the data layer of an application, making it crucial for data-driven systems.
Databases are the backbone of modern applications, storing vital information like user details, transactions, and logs. Any failure in the database layer can lead to critical issues, including data corruption, loss, or performance degradation. Therefore, database testing is integral to quality assurance (QA) processes.
The significance of database testing can’t be overstated. Here are some key reasons why organizations invest in this testing:
Data Accuracy and Integrity:
Database testing ensures that the data stored is accurate, consistent, and not corrupted during operations like insert, update, or delete. This is particularly critical in banking, healthcare, and e-commerce systems.
Validation of Data Relationships:
Databases often have complex relationships, such as foreign keys, primary keys, and constraints. Testing ensures that these relationships are correctly implemented and maintained.
Ensures Proper Functionality of Triggers and Stored Procedures:
Modern databases rely heavily on stored procedures, triggers, and functions. Database testing ensures these execute correctly under all scenarios.
Performance Optimization:
It identifies issues like slow queries, improper indexing, or inefficient stored procedures, which can negatively impact system performance.
Prevention of Data Loss:
By testing backup and recovery mechanisms, database testing ensures data is secure and recoverable during failures or disasters.
Regulatory Compliance:
Industries like finance and healthcare are subject to regulations regarding data integrity and privacy. Database testing helps maintain compliance with standards such as GDPR, HIPAA, and PCI-DSS.
Database testing is not a single technique but a collection of methods that validate different aspects of database behavior. These types include:
Structural Testing:
This focuses on testing the database schema, tables, columns, indexes, constraints, triggers, and stored procedures. The goal is to verify that the database structure meets design specifications.
Functional Testing:
Functional database testing ensures that all database operations, such as Create, Read, Update, Delete (CRUD), are working correctly. It also validates business rules implemented in stored procedures and triggers.
Data Integrity Testing:
Data integrity testing checks whether the data in the database is accurate and consistent. It validates relationships among tables, constraints, and the integrity of transactions.
Performance Testing:
Performance testing evaluates the database’s efficiency and response time under various loads. It checks for query optimization, indexing, and the ability to handle concurrent user access.
Security Testing:
Security testing ensures that the database is protected against unauthorized access, SQL injection, and data leaks. It also validates user roles and privileges.
Regression Testing:
After any changes, updates, or migrations, regression testing ensures that the database functionalities continue to work as expected without introducing new issues.
Database testing uses a variety of techniques to validate different aspects of the database:
SQL Queries Validation:
Testers use SQL queries to validate data correctness. For example, verifying if the total sales in the database match the expected reports.
Data Verification:
Compare data in the database against external files, UI output, or business reports to ensure consistency.
Stored Procedures & Triggers Testing:
Execute stored procedures and triggers with different inputs to check if they perform the expected operations.
Transaction Testing:
Ensure that transactions are committed or rolled back correctly to maintain database consistency. This is vital in financial or multi-step operations.
Boundary Value Analysis:
Testers insert extreme values into database fields to check how the system handles them. This ensures robust error handling and validation mechanisms.
Data Migration Testing:
During database migration or upgrade, data must be validated to ensure no loss or corruption occurs. Data mapping between old and new databases is critical.
Automated Testing:
Automation frameworks can execute repetitive SQL queries, perform regression tests, and validate data consistency efficiently.
Several tools are widely used in the industry for database testing. These tools help automate queries, manage test cases, and generate reports. Popular ones include:
SQL Server Management Studio (SSMS):
A tool for SQL Server databases to write and execute SQL queries, check constraints, and manage database objects.
Oracle SQL Developer:
Used for Oracle databases to perform queries, validate stored procedures, and manage schema structures.
DbUnit:
A JUnit extension for database-driven projects, enabling automated testing of database operations in Java applications.
Selenium + JDBC Integration:
Selenium can interact with the UI, while JDBC handles backend database validation to perform end-to-end testing.
Toad for Oracle/SQL Server:
A comprehensive tool for database development and testing, including performance monitoring and query optimization.
QuerySurge:
Designed for automated data testing and validation, especially useful for ETL (Extract, Transform, Load) testing.
While database testing is crucial, it comes with its own set of challenges:
Complexity of Database Schema:
Large databases with hundreds of tables and relationships can be difficult to validate comprehensively.
Handling Large Volumes of Data:
Testing with massive datasets can be slow and may require advanced strategies like sampling or automation.
Dynamic Data Issues:
In real-time applications, the database constantly changes, making it hard to verify data correctness at every moment.
Performance Bottlenecks:
Identifying slow queries or index issues can require advanced profiling and monitoring tools.
Integration with UI Testing:
Data displayed on the frontend must be consistent with backend database values, requiring synchronized testing.
Migration & Backup Validation:
Ensuring no data is lost during migration or recovery requires meticulous testing and validation.
To ensure effective and efficient database testing, QA teams should follow best practices:
Understand the Database Design:
Review ER diagrams, table relationships, and business rules before testing.
Prioritize Critical Tables & Data:
Focus on tables that store sensitive or frequently accessed data first.
Use Automated Testing Tools:
Automate repetitive tasks like SQL validation, regression testing, and data integrity checks.
Create Test Data Carefully:
Use realistic data that mimics production scenarios for accurate testing.
Validate Data at Multiple Levels:
Check data in the database, API layer, and UI to ensure consistency.
Include Performance & Security Testing:
Ensure that queries are optimized and the database is protected against unauthorized access.
Maintain Documentation:
Document test cases, scripts, and results for future audits, migration, and regression purposes.
Database testing is an essential component of modern software quality assurance. It ensures data integrity, performance, security, and reliability, which are crucial for any application. From validating stored procedures and triggers to testing large-scale data migrations, this testing approach protects businesses from costly data errors, performance bottlenecks, and security breaches.
In today’s data-driven world, where applications rely on accurate, secure, and fast-access information, database testing is not optional—it’s mandatory. By following structured testing techniques, leveraging automation tools, and adhering to best practices, organizations can ensure that their databases are robust, efficient, and reliable.
Answer:
Database Testing is the process of verifying the schema, tables, triggers, procedures, functions, and data integrity in a database. It ensures that the database works as expected after operations like CRUD (Create, Read, Update, Delete).
Answer:
Ensures data integrity and accuracy.
Validates business rules at the database level.
Detects performance issues like slow queries.
Ensures relationships between tables are maintained correctly.
Answer:
Data Integrity Testing: Verifies correctness and consistency of data.
Data Validity Testing: Checks if data entered is within valid ranges or formats.
Database Performance Testing: Ensures queries run efficiently under load.
Stored Procedures Testing: Validates business logic in procedures/functions.
Trigger Testing: Ensures triggers fire correctly when DML operations occur.
Security Testing: Ensures access restrictions, privileges, and roles are correctly implemented.
Answer:
ACID ensures reliable transactions:
Atomicity: Transaction is all or nothing.
Consistency: Database must move from one valid state to another.
Isolation: Transactions are independent of each other.
Durability: Committed transactions persist even after system failure.
Answer:
A primary key is a unique identifier for each record in a table. It cannot be null and ensures entity integrity.
Answer:
A foreign key establishes a relationship between two tables. It references the primary key of another table to maintain referential integrity.
| Feature | Primary Key | Unique Key |
|---|---|---|
| Null allowed | No | Yes (one null) |
| Uniqueness | Yes | Yes |
| Number per table | One | Multiple |
Answer:
Normalization organizes data to reduce redundancy and improve integrity.
1NF: Eliminate repeating groups.
2NF: Remove partial dependency.
3NF: Remove transitive dependency.
Answer:
Denormalization combines tables to improve query performance, often at the cost of redundancy.
| Feature | OLTP | OLAP |
|---|---|---|
| Purpose | Transactional processing | Analytical processing |
| Data | Current, detailed | Historical, summarized |
| Queries | Simple, fast | Complex, heavy |
| Example | Banking transactions | Business intelligence reports |
Answer:
SQL (Structured Query Language) is used to communicate with relational databases. It allows querying, updating, and managing data.
| Command Type | Description | Examples |
|---|---|---|
| DML (Data Manipulation) | Deals with data | INSERT, UPDATE, DELETE |
| DDL (Data Definition) | Defines schema | CREATE, ALTER, DROP |
| DCL (Data Control) | Manages permissions | GRANT, REVOKE |
| TCL (Transaction Control) | Manages transactions | COMMIT, ROLLBACK, SAVEPOINT |
Answer:
Check primary key, foreign key, and unique key constraints.
Verify triggers and stored procedures.
Validate data after CRUD operations.
Compare front-end data with back-end database data.
Answer:
A stored procedure is a precompiled SQL program stored in the database. It can accept parameters, perform operations, and return results.
Answer:
Verify input/output parameters.
Execute with valid and invalid data.
Check for proper handling of exceptions.
Ensure business logic produces correct results.
Answer:
A trigger is a database object that automatically executes a specified action when an event occurs, such as INSERT, UPDATE, or DELETE.
Answer:
Insert/update/delete data and check if trigger fires.
Validate that the trigger executes intended logic.
Check boundary cases (null, duplicates).
| Join Type | Description |
|---|---|
| Inner Join | Returns matching records only |
| Left Outer Join | Returns all records from left table + matches from right |
| Right Outer Join | Returns all from right + matches from left |
| Full Outer Join | Returns all records from both tables |
Answer:
A view is a virtual table based on the result of a SQL query. It does not store data physically but can simplify complex queries.
Answer:
Compare view output with expected results.
Verify joins, filters, and aggregations in view query.
Test with boundary and invalid inputs.
Answer:
Indexes are database objects that improve query performance by allowing faster data retrieval.
Answer:
A composite key is a combination of two or more columns to uniquely identify a record in a table.
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Deletes data | Yes | Yes |
| Can filter rows | Yes (WHERE) | No |
| Rollback | Yes | Usually No |
| Triggers fire | Yes | No |
| Speed | Slower | Faster |
| Feature | UNION | UNION ALL |
|---|---|---|
| Duplicates | Eliminates | Keeps duplicates |
| Performance | Slower | Faster |
Answer:
Database migration testing ensures data is correctly transferred from legacy systems to new databases without loss, corruption, or mismatch.
Answer:
Row counts match source and target tables.
Validate data types and constraints.
Compare random sample records.
Test stored procedures, triggers, and reports.
Answer:
Aggregate: SUM(), COUNT(), AVG(), MIN(), MAX()
String: UPPER(), LOWER(), CONCAT(), SUBSTRING()
Date: NOW(), CURDATE(), DATEDIFF()
Conversion: CAST(), CONVERT()
Answer:
A cursor allows row-by-row processing of query results, useful for handling complex operations in stored procedures.
Answer:
Execute complex queries and measure response times.
Test under concurrent user load.
Analyze execution plans and indexes.
Identify slow queries and optimize them.
Answer:
A deadlock occurs when two or more transactions are waiting for each other’s resources, causing a standstill.
Answer:
Access resources in a consistent order.
Keep transactions short and simple.
Use proper isolation levels.
Apply row-level locking carefully.
| Feature | Clustered | Non-Clustered |
|---|---|---|
| Storage | Table data stored in index | Separate from table data |
| Only one | Yes | Multiple allowed |
| Performance | Fast retrieval | Slightly slower |
Answer:
A surrogate key is an artificial key (like an auto-increment ID) used as a unique identifier instead of a natural key.
| Feature | CHAR | VARCHAR |
|---|---|---|
| Fixed/Variable | Fixed | Variable |
| Storage | Always same length | Only actual data length |
| Performance | Faster for fixed-length | Flexible for varying length |
Answer:
Referential integrity ensures relationships between tables remain consistent; foreign key values must match primary key values or be null.
Answer:
Try inserting a child record without parent. It should fail.
Try deleting a parent with children; check cascade behavior.
Validate foreign key constraints exist.
Answer:
A transaction is a sequence of SQL statements executed as a single unit, ensuring ACID properties.
Answer:
Commit: Permanently saves changes.
Rollback: Reverts changes to the previous state.
Answer:
Concurrency allows multiple users to access and modify the database simultaneously while maintaining data consistency.
| Type | Description |
|---|---|
| Pessimistic | Locks resources to prevent conflicts |
| Optimistic | Allows concurrent access, checks for conflicts before committing |
Answer:
Compare row counts and checksums.
Validate business rules across environments.
Ensure test data matches schema and constraints.
Run queries to verify migrated or updated data is consistent.
Answer:
A good test case includes:
Test Case ID & Description
Precondition (like test data setup)
Steps to execute SQL query
Expected Result (data integrity, query output)
Actual Result (to log after execution)
Q1: What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?
Answer:
INNER JOIN: Returns only the rows where there is a match in both tables.
LEFT JOIN: Returns all rows from the left table and matched rows from the right table; NULL if no match.
RIGHT JOIN: Returns all rows from the right table and matched rows from the left table; NULL if no match.
FULL OUTER JOIN: Returns all rows when there is a match in either left or right table; unmatched rows will have NULLs.
Q2: Explain UNION vs UNION ALL.
Answer:
UNION: Combines results from two queries and removes duplicates.
UNION ALL: Combines results from two queries and includes duplicates.
Performance-wise, UNION ALL is faster because it doesn’t check for duplicates.
Q3: What is a subquery? Difference between correlated and non-correlated subquery.
Answer:
Subquery: A query nested inside another query.
Non-Correlated Subquery: Independent; executes once and its result is used by the outer query.
Correlated Subquery: Depends on the outer query; executes for each row of the outer query.
Example:
-- Correlated
SELECT e1.name
FROM Employee e1
WHERE e1.salary > (SELECT AVG(e2.salary) FROM Employee e2 WHERE e2.department = e1.department);
Q4: Explain Primary Key vs Unique Key.
Answer:
Primary Key: Uniquely identifies a row; cannot be NULL; one per table.
Unique Key: Enforces uniqueness; can have multiple per table; allows one NULL (in most DBs).
Q5: What are indexes? Explain clustered vs non-clustered index.
Answer:
Index: Improves query performance.
Clustered Index: Sorts the actual data rows; one per table.
Non-Clustered Index: Has a separate structure pointing to data rows; multiple per table.
Q6: How do you write a query to find duplicate records?
Answer:
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
Q7: How do you find the second highest salary in a table?
Answer:
-- Using LIMIT (MySQL)
SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- Using subquery
SELECT MAX(salary)
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
Q8: What is normalization? Why is it important?
Answer:
Normalization organizes data to minimize redundancy and improve integrity.
1NF: Atomic columns
2NF: No partial dependency
3NF: No transitive dependency
BCNF: Stronger form of 3NF
Benefits: Saves storage, reduces anomalies, improves consistency.
Q9: What is denormalization? When would you use it?
Answer:
Denormalization combines tables to reduce joins and improve performance. Used in read-heavy systems like reporting or OLAP.
Q10: Difference between TRUNCATE, DELETE, and DROP.
Answer:
DELETE: Deletes rows; can use WHERE; logs transactions; slower.
TRUNCATE: Deletes all rows; cannot use WHERE; faster; resets identity.
DROP: Deletes table structure permanently.
Q11: How do you validate data after ETL or migration?
Answer:
Compare row counts between source and target.
Verify totals and sums for numeric columns.
Sample data check for specific records.
Column-level validation for data type and constraints.
Check for nulls, duplicates, or mismatched values.
Q12: What is data integrity and how do you test it?
Answer:
Data integrity ensures accuracy, consistency, and reliability.
Testing includes:
Primary/foreign key validation
Referential integrity checks
Business rule validation
Constraint validation
Q13: What are the types of database testing?
Answer:
Data Integrity Testing: Validates consistency.
Data Accuracy Testing: Checks correctness of data after migration.
Data Completeness Testing: Ensures no data loss.
Performance Testing: Query response time, indexing.
Stored Procedure/Trigger Testing
Security Testing: Role-based access, authentication.
Q14: How do you verify null or default values?
Answer:
-- Null values
SELECT * FROM table_name WHERE column_name IS NULL;
-- Default values
SELECT column_name FROM table_name WHERE column_name = default_value;
Q15: How do you test for referential integrity?
Answer:
Ensure foreign key values exist in parent table.
Test by attempting to insert invalid child records (should fail).
Delete parent record and check cascading effect if ON DELETE CASCADE is enabled.
Q16: How do you identify slow-running queries?
Answer:
Use EXPLAIN PLAN to check query execution path.
Check execution time and index usage.
Analyze table scans vs index scans.
Monitor locks and waits.
Q17: What is query optimization?
Answer:
Rewriting queries for efficiency.
Using indexes, avoiding nested subqueries, proper joins, limiting select columns.
Q18: How do you handle deadlocks?
Answer:
Detect deadlocks via DB logs or monitoring tools.
Ensure consistent ordering of transactions.
Use shorter transactions.
Implement retry mechanisms in application.
Q19: Explain database partitioning.
Answer:
Partitioning splits large tables into smaller parts for faster access.
Types: Range, List, Hash, Composite.
Helps in performance, manageability, and archiving.
Q20: How do you test database performance under load?
Answer:
Use tools like JMeter, LoadRunner.
Simulate concurrent users or batch jobs.
Measure response times, CPU, memory, and I/O.
Identify slow queries or locking issues.
Q21: How do you test a stored procedure?
Answer:
Validate inputs/outputs.
Test boundary conditions.
Check error handling.
Verify business logic and transactions.
Q22: Difference between procedure and function?
Answer:
Procedure: May or may not return a value, performs actions.
Function: Always returns a value, used in queries.
Q23: How do you test triggers?
Answer:
Insert/update/delete operations to check trigger firing.
Validate data changes and side effects.
Check performance impact if trigger executes frequently.
Q24: How do you handle errors in stored procedures?
Answer:
Use TRY…CATCH blocks (SQL Server) or DECLARE…EXCEPTION in PL/SQL.
Rollback transactions if necessary.
Log errors for debugging.
Q25: Difference between BEFORE and AFTER triggers.
Answer:
BEFORE Trigger: Executes before DML; can modify input data.
AFTER Trigger: Executes after DML; cannot modify original data.
Q26: How do you test database backup?
Answer:
Perform backup, restore to test environment.
Verify row counts and integrity.
Check backup logs and time taken.
Test incremental/differential backups if used.
Q27: How do you test database recovery after crash?
Answer:
Restore backup on separate server.
Apply transaction logs if available.
Validate data integrity and functionality.
Q28: What are common database security tests?
Answer:
User authentication & role-based access.
Password policies and encryption verification.
SQL injection testing.
Sensitive data masking.
Q29: Difference between database encryption at rest vs in transit?
Answer:
At rest: Data stored on disk is encrypted.
In transit: Data is encrypted while moving across networks (e.g., SSL/TLS).
Q30: How do you validate audit trails?
Answer:
Verify changes logged for each DML operation.
Check timestamps, user info, and changed data.
Ensure logs cannot be tampered.
Q31: How do you validate data after a migration from Oracle to SQL Server?
Answer:
Map data types between systems.
Use checksum or count comparison.
Test business rules and constraints.
Validate triggers and procedures.
Q32: How do you test incremental ETL loads?
Answer:
Verify new/updated/deleted records.
Compare source & target.
Check timestamps or surrogate keys used for incremental load.
Q33: How do you test a column with dynamic calculations?
Answer:
Calculate expected values using formulas.
Compare sample data in DB with expected output.
Test edge cases and null inputs.
Q34: What is ACID property? How do you test it?
Answer:
Atomicity: All-or-nothing transaction
Consistency: Valid state after transaction
Isolation: Transactions do not interfere
Durability: Committed transactions persist
Testing: Simulate failures, concurrent transactions, rollback scenarios.
Q35: How do you verify indexing in a large table?
Answer:
Use EXPLAIN PLAN or query execution stats.
Compare query performance with/without index.
Check fragmentation and rebuild if needed.
Q36: How do you test stored procedure performance?
Answer:
Run with multiple input scenarios.
Measure execution time.
Check query plans inside the procedure.
Optimize queries and indexes.
Q37: How do you test database triggers under load?
Answer:
Simulate high-volume insert/update/delete operations.
Measure trigger execution time.
Monitor for deadlocks or performance impact.
Q38: How do you test database in Agile or CI/CD pipeline?
Answer:
Automate database tests using SQL scripts.
Include data validation, schema checks in CI pipeline.
Use rollback scripts to maintain test environment consistency.
Q39: Explain shadow tables or audit tables. How do you test them?
Answer:
Shadow/audit tables track history of changes.
Test triggers or ETL populating audit tables.
Verify old vs new data, timestamps, user info, and correctness.
Q40: How do you test a complex query with multiple joins?
Answer:
Verify expected row counts.
Use sample data to manually calculate expected output.
Check join conditions, null handling, and performance.
Q41: How do you test cascading deletes or updates?
Answer:
Delete/update a parent record.
Verify child tables are affected according to cascade rules.
Ensure no orphaned records remain.
Q42: Explain how to test historical data or slowly changing dimensions (SCD).
Answer:
Identify SCD Type (1, 2, 3).
Verify insert/update logic in target tables.
Check historical records and current records separately.
