Free PostgreSQL PGCES-02 Exam Dumps and PGCES-02 Exam Questions

Question No: 1

MultipleChoice

Select two incorrect statements related to the command below.

Note: $ is the command prompt. $ psql -U foo -c 'COPY company TO stdout;' bar

Options

AIf the company table is not readable, an error occurs.

BThe content of the company table is written into a file called 'stdout'.

CThe content of the company table is output in TAB delimited format.

DAn error occurs unless the user foo has administrator privileges.

EConnects to the database bar.

Question No: 2

MultipleChoice

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;

Options

AEXCEPT

BUNION

CNAND

DINTERSECT

EINTERSECT ALL

Question No: 3

MultipleChoice

The table 't1' is defined by the following SQL statement: CREATE TABLE t1 (id integer, name varchar(20)); You want to increase the execution speed of the SQL statement below: SELECT id, name FROM t1 WHERE id < 123 AND upper(name) = 'MAMMOTH'; Select the most suitable SQL statement to create an index.

Options

ACREATE INDEX t1_idx ON t1 (id, upper(name));

BCREATE INDEX t1_idx ON t1 USING HASH (id);

CCREATE INDEX t1_idx ON t1 (name);

DALTER TABLE ADD INDEX ON t1 (id, upper(name));

EALTER TABLE ADD INDEX ON t1 (id, name);

Question No: 4

MultipleChoice

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;

Options

A'ERROR division by zero' and no rows are returned.

B0 rows with no errors.

C1 row

D2 rows

E3 rows

Question No: 5

MultipleChoice

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.

Options

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;

Question No: 6

MultipleChoice

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

Options

AVIEW

BSCHEMA

CRULE

DTYPE

EDOMAIN

Question No: 7

MultipleChoice

The tables 't1' and 't2' are defined below. Tables 't1' and 't2' have columns 'id' that are of INTEGER type, and columns 'name' that are of TEXT type. t1 t2 The following SQL command was executed. Select the number of rows in the result. SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;

Options

A2 rows

B3 rows

C4 rows

D5 rows

E6 rows

Question No: 8

MultipleChoice

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;

Options

A1 row

B2 rows

C3 rows

D4 rows

E5 rows

Question No: 9

MultipleChoice

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

Options

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

Question No: 10

MultipleChoice

The tables 't1' and 't2' are defined below. The tables 't1' and 't2' have columns 'id' which are type of INTEGER and column 'name's which are type of TEXT. t1 t2 The following SQL command was executed. Select the number of rows in the result. SELECT * FROM t1 NATURAL FULL OUTER JOIN t2;

Options

A2 rows

B3 rows

C4 rows

D5 rows

E6 rows