PostgreSQL-CE PGCES-02 Online Practice Questions and Exam Preparation

PostgreSQL-CE PGCES-02 Online Questions & Answers

• Question 81:

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

  A. The process for client "B" is blocked until the current connection for client "A" is finished.
  B. The process for client "B" is blocked until the current transaction for client "A" is finished.
  C. The process for client "B" will be deleted regardless of the condition of client "A".
  D. The process of client "B" will affect the SELECT result of client "A".
  E. The process of client "B" will not affect the SELECT result of client "A".
  B. The process for client "B" is blocked until the current transaction for client "A" is finished.
  E. The process of client "B" will not affect the SELECT result of client "A".

• Question 82:

  Select two incorrect statements regarding large objects.

  A. A large object is generated by 'CREATE LARGE OBJECT'.
  B. A large object is added by 'INSERT'.
  C. One large object is able to handle up to 2GB of data.
  D. Binary data cannot be used unless declared as a large object.
  E. An OID is used to identify a large object.
  C. One large object is able to handle up to 2GB of data.
  E. An OID is used to identify a large object.

• Question 83:

  Table t1 is defined as follows:

  CREATE TABLE t1 (value VARCHAR(5));

  A set of SQL statements were executed in the following order. Select the number of rows that table "t1" has after execution.

  BEGIN;

  INSERT INTO t1 VALUES ('A'); SAVEPOINT sp;

  INSERT INTO t1 VALUES ('B'); ROLLBACK TO sp;

  INSERT INTO t1 VALUES ('C'); COMMIT;

  A. 1 row
  B. 2 rows
  C. 3 rows
  D. 4 rows
  E. 0 rows
  B. 2 rows

• Question 84:

  Select one incorrect statement about the command shown below. Note: $ is the command prompt. $ dropuser -U admin foo A. If there is a database owned by foo, an error will occur.

  B. If admin is not the owner of foo, an error will occur.
  C. If admin doesn't have the superuser privilege, an error will occur.
  D. The user admin is removing the user foo.
  E. The same process can be performed using the SQL command "DROP USER".
  B. If admin is not the owner of foo, an error will occur.

• Question 85:

  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.
  B. It is necessary to stop the database server to perform a backup for the first time.
  E. A backup can only be performed on a per-database basis.

• Question 86:

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

  A. SELECT 'ABC' . 'abc';
  B. SELECT cat('ABC', 'abc') FROM pg_operator;
  C. SELECT 'ABC' + 'abc';
  D. SELECT 'ABC' + 'abc' FROM pg_operator;
  E. SELECT 'ABC' || 'abc';
  E. SELECT 'ABC' || 'abc';

• Question 87:

  Select the most appropriate statement about the initdb command.

  A. It can not be executed by an OS administrator-level user (root user).
  B. It can only be executed by the user who installed PostgreSQL.
  C. Unless the environment variable PGDATA is set, it can not be executed.
  D. When "auto" is designated as the value for the environment variable PGDATA, a directory is automatically set up.
  E. The directory designated by the environment variable PGDATA must exist.
  A. It can not be executed by an OS administrator-level user (root user).

• Question 88:

  The table "custom" is defined below. The "id" column and "introducer" column are of INTEGER type, and the "email" column is of TEXT type. id | email | introducer ----+-----------------+-----------2 | [email protected] | 1 3 | [email protected] | 2 4 | [email protected] | 2 Three SQL statements were executed in the following order: INSERT INTO custom SELECT max(id) + 1, '[email protected]', 4 FROM custom; UPDATE custom SET introducer = 999 WHERE email = '[email protected]'; DELETE FROM custom WHERE introducer NOT IN (SELECT id FROM custom); Select the number of rows in the "custom" table after the execution.

  A. 0 rows
  B. 1 row
  C. 2 rows
  D. 3 rows
  E. 4 rows
  C. 2 rows

• Question 89:

  Select two suitable statements regarding the data types of PostgreSQL.

  A. One field can handle up to 1GB of data.
  B. 'n' in CHARACTER(n) represents the number of bytes.
  C. Only the INTEGER type can be declared as an array.
  D. There is a non-standard PostgreSQL data type, called Geometric data type, which handles 2- dimensional data.
  E. A large object data type can be used to store data of unlimited size.
  A. One field can handle up to 1GB of data.
  D. There is a non-standard PostgreSQL data type, called Geometric data type, which handles 2- dimensional data.

• Question 90:

  I would like to enable all users to SELECT the "item" table. Select the most appropriate SQL statement from below.

  A. GRANT public SELECT ON item;
  B. GRANT SELECT ON item TO public;
  C. REVOKE 'r' ON item TO public;
  D. REVOKE READ ON item TO public;
  E. REVOKE public SELECT ON item;
  B. GRANT SELECT ON item TO public;