Basic Queries in SQL a tester must know - I

Making SQL query of database is must for a tester to know. In this blog, I will explain some basic query of SQL must for a tester to know. In later posts in this series, we will discuss more on nested queries, joins,working with conditional loops in sql and other useful concepts from a tester point of view. The post is prepared using SQL Server, and there might be slight change in the query in Oracle or other database servers.

To start with, let us assume, there is a table name Organisation with following details as shown below:

Let us know some basic queries to fetch information from a single table.

•     Query to get all information from the table
•    Select * from Organisation  
•   Syntax: Select * from [tableName]

•     Query to get particular column(s) information from table
•  Select Org_Name, Org_Code from Organisation
• Syntax : Select [Col_Name1], [Col_Name2] from [tableName]

• ·  Using Where Clause to get subset of information
•    Select * from Organisation where Org_Type = ‘Auto’
• Syntax: Select * from [tableName] where [Col_Name1] = <expected value>                                                      
•    Using Where Clause to get subset based on multiple condition in where clause
•      Select * from Organisation where Org_Type = ‘Auto’ Or Org_Code = 201
• Syntax: Select * from [tableName] where [Col_Name1] = <expected value> Or [Col_Name2] = <expected value> 

•    Using alias to get more readable and presentable form
•     Select Org_Name as Organisation Name from Organisation
• Syntax: Select [Column Name] as AliasName from [TableName]
•    Using Distinct to get distinct records of a table
•    Select distinct Org_Type from Organisation
• Syntax: Select distinct [Column Name] from [TableName]
•     To get multiple distinct combination use:
•   Select distinct Org_Type,Org_Code from Organisation
• Syntax: Select distinct [Column Name] from [TableName] 

•     Using Order by to sort the data in their ascending or descending order:
•   Select * from Organisation Order by Org_Code desc
• Syntax: Select * from [table] Order by [column name] desc/asc
•   Select * from Organisation where Org_Code>100 order by Org_Name desc

•  Finding rows with value as null for a column
•   Select * from Organisation where Org_type is null
•  Syntax: Select * from [table] where [column name]  is null

•        Finding rows with value as not null for a column
•         Select * from Organisation where Org_type is not null
•       Syntax: Select * from [table] where [column name]  is not null

•   Finding Count of records matching the SQL query
•    Select Count(1) from Organisation where Org_Type = ‘Auto’ Or Org_Code = 201
• Using count(1) makes the query more faster compared to count(*)

•    The LIKE operator is used to search for a specified pattern in a column.
• Select * from Organisation where Org_Name like ‘%test%’.
• In the example % acts as wildcard to search for pattern which test displayed anywhere in the Org_Name.
• Other examples include:
• Select * from Organisation Where Org_Name  like '_ estOrg2'; (wildcard for one character)
• Select * from Organisation Where OrgName  like '[tfd]%’; (wildcard for all org_name starting with t, f, or d)