PostgreSQL PGCES-02 Exam Questions Instant Access

Question 1

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.

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;

Answer : C

Question 2

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 3

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.

Answer : A, C

Question 4

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 5

Select the most appropriate statement about the initdb command.

AIt can not be executed by an OS administrator-level user (root user).

BIt can only be executed by the user who installed PostgreSQL.

CUnless the environment variable PGDATA is set, it can not be executed.

DWhen 'auto' is designated as the value for the environment variable PGDATA, a directory is automatically set up.

EThe directory designated by the environment variable PGDATA must exist.

Answer : A

Question 6

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;

A0 rows

B1 row

C2 rows

D3 rows

E4 rows

Answer : C

Question 7

Select one incorrect statement about the command shown below.

Note: $ is the command prompt. $ dropuser -U admin foo

AIf there is a database owned by foo, an error will occur.

BIf admin is not the owner of foo, an error will occur.

CIf admin doesn't have the superuser privilege, an error will occur.

DThe user admin is removing the user foo.

EThe same process can be performed using the SQL command 'DROP USER'.

Answer : B

PostgreSQL CE 8 Silver PGCES-02 Exam Questions