PostgreSQL Interview Questions and Answers (13) - Page 1

What is the purpose of Overlay function of PostgreSQL?

It's purpose is to replace substring from a specified position to a specified position.

e.g.

Select Overlay('www.dotnetanda.com' Placing 'funda' From 11 For 4) "Overlay Example"

/* Result */

Overlay Example
---------------
www.dotnetfunda.com


As can be figure out that "anda" has been replaced by "funda". The replacement started from position 11 and should continue till 4 since the length of "anda" is 4.So the characters between 11 to 15 (11+4) are "anda" and those should be replaced by "funda".
What is the purpose of String_Agg function in PostgreSQL?

The "String_Agg" function input values concatenated into a string separated by delimiter.

Let us consider that we have two tables say Employee and Department with the below data.

/* Employee Table(tblEmployee) */

DeptId EmpName
------- -------
1 Shashi Bhushan
1 Deepak Singh
1 Abhijeet Moshambique
1 Manish Bharat
2 Fatima Sarani
2 Sumanta Manik
3 Amitav Mallik
3 Deepak Kumar Goyal
3 Amitav Salonki
4 Amit Ojha



/* Department Table(tblDept) */
DeptId DeptName
------ --------
1 Accounts
2 Finance
3 IT
4 Sales

*/


And we are looking for the below output

/* Result */

Deptname Employee List
-------- -------------
Accounts Shashi Bhushan,Deepak Singh,Abhijeet Moshambique,Manish Bharat
Finance Fatima Sarani,Sumanta Manik
IT Deepak Kumar Goyal,Amitav Salonki
Sales Amit Ojha


We can achieve this by using String_Agg function as under

Select DeptName, String_Agg((EmpName), ',') "Employee List"

From tblEmployee
Join tblDept
Using(DeptId)
Group By DeptName;

What is the purpose of Array_To_String in PostgreSQL?

The "Array_To_String" function concatenates array elements using provided delimiter.

Let us consider that we have two tables say Employee and Department with the below data.

/* Employee Table(tblEmployee) */

DeptId EmpName
------- -------
1 Shashi Bhushan
1 Deepak Singh
1 Abhijeet Moshambique
1 Manish Bharat
2 Fatima Sarani
2 Sumanta Manik
3 Amitav Mallik
3 Deepak Kumar Goyal
3 Amitav Salonki
4 Amit Ojha



/* Department Table(tblDept) */
DeptId DeptName
------ --------
1 Accounts
2 Finance
3 IT
4 Sales

*/


And we are looking for the below output

/* Result */

Deptname Employee List
-------- -------------
Accounts Shashi Bhushan,Deepak Singh,Abhijeet Moshambique,Manish Bharat
Finance Fatima Sarani,Sumanta Manik
IT Deepak Kumar Goyal,Amitav Salonki
Sales Amit Ojha


We can achieve this by using Array_To_String function as under

SELECT DeptName, Array_To_String(Array_Agg(EmpName),',') "Employee List"

FROM tblEmployee
Join tblDept
Using(DeptId)
Group By DeptName;

What is the purpose of USING Operator in the ORDER BY clause of PostgreSQL?

It is a short hand notation of Descending and Ascending .e.g.

CREATE TABLE test

(
id serial NOT NULL,
"number" integer,
CONSTRAINT test_pkey PRIMARY KEY (id)
)

insert into test("number") values (1),(2),(3),(0),(-1);

select * from test order by number USING > //gives 3=>2=>1=>0=>-1

select * from test order by number USING < //gives -1=>0=>1=>2=>3

How will you create your aggregate function using PostgreSQL?

Let us consider that we have two tables say Employee and Department with the below data.

/* Employee Table(tblEmployee) */

DeptId EmpName
------- -------
1 Shashi Bhushan
1 Deepak Singh
1 Abhijeet Moshambique
1 Manish Bharat
2 Fatima Sarani
2 Sumanta Manik
3 Amitav Mallik
3 Deepak Kumar Goyal
3 Amitav Salonki
4 Amit Ojha



/* Department Table(tblDept) */
DeptId DeptName
------ --------
1 Accounts
2 Finance
3 IT
4 Sales

*/


And we are looking for the below output

/* Result */

Deptname Employee List
-------- -------------
Accounts Shashi Bhushan,Deepak Singh,Abhijeet Moshambique,Manish Bharat
Finance Fatima Sarani,Sumanta Manik
IT Deepak Kumar Goyal,Amitav Salonki
Sales Amit Ojha


We can achieve this by using custom aggregate function as under

CREATE  AGGREGATE ListAggregation1 (anyelement)(  

sfunc = array_append,
stype = anyarray,
initcond = '{}'
);


Then invoke it as under

Select DeptName,Array_To_String(ListAggregation1(EmpName),',') "Employee List"

FROM tblEmployee
Join tblDept
Using(DeptId)
Group By DeptName;

What is CBRT function in PostgreSQL?how can we simulate the same in SQL Server?

CBRT function returns the cube root of a number.

e.g Select CBRT(64)

Result is : 4

In Sql Server we can achieve the same by using the POWER function where we need to apply the formula as POWER(float_expression,1/y)

e.g. SELECT POWER(64.0,1.00/3.00)
What is Cube Root Operator(||/) in PostgreSQL?

Returns Cube Root of a number.

e.g. Select ||/ 16 "CubeRoot of 16"

Result:

CubeRoot of 16
----------------
2.5
What is Array_To_String function in PostgreSQL?

This functions concatenates an array element to a string provided the join string is supplied. It returns a string.

Syntax :Array_To_String(Array,join string)

e.g.

Select Array_To_String(ARRAY['Hello','World','Today','I','am','learning','Array_To_String','It','is','very','cool'], ' ')
/* Result */
array_to_string
----------------
"Hello World Today I am learning Array_To_String It is very cool"
What is String_To_Array function in PostgreSQL?

This functions splits a string to an array provided the delimeter is supplied. It returns an array.

Syntax: String_To_Array(String,delimeter)

e.g.

Select String_To_Array('Hello World Today I am learning String_To_Array It is very cool',' ')

/* Result */
string_to_array
----------------
{Hello,World,Today,I,am,learning,String_To_Array,It,is,very,cool}
How to find the Version of PostgreSQL?

We can get the version information in PostgreSQL by using the version function as shown under

select version();


/* Result */
version
-------
"PostgreSQL 9.1.0, compiled by Visual C++ build 1500, 32-bit"

Which keyword is used to select N no of records in PostgreSQL?

NOTE: This is objective type question, Please click question title for correct answer.
Which functions concatenates an array element to a string provided the join string is supplied?

NOTE: This is objective type question, Please click question title for correct answer.
Which function splits a string to an array provided the delimiter is supplied?

NOTE: This is objective type question, Please click question title for correct answer.
Found this useful, bookmark this page to the blog or social networking websites. Page copy protected against web site content infringement by Copyscape

 Interview Questions and Answers Categories