From my past experiences, I believe a tester should have the below database and SQL knowledge:
- Recognize the different types of databases
- Connect to the database using different SQL connection clients
- Understand the relationship between database tables, keys, and indices
- Write a simple select or SQL statement along with more complex join queries
- Interpret more complex queries
How important are SQL skills for a Software Tester?
As a Software Quality Assurance Tester, I have worked on several different applications. Some of the applications require strong SQL verification skills, some of them required medium skills, and for some of the applications, I didn’t need any SQL knowledge.
While working on a different project, I was involved in back-end testing where complex SQL Query knowledge was a must. There was an internal user interface tool to get the data from the Oracle database based on the input values. As part of our testing we compared the UI tool output and the database output by inputting the same values to the tool and the database to make sure that the tool was functioning properly. Every time input values varied, the Data Base Administrator gave the testing team very big queries with select statement to use but we needed to understand the relation between the tables, columns, and the query before we used it. In addition, we used different types of SQL Statements to verify the test data.
What is SQL?
SQL (Structured Query Language) is the primary language responsible for managing data and data structures contained within a relational database management system (RDBMS). Put simply, SQL is the language you use to interact with a database. There are four basic operations that SQL can perform: INSERTs, SELECTs, UPDATEs, and DELETEs (these are sometimes referred to as CRUD operations - create, read, update, delete).
What are some database concepts?
A database, in simplest terms, is an organized collection of data. A database is comprised of many tables, and a table stores rows of data in a structured format defined by the table's columns. This represents the basic hierarchy of a database. When writing SQL queries, you are interacting with rows of data stored in tables contained within a database.
Relational Databases:
A relational database consists of one or more tables, where each table consists of 0 or more records, or rows, of data. The data for each row is organized into discrete units of information, known as fields or columns.
SQL statements we frequently use in testing are:
- Data Manipulation Language (DML): Used to retrieve, store, modify, delete, insert, and update data in the database. Examples: SELECT, UPDATE and INSERT statements.
- Data Definition Language (DDL): Used to create and modify the structure of database objects in the database. Examples: CREATE, ALTER and DROP statements.
- Transactional Control Language (TCL): Manages different transactions occurring within the database. Examples: COMMIT, ROLLBACK statements.
- Inner Join: Retrieves the matched records from both tables.
- Distinct: Retrieves the different values from one or more fields.
- In: This operator is used to find the value is within the list or not.
- Between: This operator is used to retrieve the values with in a range.
- Like: This operator is used perform pattern matching using wildcards; it is used in the where clause.
- Order By Clause: Sorts the table records in ascending or descending order. Default order is ascending.
- Group By: Use Group By statements with the aggregate function to group the result set with one or more columns.
- Aggregate Functions: Performs a calculation on a set of values and return a single value. Example: Avg, Min, Max, Sum, count etc.
Common convention is to write these keywords in all capital letters:
SELECT
FROM
WHERE
ORDER BY
Operator: NOT, AND, OR, IN , DESC, ASC, SET,
BETWEEN, NOT BETWEEN
UPDATE
- DROP
- DELETE
- INSERT
etc...
SELECT
example
If we want to retrieve all of the information about all of the customers in the Employees table, we could use the asterisk (*) as a shortcut for all of the columns, and our query looks like
If we want only specific columns (as is usually the case), we can/should explicitly specify them in a comma-separated list, as in
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
Explicitly specifying the desired fields also allows us to control the order in which the fields are returned, so that if we wanted the last name to appear before the first name, we could write
SELECT EmployeeID, LastName, FirstName, HireDate, City FROM Employees
We can continue with our previous query, and limit it to only those employees living in London:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City = 'London'
If you wanted to get the opposite, the employees who do not live in London, you would write
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City <> 'London'
It is not necessary to test for equality; you can also use the standard equality/inequality operators that you would expect. For example, to get a list of employees who where hired on or after a given date, you would write
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE HireDate >= '1-july-1993'
Of course,
we can write more complex conditions. The obvious way to do this is by having multiple conditions in the WHERE clause. If we want to know which employees were hired between two given dates, we could write
SELECT EmployeeID, FirstName, LastName, HireDate, City
FROM Employees
WHERE (HireDate >= '1-june-1992') AND (HireDate <= '15-december-1993')
Note that SQL also has a special BETWEEN operator that checks to see if a value is between two values (including equality on both ends). This allows us to rewrite the previous query as
SELECT EmployeeID, FirstName, LastName, HireDate, City
FROM Employees
WHERE HireDate BETWEEN '1-june-1992' AND '15-december-1993'
We could also use the NOT operator, to fetch those rows that are not between the specified dates:
SELECT EmployeeID, FirstName, LastName, HireDate, City
FROM Employees
WHERE HireDate NOT BETWEEN '1-june-1992' AND '15-december-1993'
What if we want to check if a column value is equal to more than one value? If it is only 2 values, then it is easy enough to test for each of those values, combining them with the OR operator and writing something like
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City = 'London' OR City = 'Seattle'
However, if there are three, four, or more values that we want to compare against, the above approach quickly becomes messy. In such cases, we can use the IN operator to test against a set of values. If we wanted to see if the City was either Seattle, Tacoma, or Redmond, we would write
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City IN ('Seattle', 'Tacoma', 'Redmond')
As with the BETWEEN operator, here too we can reverse the results obtained and query for those rows where City is NOT in the specified list:
SELECT EmployeeID, FirstName, LastName, HireDate, City
FROM Employees
WHERE City NOT IN ('Seattle', 'Tacoma', 'Redmond')
The ORDER BY clause includes one or more column names that specify the sort order. If we return to one of our first SELECT statements, we can sort its results by City with the following statement:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
ORDER BY City
By default, the sort order for a column is ascending (from lowest value to highest value), as shown below for the previous query:
If we want the sort order for a column to be descending, we can include the DESC keyword after the column name.
The ORDER BY clause is not limited to a single column. You can include a comma-delimited list of columns to sort by—the rows will all be sorted by the first column specified and then by the next column specified. If we add the Country field to the SELECT clause and want to sort by Country and City, we would write:
SELECT EmployeeID, FirstName, LastName, HireDate, Country, City
FROM Employees
ORDER BY Country, City DESC
Note that to make it interesting, we have specified the sort order for the City column to be descending (from highest to lowest value). The sort order for the Country column is still ascending. We could be more explicit about this by writing
SELECT EmployeeID, FirstName, LastName, HireDate, Country, City
FROM Employees
ORDER BY Country ASC, City DESC
but this is not necessary and is rarely done. The results returned by this query are
Another Example:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would update ADDRESS for a customer whose ID is 6:
Now, CUSTOMERS table would have the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Pune | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
If you want to modify all ADDRESS and SALARY column values in CUSTOMERS table, you do not need to use WHERE clause and UPDATE query would be as follows:
Now, CUSTOMERS table would have the following records:
+----+----------+-----+---------+---------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+---------+
| 1 | Ramesh | 32 | Pune | 1000.00 |
| 2 | Khilan | 25 | Pune | 1000.00 |
| 3 | kaushik | 23 | Pune | 1000.00 |
| 4 | Chaitali | 25 | Pune | 1000.00 |
| 5 | Hardik | 27 | Pune | 1000.00 |
| 6 | Komal | 22 | Pune | 1000.00 |
| 7 | Muffy | 24 | Pune | 1000.00 |
+----+----------+-----+---------+---------+
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would DELETE a customer, whose ID is 6:
Now, CUSTOMERS table would have the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
If you want to DELETE all the records from CUSTOMERS table, you do not need to use WHERE clause and DELETE query would be as follows:
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | | |
| NAME | varchar(20) | NO | | | |
| AGE | int(11) | NO | | | |
| ADDRESS | char(25) | YES | | NULL | |
| SALARY | decimal(18,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
This means CUSTOMERS table is available in the database, so let us drop it as follows:
Following statements would create six records in CUSTOMERS table:
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
You can create a record in CUSTOMERS table using second syntax as follows:
INSERT INTO CUSTOMERS
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
All the above statements would produce the following records in CUSTOMERS table:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Populate one table using another table:
You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate first table. Here is the syntax:
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];
The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator:
The percent sign (%)
The underscore (_)
The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.
Syntax:
The basic syntax of % and _ is as follows:
You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.
Wildcard
|
Description
|
_ (underscore)
|
matches any single character
|
%
|
matches a string of one or more characters
|
[ ]
|
matches any single character within the specified range (e.g. [a-f]) or set (e.g. [abcdef]).
|
[^]
|
matches any single character not within the specified range (e.g. [^a-f]) or set (e.g. [^abcdef]).
|
A few examples should help clarify these rules.
WHERE FirstName LIKE '_im' finds all three-letter first names that end with 'im' (e.g. Jim, Tim).
WHERE LastName LIKE '%stein' finds all employees whose last name ends with 'stein'
WHERE LastName LIKE '%stein%' finds all employees whose last name includes 'stein' anywhere in the name.
WHERE FirstName LIKE '[JT]im' finds three-letter first names that end with 'im' and begin with either 'J' or 'T' (that is, only Jim and Tim)
WHERE LastName LIKE 'm[^c]%' finds all last names beginning with 'm' where the following (second) letter is not 'c'.
Example:
Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators:
Statement
|
Description
|
WHERE SALARY LIKE '200%'
|
Finds any values that start with 200
|
WHERE SALARY LIKE '%200%'
|
Finds any values that have 200 in any position
|
WHERE SALARY LIKE '_00%'
|
Finds any values that have 00 in the second and third positions
|
WHERE SALARY LIKE '2_%_%'
|
Finds any values that start with 2 and are at least 3 characters in length
|
WHERE SALARY LIKE '%2'
|
Finds any values that end with 2
|
WHERE SALARY LIKE '_2%3'
|
Finds any values that have a 2 in the second position and end with a 3
|
WHERE SALARY LIKE '2___3'
|
Finds any values in a five-digit number that start with 2 and end with 3
|
Let us take a real example, consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would display all the records from CUSTOMERS table where SALARY starts with 200:
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
+----+----------+-----+-----------+----------+
Here too, we can opt to use the NOT operator: to find all of the employees whose first name does not start with 'M' or 'A', we would write
SELECT EmployeeID, FirstName, LastName, HireDate, City
FROM Employees
WHERE (FirstName NOT LIKE 'M%') AND (FirstName NOT LIKE 'A%')
Learning SQL can add a big value to your career
and the decisions can make you greatest on your way.
The knowledge enables you to do so much more than
you could have done previously!!
Thanks. :)
