PostgreSQL PGCES-02 Exam Questions Instant Access

Question 1

Choose the most suitable statement about user management of PostgreSQL.

Note: the version of PostgreSQL is 8.0.

AUsernames not registered in the operating system cannot be registered as PostgreSQL users.

BTo create a new user for PostgreSQL, the 'newuser' command is used.

CPostgreSQL cannot be in a state where multiple users exist at the same time.

DIf you create a user that has permission to create other users, that user will become a superuser that is not subject to any access restriction checks.

ETo delete an existing user for PostgreSQL, 'deleteuser' command is used.

Answer : D

Question 2

Select two appropriate statements from below about the following SQL statements:

CREATE FUNCTION myfunc(INTEGER) RETURNS text LANGUAGE plpgsql STRICT AS '

DECLARE x ALIAS FOR $1; r text := ''default''; BEGIN IF x > 100 THEN SELECT INTO r data FROM mytable WHERE id = x; END IF; RETURN r; END;';

AAn error is generated unless the plpgsql language is registered in the database beforehand.

BThe execution results of SELECT myfunc(-123) differs based on the content of 'mytable'.

CWhen SELECT myfunc(123) is executed an error occurs.

DWhen SELECT myfunc(NULL) is executed an error occurs.

EWhen SELECT myfunc (0) is executed the text 'default' is returned.

Answer : A, E

Question 3

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

ACREATE FUNCTION

BPREPARE

CDECLARE

DLOAD

EALLOCATE

Answer : B

Question 4

I would like to copy a database cluster directory for backup.

Select two incorrect statements from below.

AThe directory must be copied after stopping the database server.

BWhen using the Tablespace function, it is necessary to back up the directory that stores the tablespace as well.

CA database cluster that has been restored can be used on a separate machine with the same structure.

DA database cluster that has been restored can be used on a newer version of PostgreSQL.

EA database cluster that has been restored can be used on an older version of PostgreSQL.

Answer : D, E

Question 5

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

Answer : A

Question 6

Four SQL statements were executed in the following order.

CREATE TABLE foo (bar INT); ALTER TABLE foo ALTER bar

TYPE BIGINT; ALTER TABLE foo ADD baz VARCHAR(5);

ALTER TABLE foo DROP bar; Select two SQL statements that generate an error when executed.

AINSERT INTO foo VALUES ('12345');

BINSERT INTO foo VALUES ('5000000000');

CINSERT INTO foo VALUES ('ABC');

DINSERT INTO foo VALUES (2000000000);

EINSERT INTO foo VALUES (NULL);

Answer : B, D

Question 7

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

PostgreSQL CE 8 Silver PGCES-02 Exam Questions