SQL Commands/Database knowledge for Tester

                             SQL Commands/Database knowledge for Tester

Database learning is one of the most aspect of software testing. As a tester we need to have some basic knowledge of Database.

 In this post, I will try to explain basic Database commands which help us during testing using SQL commands. Concept of database stays same throughout only syntax changes depending on selected database product like sql oracle & more

In basic words, database is collection of tables which have records stored in various field arranged in columns.

Let’s take a example of table: below is table which have information Name, address and ID

Table name is EmpDetails

EmpDetails
ID	FirstName	LastName	Address	City	State
512687458	Joe	Smith	83 First Street	Howard	Ohio
758420012	Mary	Scott	842 Vine Ave.	Losantiville	Ohio

  Now we will use “Select” statement on this table

-Select: The SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table.

 SELECT ID, FirstNmae, City FROM EmpDetails;

Output of this SQL query :

ID	FirstName	City
512687458	Joe	Howard
758420012	Mary	Losantiville

To get all columns of a table without typing all column names, use:

SELECT * FROM TableName;

-Conditional Selection: We can also use condition to in SELECT statement using various relational operators (= Equal, = Not Equal, < less than, > greater than,<= less than  or equal to,>= greater than or equal to ). These are also known as Comparison Operators.

We have some clause to use filter table data using SELECT statement.

To understand the conditional selection, lets consider a new table Emp

EmployeeIDNo	Salary	Benefits	Position
10	75000	15000	Manager
105	65000	15000	Manager
152	60000	15000	Manager
215	60000	12500	Manager
244	50000	12000	Staff
300	45000	10000	Staff
335	40000	10000	Staff
400	32000	7500	Entry-Level
441	28000	7500	Entry-Level

-Where: Used to specify that only certain rows of the table are displayed, based on the criteria described in that WHERE clause

Question: Fetch the details of EmployeeIDNo who has salary greater than 50000

Solution:

SELECT EMPLOYEEIDNO FROM Emp WHERE SALARY >= 50000

Results:

EMPLOYEEIDNO
————
010
105
152
215
244

- BETWEEN: to fetch column value is between two values, including the end values specified in the range.

To list those making greater than or equal to $30,000, but less than or equal to $50,000

SELECT EMPLOYEEIDNO FROM EMP WHERE SALARY NOT BETWEEN 30000 AND 50000;

-IN: To list out column value is equal to any one of a specified set of values.

Now list all managers & staff

SELECT EMPLOYEEIDNO FROM Emp WHERE POSITION IN (‘Manager’, ‘Staff’);

Also, there are some logical operators which used in SQL. These operators are used to compare two conditions at as time. Logical operators are used with where clause

-OR: For the row to be selected at least one of the conditions must be true.

-NOT: For a row to be selected all the specified conditions must be true.

-AND: For a row to be selected the specified condition must be false.

For example, to display all staff making over $40,000, query can be like this:

SELECT EMPLOYEEIDNO FROM EMP WHERE SALARY > 40000 AND POSITION = ‘Staff’;

-Like: To list out column value is similar to specified character(s).

Now let’s a query which can list out all people whose last names started with “S”

SELECT EMPLOYEEIDNO FROM EMP WHERE LASTNAME LIKE ‘S%’;

-ORDER BY: ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order.

SELECT EMPLOYEEIDNO, salary FROM EMP ORDER BY salary;

This query would fetch all records in ascending order salary

Note:

1. The percent sign (%) is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the “S”.

2. Query is not case sensitive

This one is basic of sql query. I will share more post on join and other imp DB part related to testing.

Happy Testing!!