SQL For QA : Tutorial#1 – Select Query

The use of SQL is not limited to developers only. If you are a Tester or QA professional, you also need to run some tests against databases, verify data using some tool. In short, database testing is not limited to database testers only. Most of the times manual functional testing also needs to test data.

In this tutorial series, we are trying to keep our focus on simple database testing and try to provide some easy to understand query examples that you may need on a daily basis to complete tasks at hand. So lets start with the most used query Select Query

A SELECT query is used to extract the data from the Database and the result of this query is stored in a result table which is called as ResultSet.

This SELECT command comes under DML (Data Manipulation Language)

SELECT Command Syntax:

here Column1, Column2 are the Field names or Attribute names.

Let’s assume there is an Employee table as below:

1.  If you want to display all the available fields from the table,  use the following syntax

Query :

Result :

2.  Suppose you want to display only specific Columns FROM the table and not all, then use SELECT command with the Column name separated with comma,

For example, You need employee id and its salary details then use following syntax

Query

Result

3.  SELECT with DISTINCT keyword

You can select only the DISTINCT(avoids duplicate values) values from the table.

for eg., Distinct departments FROM the “Department” Column in the “Employee” table can be selected using below query

Query:

Result:

4.  SQL Alias

SQL Alias are used to give a temporary name to a Column or a table. Aliases are basically used to make Column name more readable. It exists only for the duration of the query.

we can use AS keyword for Alias name so that in the result set, name of the Column is appears as the Alias name what you given in the SELECT query.

Example 1:

Result:

You can also do some calculations in your select query. Here we are calculating Bonus (in this case 10% of salary) on salary and display

Query:

Result:

5.  SQL Concat() Function with SELECT query:

In SQL, Concat() function is used with SELECT query to join the multiple Column data together & display in one Column.

Suppose you need to combine Name and Department Columns data together using concate() function and display combined result under one Column name ie. name.

OUTPUT:

SELECT query AGGREGATE FUNCTIONS.

 There are 5 aggregate functions.

 1. COUNT() : this function returns the number of rows that matches a specified criteria.

2. SUM() : this function returns the total sum of a selected numeric Column.

3. MAX() : this function returns the largest value of the selected numeric Column.

4. MIN() : this function returns the smallest value of the selected numeric Column.

5. AVG(): this function returns the average value of a selected numeric Column.

The SELECT statement has many optional clauses, we will see all these clauses in the next articles.

1. WHERE specifies which rows to retrieve.
2. GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.
3. HAVING selects among the groups defined by the GROUP BY clause.
4. ORDER BY specifies an order in which to return the rows.

Tutorial Index:

1. SQL For QA : Tutorial#1 – Select Query
2. SQL For QA : Tutorial#2 – Where Clause
3. SQL for QA: Tutorial#3 – INSERT INTO Statement
4. SQL for QA: Tutorial#4 – UPDATE Statement
5. SQL for QA: Tutorial#5 – DELETE Statement
6. SQL for QA: Tutorial#6 – ORDER BY Clause
7. SQL for QA: Tutorial#7 – GROUP BY Clause
8. SQL for QA: Tutorial#8 – UNION Clause
9. SQL for QA: Tutorial#9 – JOIN Clause