PGCES-02 Online Practice Questions and Answers

Select an incorrect statement regarding the following SQL statement. Note that "user_view" is a view. CREATE OR REPLACE RULE rule_1 AS ON UPDATE TO user_view DO INSTEAD NOTHING;

A. It is defining a rule "rule_1".

B. It will replace "rule_1" if it already exists.

C. Executing 'UPDATE user_view' will no longer output errors.

D. When executing 'UPDATE user_view', data is updated in the table that is the origin of the view.

E. 'DROP RULE rule_1 ON user_view' deletes the above definition.

I would like to insert the contents of the text file users.dat into the table t1 using psql. Contents of text file

users.dat:

Definition of table t1:

CREATE TABLE t1 (uname TEXT, pass TEXT, id INTEGER); Select the most appropriate input from those

below.

A. \copy t1 FROM users.dat WITH DELIMITER ':'

B. \copy t1 TO users.dat WITH DELIMITER ':'

C. INSERT INTO t1 FROM file('users.dat');

D. INSERT INTO t1 SELECT uname, pass, id FROM file('users.dat');

E. \insert t1 FROM users.dat WITH DELIMITER ':';

I would like to copy a database cluster directory for backup. Select two incorrect statements from below.

A. The directory must be copied after stopping the database server.

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

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

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

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

Select the correct SQL statement that records the space occupied by deleted or updated rows for later reuse, and also updates statistics.

A. VACUUM

B. VACUUM ANALYZE

C. EXPLAIN

D. EXPLAIN ANALYZE

E. NOTIFY

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

A. CREATE FUNCTION

B. PREPARE

C. DECLARE

D. LOAD

E. ALLOCATE

Select one incorrect description regarding the following SQL statement defining a function. CREATE OR REPLACE FUNCTION get_file_list(TEXT, BOOLEAN) RETURNS SETOF TEXT LANGUAGE C STRICT SECURITY DEFINER AS 'myfuncs.so';

A. This function may be defined in 'myfuncs.so'.

B. This function can return multiple rows.

C. This SQL statement defines a function written in the C language.

D. If this function is called with a NULL parameter, it will return 0 when executed.

E. This function operates with the authority of the user who executed it.

Which psql command do you need to execute to display the list of tables in the currently connected database?

A. SELECT * FROM pg_table_list;

B. SHOW tables;

C. \dT

D. \dt "database_name"

E. \dt

Select two incorrect statements concerning the BOOLEAN type in PostgreSQL.

A. BOOLEAN is an alias of the INTEGER type in PostgreSQL.

B. BOOLEAN only takes either NULL, TRUE, or FALSE.

C. You can use the characters 't' or 'f' as a value for the BOOLEAN type.

D. You can use the TRUE or FALSE keywords as a value for the BOOLEAN type.

E. If the INTEGER value of '0' is inserted into a BOOLEAN column, it will be treated as FALSE.

What does the following command do? Choose one incorrect statement from the selection below. $ pg_dumpall -U postgres > 20060601.bak

A. Backup all the databases in a database cluster.

B. Backup postgresql.conf along with databases.

C. Backup with plain-text format.

D. Backup user and group information along with databases.

E. You can access databases while the backup is in process.

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.