SQL Query Skills for Better Software Quality Assurance

Posted in Functional Testing

The traditional perception of manual software testing is that of testers pointing and clicking through graphical user interfaces (GUIs) on a website or application. Testing this way is important, as it replicates the user experience and uses the software in the way normally used by customers. However, this front-end testing should be supplemented by back-end testing wherever possible. Back-end testing involves testing databases and storage systems, which increases test coverage, saves time, and ultimately helps achieve the goal of quality assurance: decreasing risk.

A major form of back-end testing that is often very helpful to testing efforts is the act of querying SQL database servers. By querying databases, software testers can check to see if data entered in a user interface is correctly mapped to the appropriate database table and field. Testers can also check front-end functionality that relies on the presence of specific table data.

One example of testing through SQL querying would be testing zip code verification functionality. Imagine a website that receives customer inputs of a zip code and state, then verifies that the entered zip code is located within the corresponding state chosen. If software testers need to test whether or not all 2,590 zip codes within California will be accepted, how can they verify each individual zip code using GUI testing?

This type of verification is just not feasible for any company that values time and efficiency. Simple SQL queries would be better alternatives than manually checking each zip code, allowing the testers to verify functionality in a short amount of time. The following simple example query could check whether the database table contains the correct number of zip codes:

SELECT COUNT(DISTINCT ZipCode) AS NumberOfZipCodes
FROM table_name
WHERE State = 'California'

If the result set brought back a count of more than 2,590 records, then the tester knows something is wrong and can investigate further. The following example query would show only the zip codes incorrectly labeled as California by comparing to a list of the correct 2,590 California zip codes:

SELECT ZipCode
FROM table_name
WHERE ZipCode NOT IN (--list of all California zip codes here)
AND State = 'California'

If any records returned when the query is executed, then those zip codes could be corrected and assigned to the appropriate state.

The previous example is only the tip of the iceberg to what can be accomplished with SQL. By joining multiple tables, testers can search for more complex groupings of data and apply a potentially vast amount of back-end testing to the testing scope.

Knowledge of SQL skills is always beneficial for QA team members. If a software tester is experienced in only GUI testing, they will be more limited in terms of career options and opportunities within their company. By learning how to properly use SQL queries for testing, a broader realm of ability will be available for testers and their potential will be more realized. In addition, companies will benefit from have more thoroughly-tested software applications and greater quality for their customers.

Need help getting started? Contact us here or give us a call at 205-487-8793 for any questions about our software testing services.