Image from Internet
The Importance of SQL in Software Testing
Every system needs to have a database to store necessary information such as human, product or even geography information and its status. Additionally, this data is typically managed in traditional relational databases like MySQL and Oracle.
Moreover, SQL (Structured Query Language), is a powerful tool for testers who need to interact with databases, verify data quality, and perform complex queries. SQL is not only a programming language, but also a way of thinking and organizing data.
When you do functional testing of the system through the frontend (website, mobile apps, etc.), it’s important to verify if the information sent is properly updated in the database.
A basic SQL command is Select * from Table
By looking at this query, you can easily understand what it does: it returns all data from the table. Therefore, knowing the basics of SQL is necessary for software testers to perform database testing.
How to Test a SQL Database Effectively
As a Software Tester, I’ve worked on different applications. Some applications will require strong SQL skills, some require medium skills, and some do not require any SQL knowledge.
If you’re testing the FE (front-end) of the web applications to verify whether they’re working properly as expected, you might need basic SQL skills. These skills are required to verify test data: insert, update, delete records in the Database. You’ll have to use SQL commands against the SQL Database for this verification.
Example 1:
You’re testing a web application’s registration form, users input their information such as name, email, and password, and then submit their registration. This data is stored in a database. As a FE tester, you must make sure that the data entered by users is correctly stored in the database.
Suppose user inputs as following:
Name: ‘test_user’
Email: ‘test@example.com’
Password: ‘password123’
The SQL query below proves that whether data which is added in FE is matching with values stored in the Database.
SELECT * FROM users WHERE username = ‘test_user’;
Besides checking user input data, this query also tells that whether the password is properly encoded before being stored in the Database.
Example 2:
Let’s go to another example, now your web application allows admin to delete any user account.
When the ‘Admin’ user deletes ‘test_user’ account for any reason, the account still exists in DB but the ‘isDeleted’ flag of that user is set to TRUE. However, you need to make sure that the delete function in the frontend (FE) works properly according to the requirement.
To verify this, you can run the following SQL command :
SELECT * FROM users WHERE username = ‘test_user’;
- If there’s no record returned, it proves that the delete feauture doesn’t work as required.
- If there’s a record returned with ‘isDeleted’ flag set to FALSE, it proves that the delete feature doesn’t work as required.
- If there’s a record returned with ‘isDeleted’ flag set to TRUE, it proves that the delete feature meets the requirement.
If you’re primarily working on backend testing projects such as data warehousing, ETL SQL Query knowledge is an essential skill to have.
Let’s take a quick look about ETL testing. ETL (extract, transform, and load) is basically understood as a process where data is extracted from a source system, transformed according to meet business rules (which can involve changes about format or schema), and loaded into target databases. ELT testing aims to make sure that the ETL process works correctly and generates accurate, consistent data. Therefore, one of tester’s important test cases is to verify precise data transformation and avoid loss or damage during processing by comparing data before and after.
Example:
Suppose that there is a retail store which has different branch like Branch1, Branch2 . Each branch will manage their customer information separately, leading to different data storage. Branch1 stores data using ‘customer_name’, while Branch2 uses ‘customer_id’. Eventually, all customer information will be centralized in a single database. Below is one of test cases that using SQL command to verify it.
Case 1: Verify that the total number of customers from all branches matches the actual count in the target database.
// This query return the total number of customers from all branches
SELECT (SELECT COUNT(*) FROM [Branch1].Customers) + (SELECT COUNT(*) FROM [Branch2].Customers) AS Total_Customers;
// This query return the number of actual customers in the target database
SELECT COUNT(*) FROM [Target].Customers;
The number returned from both SQL commands is expected to be the same.
Case 2: Verify whether any customers are missing from the target database.
SELECT customer_name
FROM [Branch1].Customers
WHERE customer_name NOT IN (SELECT customer_name FROM [Target].Customers)
UNION
SELECT customer_id
FROM [Branch2].Customers
WHERE customer_id NOT IN (SELECT customer_id FROM [Target].Customers)
If there are no records returned, it means all customers have been successfully loaded into the target database. Conversely, if there are results returned, it means some customers from the source are missing in the target database.
Conclusion
SQL is vital for software testers, whether you are working on frontend or backend projects. Knowing SQL commands well will help increase the efficiency and accuracy of testing as well as achieve your future career goals. To become an agile tester valued by customers and companies, learning at least a little bit of SQL is important.