PGCES-02 Online Practice Questions and Answers

The following is the result of executing the createlang command which is installed with PostgreSQL.

$ createlang -U postgres --list mydb

Procedural Languages

Name | Trusted?

---------+---------- plpgsql | yes

Select two correct statements from below.

A. The procedural language plpgsql is installed in the database mydb using the above command.

B. The procedural language plpgsql can be used in the database mydb.

C. plpgsql is a trusted language, so it can execute the OS commands on the server side.

D. plpgsql is a trusted language, so it can read/write OS files on the server side.

E. plpgsql is a safe language with restricted operations.

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;

A.

2 rows

B.

3 rows

C.

4 rows

D.

5 rows

E.

6 rows

Select two incorrect statements concerning PostgreSQL license.

A. It can be used freely.

B. It can be duplicated freely.

C. It can be freely redistributed.

D. Developers are responsible for its maintenance support.

E. Developers are only responsible for handling its crucial faults.

You want to create a cursor that will SELECT the "customer" table. The created cursor must be able to move in any direction and reference data even after the end of the transaction. Select one answer containing the correct keyword(s) to fill in the underlined blank below. DECLARE cursor1 FOR SELECT * FROM customer;

A. CURSOR

B. SCROLL CURSOR WITH HOLD

C. INSENSITIVE CURSOR

D. NO SCROLL CURSOR WITH HOLD

E. CURSOR WITHOUT HOLD

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

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

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

C. Updated data is continuously saved.

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

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

Select two incorrect statements related to the command below. Note: $ is the command prompt. $ psql -U foo -c "COPY company TO stdout;" bar

A. If the company table is not readable, an error occurs.

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

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

D. An error occurs unless the user foo has administrator privileges.

E. Connects to the database bar.

Based on the relationship of columns within a table, select the most suitable description that shows that column A is dependent on column B.

A. The value in column B is uniquely determined when a value in column A is selected.

B. The value in column A is uniquely determined when a value in column B is selected.

C. When the value in column A is changed, the corresponding value in column B also must be changed.

D. When the value in column B is changed, the corresponding value of column A also must be changed.

E. As long as column B exists, the amount of information will not decrease even if column A is deleted.

SQL statements were executed in the following order: CREATE TABLE fmaster (id INTEGER PRIMARY

KEY, name TEXT);

CREATE TABLE ftrans

(id INTEGER REFERENCES fmaster (id), stat INTEGER, date DATE); INSERT INTO fmaster VALUES (1,

'itemA');

INSERT INTO ftrans VALUES (1, 1, CURRENT_DATE);

Select the two SQL statements that will generate an error when executed next.

A. DROP TABLE ftrans;

B. INSERT INTO fmaster VALUES (1, 'itemB');

C. DELETE FROM fmaster;

D. UPDATE fmaster SET name = NULL;

E. INSERT INTO ftrans VALUES (1, 2, NULL);

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;

A. 0 rows

B. 1 row

C. 2 rows

D. 3 rows

E. 4 rows

Select two correct statements about the command shown below. Note: $ is the command prompt. $ vacuumdb -az

A. Recovers unused areas from all of the databases.

B. Collects statistical information related to the table content for all of the databases.

C. Processes the job equivalent of the VACUUM FULL command for all of the databases.

D. Processes the job equivalent of the VACUUM VERBOSE command for all of the databases.

E. The database can not be accessed until this command is finished.