Why SQL learning is important for testers and Some simple but important SQL Query need to know!!

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

• SELECT * FROM Employees

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')

• To sort the data rows, we include the ORDER BY clause.

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

• with the results being sorted in the same order as before:

Another 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 update ADDRESS for a customer whose ID is 6:

• SQL> UPDATE CUSTOMERS
  SET ADDRESS = 'Pune'
  WHERE ID = 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:

• SQL> UPDATE CUSTOMERS
  SET ADDRESS = 'Pune', SALARY = 1000.00;

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 |
+----+----------+-----+---------+---------+

• AGAIN 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 DELETE a customer, whose ID is 6:

• SQL> DELETE FROM CUSTOMERS
  WHERE ID = 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:

• SQL> DELETE FROM CUSTOMERS;

• Drop
• Let us first verify CUSTOMERS table and then we would delete it from the database:

• SQL> DESC CUSTOMERS;

+---------+---------------+------+-----+---------+-------+
| 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:

• SQL> DROP TABLE CUSTOMERS;

• INSERT Example:

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];

• LIKE clause

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:

• SELECT FROM table_name
  WHERE column LIKE 'XXXX%'
  
  or
  
  SELECT FROM table_name
  WHERE column LIKE '%XXXX%'
  
  or
  
  SELECT FROM table_name
  WHERE column LIKE 'XXXX_'
  
  or
  
  SELECT FROM table_name
  WHERE column LIKE '_XXXX'
  
  or
  
  SELECT FROM table_name
  WHERE column LIKE '_XXXX_'

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:

• SQL> SELECT * FROM CUSTOMERS
  WHERE SALARY LIKE '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. :)