Trusted PostgreSQL PGCES-02 Exam Dumps – Avoid Costly Exam Retakes

Question No: 1

What phenomenon occurs if PostgreSQL is used without performing VACUUM ?

Select two appropriate descriptions from those below.

APerformance is reduced.

BIt gradually gets to the point where connections are denied.

CThe physical size of the database increases considerably in size.

DOnly SELECT queries will be accepted.

EAn e-mail prompting the administrator to perform VACUUM is sent from PostgreSQL.

Show Answer

Question No: 2

MultipleChoice

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

Options

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

Question No: 3

MultipleChoice

Select one false statement about the benefits of using database management systems from below.

Options

AYou can separate the data storage method from the application.

BYou can separate the data search method from the application.

CYou can separate the data display method from the application.

DYou can reduce the programming workload of programming for managing data.

EYou can share data more easily on systems consisting of multiple computers.

Question No: 4

MultipleChoice

I would like to restore the database cluster from the "db1.dump" backup file. Select the correct command from below. (Note: "postgres" is the superuser)

Options

Apg_restore -U postgres -f db1.dump db1

Bpg_dump --restore db1 < db1.dump

Cpg_dump -U postgres --restore db1 < db1.dump

Dpsql -U postgres -f db1.dump db1

Epg_resetxlog -U postgres db1 < db1.dump

Question No: 5

MultipleChoice

Choose an incorrect statement regarding prepared statements, and 'PREPARE' / 'EXECUTE' commands.

Options

A'PREPARE'/'EXECUTE' is mainly used to optimize performance.

B'PREPARE' creates a plan for the prepared statement.

C'PREPARE' can only specify 'SELECT' as a prepared statement.

D'EXECUTE' executes the plan defined by 'PREPARE'.

E'DEALLOCATE' deallocates prepared statements.

Question No: 6

MultipleChoice

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 ('A'); SAVEPOINT sp; INSERT INTO t1 VALUES ('B'); ROLLBACK TO sp; INSERT INTO t1 VALUES ('C'); COMMIT;

Options

A1 row

B2 rows

C3 rows

D4 rows

E0 rows

Question No: 7

MultipleChoice

Select two correct statements about the command shown below.

Note: $ is the command prompt. $ pg_ctl reload

Options

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

BThe command temporarily stops the PostgreSQL server process and restart it.

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

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

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

Question No: 8

MultipleChoice

Select the most suitable statement about the PostgreSQL license from below.

Options

APostgreSQL is distributed under the GPL license.

BPostgreSQL is distributed under the PostgreSQL license.

CPostgreSQL is distributed under the LGPL license.

DPostgreSQL is distributed under the BSD license.

EPostgreSQL is distributed under the X11(MIT) license.

Question No: 9

MultipleChoice

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;

Options

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.

Question No: 10

MultipleChoice

Select two incorrect statements related to the command below.

Note: $ is the command prompt. $ psql -U foo -c 'COPY company TO stdout;' bar

Options

AIf the company table is not readable, an error occurs.

BThe content of the company table is written into a file called 'stdout'.

CThe content of the company table is output in TAB delimited format.

DAn error occurs unless the user foo has administrator privileges.

EConnects to the database bar.

Question No: 11

MultipleChoice

The tables 't1' and 't2' are defined in the same way (they have the same data types and column names). You want to select rows in 't1' which are not in 't2'. Select a correct keyword to fill in the blank below. SELECT * FROM t1 ______ SELECT * FROM t2;

Options

AEXCEPT

BUNION

CNAND

DINTERSECT

EINTERSECT ALL

Question No: 12

MultipleChoice

The table 't1' is defined by the following SQL statement: CREATE TABLE t1 (id integer, name varchar(20)); You want to increase the execution speed of the SQL statement below: SELECT id, name FROM t1 WHERE id < 123 AND upper(name) = 'MAMMOTH'; Select the most suitable SQL statement to create an index.

Options

ACREATE INDEX t1_idx ON t1 (id, upper(name));

BCREATE INDEX t1_idx ON t1 USING HASH (id);

CCREATE INDEX t1_idx ON t1 (name);

DALTER TABLE ADD INDEX ON t1 (id, upper(name));

EALTER TABLE ADD INDEX ON t1 (id, name);

Question No: 13

MultipleChoice

The 'sample' table consists of the data below. The column 'x' is of type INTEGER.

How many rows are returned by executing the following SQL statement? SELECT

6 / x FROM sample WHERE CASE WHEN x = 0 THEN FALSE ELSE TRUE END;

Options

A'ERROR division by zero' and no rows are returned.

B0 rows with no errors.

C1 row

D2 rows

E3 rows

Question No: 14

MultipleChoice

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.

Options

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;

Question No: 15

MultipleChoice

The table 'tbl' is defined below such that it could only store non-negative integers in the column 'nn'. Select the keyword that is applicable for the underlined blank. CREATE _______ natural_number AS DECIMAL CHECK (VALUE >= 0); CREATE TABLE tbl(nn natural_number);

Options

AVIEW

BSCHEMA

CRULE

DTYPE

EDOMAIN

Question No: 16

MultipleChoice

The tables 't1' and 't2' are defined below. Tables 't1' and 't2' have columns 'id' that are of INTEGER type, and columns 'name' that are of TEXT type. t1 t2 The following SQL command was executed. Select the number of rows in the result. SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;

Options

A2 rows

B3 rows

C4 rows

D5 rows

E6 rows

Question No: 17

MultipleChoice

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;

Options

A1 row

B2 rows

C3 rows

D4 rows

E5 rows

Question No: 18

MultipleChoice

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

Options

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

Question No: 19

MultipleChoice

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;

Options

A2 rows

B3 rows

C4 rows

D5 rows

E6 rows

Question No: 20

MultipleChoice

The 'animal' table consists of the following data: Select the correct result returned by executing the following SQL statement: SELECT name FROM animal ORDER BY weight DESC LIMIT 2 OFFSET 1;

Options

AA syntax error will occur.

Free PostgreSQL PGCES-02 Exam Dumps November 2026