PostgreSQL PGCES-02 Exam Practice Test Instant Access

Question 1

Select two suitable statements regarding creating a new table.

AThere is no upper limit to the number of columns in a table.

BA newly created table is empty and has 0 rows.

CYou can only use alphabetic characters for a table name.

DThe row name must be within 16 characters.

EThe SQL 'CREATE TABLE' statement is used to create a new table.

Answer : B, E

Question 2

Given the following two table definitions, select one SQL statement which will cause an error. CREATE TABLE sample1 (id INTEGER, data TEXT); CREATE TABLE sample2 (id INTEGER);

ASELECT id AS data, data FROM sample1;

BSELECT id, id FROM sample1;

CSELECT s1.id, s2.id FROM sample1 AS s1, sample1 AS s2;

DSELECT s1.id, s2.id FROM sample1 s1, sample2 s2;

ESELECT s1.id, s2.data FROM sample1 s1, sample2 s2;

Answer : E

Question 3

There is a table "tb1" that has a column "c1" defined as type TEXT. The following SQL is executed while client "A" is connected. BEGIN; LOCK TABLE tb1 IN ACCESS EXCLUSIVE MODE; SELECT * FROM tb1; While the above 'SELECT' statement is being executed, client "B" connects to the same database and executes the following SQL. Select two correct statements describing the behavior of PostgreSQL. INSERT INTO tb1 (c1) VALUES ('new line'); Note: the default transaction isolation level is set to "read committed".

AThe process for client 'B' is blocked until the current connection for client 'A' is finished.

BThe process for client 'B' is blocked until the current transaction for client 'A' is finished.

CThe process for client 'B' will be deleted regardless of the condition of client 'A'.

DThe process of client 'B' will affect the SELECT result of client 'A'.

EThe process of client 'B' will not affect the SELECT result of client 'A'.

Answer : B, E

Question 4

Select two suitable statements regarding the pg_dump command.

Apg_dump is an SQL command.

BOnly the user who executed initdb can execute pg_dump.

Cpg_dump can create a backup while postmaster is running.

Dpg_dump can create a backup while postmaster is stopped.

Epg_dump can create a backup of a specified table.

Answer : C, E

Question 5

Table "t1" is defined below. Table "t1" has a column "id" of type INTEGER, and a column "name" of type TEXT. t1: The following SQL is executed while client "A" is connected. BEGIN;

SELECT * FROM t1 WHERE id = 2 FOR UPDATE; SELECT * FROM t1 WHERE id = 1 FOR UPDATE; -- (*) While the second 'SELECT' statement, shown with (*), is being executed, a separate client "B" connects and executes the following SQL. Select the correct statement about the execution results. UPDATE t1 SET name = 'turtle' WHERE id = 2; Note: the default transaction isolation level is set to "read committed".

AThe update process for client 'B' is blocked until the current connection for client 'A' is finished.

BThe update process for client 'B' is blocked until the current transaction for client 'A' is finished.

CThe 'UPDATE' process for client 'B' proceeds regardless of the condition of client 'A'.

DThe process of client 'B' immediately generates an error.

EThe processes for both clients are blocked, and an error stating that a deadlock has been detected is generated.

Answer : B

Question 6

Select two incorrect statements regarding 'DOMAIN'.

AWhen defining a domain, you can add a default value and constraints to the original data.

BDomain is a namespace existing between databases and objects such as tables.

CA domain is created by 'CREATE DOMAIN'.

DA domain can be used as a column type when defining a table.

ETo define a domain, both input and output functions are required.

Answer : B, E

Question 7

Table "t1" is defined as follows: CREATE TABLE t1 (value VARCHAR(5)); A set of SQL statements were executed in the following order. Select the number of rows that table "t1" has after execution. BEGIN; INSERT INTO t1 VALUES ('AA'); SAVEPOINT point1; INSERT INTO t1 VALUES ('BB'); SAVEPOINT point2; INSERT INTO t1 VALUES ('CC'); ROLLBACK TO point1; INSERT INTO t1 VALUES ('DD'); END;

A1 row

B2 rows

C3 rows

D4 rows

E0 rows

Answer : B

PostgreSQL PGCES-02 PostgreSQL CE 8 Silver Exam Practice Test