PGCES-02 Real Exam Questions and Verified Answers

Question 1 Multiple choice

Select two statements that the command below DOES NOT do. Note: $ is the command prompt. $
pg_dump -b -F c b > d

A
Backs up the "b" database to the "c" file, and stores error messages to the "d" file.
B
Backs up the "b" database to the "d" file.
C
Backs up large objects.
D
Backs up the "c" database to the "d" file as the "c" user.
E
Creates a backup, which is restorable with the pg_restore command.

Show answer and explanation

Correct answers: A, D

Question 2 Multiple choice

Select two incorrect statements concerning the system catalog.

A
It stores the object definition information for tables and columns.
B
There may be changes to specification by major version upgrades.
C
It is accessible as a table; however, it is output in binary format so it can not be read as is.
D
It stores the internal information of the database management system.
E
It is defined based on the standard SQL specification.

Show answer and explanation

Correct answers: C, E

Question 3 Single choice

Select one incorrect description regarding the following SQL statement defining a function. CREATE OR REPLACE FUNCTION get_file_list(TEXT, BOOLEAN) RETURNS SETOF TEXT LANGUAGE C STRICT
SECURITY DEFINER AS 'myfuncs.so';

A
This function may be defined in 'myfuncs.so'.
B
This function can return multiple rows.
C
This SQL statement defines a function written in the C language.
D
If this function is called with a NULL parameter, it will return 0 when executed.
E
This function operates with the authority of the user who executed it.

Show answer and explanation

Correct answer: E

Question 4 Single choice

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;

Show answer and explanation

Correct answer: C

Question 5 Single choice

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 | [email protected] | 1
3 | [email protected] | 2
4 | [email protected] | 2
Three SQL statements were executed in the following order: INSERT INTO custom SELECT max(id) + 1,
'[email protected]', 4 FROM custom; UPDATE custom SET introducer = 999
WHERE email = '[email protected]'; DELETE FROM custom WHERE introducer NOT IN (SELECT id
FROM custom);
Select the number of rows in the "custom" table after the execution.

A
0 rows
B
1 row
C
2 rows
D
3 rows
E
4 rows

Show answer and explanation

Correct answer: C

Question 6 Multiple choice

Select two incorrect statements concerning PostgreSQL license.

A
It can be used freely.
B
It can be duplicated freely.
C
It can be freely redistributed.
D
Developers are responsible for its maintenance support.
E
Developers are only responsible for handling its crucial faults.

Show answer and explanation

Correct answers: D, E

Question 7 Single choice

A sequence has the following definition: CREATE SEQUENCE seq1 CACHE 10 CYCLE; Select the value
that is returned by executing the following SQL. SELECT nextval('seq1');

A
0
B
1
C
10
D
11
E
-10

Show answer and explanation

Correct answer: B

Question 8 Single choice

Which psql command do you need to execute to display the list of tables in the currently connected database?

A
SELECT * FROM pg_table_list;
B
SHOW tables;
C
\dT
D
\dt "database_name"
E
\dt

Show answer and explanation

Correct answer: E

Question 9 Single choice

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;

A
"ERROR division by zero" and no rows are returned.
B
0 rows with no errors.
C
1 row
D
2 rows
E
3 rows

Show answer and explanation

Correct answer: D

Question 10 Single choice

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 HAVING count(*) = 2;

A
0 rows
B
1 row
C
2 rows
D
3 rows
E
4 rows

Show answer and explanation

Correct answer: C