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)

Automation Concepts in QTP and Selenium .