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

Question No: 1

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: 2

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: 3

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: 4

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: 5

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: 6

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: 7

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: 8

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: 9

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: 10

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: 11

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: 12

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: 13

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: 14

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: 15

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.

Question No: 16

MultipleChoice

The 'sample' table consists of the following data: How many rows are returned by executing the following SQL statement? SELECT * FROM sample WHERE v ~ 'ab';

Options

A0 rows

B1 row

C2 rows

D3 rows

E4 rows

Question No: 17

MultipleChoice

The table 'custom' is defined below. The 'id' column and 'introducer' column are of

INTEGER type, and the 'email' column is of TEXT type.

id | email | introducer ---- +-----------------+-----------2 |

aaa@example.com | 1 3 |

bbb@example.com | 2 4 |

ccc@example.com | 2

Three SQL statements were executed in the following order:

INSERT INTO custom SELECT max(id) + 1, 'ddd@example.com', 4

FROM custom; UPDATE custom SET introducer = 999 WHERE email = 'bbb@example.com'; DELETE FROM custom WHERE introducer NOT IN (SELECT id FROM custom); Select the number of rows in the 'custom' table after the execution.

Options

A0 rows

B1 row

C2 rows

D3 rows

E4 rows

Question No: 18

MultipleChoice

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

Options

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.

Question No: 19

MultipleChoice

The 'sample' table consists of the following data: How many rows are returned by executing the following SQL statement? SELECT DISTINCT ON (data) * FROM sample;

Options

A2 rows

B3 rows

C4 rows D. 5 rows

E6 rows

Question No: 20

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 ('AA'); SAVEPOINT point1; INSERT INTO t1 VALUES ('BB'); SAVEPOINT point2; INSERT INTO t1 VALUES ('CC'); ROLLBACK TO point1; INSERT INTO t1 VALUES ('DD'); END;

Options

A1 row

B2 rows

C3 rows

D4 rows

E0 rows

Free PostgreSQL PGCES-02 Exam Dumps May 2026