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.
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.
A database has tables, views, stored procedures, functions, triggers and security settings. By expanding the database node, users can view components of a database. For example,
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.
SQL Statement retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables.
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.
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.
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.
Specifies that multiple result sets are to be combined and returned as a single result set.
Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.
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.
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.