During the test of a web product, the tester often encounters data that can only get the necessary data when performing database queries through SQL queries.
What is SQL?
SQL (Structured Query Language) or full name is the query language, is a common language used to manipulate how to act on database management systems. Thanks to SQL, the tester is able to retrieve the data for his test, so knowing how to use sQL is a huge plus for the tester.
Basic knowledge about databases:
A database is a collection of organized data, made up of tables and records in a table. Records include the values of something, such as for a human being, we have our name, age, gender, date of birth, etc., for each data we will There are corresponding columns such as name column, age column, gender column, etc. To write SQL queries, users must understand the table names, column names, and value types of columns. To execute SQL statements, we use a tool called database management system. Database management system is a tool to interact with end users, using SQL query statements to manipulate the database such as adding, editing, deleting data contained in tables.
The commands are simple
1. SELECT statement
The SELECT statement is the most common statement in SQL. In order to retrieve one or more of the required records, we will use this SELECT statement. The formula for this statement is as follows:
1 2 | SELECT columnName FROM tableName; |
The SELECT statement can help us retrieve the necessary data columns or if you want to retrieve all the columns in the table, use the * to represent all columns (Example: SELECT * FROM tableName; ). Then the FROM keyword tells the system which table we are pulling data from. For example, we have a table of information about students named [SinhVien] including data columns such as ID, Student’s Name, Gender, Date of Birth, Place of Birth and we want to get ID Of the student, student’s name and date of birth, we will use the following command:
1 2 | SELECT sinhvienID, TenSinhVien, GioiTinh, NgaySinh FROM SinhVien; |
2. WHERE statement
To find the records that are explicitly conditional or we are looking for the correct data, the WHERE statement will fulfill its role. The purpose of this statement is to retrieve data that has certain conditions. Use the following formula statement:
1 2 | SELECT columnName FROM tableName WHERE condition; |
It can be seen that we still have to use the SELECT statement, rightly, the WHERE statement is just a supplement to the exact information for the record we want to find. With the above example, if we want to find students named “Mai”, we can use the following query:
1 2 | SELECT * FROM SinhVien Where TenSinhVien = 'Mai'; |
Note, when you use parentheses to mark the system that the line in parentheses is a string.
3. The operators AND, OR, NOT, BETWEEN
Sometimes, in order to find the right records for testing, we have to combine conditions and to do that, let’s use the operators AND, OR, NOT and BETWEEN.
AND: Use to retrieve records that must match two or more conditions
1 2 | SELECT columnName FROM tableName WHERE condition1 AND condition2; |
OR: Use to retrieve records only need to match one of the conditions
1 2 | SELECT columnName FROM tableName WHERE condition1 OR condition2; |
NOT: Use to retrieve us records that do not meet the conditions we set
1 2 | SELECT columnName FROM tableName WHERE NOT condition; |
BETWEEN: Use to retrieve records whose values are between the two specified conditions
1 2 | SELECT columnName FROM tableName WHERE condition1 BETWEEN condition2; |
3. Sorting statement
In order to sort our records in ascending or descending order, we use the ORDER BY command. This statement has the following formula:
Sort in ascending direction:
1 2 | SELECT columnName FROM tableName WHERE condition ORDER BY columnName ASC; |
Sort by descending order:
1 2 | SELECT columnName FROM tableName WHERE condition ORDER BY columnName DESC; |
4. Calculation functions
During the test, we need to calculate the values together, to do this the calculation functions will help us. The calculation functions include:
COUNT (): Use to count the number of non-NULL records in columns that match conditions
1 2 3 | SELECT COUNT(columnName) FROM tableName WHERE condition; SELECT COUNT(*) FROM tableName WHERE condition; |
AVG (): Use to calculate the average of columns of numeric data types
1 2 | SELECT AVG(columnName) FROM tableName WHERE condition; |
SUM (): Use to sum the values contained in columns with a data type of numbers
1 2 | SELECT SUM(columnName) FROM tableName WHERE condition; |
MIN (): Used to return the record with the smallest value
1 2 | SELECT MIN(columnName) FROM tableName WHERE condition; |
MAX (): Use to return the record with the highest value
1 2 | SELECT MAX(columnName) FROM tableName WHERE condition; |
5. Wildcard data type
On websites, we often have the search results bar and to test whether our search bar is working properly, we will write a query containing wildcard symbols. The 2 wildcard symbols that are commonly used are% and. Using% to represent 0 or lots of characters, we use _ to represent 1 character. Continuing with the example in section 1, we want to find students whose names start with the letter “N”, we use the following statement:
1 2 | SELECT * FROM SinhVien WHERE tenSinhVien = 'N%'; |
To understand how to use wilcard, please follow the table below:
| wildcard position | Meaning |
|---|---|
| ‘a%’ | Find all results that start with the letter “a” |
| ‘% a’ | Find all results that end with the letter “a” |
| ‘% a%’ | Find all results with the letter “a” in every position |
| ‘_a%’ | Find all results with the letter “a” in the second position |
| ‘a _% _%’ | Find all results that start with the letter “a” and have at least 3 characters behind |
| ‘pond’ | Find all results starting with the letter “a” and ending with the letter “o” |
Hopefully the above table has helped you understand how to use wildcard.
I have stated the simple SQL query statements that testers should know to apply to the project they are working on if they require database testing. However, these are just the simplest commands at the lowest level. In the second part, I will show you a bit more complicated manipulations, but it is still necessary to handle more complex operations.
References: https://www.w3schools.com/