Verify a Record Is Inserted Correctly

After performing an action such as a form submission or an API call, verify that the record has been inserted correctly by running a SELECT query using a unique value (such as an email or ID).

Interview Answer

"After performing an insert operation through the UI or API, I execute a SELECT query using a unique identifier such as an email or ID. I verify that the record exists and that all column values match the expected data. If the record is missing or contains incorrect values, it indicates an issue in the backend or the UI-to-database flow."

Example Query

SELECT *
FROM users
WHERE email = 'testuser@gmail.com';

Verification Checklist

• Record exists in the database.
• All column values are correct.
• Data types are correct.
• Default values are populated correctly.
• No duplicate records are created.

Real-Time Example

In one project, after completing a payment through the application, I queried the payments table to verify that the payment amount, transaction ID, and payment status matched the values displayed in the UI.

Advertisement

Validate Backend Data Against the UI

Backend validation ensures that the information displayed in the UI matches the data stored in the database.

Interview Answer

"After performing an operation in the application, I query the corresponding database table and compare the stored values with those displayed in the UI. This helps identify backend data inconsistencies and display-related issues."

Validation Process

User Action
      │
      ▼
Application
      │
      ▼
Database
      │
      ▼
Run SQL Query
      │
      ▼
Compare with UI

Example

UI shows:

Payment Amount : ₹500

Database query:

SELECT amount
FROM payments
WHERE payment_id = 1001;

Expected:

500

Why It Matters

• Detects backend defects.
• Verifies data consistency.
• Ensures UI displays accurate information.
• Validates business logic.

Fetch Test Data Before Testing

Before executing test cases, existing data is often retrieved from the database to use as valid test input.

Interview Answer

"Before executing test cases, I retrieve valid data such as active users or existing orders from the database. This ensures the test uses realistic and valid input instead of hardcoded or invalid values."

Example Queries

SELECT *
FROM test_cases
WHERE module_name = 'Login';

SELECT *
FROM users
WHERE status = 'active';

Benefits

• Uses valid production-like data.
• Reduces test failures.
• Supports data-driven testing.
• Avoids invalid input values.

Real-Time Example

Before testing the Login API, I retrieved an active user from the database and used those credentials for authentication testing.

Join User and Bug Tables for a User-Wise Bug Count

To determine how many bugs are assigned to each user, join the users and bugs tables and group the results.

Interview Answer

"To generate a user-wise bug count, I join the users and bugs tables using the user ID and group the results by user name. I generally use a LEFT JOIN so that users without bugs are also included."

SQL Query

SELECT
    u.user_name,
    COUNT(b.bug_id) AS bug_count
FROM users u
LEFT JOIN bugs b
       ON u.user_id = b.user_id
GROUP BY u.user_name;

Why Use LEFT JOIN?

• Includes users with zero bugs.
• Returns a complete user list.
• Useful for reporting dashboards.

Result Example

Check Foreign Key Data Before Inserting

Before inserting a child record, verify that the referenced parent record exists.

This maintains referential integrity.

Interview Answer

"Before inserting records containing foreign keys, I verify that the parent record already exists. This prevents foreign key constraint violations and ensures data integrity."

Example Query

SELECT *
FROM users
WHERE user_id = 101;

Workflow

Parent Record Exists?
       │
   Yes │ No
       ▼
Insert Child Record

Example

Before inserting an order:

orders.user_id = 101

Verify:

SELECT *
FROM users
WHERE user_id = 101;

Perform Database Testing Manually

Manual database testing validates data directly in the database after performing application operations.

Interview Answer

"During manual database testing, I understand the database schema, perform actions through the UI or API, execute SQL queries to validate inserts, updates, and deletes, verify relationships using JOINs, and confirm constraints and business rules."

Manual Database Testing Process

Understand Requirement
         │
         ▼
Study Database Schema
         │
         ▼
Perform UI/API Action
         │
         ▼
Run SQL Queries
         │
         ▼
Validate Results

Typical Activities

• Verify Inserts
• Verify Updates
• Verify Deletes
• Validate Constraints
• Verify Foreign Keys
• Validate Business Rules

Common Tools

• MySQL Workbench
• SQL Server Management Studio (SSMS)
• DBeaver
• Oracle SQL Developer

Testing When Data Is Spread Across Multiple Tables

Large applications often store related data across multiple tables.

Testing requires retrieving related data using SQL JOINs.

Interview Answer

"When application data is distributed across multiple tables, I analyze the schema, identify table relationships, use appropriate JOINs, and validate data consistency across all related tables."

Example Query

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;

Validation Checklist

• Primary key relationships
• Foreign key mapping
• NULL handling
• Duplicate records
• Data consistency

Workflow

Multiple Tables
       │
       ▼
Identify Relationships
       │
       ▼
Write JOIN Query
       │
       ▼
Validate Data

Writing Complex Queries Without Developer Help

A good QA Engineer should be able to write SQL queries independently.

Interview Answer

"I first analyze the database schema, identify table relationships, and then write SQL queries using JOINs, subqueries, aggregate functions, GROUP BY, and HAVING to validate business requirements without depending on developers."

Example Query

SELECT u.user_name
FROM users u
JOIN orders o
     ON u.user_id = o.user_id
LEFT JOIN activation_logs a
     ON u.user_id = a.user_id
WHERE a.user_id IS NULL;

SQL Features Commonly Used

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• GROUP BY
• HAVING
• Aggregate Functions
• Subqueries
• Nested Queries

Real-Time Example

I wrote a SQL query to identify customers who purchased a product but never activated it. This helped identify backend synchronization issues.

Challenges Faced in Backend Testing

Backend testing often exposes issues that are not visible in the UI.

Interview Answer

"Some common backend testing challenges include data inconsistencies, missing logs, environment mismatches, and authentication issues. I resolve them by validating data with SQL queries, analyzing logs, verifying environments, and checking authentication headers."

Common Challenges

Overall Approach

Execute Test
      │
      ▼
Validate Database
      │
      ▼
Check Logs
      │
      ▼
Verify Environment
      │
      ▼
Identify Root Cause

Real-Time Example

During API testing, the UI displayed successful order creation, but no record existed in the database. SQL validation revealed that the backend transaction had failed, helping the development team quickly identify the issue.

Frequently Asked Questions (FAQs)

1. How do you verify that a record is inserted correctly?

After performing the insert operation, execute a SELECT query using a unique identifier such as an ID or email. Verify that the record exists and that all column values match the expected data.

2. How do you validate backend data against the UI?

Perform the required action in the application, retrieve the corresponding record from the database using SQL, and compare the database values with those displayed in the UI to ensure complete consistency.

3. How do you get a user-wise bug count?

Join the users and bugs tables and group the results by user.

SELECT
    u.user_name,
    COUNT(b.bug_id) AS bug_count
FROM users u
LEFT JOIN bugs b
       ON u.user_id = b.assigned_to
GROUP BY u.user_name;

A LEFT JOIN ensures that users with zero assigned bugs are also included.

4. How do you check foreign key data before inserting?

Before inserting a child record, query the parent table to verify that the referenced record exists. This maintains referential integrity and prevents foreign key constraint violations.

5. How do you perform database testing manually?

The typical process is:

1. Understand the database schema.
2. Perform the required action in the application.
3. Execute SQL queries to verify inserts, updates, and deletes.
4. Use JOIN queries to validate related data.
5. Verify constraints, foreign keys, data types, and business rules.

6. How do you test when data is spread across multiple tables?

Use SQL JOIN operations based on primary and foreign key relationships to retrieve related data from multiple tables, then validate consistency and accuracy across all tables.

7. What challenges have you faced in backend testing?

Common challenges include:

• Data inconsistencies between the UI and database.
• Missing or insufficient logs.
• Environment mismatches.
• Missing authentication headers.

These are resolved by validating SQL data, analyzing logs, verifying environment configuration, and checking request headers before testing.