Print

Introduction of SQL for Software Testers

Every application stores information in various data format in the back end of the system. One of the common ways of storing data is to use database. Database is a systematic collection of data. Databases support storage and manipulation of data. Software testers need to understand the following few terms below. 

 

Print

How to Connect to SQL Server

To manipulate database, a client should connect to the server. We can connect to the SQL Server in two way with Windows authentication or SQL server authentication. 

 

Print

Manipulate Database Using Database Object Menu on SQL Server Management Studio

SQL Server Management Studio provides rich commands for database connection, database manipulation, and database object operations such as create, insert, update, and delete.

 

With the help of the Management Studio, QA testers do not need to memorize various commands. However, memorizing basic commands and their syntax are extremely important for test work efficiency. 

Print

Using Select to Retrieve rows and columns

Select statement is used to retrieve rows and columns from a table, view or joined tables. Using different arguments, select statement query will return different results.  

 

All these examples below uses AdventureWorks sample database provided by Microsoft

Print

Compare Tables in SQL Server Database

When testing database tables in SQL server, sometimes, we need to do A B testing. Basically, we need to compare table A with Table B to find out common records or differences.

In SQL statement, we can use INTERSECT to find common rows between two tables. 

 

In SQL statement, we can use EXCEPT to find different rows between two tables.

Print

Combine tables with Union in SQL Statement

We can use Union or Union All in SQL statement to combine the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

 

UNION

Specifies that multiple result sets are to be combined and returned as a single result set.

UNION ALL

    Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

Print

Using Joins in SQL Statement

When doing database testing, we may need to join tables to retrieve information from multiple tables, views or query result sets. For joining tables, we can use JOIN in SQL statement.

 

Print

Delete Records in SQL Statement

When testing, sometimes, we need to delete data from the database. In SQL Server, DELETE statement removes one or more rows from a table or view.