PostgreSQL PGCES-02 Exam Questions Instant Access

Question 1

I would like to check the privileges on the "items" table in psql. Select the most appropriate command.

A\a items

B\d items

C\t items

D\p items

E\z items

Answer : E

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

PostgreSQL can use an index to access a table. Select two incorrect statements about indexes.

AAn index is created by 'CREATE INDEX', and deleted by 'DROP INDEX'.

BBy using an index effectively, searching and sorting performs faster.

CThere are B-tree, Hash, R-tree and GiST index types.

DBy creating an index, performance always improves.

ECreating an unused index does not affect the performance of a database at all.

Answer : D, E

Question 4

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);

AVIEW

BSCHEMA

CRULE

DTYPE

EDOMAIN

Answer : E

Question 5

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

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

Answer : B, E

Question 6

Select two correct statements about the command shown below.

Note: $ is the command prompt. $ dropdb -U foo foodb

AIf foo doesn't have the OS superuser privilege, an error will occur.

BIf any table definition remains in database foodb, an error will occur.

CThis command removes database foodb.

DThis command removes all of the objects inside the database foodb.

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

Answer : C, E

Question 7

Select the correct SQL statement which concatenates strings 'ABC' and 'abc' to form 'ABCabc'.

ASELECT 'ABC' . 'abc';

BSELECT cat('ABC', 'abc') FROM pg_operator;

CSELECT 'ABC' + 'abc';

DSELECT 'ABC' + 'abc' FROM pg_operator;

ESELECT 'ABC' || 'abc';

Answer : E

PostgreSQL CE 8 Silver PGCES-02 Exam Questions