PostgreSQL PGCES-02 Exam Practice Test Instant Access

Question 1

The "sample" table consists of the following data. How many rows are returned by executing the following SQL statement? SELECT i FROM sample GROUP BY i;

A1 row

B2 rows

C3 rows

D4 rows

E5 rows

Answer : C

Question 2

I want to restore data from a text format backup file foo.dump. Select an appropriate command.

Apg_dump -R foo < foo.dump

Bpg_restore -d foo foo.dump

Cpg_restore -f foo.dump foo

Dpsql -f foo.dump foo

Ecreatedb foo < foo.dump

Answer : D

Question 3

Select the correct command to collect and save the statistical information of a table.

AANALYZE

BCLUSTER

CREINDEX

DSTATISTIC COLLECTION

ESTATISTIC COLLECTOR

Answer : A

Question 4

In the "customer" table, you want to change the "email" values which have an "id" value of 10000 or less, to NULL. Select the correct SQL statement to achieve this task.

AUPDATE email = NULL FROM customer WHERE id <= 10000;

BUPDATE customer SET email IS NULL WHERE id < 10001;

CUPDATE customer SET email = NULL WHERE id <= 10000;

DDELETE FROM customer.email WHERE id < 10001;

EUPDATE FROM customer SET email = NULL WHERE id <= 10000;

Answer : C

Question 5

A table is defined as below. Select the most suitable description about the foreign key constraint. CREATE TABLE master (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE record (id INTEGER REFERENCES master (id), count INTEGER);

AIf any row exists in the 'record' table, no change can be made to the 'master' table.

BIf the 'record' table contains a row with an 'id', no change can be made at all to the corresponding 'id' row in the 'master' table.

CIf the 'record' table contains a row with an 'id', the corresponding 'id' row in the 'master' table cannot be deleted.

DThe 'record' table cannot have duplicate 'id's.

EThese SQL statements are invalid; no constraints are created.

Answer : C

Question 6

Select two suitable statements about sequences.

AA sequence always returns a 4-byte INTEGER type value, so the maximum value is 2147483647.

BA sequence is defined by 'CREATE SEQUENCE', and deleted by 'DROP SEQUENCE'.

CAlthough the 'nextval' function is called during a transaction, it will have no effect if that transaction is rolled back.

DA sequence always generates 0 or consecutive positive numbers.

EA sequence number can be set by calling the 'setval' function.

Answer : B, E

Question 7

The table "t1" is defined below. The column "id"

for table "t1" is of INTEGER type.

id | name ----

+-----------1 |

mammoth 2 |

tortoise 3 |

coelacanth

The following SQL statements were executed. Select the correct statement about the execution result. BEGIN; DECLARE c SCROLL CURSOR FOR SELECT name FROM t1 ORDER BY id;

MOVE FORWARD 2 FROM c; FETCH FORWARD ALL FROM c; COMMIT;

AThe number of rows returned by the FETCH statement is 0.

BThe number of rows returned by the FETCH statement is 1.

CThe number of rows returned by the FETCH statement is 2.

DThe number of rows returned by the FETCH statement is 3.

EAn error occurs part way through.

Answer : B

PostgreSQL CE 8 Silver PGCES-02 Exam Practice Test