![](data:image/svg+xml;charset=utf-8,<svg height="185" width="199" xmlns="http://www.w3.org/2000/svg" version="1.1"/>)
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:
SELECT Column1, Column2,...... FROM xTablenamex;
here Column1, Column2 are the Field names or Attribute names.
Letxs assume there is an Employee table as below:
ID | Name | Department | Salary ------+-----------+--------------+------- 001 | John | IT | 40000 002 | Mike | IT | 30000 003 | Ryan | HR | 60000 004 | Anna | Testing | 20000 005 | Merry | Admin | 50000 006 | Sam | Database | 35000 007 | Jack | HR | 75000 008 | Penny | Database | 25000 009 | Jenny | Admin | 37000
1. If you want to display all the available fields from the table,  use the following syntax
Query :
SELECT * FROM Employee;
Result :
ID | Name | Department | Salary ------+-----------+--------------+------- 001 | John | IT | 40000 002 | Mike | IT | 30000 003 | Ryan | HR | 60000 004 | Anna | Testing | 20000 005 | Merry | Admin | 50000 006 | Sam | Database | 35000 007 | Jack | HR | 75000 008 | Penny | Database | 25000 009 | Jenny | Admin | 37000
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
SELECT ID,Salary FROM Employee;
Result
ID | Salary ------+------- 001 | 40000 002 | 30000 003 | 60000 004 | 20000 005 | 50000 006 | 35000 007 | 75000 008 | 25000 009 | 37000
3. SELECT with DISTINCT keyword
You can select only the DISTINCT(avoids duplicate values) values from the table.
for eg., Distinct departments FROM the xDepartmentx Column in the xEmployeex table can be selected using below query
Query:
SELECT DISTINCT Department FROM Employee;
Result:
Department -------------- Testing IT Admin HR Database
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:
SELECT Name AS Employee Name, Salary AS Compensation FROM Employee;
Result:
Employee Name | Compensation -----------------+--------------- John | 40000 Mike | 30000 Ryan | 60000 Anna | 20000 Merry | 50000 Sam | 35000 Jack | 75000 Penny | 25000 Jenny | 37000
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:
SELECT Name, Salary*0.1 AS Bonus FROM Employee;
Result:
Result: Name | Bonus -----------+-------- John | 4000.0 Mike | 3000.0 Ryan | 6000.0 Anna | 2000.0 Merry |5000.0 Sam |3500.0 Jack |7500.0 Penny |2500.0 Jenny |3700.0
5. SQL Concat() Function with SELECT query:
In SQL, Concat() function is used with SELECT query to join the multiple Column data together x 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.
SELECT CONCAT(Name, '(', Department, ')') as name ,Salary FROM Employee;
OUTPUT:
Name | Salary -------------------------+------- John(IT) | 40000 Mike(IT) | 30000 Ryan(HR) | 60000 Anna(Testing) | 20000 Merry(Admin) | 50000 Sam(Database) | 35000 Jack(HR) | 75000 Penny(Database) | 25000 Jenny(Admin) | 37000
SELECT query AGGREGATE FUNCTIONS.
 There are 5 aggregate functions.
 1. COUNT() : this function returns the number of rows that matches a specified criteria.
SELECT COUNT(Name) FROM Employee;
COUNT ------- 9
2. SUM() : this function returns the total sum of a selected numeric Column.
SELECT SUM(Salary) FROM Employee;
SUM -------- 372000
3. MAX() : this function returns the largest value of the selected numeric Column.
SELECT MAX(Salary) FROM Employee;
MAX ------- 75000
4. MIN() : this function returns the smallest value of the selected numeric Column.
SELECT MIN(Salary) FROM Employee;
MIN ------- 20000
5. AVG(): this function returns the average value of a selected numeric Column.
SELECT AVG(Salary) FROM Employee;
AVG -------------------- 41333.333333333333
The SELECT statement has many optional clauses, we will see all these clauses in the next articles.
- WHERE specifies which rows to retrieve.
- GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.
- HAVING selects among the groups defined by the GROUP BY clause.
- ORDER BY specifies an order in which to return the rows.
Tutorial Index:
- SQL For QA : Tutorial#1 â Select Query
- SQL For QA : Tutorial#2 â Where Clause
- SQL for QA: Tutorial#3 â INSERT INTO Statement
- SQL for QA: Tutorial#4 â UPDATE Statement
- SQL for QA: Tutorial#5 â DELETE Statement
- SQL for QA: Tutorial#6 â ORDER BY Clause
- SQL for QA: Tutorial#7 â GROUP BY Clause
- SQL for QA: Tutorial#8 â UNION Clause
- SQL for QA: Tutorial#9 â JOIN Clause