PGCES-02 Online Practice Questions and Answers

SQL statements were executed in the following order: CREATE TABLE fmaster (id INTEGER PRIMARY

KEY, name TEXT); CREATE TABLE ftrans (id INTEGER REFERENCES fmaster (id), stat INTEGER, date

DATE); INSERT INTO fmaster VALUES (1, 'itemA');

INSERT INTO ftrans VALUES (1, 1, CURRENT_DATE);

Select two SQL statements that will generate an error when executed next.

A. INSERT INTO ftrans VALUES (1, 1, CURRENT_DATE);

B. INSERT INTO ftrans VALUES (2, 1, '2007-07-07');

C. UPDATE fmaster SET name = 'itemAX' WHERE id = 1;

D. UPDATE fmaster SET id = 100 WHERE id = 1;

E. UPDATE ftrans SET id = 200 WHERE id = 1;

The tables "t1" and "t2" are defined below. The tables "t1" and "t2" have columns "id" which are type of INTEGER and column "name"s which are type of TEXT. t1 t2 The following SQL command was executed. Select the number of rows in the result. SELECT

*

FROM t1 NATURAL FULL OUTER JOIN t2;

A.

2 rows

B.

3 rows

C.

4 rows

D.

5 rows

E.

6 rows

Select two correct statements about the command shown below. Note: $ is the command prompt. $ pg_ctl reload

A. The command forces the content of pg_hba.conf to be re-read into PostgreSQL server process.

B. The command temporarily stops the PostgreSQL server process and restart it.

C. The command re-reads the postgresql.conf details into the PostgreSQL server process and changes the values of any configuration parameters that can be changed.

D. The command forces the content of the database cluster to be re-read into PostgreSQL server process.

E. The command causes a recovery to be performed from a standard backup file in the PostgreSQL server process.

Select one incorrect statement concerning changes from PostgreSQL version 7.4 to 8.0.

A. SAVEPOINT function was added.

B. Point-In-Time Recovery function was added.

C. The shared buffer control algorithm was improved.

D. Two-phase commit function was added.

E. CSV mode was added to the copy command.

Select an appropriate command to check the PostgreSQL version in psql.

A. \server_version

B. SELECT version;

C. SELECT version();

D. SHOW version;

E. SHOW server;

Select two incorrect statements regarding large objects.

A. A large object is generated by 'CREATE LARGE OBJECT'.

B. A large object is added by 'INSERT'.

C. One large object is able to handle up to 2GB of data.

D. Binary data cannot be used unless declared as a large object.

E. An OID is used to identify a large object.

The table "score" is defined as follows: gid | score -----+------1 | 70 1 | 60 2 | 100 3 | 80 3 | 50 The following query was executed. Select the correct result value. SELECT score FROM score ORDER BY gid DESC, score ASC LIMIT 1;

A. 50

B. 60

C. 70

D. 80

E. 100

The following table called company is defined as follows:

id | name

----+-----------------1 | Pgsql,inc.

2 | Postgres Co.,Ltd

3 | SQL Company.

Select the most appropriate psql command for generating a text file company.txt with the following content

on the client side.

1,Pgsql \,inc.

2,Postgres Co. \,Ltd

3,SQL Company.

A. \copy company TO 'company.txt' WITH ',';

B. \copy company TO 'company.txt' WITH DELIMITER AS ',';

C. \copy company TO 'company.txt' DELIMITER ','

D. \copy company TO "company.txt"

E. \copy company TO company.txt CSV

Select one incorrect statement about the SQL COPY command.

A. It copies data between a file and a table.

B. You can output the row data of a table to a specified client side file.

C. Copies row data from a file into a table.

D. Only the superuser can specify the output file name.

E. The delimiter that separates column data can be changed from the default TAB character.

Select one incorrect statement from the below about a database cluster.

A. It is possible to have multiple databases within a database cluster.

B. A database cluster is created using initdb command.

C. Each host can have only one database cluster.

D. Special databases called template0 and template1 are created in a database cluster by default.

E. When a database cluster is created, a database superuser is registered using the username of the OS at the time of creation unless otherwise designated.