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.
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".
Select Overlay('www.dotnetanda.com' Placing 'funda' From 11 For 4) "Overlay Example" /* Result */ Overlay Example 2.What is the purpose of String_Agg function in PostgreSQL?The "String_Agg" function input values concatenated into a string separated by delimiter. /* Employee Table(tblEmployee) */
/* Department Table(tblDept) */
*/
And we are looking for the below output /* Result */
3.What is the purpose of Array_To_String in PostgreSQL?The "Array_To_String" function concatenates array elements using provided delimiter. /* Employee Table(tblEmployee) */
/* Department Table(tblDept) */
*/
And we are looking for the below output /* Result */
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) */
/* Department Table(tblDept) */
*/
And we are looking for the below output /* Result */
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. 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. 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. 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. 15.PostgreSQL database general conceptsBeside 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 |
copyright © 2014 - all rights riserved by javatechnologycenter.com