PGCES-02 Online Practice Questions and Answers

The "sample" table consists of the following data: How many rows are returned by executing the following SQL statement? SELECT * FROM sample WHERE v ~ 'ab';

A. 0 rows

B. 1 row

C. 2 rows

D. 3 rows

E. 4 rows

The "animal" table consists of the following data:Select the correct result returned by executing the following SQL statement: SELECT name FROM animal ORDER BY weight DESC LIMIT 2 OFFSET 1;

A. A syntax error will occur.

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.

Select two suitable statements regarding creating a new table.

A. There is no upper limit to the number of columns in a table.

B. A newly created table is empty and has 0 rows.

C. You can only use alphabetic characters for a table name.

D. The row name must be within 16 characters.

E. The SQL 'CREATE TABLE' statement is used to create a new table.

Select two suitable statements about major version upgrades of PostgreSQL from below.

A. You can use the databases of the old major version.

B. To use the data from the old version, you only need to replace the program.

C. To use the data from the old version, you need to conduct a backup and restore.

D. There is a possibility of configuration parameter changes after major version upgrades.

E. Upgrade scripts can be executed while the old version of PostgreSQL is running.

Select two correct statements about the command shown below. Note: $ is the command prompt. $ dropdb -U foo foodb

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

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

C. This command removes database foodb.

D. This command removes all of the objects inside the database foodb.

E. The same process can be performed using the SQL command "DROP DATABASE".

I would like to restore the database cluster from the "db1.dump" backup file. Select the correct command from below. (Note: "postgres" is the superuser)

A. pg_restore -U postgres -f db1.dump db1

B. pg_dump --restore db1 < db1.dump

C. pg_dump -U postgres --restore db1 < db1.dump

D. psql -U postgres -f db1.dump db1

E. pg_resetxlog -U postgres db1 < db1.dump

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

From the SQL commands below, select one that is generally classified as "DDL".

A. START TRANSACTION

B. CREATE TABLE

C. SELECT

D. INSERT

E. DELETE

I would like to be able to save log entries as shown below. Select a correct configuration setting from

statements below.

LOG: connection received: host=[local] port=

LOG: connection authorized: user=postgres database=test

A. syslog = true

B. log_connections = true

C. log_authorization = true

D. log_hostname = true

E. log_min_level = log