Basic of SQL a tester must know – II

In the first post on SQL, We discuss on very basic queries in SQL , In this post we will discuss further from where we left in post - 1

The post is prepared using SQL Server, and there might be slight change in the query in Oracle or other database servers. We will cover these wherever applicable. Let us continue with table name Organisation with following details as shown below:

Let us discuss on more queries from where we left in the previous article.

•   Query to get top records from the table

•    Select Top 2 from Organisation

•    Syntax: Select top number|Percentage from [tableName].

•  The above query is specific to SQL Server, The syntax for the same in MySQL and Oracle is as follows:

•    Select * from Organisation where rownum<=2   --- Oracle

•    Select * from Organisation limit 2   --- MySQL 

• 
  

• Query to search for multiple values in a column in SQL – Using In Operator or between Operator, we can search for multiple values for a specific column

•   Select * from Organisation where Org_Name in (testorg1,testOrg3)

•  Syntax: Select * from [tableName] where columnName in (value1,value2) 

•  Query to search for multiple values in a Column in SQL – Using between Operator. The below example will list all Organisation with values of Org_Code between 10 and 2000.

•   Select * from Organisation where Org_Code between 10 and 2000

• Syntax: Select * from [tableName] where columnName between  value1 and value2

• Query to search for multiple values in a Column in SQL – Similar to Between , We can use not Between Operator. The below example will list all Organisation with values of Org_Code excluding values between 10 and 2000.

• Select * from Organisation where Org_Code not between 10 and 2000
•  Syntax: Select * from [tableName] where column_Name  not between  value1 and value2

•  Query to search for multiple values in a Column in SQL – Similar to In and Between, We can use LIKE operator together with Wild cards to search for a specified pattern in a column.

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

• Using Group By with aggregate functions can help us to extract useful information from the tables. For e.g In above Organisation table, we need to know how many organizations’ belongs to Organisation Type ‘Auto’ and how many to ‘Manual’. We can use group by Clause together with aggregate function as shown below:

•     Select Org_Type , count(*) as CntOrg from Organisation group by Org_Type.
•       The best practice is to provide Column names used in Group By Clause in Select Clause also to generate meaningful information.
•       We can use multiple columns in Group by Clause, e.g : Select Org_Type, Is_Active, count(*) from Organisation where Org_Type is not null group by Org_Type,Is_Active.

• We can use having clause to search for required in SQL for aggregate function similar to Where clause for a column in table: 

• e.g : Select Org_Type, count(1) as Org_Cnt from Organisation group by Org_Type having Org_Cnt>2.
• 
  

•   Different Aggregate function used in SQL  are as follows:

• Sum() - returns the total sum of a numeric column.

• Avg() - returns the average of a numeric column.

• Count() - return the count of rows satisfying condition

• Max() - returns them ax value of numeric column

• Min() - returns the minimum value of a numeric column

• The syntax for aggregate function is :  Select aggregate_Function(column_Name) from Table Name  e.g: Select max(Org_Code) from Organisation.

In the first two posts in SQL, we are done with the basic concept of SQL useful if we need to extract information from a single table. In the next post, we will discuss on string functions, joins and working with multiple tables.