Understanding Union, Nested Queries and String function in SQL

In the previous article, we discuss on joins in SQL. In this post, we will start with nested query, and then understand how to use union, string function in SQL, and other topics that are useful for a tester to know.

   Problem 1: Let us start with a simple example. We need to know the name of user belonging to Organisations with org_code in Org003, Org004, Org005 using nested queries?

Answer: In this problem, we need to find the User_Name from User table but we need to create query to identify users which belongs to Organisations Org003, Org004, and Org005. Since User table is linked to organisation table by Org_Id. The Query should do something like. Select users from user table which belongs to Organisation having org_code as Org003, Org004, and Org005. This can be written in SQL using nested queries as follows:

Select User_Name from User where Org_Id in (Select Id from Organisation where Org_Code in (‘Org003’,‘Org004’,’Org005’)).

The above is an example of nested query, the same can be written using join as follows:

Select User_ from User us join Organisation org on us.Org_Id = org.Id where org.User_Code in(‘Org003’,‘Org004’,’Org005’)

Problem 2: Next problem is to find count of Organisation which pay salary to employees greater than 1000?

Answer: Salary is a field in User table and also we have org_Id in User table. And we are only concerned with the count of Organisation with salary greater than 1000. We can get the information from only User Table as:

Select count(distinct Org_Id) as OrganisationCount from User where Salary>1000;

Problem 3: Please explain the concept of union in SQL?

Answer: Union is used to combine data from multiple select queries in a record set. Number and data type of items in the select statement should match together.

e.g.: In above tables,

Select Id, Org_Name from Organisation Union Select Id, User_Name from User will fetch results

Select Id, IsActive from Organisation Union Select Id, User_Name from User will fail as the data type of items in the two select statements is not matching.

Select Id from Organisation Union Select Id, User_Name from User will fail as number of items in the two select statements is not matching.

So to union data from two or multiple select statements, the number of arguments, data type of argument, and order of arguments should match, else the union statement will fail.

Similar to Union is Union All. The difference between union and union All is while Union returns distinct record set ignoring duplicate record rows, Union All returns all the rows.

Problem 4: Suggest some useful string functions in SQL?

Answer: Some of useful SQL functions to work with string are:

UCASE() – Converts the string value into Upper Case.

Select UCASE(Org_Name) from Organisation

lcase() – Converts the string value into Lower Case.

Select lcase(Org_Name) from Organisation

Substring() – returns substring for column from start location and length as provided in argument. Similar to this in SQL Server, In Oracle we can Use Mid function.

Select substring(Org_Name,1,4) AS Org_4 from Organisation;

Use functions Substring and len to order by a specific part of a string

Select Org_Name from Organisation order by substring(Org_Name,len(Org_Name)-1,3)

Use + , we can concatenate multiple string and return the results.   

                                                                                                   

how to work with Multiple tables in SQL for tester– Understanding joins

We have gained knowledge on basic queries in SQL working with a single table in previous articles. Let us extend our knowledge working with multiple tables in SQL. Below are the tables, we will use in our examples.

This post will mainly discuss on concept of joins in SQL.In the next post, we will go further and discuss on nested queries, union, and other useful concepts on SQL.

Problem 1: From the above tables, provide Org_Name for each of the users in User Table?

Answer: First of all, we need to identify which information we need to extract from above table. We need Org_Name from Organisation table and Username from User Table.

Second Point is to identify the common attribute/column in two tables based on which we will create the relation between two tables. As in above tables, Org_Id is the foreign Key in table User which is mapped with Id in the Organisation table and is used to create a relation between the two tables.

We can use joins to get data the two tables. JOIN is used to combine rows from two or more tables, based on a common field between them. In this problem, we will use join or inner join or a equi – join to extract the required information

•  INNER JOIN: Returns all rows when there is at least one match in BOTH tables. An Inner Join is same as Join or equi-join.

•  Since we have make a match between Organisation and user table on Org_Id, we will create inner join or equi-join or simply join as shown in example below

• Syntax - Join : Select * from Organisation a join User b on a.Id = b.Org_Id

• Syntax - InnerJoin : Select * from Organisation a innerjoin User b on a.Id = b.Org_Id

• Syntax – Equi-Join : Select * from Organisation a,User b where a.Id = b.Org_Id

Join and Inner Join are same thing and fetches the same results as Equi-Join between two tables.

Other than Inner Join or equi-Join, Other examples of joins are following:

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

• Syntax- Left Join : Select * from Organisation a LEFT join User b on a.Id = b.Org_Id

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

• Syntax- Right Join : Select * from Organisation a Right join User b on a.Id = b.Org_Id

• FULL JOIN: Return combined result of both LEFT and RIGHT joins.

• Syntax- Full Join : Select * from Organisation a full outer join User b on a.Id = b.Org_Id

In above problem, we will use an inner join and can use any syntax from Join, inner-join or equi-join.Since we require Org_Name and User_Name in Output, Instead of fetching all information using Select *, we will extract Org_Name from Organisation table and similarly User_Name from User table. Also we can use alias to giving meaningful name to column. The syntax will be :

Select a.Org_Name as OrganisationName ,b.User_Name as User from Organisation a join User b on a.Id = b.Org_Id

Problem 2: In the above tables, provide Org_Name, Salary and Designation for each of the user in User Table?

Answer: In this example we need to create multiple join between tables. The query will be as follows. Try to understand the query with the concept explained in previous table.

Select us.User_Name, org.Org_Name, us.Salary, des.staff_role from User us join Organisation org on us.Org_Id =org.Id join User_Designation usd on us.Id = usd.User_Id join Designation des on usd.Designation_Id = des.Id

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.