Free Practice Questions for PostgreSQL PGCES-02 Exam

Question 1

Select one incorrect statement concerning the following SQL statement.

CREATE OR REPLACE VIEW sales_view AS SELECT * FROM sales_table ORDER BY sales_date DESC LIMIT 10;

ADefines the view called 'sales_view'.

BReplaces 'sales_view' if it already exists.

CWhen you 'SELECT' the 'sales_view', it displays the first 10 records from the 'sales_table' sorted by the 'sales_date' column in descending order.

DSome errors occur when 'SELECT * FROM sales_table' is executed after the view is defined.

EYou can confirm that the 'sales_view' has been added by querying the view called 'pg_views'.

Correct Answer: D. Some errors occur when 'SELECT * FROM sales_table' is executed after the view is defined.

Question 2

Select two incorrect descriptions regarding the following SQL statements.

CREATE TABLE cities ( name text,

population float ); CREATE

TABLE capitals ( state

char(2) ) INHERITS

(cities);

ADefines the tables called 'cities' and 'capitals'.

B'capitals' inherits 'cities'.

CSearching 'capitals' also searches rows in 'cities'.

DThe columns 'name' and 'population' are also defined in 'capitals'.

EThe second SQL statement results in an error, since the 'INHERITS' keyword is no longer available.

Correct Answer: C. Searching 'capitals' also searches rows in 'cities'.; E. The second SQL statement results in an error, since the 'INHERITS' keyword is no longer available.

Question 3

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;

AEXCEPT

BUNION

CNAND

DINTERSECT

EINTERSECT ALL

Correct Answer: A. EXCEPT

Question 4

Based on the following request, select the most appropriate command for creating a database cluster. ?Character encoding of the template database needs to be EUC_JP ?Locale is not used

Ainitdb --locale=EUC_JP --no-encoding

Binitdb --default-encoding=EUC_JP

Cpg_ctl init --locale=EUC_JP

Dinitdb --encoding=EUC_JP --no-locale

Epg_ctl init --encoding=EUC_JP

Correct Answer: D. initdb --encoding=EUC_JP --no-locale

Question 5

Select two incorrect statements regarding 'TRIGGER'.

AWhen UPDATE is executed to the table, the specified function can be called.

BWhen INSERT is executed to the table, the specified function can be called.

CWhen SELECT is executed to the table, the specified function can be called.

DA trigger can be set up to call a specified function before or after the event occurs.

EA corresponding rule is automatically created when a trigger is created.

Correct Answer: C. When SELECT is executed to the table, the specified function can be called.; E. A corresponding rule is automatically created when a trigger is created.

Question 6

Select an incorrect statement regarding the following SQL statement. Note that "user_view" is a view. CREATE OR REPLACE RULE rule_1 AS ON UPDATE TO user_view DO INSTEAD NOTHING;

AIt is defining a rule 'rule_1'.

BIt will replace 'rule_1' if it already exists.

CExecuting 'UPDATE user_view' will no longer output errors.

DWhen executing 'UPDATE user_view', data is updated in the table that is the origin of the view.

E'DROP RULE rule_1 ON user_view' deletes the above definition.

Correct Answer: D. When executing 'UPDATE user_view', data is updated in the table that is the origin of the view.

Question 7

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

Correct Answer: C. 3 rows

Question 8

Select two incorrect statements about the Point-In-Time Recovery (PITR) from below.

AThis is a backup method integrating a physical backup and a transaction log (WAL).

BIt is necessary to stop the database server to perform a backup for the first time.

CUpdated data is continuously saved.

DA restore can be performed to any arbitrary point in time since the starting point of PITR.

EA backup can only be performed on a per-database basis.

Correct Answer: B. It is necessary to stop the database server to perform a backup for the first time.; E. A backup can only be performed on a per-database basis.

Question 9

Select the SQL command that must be executed prior to executing the EXECUTE command.

ACREATE FUNCTION

BPREPARE

CDECLARE

DLOAD

EALLOCATE

Correct Answer: B. PREPARE

Question 10

What does the following command do? Choose the most appropriate statement from the selection below.

Note: $ is the command prompt.

$ pg_dump postgres > pgsql

AWrites a backup of the database postgres to the file pgsql.

BWrites a backup of the entire database cluster using user postgres to the file pgsql.

CBacks up the database postgres and writes an error message to the file pgsql.

DWrites a backup of the entire database cluster to the file postgres and writes an error message to the file pgsql.

EOutputs all of the content of the database postgres to the screen using the user pgsql.

Correct Answer: A. Writes a backup of the database postgres to the file pgsql.