SQL for Testers: 5 Practical Ways to Find Hidden Bugs and Improve Automation

Summary: Learn five practical ways SQL makes testers more effective: validate UI changes at the source, find invisible data bugs with joins, verify complex business logic with advanced queries, diagnose performance issues, and add database assertions to automation for true end-to-end tests.

Introduction: More Than Just a Developer's Tool

When most people hear "SQL," they picture a developer pulling data or a tester running a quick "SELECT *" to check if a record exists. That is a start, but it misses the real power. Critical bugs can hide in the database, not only in the user interface. Knowing SQL turns you from a surface-level checker into a deep system validator who can find issues others miss. View the SQL for Testers video below. Then read on.

1. SQL Is Your Multi-Tool for Every Testing Role

SQL is useful for manual testers, SDETs, and API testers. It helps each role to validates data at its source. If you want to learn SQL queries, please view my SQL Tutorial for Beginners-SQL Queries tutorial here.

• Manual Testers: Use SQL to confirm UI actions are persisted. For example, after changing a user's email on a profile page, run a SQL query to verify the change.
• SDETs / Automation Testers: Embed queries in automation scripts to set up data, validate results, and clean up after tests so test runs stay isolated.
• API Testers: An API response code is only part of the story. Query the backend to ensure an API call actually created or updated the intended records.

SQL fills the verification gap between UI/API behavior and the underlying data, giving you definitive proof that operations worked as expected.

2. Find Invisible Bugs with SQL Joins

Some of the most damaging data issues are invisible from the UI. Orphaned records, missing references, or broken relationships can silently corrupt your data. SQL JOINs are the tester's secret weapon for exposing these problems.

The LEFT JOIN is especially useful for finding records that do not have corresponding entries in another table. For example, to find customers who never placed an order:

SELECT customers.customer_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

This query returns a clear, actionable list of potential integrity problems. It helps you verify not only what exists, but also what should not exist.

3. Go Beyond the Basics: Test Complex Business Logic with Advanced SQL

Basic SELECT statements are fine for simple checks, but complex business rules often require advanced SQL features. Window functions, Common Table Expressions (CTEs), and grouping let you validate business logic reliably at the data level.

For instance, to identify the top three customers by order amount, use a CTE with a ranking function:

WITH CustomerRanks AS (
  SELECT
    customer_id,
    SUM(order_total) AS order_total,
    RANK() OVER (ORDER BY SUM(order_total) DESC) AS customer_rank
  FROM orders
  GROUP BY customer_id
)
SELECT
  customer_id,
  order_total,
  customer_rank
FROM CustomerRanks
WHERE customer_rank <= 3;

CTEs make complex validations readable and maintainable, and they let you test business rules directly against production logic instead of trusting the UI alone.

4. Become a Performance Detective

Slow queries degrade user experience just like functional bugs. Testers can identify performance bottlenecks before users do by inspecting query plans and indexing.

• EXPLAIN plan: Use EXPLAIN to see how the database executes a query and to detect full table scans or inefficient joins.
• Indexing: Suggest adding indexes on frequently queried columns to speed up lookups.

By learning to read execution plans and spotting missing indexes, you help the team improve scalability and response times as well as functionality.

5. Your Automation Is Incomplete Without Database Assertions

An automated UI or API test that does not validate the backend is only half a test. A UI might show success while the database did not persist the change. Adding database assertions gives you the ground truth.

Integrate a database connection into your automation stack (for example, use JDBC in Java). In a typical flow, a test can:

1. Call the API or perform the UI action.
2. Run a SQL query to fetch the persisted row.
3. Assert that the database fields match expected values.
4. Clean up test data to keep tests isolated.

This ensures your tests verify the full data flow from user action to persistent storage and catch invisible bugs at scale.

Conclusion: What's Hiding in Your Database?

SQL is far more than a basic lookup tool. It is an essential skill for modern testers. With SQL you can validate data integrity, uncover hidden bugs, verify complex business logic, diagnose performance issues, and build automation that truly checks end-to-end behavior. The next time you test a feature, ask not only whether it works, but also what the data is doing. You may find insights and silent failures that would otherwise go unnoticed.

Send me a message using the Contact Us (right pane) or message Inder P Singh (18 years' experience in Test Automation and QA) in LinkedIn at https://www.linkedin.com/in/inderpsingh/ if you want deep-dive Test Automation and QA projects-based Training.

SQL for SDET, QA Tester and Manual Testers - Interview Questions and Answers

Here are my SQL Interview Questions and Answers for SDET, QA Tester and Manual Testers. Read the interview questions on Introduction to SQL for Testers, Basic SQL Concepts (databases, tables, rows, and columns, SQL Data Types, DDL commands, DML commands, Writing SQL Queries, Working with Joins and Multiple Tables, Intermediate SQL Concepts (Grouping data, Aggregate functions, Subqueries and Using UNION and INTERSECT) and Advanced SQL Concepts (Common Table Expressions, Window Functions, Creating and using VIEWS for testing, Indexes and performance tuning and Handling SQL transactions).

If you want my complete set of SQL Interview Questions and Answers as a document that additionally contain the following topics, you can message me on LinkedIn or send me a message in the Contact Us form in the right pane:
SQL in Different Database Platforms (Oracle, PostgreSQL, SQL Server and NoSQL Databases (MongoDB)), SQL Queries for Manual Testers, SQL in Automation Testing (Java, Python, etc.) and SQL for API Testing, SQL Queries for Performance Testing, Data Validation Using SQL, More SQL Questions for QA Interview Preparation and Writing SQL queries for real-world problem-solving in interviews, Scenario-based SQL questions, Best practices for SQL-based problem-solving in QA interviews, SQL Best Practices for Testers and Best practices for organizing SQL queries in testing projects and SQL Tips and Tricks for QA Testers.

Question: What is SQL? Why is it important in QA testing?
Answer: SQL (Structured Query Language) is a standard language used to interact with relational databases for storing, retrieving, and manipulating data. In QA testing, SQL is needed because testers need to validate the data stored in databases, verify data consistency, and test if the application’s backend is functioning correctly.
For example, when testing a web application, a tester may need to run SQL queries to find out if the data entered in the frontend is correctly saved in the database. QA testers frequently use SQL for:
- Verifying if CRUD operations (Create, Read, Update, Delete) are working as expected
- Validating reports or UI data against the database for accuracy
- Checking database constraints (e.g. unique keys, foreign keys) during functional testing.
Example: To verify that user data is correctly inserted into the users table after registration, a QA tester might run:

SELECT * FROM users WHERE username = 'john_doe';

SQL is needed in different testing approaches:
- Manual Testing:
a. Manual testers use SQL to manually validate if the data in the database is the same as what is shown in the application’s user interface (UI).
b. They can write SQL queries to check that new entries, updates, or deletions made through the UI are reflected correctly in the database. Example: After updating a user’s email address through the application UI, a manual tester may run the following query to verify if the email has been updated:

SELECT email FROM users WHERE username = 'john_doe';

* Automation Testing (SDET):
a. In automation, SDETs can write SQL queries within their test scripts to fetch and validate data directly from the database as part of automated test validation. Note that embedding SQL in UI-driven test scripts requires managing database connections and cleanup.
b. SQL can also be used to set up test data before executing test cases or to clean up after tests.
c. Example: In a Selenium test script, SQL queries might be used to verify database records:

- API Testing: Testers use SQL in API testing to validate that the data sent via API calls is correctly inserted or updated in the database. Example: After making a POST request to an API that creates a new order, a tester can run SQL to validate the order creation:

SELECT * FROM orders WHERE order_id = '12345';

Question: What is the difference between relational databases (SQL) and non-relational databases (NoSQL)?
Answer: Relational databases (SQL) and non-relational databases (NoSQL) differ in their structure, use cases, and data handling methods.
Relational Databases (SQL): SQL databases store data in structured tables with rows and columns. They use SQL queries to perform operations on the data. Data is organized in relations (tables), and each table has a predefined schema. In the example below, Customers, Orders, OrderDetails and Products are the tables. The Customers table has the columns CustomerID, FirstName and so on.
Relational databases are ideal for complex queries, transactions, and applications where data integrity is crucial (e.g. financial applications, ERPs). Examples: MySQL, Oracle, PostgreSQL, SQL Server.

Non-relational Databases (NoSQL): NoSQL databases store data in a flexible, schema-less manner, typically as documents, key-value pairs, or wide-column stores. They are suited for handling large volumes of unstructured or semi-structured data, such as JSON, XML, or blobs. NoSQL is used for applications that need scalability, like social media platforms or real-time analytics. Examples: MongoDB (Document-based), Cassandra (Wide-column), Redis (Key-value), Neo4j (Graph database).

Question: When should QA testers use SQL databases vs NoSQL databases?
Answer: It depends on the type of application, its data structure, and specific project requirements:
- SQL databases should be used when:
o Data integrity and ACID (Atomicity, Consistency, Isolation, Durability) properties are critical.
o There is a need for complex joins, relationships, and transactional consistency.
o The data is structured and has well-defined relationships (e.g. e-commerce sites, inventory management).
o Example: If a QA team is testing a banking application, a SQL database like PostgreSQL would be suitable due to the need for data accuracy, complex queries, and strong relationships between tables.

- NoSQL databases should be used when:
o The application requires high scalability and performance over large datasets.
o The data is semi-structured or unstructured (e.g. JSON, XML).
o There are no complex relationships or strict schema requirements (e.g., social media, IoT applications).
o Example: For testing a document-heavy application, like a content management system (CMS), a NoSQL database like MongoDB would be appropriate because of the flexibility in storing different document formats.

You can learn about various aspects of database testing including SQL in my Database Testing tutorials playlist (I’ve published 13 videos in it as of date) at https://www.youtube.com/playlist?list=PLc3SzDYhhiGVVb76aFOH9AcIMNAW-JuXE

Question: What are databases, tables, rows, and columns in the context of SQL?
Answer: In SQL, databases, tables, rows, and columns are components used to store and organize data. I’ve explained these components and shown examples in my Database Testing tutorial at https://youtu.be/W_fH6CqiTDU
- Database: A collection of organized data that can be accessed, managed, and updated. It acts as a container that holds tables and other database objects such as views, indexes, stored procedures and triggers.
- Table: A structured set of data that contains rows and columns. It represents a specific entity in the database, such as customers, orders, or products.
- Row (Record): Each row in a table represents a single, complete set of data (i.e. a record) for that entity. For example, a row in a users table would represent one individual user’s data (name, email, etc.).
- Column (Field): A column represents a specific attribute of the entity being modeled. Each column contains data of a particular type, like VARCHAR for text or INT for numbers.

Test automation example: As an SDET, you may be testing an e-commerce system. You need to verify whether product data is correctly inserted into the products table. Below’s an example of a table. Each row represents a product (Laptop, Headphones), and columns represent specific attributes of each product (e.g. product_id, product_name, price). To check if the Laptop product exists after an API or UI test, you might run:

SELECT * FROM products WHERE product_id = 1;

Table products:

Manual Tester Example: As a manual tester, after performing a transaction, you should confirm if a user record was correctly inserted. The users table may look like below. You could run the SQL to validate the presence of one user with the username given in the SQL query:

SELECT * FROM users WHERE username = 'john_doe';

Table users:

Question: What are common SQL data types, and why are they important?
Answer: SQL data types define the kind of data that can be stored in a column. Choosing the correct data type ensures data integrity, optimizes storage, and improves query performance.

SQL Data Type	Description	Example
VARCHAR(size)	Variable-length character string. It is used to store text data.	VARCHAR(50) can store text up to 50 characters long.
INT	Integer number. It is used to store whole numbers (e.g. age, quantity).	INT can store numbers like 42 or 1000.
DATE	Used to store calendar dates (year, month, and day).	DATE stores values like 2025-06-30.
DECIMAL(precision, scale)	Stores decimal numbers with exact precision, useful for monetary values.	DECIMAL(10, 2) stores values like 12345.67.

Example: If you are verifying if the correct data type is used in a table (e.g. price in a products table should be in decimals), you might check the schema (meaning table structure) with the following SQL. There is more in database schema testing, which I’ve explained in the database testing tutorial in my Software and Testing Training channel.

DESCRIBE products;

Question: What are DDL (Data Definition Language) commands, and how are they used?
Answer: DDL (Data Definition Language) commands are used to define, modify, and remove database structures such as tables, schemas, and indexes. These commands do not manipulate the data inside the tables but instead manipulate the schema.
- CREATE: Used to create a new database object (e.g. table, index).

- ALTER: Used to modify an existing database object (e.g. adding a column to a table). Example: 

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

- DROP: Used to delete a database object. Example:

DROP TABLE employees;

Examples:

SDET Example: As an SDET, you may need to verify that a new table is created or modified correctly during automated tests. You might validate this with SQL commands such as CREATE and ALTER within your automation suite:

Manual Tester Example: While manual testers typically don’t create or alter database structures, you may need to confirm that a new table or column exists after a database migration. You might run the following SQL to check that a column like salary has been successfully added:

DESCRIBE employees;

Question: What are DML (Data Manipulation Language) commands, and how are they used?
Answer: DML (Data Manipulation Language) commands are used to manipulate the data within tables. These commands allow testers to retrieve, insert, update, and delete data in the database.

DML Command	Description	Example
SELECT	Retrieves data from one or more tables.	SELECT first_name, last_name FROM employees WHERE hire_date > '2025-01-01';
INSERT	Adds new records into a table.	INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES (101, 'Inder', 'P Singh', '2025-01-01');
UPDATE	Modifies existing record in a table.	UPDATE employees SET salary = 50000 WHERE employee_id = 101;
DELETE	Removes record from a table.	DELETE FROM employees WHERE employee_id = 101;

Examples

SDET Example: In automated tests, you might run INSERT, UPDATE, and DELETE commands to verify how the system handles various data manipulations. For example, after adding a record to the employees table, you can check that it was inserted:

Manual Tester Example: As a manual tester, you may run SELECT queries to validate that updates or deletions performed through the application UI are reflected correctly in the database. For example:

SELECT * FROM employees WHERE first_name = 'Inder'; 

In order to dive deeper, you might view my tutorials on DBMS, database schema, relational algebra and relational calculus in my Software and Testing Training channel.

Question: How do you SELECT data from a single table?
Answer: I’ve demonstrated many SQL queries in my SQL queries tutorial at https://youtu.be/BxMmC77fJ9Y but, put simply, the SELECT statement is used to query data from a single table in a database. It allows testers to retrieve specific columns or all columns from the table.
1st technique: Retrieve specific columns from the table:

SELECT column1, column2, ... FROM table_name;

2nd technique: If you want to retrieve all columns, use \* instead:

SELECT * FROM table_name;

Test Automation Example: Suppose you're testing an e-commerce system, and you want to validate that the products table contains a specific product. You could run the following query into your test automation script to verify the presence and values of specific products:

SELECT product_id, product_name, price FROM products;

Manual Testing Example: If you want to manually check all customer details in a customers table, you can run the following query. will display all columns (like customer_id, customer_name, email, etc.) for all customers in the table, which you can visually inspect to verify.

SELECT * FROM customers;

Question: How do you filter data with WHERE clauses?
Answer: The WHERE clause is used to filter records based on specific conditions. WHERE clause is given after FROM table name. It narrows down the results to only those rows that meet the defined criteria. Its syntax is:

SELECT column1, column2, ... FROM table_name WHERE condition;

Test automation example: If you need to verify that products with a price above $100 exist in the products table, run the following query. You can use assertions in your automation code to validate the returned data matches your expectations.

SELECT product_id, product_name, price FROM products WHERE price > 100;

Manual Testing Example: If you want to manually check which customers registered after January 1st, 2025, you can run the following query. It will display only customers who registered after the specified date, allowing you to verify the filtering logic manually.

SELECT customer_id, customer_name, registration_date FROM customers WHERE registration_date > '2025-01-01';

Question: How do you sort data using ORDER BY?
Answer: The ORDER BY clause is used to sort the result set based on one or more columns. By default, it sorts in ascending order (ASC), but you can specify descending order (DESC) instead. The syntax is:

SELECT column1, column2, ... FROM table_name ORDER BY column_name [ASC|DESC];

Test Automation Example: If you want to validate that the products are listed in descending order by price in the products table, you can run the query below. The automation code can verify that the prices appear in the correct order as expected.

SELECT product_id, product_name, price FROM products ORDER BY price DESC;

Manual Testing Example: If you want to manually view a list of customers sorted by their registration date, run the query below. This will list all customers in increasing chronological (time) order, making it easier for you to validate registration trends.

SELECT customer_id, customer_name, registration_date FROM customers ORDER BY registration_date ASC;

Question: How can you limit results using SQL?
Answer: LIMIT (used in MySQL and PostgreSQL) or TOP (used in SQL Server) restricts the number of rows returned by a query, which is useful for testing with a subset of data.
 

Syntax (for MySQL/PostgreSQL):

 

Syntax (for SQL Server):

 

Test Automation Example: If you need to validate that only the top 3 most expensive products are returned, use the following query. This query can let you test pagination or validate specific subsets of data in the application.

SELECT product_id, product_name, price FROM products ORDER BY price DESC LIMIT 3;

Output:

Manual Testing Example: To manually check only the first 5 customers in the customers table, use the following query. This will show the top 5 rows in the table, allowing for quick data inspection during manual validation.

SELECT * FROM customers LIMIT 5;

If the system uses SQL Server, the equivalent query would be:

SELECT TOP 5 * FROM customers;

If you are finding my SQL post useful, you can follow me in in this Software Testing Space blog for more practical information in test automation and software testing by clicking the Follow button in the right pane.

Question: What are table relationships in SQL, and what are Primary Key and Foreign Key?
Answer: In relational databases, table relationships link data across different tables. The Primary Key and Foreign Key maintain these relationships:
* Primary Key (PK): A column (or a set of columns) that uniquely identifies each row in a table. It ensures that there are no duplicate or NULL values in that column.
* Foreign Key (FK): A column (or a set of columns) in one table that references the Primary Key of another table. It creates a link between two tables.
These keys allow us to join tables and combine data from multiple tables, ensuring data integrity.

Test automation example: You may have a customers table and an orders table. The customer_id column in customers table would be the Primary Key, and the customer_id column in orders table would be the Foreign Key. You can join these tables to check if each order is associated with a valid customer.
Manual Testing Example: A common database test (view my data testing tutorial) is validating data integrity between tables. For example, when manually testing a students table and a courses table, the student_id in the courses table should correspond to a valid student_id in the students table.

Question: What are the different types of joins in SQL?
Answer: Joins allow you to retrieve data from multiple tables based on related columns. I’ve explained and demonstrated joins (inner joins, left joins and self joins) in my SQL tutorial for beginners at https://www.youtube.com/watch?v=BxMmC77fJ9Y&t=518s

The most commonly used joins are:
- INNER JOIN: Returns only the rows that have matching values in both tables.
- LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned from the right table.
- RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned from the left table.
- FULL JOIN (FULL OUTER JOIN): Returns all rows from both tables, with NULLs where a match doesn’t exist in either table.

Question: How do you write SQL queries that combine data from multiple tables using joins?
Answer: You can write SQL queries that join two or more tables by specifying the relationship between the tables using the join condition (ON). Here are examples of how different joins work. 

 

Test automation example:
- INNER JOIN: Suppose you want to automate the validation of customer orders. You can write a query to ensure that every order has an associated customer. The query below retrieves only the orders that are linked to valid customers.

- LEFT JOIN: If you're testing for customers who haven’t placed any orders yet, you would use a LEFT JOIN to include all customers, even those without orders. The query below retrieves all customers, displaying NULL for orders for customers who haven't made any orders.

 

Manual testing example:
- RIGHT JOIN: If you're testing a healthcare system and want to verify that every patient has a doctor assigned, you would use a RIGHT JOIN to ensure that no patients are missing doctor assignments. The query below returns all patients, including those who may not yet have an assigned doctor.

- FULL JOIN: To manually check for any missing relationships between two tables in a database, you would use a FULL JOIN to get all rows from both tables, including rows with no match in either table. This allows you to detect any missing relationships.
Note: FULL JOIN / FULL OUTER JOIN exists in SQL Server, PostgreSQL, Oracle, etc. In MySQL, you must emulate it via LEFT JOIN UNION RIGHT JOIN or LEFT JOIN UNION ALL (without duplicates).

Question: What are some common test scenarios that need SQL joins?
Answer: Joins are used to validate data consistency and integrity between related tables.
Test automation example:
- Order validation: In e-commerce applications, SDETs can use INNER JOIN queries to validate that every order in the orders table has a valid customer in the customers table.
- Null data validation: Use LEFT JOIN to ensure no orphaned records exist, such as customers without associated orders or transactions. 

 

Manual testing example:
- Data consistency checks: When manually testing a student enrollment system, the tester can use a LEFT JOIN to check that all students in the students table are enrolled in at least one course, or use FULL JOIN to check for students or courses that don't match:

Note: For SQL training or DBMS training you can contact Inder P Singh on LinkedIn.

Question: How can you group data using GROUP BY and filter groups with HAVING?
Answer: GROUP BY is used to group rows that have the same values into summary rows, such as totals or counts. It is often combined with aggregate functions like COUNT, SUM, AVG, MIN and MAX. The HAVING clause is used to filter records after grouping has been applied, typically based on aggregate results. I’ve demonstrated GROUP BY and HAVING in my Software and Testing Training channel’s SQL tutorial from this time stamp in that video. 

 

Test automation example: You want to test that customer orders are grouped correctly by customer in an e-commerce system. The following query groups the orders by customer_id and calculates the total order value for each customer. It then filters groups to show only customers with a total order value above $500:

Manual testing example: In a sales application, you might manually test by checking which sales agents have completed more than 10 orders. You might use the GROUP BY and HAVING clauses as follows:

Question: What are aggregate functions, and how do you use them in SQL?
Answer: Aggregate functions perform calculations on multiple rows of data and return a single value. Common aggregate functions include:
- COUNT: Returns the number of rows.
- SUM: Adds up numeric values.
- AVG: Calculates the average value.
- MIN: Returns the smallest value.
- MAX: Returns the largest value.

Example: You want to test the total number of orders for each product. You can use the COUNT function to retrieve the total orders for each product_id:

Example: When testing a library system, you might need to manually check which book has the highest number of borrowings. Using MAX for this shows the book with the highest borrow count.

SELECT book_title, MAX(borrow_count) AS most_borrowed FROM books;

Question: How and when should you use subqueries (nested queries) in SQL?
Answer: You can learn about subqueries from my SQL tutorial for beginners from this timestamp. Basically, subqueries are queries inside another query. They are useful when you need to perform a query that relies on the result of another query.
- In the SELECT clause: To calculate derived columns.
- In the WHERE clause: To filter records based on another query's result.
- In the FROM clause: To create a temporary table for further querying.
 

Test automation example: You want to test whether orders with high totals have the highest-paying customers. You could use a subquery in the WHERE clause to find customers whose total order value exceeds the average:

SELECT customer_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING SUM(order_total) > (
    SELECT AVG(order_total) FROM orders
  )
);

Manual testing example: When testing an inventory system, you want to manually check the products with below-average stock levels. You might use a subquery in the WHERE clause:

Question: How can you combine query results using UNION and INTERSECT?
Answer: I’ve explained UNION etc. with examples in my SQL tutorial from this timestamp.
- UNION: Combines the results of two queries and removes duplicates.
- UNION ALL: Combines the results of two queries without removing duplicates.
- INTERSECT: Returns only the rows that are common to both queries.

Example 1: Suppose you're testing customer data stored in two different tables: active_customers and inactive_customers. You want to generate a list of all unique customers across both tables. Use UNION so that all unique customer records are retrieved from both tables:

Example 2: You’re manually testing an educational platform where you need to find students who are enrolled in both Math and Science courses. You can use INTERSECT to validate the students enrolled in both subjects:

More resources: If you want to see more examples, you can view them in my Database Testing tutorials . You can subscribe to my Software and Testing Training channel to get updates on new tutorials for SDET, QA and manual testers here.

Question: What are Common Table Expressions (CTEs), and how do you use them in complex queries?
Answer: A Common Table Expression (CTE) is a temporary result set defined within the execution of a SELECT, INSERT, UPDATE, or DELETE query. CTEs make complex queries more readable by allowing you to define and reuse subqueries. 

Test automation example: You want to test an e-commerce system to identify high-value customers, those who have placed orders totaling more than $1000. You can use a CTE to simplify the query as follows. It makes it easy to break the logic into manageable steps for your automation test.

Manual testing example: If you need to manually check the products with sales greater than $5000, you can use a CTE as follows to first aggregate sales data and then select only high-selling products. It helps in verifying large datasets step-by-step.

In fact, the manual tester can specialize in data quality. In my Data Quality tutorial below, I’ve explained the data quality concepts and data quality analyst role with many examples. Data Quality Concepts for Testers: https://youtu.be/N9olq42z-AE

Question: How do Window Functions like ROW_NUMBER(), RANK(), LEAD(), and LAG() work?
Answer: Window functions perform calculations across a set of table rows related to the current row. They do not collapse rows into groups like aggregate functions but allow the result of a function to be "windowed" over rows.
- ROW_NUMBER(): Assigns a unique sequential integer to rows.
- RANK(): Similar to ROW_NUMBER(), but assigns the same rank to rows with equal values.
- LEAD(): Returns the value of the next row.
- LAG(): Returns the value of the previous row.
 

Test automation example: You want to test that customer orders are ranked by total order value. You can use RANK() as follows to identify the top three customers by order amount. It validates that your application correctly ranks top customers.

Manual testing example: To check the order history and identify sequential patterns, you might use LAG() to compare current and previous order dates for each customer. It helps in manually reviewing customer behavior and order trends.

SELECT customer_id, order_date,  
LAG(order_date, 1) OVER (PARTITION BY customer_id ORDER BY 
order_date) AS previous_order 
FROM orders;

Question: How do you create and use VIEWS for testing purposes?
Answer: I’ve explained how to test database schema objects like TABLE, VIEW, STORED PROCEDURE AND TRIGGERS in my Database Testing tutorial from this time stamp. But, as far as a VIEW is concerned, it’s a virtual table that consists of a SQL query result. It simplifies complex queries by allowing you to encapsulate them within a reusable object.
 

Test automation example: You need to repeatedly test data on active customer orders. Instead of writing complex queries in each automated test, you can create a VIEW for active orders:

You can test this view with a simple SQL query:

SELECT * FROM active_orders;

Manual testing example: In an inventory system, to test low-stock products regularly, create a VIEW like below:

You can now manually check low stock levels using: 

Question: How do you use indexes and optimize queries for large datasets?
Answer: Indexes improve the speed of data retrieval by creating a data structure (index) that allows the DBMS to find rows more quickly. However, they can slow down INSERT, UPDATE, and DELETE operations. 

Test automation example: When testing an application that retrieves customer records, performance may degrade as the dataset grows. You can optimize a query using an index on the customer_id column:

Now your following SQL query will run faster, especially with large datasets:

SELECT * FROM customers WHERE customer_id = 123;

Manual testing example: If manually retrieving product data is slow, you can save your time by adding an index on the product_name column. It should improve query speed when searching for product names.

Question: How do you handle SQL transactions with COMMIT, ROLLBACK, and SAVEPOINT?
Answer: A transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure that either all operations succeed or none at all (atomicity).
- COMMIT: Saves all changes made in the transaction.
- ROLLBACK: Reverts the changes made in the transaction.
- SAVEPOINT: Sets a point within a transaction to which you can roll back partially. 

Test automation example: You're testing a banking application where funds are transferred between accounts. You use a transaction to ensure that both debit and credit actions occur together:

If there's an issue during the transfer, you can roll back using the following SQL query:

ROLLBACK;

Manual testing example: When testing an inventory system, you may use transactions to ensure that updating stock quantities is atomic. This helps to maintain your test environment’s data integrity by ensuring that the stock update is not partially completed:

Want to learn more? You can message me on LinkedIn to get my complete SQL document that includes SQL in Different Database Platforms (Oracle, PostgreSQL, SQL Server and NoSQL Databases (MongoDB)), SQL Queries for Manual Testers, SQL in Automation Testing (Java, Python, etc.) and SQL for API Testing, SQL Queries for Performance Testing, Data Validation Using SQL, More SQL Questions for QA Interview Preparation and Writing SQL queries for real-world problem-solving in interviews, Scenario-based SQL questions, Best practices for SQL-based problem-solving in QA interviews, SQL Best Practices for Testers and Best practices for organizing SQL queries in testing projects and SQL Tips and Tricks for QA Testers. Thank you!

Test Types in Software Testing

Great job on starting a new lesson! After reading this lesson, click Next 👉 button at bottom right to continue to the next lesson.

Test types in software testing are the different types of software testing that have specific objectives, foci, and strategies. Test types help to validate your software for various quality attributes, such as functionality, performance, security, and usability. Test types of software testing are different from test levels in software testing.

What are the different types of testing? Test types examples include

• Functional testing: It is a test type that tests the functionality and features of the software against the requirements and specifications. The testers may use tools like Selenium, UFT and SoapUI for functional testing.
• Regression testing: It is a test type that validates that the software works as expected after any changes. The testers may use tools like Jenkins, Maven, etc.
• Performance testing: It is a test type that measures the response speed, scalability, stability, and reliability of the software under various load conditions. The testers may use tools like JMeter, LoadRunner, etc.
• Security testing: It is a test type of testing that tests the security of the software against various threats or attacks. The testers may use tools like Nmap, Burp Suite, etc.
• Usability testing: It is a test type that evaluates the user-friendliness and ease of use of the software. The end users or customers may use tools like UserTesting, Usabilla, etc.
• Compatibility testing: It is a test type that tests the compatibility and interoperability of the software with different browsers and browser versions, operating systems, devices, etc. 
• Exploratory testing: It is a test type that involves exploring the software without any predefined test cases but with a test mission. It is done by the testers using their curiosity, creativity and intuition.
• Database testing: It is a test type that validates the data quality of the database used by the software. Depending on the DBMS, the testers use tools like SQL Server Management Studio, Oracle SQL Developer, etc.
• Localization testing: It is a test type that tests the localization and internationalization of the software for different languages, cultures, regions, etc. The testers may use tools like Google Translate, Linguee, etc.

Tips for test types

• Perform the necessary test types mentioned in scope of your test plan.
• Use automated testing to achieve more coverage and efficiency.
• Use suitable tools and techniques for each test type, based on your preferences and skills, specific project needs, and technological advancements.
• Follow industry and project best practices and standards for each test type.
• Report your test results appropriately for each test type that you perform. For example, report your performance test results with business transactions tested and response times observed.

FAQ (interview questions and answers)

1. What are the advantages of automated testing over manual testing?
   It saves time and resources, it reduces human errors and biases, it increases test coverage and reliability, and it supports continuous integration and delivery.
2. What are the challenges that you faced in functional testing?
   It requires correct, and complete requirements and specifications, it may not cover all possible scenarios and edge cases, and it may not detect non-functional issues such as performance or security.
3. What are the benefits of exploratory testing?
   It allows creativity and flexibility in testing, it uncovers unexpected defects, it enhances learning and understanding of the software, and it's supplements scripted testing (with documented test cases).
   
4. What factors do you consider for compatibility testing?
   The target browsers and their versions, operating systems, devices, platforms, the hardware and software configurations and dependencies of the software, and the industry standards and guidelines for compatibility testing.

Remember to just comment if you have any doubts or queries.

Database Testing

Great job on starting a new lesson! After reading this lesson, click Next 👉 button at bottom right to continue to the next lesson.

Database testing

Database testing is a type of software testing that tests the structure, data, and database objects of a database system. Database testing tests the schema, and database objects, such as tables, views and triggers of the database and verifies the data integrity and consistency. Database testing also involves creating queries to perform load testing on the database to test its performance aspects, like responsiveness and latency.

Database testing examples

• You do the database testing of your online shopping website by testing the data validation, data conversion, and data integrity of the customer orders, payments, and deliveries. You insert, update, delete, and retrieve data in the user interface and run queries on the database to verify that the data is correct.
• You perform database testing of your inventory management system by testing the schema, tables, columns, keys, and indexes related to product inventory, sales, and purchases. You create and execute queries to test the constraints, relationships, and dependencies of the database objects.
• You do the database testing of your payroll system by testing the stored procedures, functions, and triggers managing the employee tax, benefits, and salary. You create and run queries to test the logic, calculations, and validations of the database operations.
• You perform the database testing of your social media platform by testing the performance and scalability of the user profiles, posts, comments, and likes. In your test environment, you execute queries to simulate high volume and concurrent data transactions and measure the response time, throughput, and error rate of the database.

Tips for database testing

• Identify the database requirements from the technical design and specifications of your software.
• Design the database test cases that cover the most frequently used, realistic and critical test scenarios of your software.
• Use database testing tools and techniques to automate your database test cases to run.
• Analyze and the database test results and report them, along with any database defect, data quality defects and recommendations.

FAQ (interview questions and answers)

1. What is the difference between database testing and data warehouse testing?
   Database testing is a type of software testing that tests a single or multiple databases that store operational data for one or more software or application(s). Data warehouse testing is a type of software testing that tests a data warehouse that stores historical data for analysis and reporting purposes.
2. What are some tools for database testing?
   The tools depend on the DBMS in use e.g. SQL Server Management Studio, Oracle SQL Developer, MySQL Workbench, Toad for Oracle, etc.
3. Is database testing a type of functional or non-functional testing?
   Database testing includes both functional and non-functional aspects of testing. Functional testing includes testing the functionality of the database objects. Non-functional testing includes testing the data quality, performance, security, reliability, etc. of the database.
4. How do you perform data migration testing?
   You perform data migration testing (called ETL testing) by verifying that the data is transferred correctly from a source to the target without any loss or corruption. You compare the source and target data using queries or tools and test for any omissions or discrepancies.

Remember to just comment if you have any doubts or queries.

Regression Testing

Great job on starting a new lesson! After reading this lesson, click Next 👉 button at bottom right to continue to the next lesson.

Regression testing

Regression testing is a test type of software testing that tests if the existing functionalities of your software still work as expected, after any changes in the code. Changes may be new features, defect fixes, enhancements, performance improvements, configuration updates or platform update. Regression testing helps to finds defects that may have been introduced by the changes. In my experience, regression testing was sometimes skipped by the team due to lack of time, which resulted in more effort to fix the missed regression defects later.

Regression testing examples

• The developer adds a new feature to your e-commerce website that allows customers to apply coupons for discounts. You perform regression testing to see if the new feature works well with the existing features such as shopping cart, checkout, payment, etc.
• Your mobile developer fixes a defect in your mobile app that caused it to crash when users tapped on a certain button. You perform regression testing to see if the bug is fixed and if the app still functions normally on different devices and operating systems (regression compatibility testing).
• Your web developer optimizes the code of your web application to make it load faster and use less memory. You perform regression testing to see if the optimization has improved the performance and if it has not affected the functionality of the web application (regression performance testing and functional testing).
• You update the configuration of your software system to integrate with a new external system. You perform regression testing to see if the integration works smoothly and if it has not caused any issues with the existing features of your software.

Tips for regression testing

• Update and maintain your regression test cases after software changes to make them effective.
  
• Select and prioritize the regression test cases (that cover the most critical and frequently used features of the software).
• Use test automation tools to perform regression testing faster and more efficiently.
• Use your project test management tool to track and report the test results and defects.

FAQ (interview questions and answers)

1. What is the difference between regression testing and re-testing?
   Re-testing is testing if a specific defect or issue has been fixed. Regression testing is testing the entire software or a subset of it after any changes in the software system.
2. What are some tools for regression testing in your knowledge?
   Some tools for regression testing are Katalon Studio, Selenium, TestComplete, UFT, etc.
3. Is user acceptance testing a type of regression testing?
   No, user acceptance testing is a type of functional testing that tests whether the software meets the end user needs and business requirements.
4. How do you select test cases for regression testing?
   Based on factors such as risk, impact, complexity, frequency of use, etc. using techniques such as impact analysis, traceability matrix, or test case prioritization.

Remember to just comment if you have any doubts or queries.

SQL Queries Tutorial | Sql Query tutorial for Beginners with Examples

This is my first post of the year 😀. As I mentioned in my SQL Queries Tutorial and Sql Query tutorial for beginners with examples, the SQL queries that you can use for practice are below.

 

SQL Queries (these SQL queries in DBMS are explained in the above SQL Tutorial for Beginners):

 

1) [SQL for beginners] Get all fields of all records from the Customers table.
select * from Customers

2) [SQL for beginners] Select only the named fields from the Customers table.
select CustomerID, CustomerName, Country from Customers

3) [SQL for beginners] Select only 5 records and only the given fields from the Customers table.
select top 5 CustomerID, CustomerName, Country from Customers

4) [SQL for beginners] Select only the given fields from all records which match the given condition in the Customers table.

select  CustomerID, CustomerName, Country from Customers where Country = 'USA'
5) [SQL for beginners] Select all fields of all records from the Employees table.
select * from Employees

6) Select all records from the Employees table ordered by FirstName field.
select * from Employees order by FirstName

7) Select all records from the Employees table ordered by FirstName field in descending order.
select * from Employees order by FirstName desc
8) Select OrderID field with alias ID, CustomerID field with alias Customer and so on from the Orders table.
select OrderID as ID, CustomerID as Customer, OrderDate as [Date] from Orders

9) [SQL Joins] Select with Inner Join of Orders table with Customers table.
select Orders.OrderID, Customers.CustomerName, Customers.Country
from Orders inner join Customers on Orders.CustomerID = Customers.CustomerID
10) Select those Employees whose EmployeeID does not appear in the Orders table.
select * from Employees where EmployeeID not in (select EmployeeID from Orders)

11) [SQL Joins] Select with Left Join of Employees table with Orders table.
select Employees.FirstName, Employees.LastName, Orders.OrderID
from Employees left join Orders on Employees.EmployeeID =Orders.EmployeeID
order by Employees.FirstName, Employees.LastName
12) [SQL Joins] Select with Right Join of Orders table with Employees table.
select Employees.FirstName, Employees.LastName, Orders.OrderID
from Orders right join Employees
on Employees.EmployeeID =Orders.EmployeeID
order by Employees.FirstName, Employees.LastName
13) [SQL Joins] Select pairs of products with the same price using Self Join of Products table.
select P1.ProductID as ID1, P1.ProductName as Name1, P1.Price as Price1, P2.ProductID as ID2, P2.ProductName as Name2, P2.Price as Price2
from Products P1 inner join Products P2 on P1.Price = P2.Price and P1.ProductID <> P2.ProductID
14) [SQL queries examples] Using Union, select Country field values from the Customers table and Suppliers table.

select Country from Customers union select Country from Suppliers

15) [SQL queries examples] Using Union All, select Country field values including duplicates from the Customers table and Suppliers table.
select Country from Customers union all select Country from Suppliers
16) Using Group By, count the total number of suppliers per country from the Suppliers table.
select count(SupplierID) as TotalSuppliers, Country
from Suppliers group by Country
17) Using Group By, count the total number of products per price point from the Products table.
select count(ProductID) as ProductsNumber, Price
from Products group by Price order by Price
18) Using the above SQL query, select only those price points that have more than one product.
select count(ProductID) as ProductsNumber, Price
from Products group by Price having count(ProductID)>1 order by Price
19) Using a sub query, select those customers who have ordered any quantity more than 90 units.
select CustomerID, CustomerName from Customers 

where CustomerID = any (select Orders.CustomerID from Orders inner join OrderDetails on Orders.OrderID = OrderDetails.OrderID  where OrderDetails.Quantity > 90)

You can practice the above SQL Queries on W3Schools website.

Data Quality and Data Quality Assurance

This post is on data quality and how to go about assuring high data quality. View my video on Data Quality (I have explained multiple examples in detail in it) or read on...

First, let us understand data quality. Put simply, data are of high quality if they do not suffer from data issues. There are many potential issues with data (see examples below). Now, data are used for a number of organizational functions such as on-going operations, dealing with customers, marketing and analysis and decision making. If the data are not of high quality, there are a number of problems. Users get incorrect reports. Time and money is wasted in miscommunication. Bad data can lead to poor decisions. It can frustrate employees and most importantly, it can frustrate customers.

Although data quality assurance is particularly useful for production databases, it can very well be used in software testing as software testers need to ensure high data quality in gold test databases. Now, let us see examples of data issues that bring down data quality. 

Database Normalization: What to test for Third Normal Form?

In the last post, you saw the tests (based on the candidate key) that should be executed to check the second normal form (2NF). In this post, let us understand the third normal form (3NF) and the tests that should be executed to check it. View my video on Third Normal Form explained with examples or read on...

First, what is the 3NF? Just to recall, the purpose of normalization is to eliminate insertion, update and deletion anomalies. The tables in a normalized database are intuitive in design. They do not require extra query logic or application logic to query or filter the required data. Now, a table that is in 3NF is already in 2NF. Also, each non-key column depends on the candidate key and nothing else.

Now, let us understand why the following examples are not in 3NF and how to convert them to 3NF?

a. TokenAllocation (Token, Date, CustomerName, CustomerAddress)

This table stores data for tokens given out to customers after they walk in a place with a queue (e.g. bank branch, hospital OPD, ISP customer care center) and wait for their turn to be seen. The tokens always start from 1 each morning. The candidate key in this table is {Token, Date} because this pair is unique in each row. A specific token allocated on a particular date is associated with exactly one customer. So, CustomerName depends on the key. Likewise, a specific token on a particular date is associated with exactly one customer address. But, CustomerAddress also depends on CustomerName (which is not the key). Therefore, this table is not in 3NF.

To convert TokenAllocation table to 3NF, it needs to be broken into two tables:

TokenAllocation (Token, Date, CustomerID)

{Token, Date} is the only candidate key of this table. 

Customer (CustomerID, CustomerName, CustomerAddress)

CustomerID is the candidate key of this table. CustomerName and CustomerAddress depend on the CustomerID. Also, addition of CustomerID column ensures that customers with duplicate names can live in the same table.

Once TokenAllocation is taken to 3NF, the CustomerAddress values need not be updated in multiple rows in the TokenAllocation table. This is because CustomerAddress now lives only in the Customer table.

b. OrderDetails (OrderNumber, ProductNumber, Quantity, Total)

This table stores data of each line item of each order. The order information is stored in the Orders table. The product information including unit price is stored in the Products table. The candidate key in the OrderDetails table is {OrderNumber, ProductNumber}. Quantity depends on the specific order and the specific product. But the Total column contains a calculated value obtained by multiplying unit price for the specific product by the quantity. So, Total does not depend on the key but something else. Therefore, this table is not in 3NF. To convert it to 3NF, the Total column needs to be dropped: 

OrderDetails (OrderNumber, ProductNumber, Quantity)

Based on the understanding above, here the tests that should be applied to check 3NF on every table in the database:

1. Is each criterion of 1NF and 2NF satisfied?

2. What are the candidate keys in each table? For each candidate key, which columns are not a part of it? Does each such column depend on the candidate key and nothing else?

Want to learn more? See more explanation with example data in my video on Third Normal Form.