Interview Questions
« Previous | 0 | 1 | 2 | 3 | 4 | Next »

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
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".

2.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 Zaina khan
1 Aksha khan
1 Nasreen khan
1 Ashmayera khan
2 Hudanoor
2 Uzma Khan
3 Bushra khan
3 Shabnam Khan
3 A.N.Khna
4 Farhan khan
/* Department Table(tblDept) */
DeptId DeptName
1 Accounts
2 Finance
3 IT
4 Sales
*/
And we are looking for the below output
/* Result */
DeptId Employee List
AccountsArif Mohammad Khan Malik
Finance FatimaArif Mohammad Khan
ITNaushaad khan
SalesAmit Ojha

3.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 Zaina khan
1 Aksha khan
1 Nasreen khan
1 Ashmayera khan
2 Hudanoor
2 Uzma Khan
3 Bushra khan
3 Shabnam Khan
3 A.N.Khna
4 Farhan khan
/* Department Table(tblDept) */
DeptId DeptName
1 Accounts
2 Finance
3 IT
4 Sales
*/
And we are looking for the below output
/* Result */
DeptId Employee List
AccountsArif Mohammad Khan Malik
Finance FatimaArif Mohammad Khan
ITNaushaad khan
SalesAmit 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;

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

(

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

5.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 Zaina khan
1 Aksha khan
1 Nasreen khan
1 Ashmayera khan
2 Hudanoor
2 Uzma Khan
3 Bushra khan
3 Shabnam Khan
3 A.N.Khna
4 Farhan khan
/* Department Table(tblDept) */
DeptId DeptName
1 Accounts
2 Finance
3 IT
4 Sales
*/
And we are looking for the below output
/* Result */
DeptId Employee List
AccountsArif Mohammad Khan Malik
Finance FatimaArif Mohammad Khan
ITNaushaad khan
SalesAmit 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;

6.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)

7.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

8.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"

9.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}

10.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"

11.What is PostgreSQL? What do you know about PostgreSQL?

PostgreSQL, often simply "Postgres", is an open-source object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. It is released under the PostgreSQL License, a free/open source software license, similar to the MIT License. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of volunteers employed and supervised by companies such as Red Hat and EnterpriseDB.

Read more about PostgreSQL on Wikipedia and PostgreSQL official website

12.What are the various features and advantages of PostgreSQL?

This is very basic question and you should be updated on this. You should know why are you using PostgreSQL in your project, what features and advantages does PostgreSQL provide.

Visit official PostgreSQL website to learn more features and advantages of PostgreSQL

13.What are the key differences between MySQL and PostgreSQL? Which Open Source Database to Choose? Which one is best?

MySQL and PostgreSQL are both free and open source powerful and full-featured databases. You should be able to compare these two databases. Here is the complete article on this.

14.What are the various PostgreSQL database administration commands and tools?

You should know basic PostgreSQL database administration commands like creating users in PostgreSQL, setting up user credentials in PostgreSQL, change / update PostgreSQL user password, check whether PostgreSQL is up and running, commands to create, delete, drop, start, stop, restart, backup, restore PostgreSQL database, getting the list of all databases in PostgreSQL, finding out what version of PostgreSQL is running, PostgreSQL help and history commands, commands to get the list of all the tables in a PostgreSQL database, commands to turn on timing and checking how much time a query takes to execute, commands to see the list of available functions in PostgreSQL etc. Here is the complete article on this topic.

You should also know some of the PostgreSQL administration tools. You can visit Wiki andStackoverflow to get to know various PostgreSQL administration tools.

15.PostgreSQL database general concepts

Beside all this you should be well aware of datatypes in PostgreSQL, DDL, DML, DCL commands used in PostgreSQL. You should have good knowledge of Indexes, Joins, Subqueries, Stored Procedures, Functions, Triggers, Cursors etc.

16.PostgreSQL Database Administration Commands?

Following are basic PostgreSQL database administration commands which each PostgreSQL database administrator should know. These PostgreSQL database administration commands include creating users in PostgreSQL, setting up user credentials in PostgreSQL, change / update PostgreSQL user password, check whether PostgreSQL is up and running, commands to create, delete, drop, start, stop, restart, backup, restore PostgreSQL database, getting the list of all databases in PostgreSQL, finding out what version of PostgreSQL is running, PostgreSQL help and history commands, commands to get the list of all the tables in a PostgreSQL database, commands to turn on timing and checking how much time a query takes to execute, commands to see the list of available functions in PostgreSQL etc. Lets have a look on following PostgreSQL Database Administration Commands.

17.How to change PostgreSQL root user password?

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (oldpassword)
# ALTER USER postgres WITH PASSWORD 'tmppassword';
$ /usr/local/pgsql/bin/psql postgres postgres
Password: (tmppassword)
Changing the password for a normal postgres user is similar as changing the password of the root user. Root user can change the password of any user, and the normal users can only change their passwords as Unix way of doing.
# ALTER USER username WITH PASSWORD 'tmppassword';

18.How to setup PostgreSQL SysV startup script?

$ su - root
# tar xvfz postgresql-8.3.7.tar.gz
# cd postgresql-8.3.7
# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql
# chmod a+x /etc/rc.d/init.d/postgresql

19.How to check whether PostgreSQL server is up and running?

$ /etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[Note: The status above indicates the server is up and running]
$ /etc/init.d/postgresql status
Password:
pg_ctl: no server running
[Note: The status above indicates the server is down]

20.How to start, stop and restart PostgreSQL database?

# service postgresql stop
Stopping PostgreSQL: server stopped
ok
# service postgresql start
Starting PostgreSQL: ok
# service postgresql restart
Restarting PostgreSQL: server stopped
ok

« Previous | 0 | 1 | 2 | 3 | 4 | Next »


copyright © 2014 - all rights riserved by javatechnologycenter.com