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

PostgreSQL-CE PGCES-02 Online Questions & Answers

• Question 71:

  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.
  D. When executing 'UPDATE user_view', data is updated in the table that is the origin of the view.

• Question 72:

  What happens if an SQL statement syntax error occurs while a transaction is running? Select the correct action from below.

  A. The transaction continues.
  B. The transaction is aborted and a new transaction is started automatically.
  C. The transaction is stopped and you cannot issue any SQL commands other than a command to end the transaction.
  D. The connection is terminated.
  E. The "postmaster" process is terminated.
  C. The transaction is stopped and you cannot issue any SQL commands other than a command to end the transaction.

• Question 73:

  Select one incorrect statement about schemas.

  A. A schema is the name space for a database object.
  B. A new schema is created by 'CREATE SCHEMA'.
  C. One user cannot own multiple schemas.
  D. 'SELECT current_schema();' returns the current schema.
  E. 'DROP SCHEMA' deletes a schema.
  C. One user cannot own multiple schemas.

• Question 74:

  A set of tables are defined as follows:

  t1

  t2

  How many rows are returned by executing the following SQL statement? SELECT * FROM t1 LEFT OUTER JOIN t2 USING (id);

  A. 2 rows
  B. 3 rows
  C. 4 rows
  D. 5 rows
  E. 6 rows
  C. 4 rows

• Question 75:

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

• Question 76:

  Given the following two table definitions, select one SQL statement which will cause an error.

  CREATE TABLE sample1 (id INTEGER, data TEXT);

  CREATE TABLE sample2 (id INTEGER);

  A. SELECT s1.id FROM sample1 s1;
  B. SELECT s1.id FROM sample1 AS s1;
  C. SELECT data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = 1 AND s2.id = 2;
  D. SELECT id, data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = s2.id;
  E. SELECT s1.id, s1.data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = s2.id;
  D. SELECT id, data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = s2.id;

• Question 77:

  The "sample" table consists of the following data: How many rows are returned by executing the following SQL statement?

  SELECT DISTINCT ON (data) * FROM sample;

  A. 2 rows
  B. 3 rows
  C. 4 rows
  D. 5 rows
  E. 6 rows
  B. 3 rows

• Question 78:

  Select the correct command to collect and save the statistical information of a table.

  A. ANALYZE
  B. CLUSTER
  C. REINDEX
  D. STATISTIC COLLECTION
  E. STATISTIC COLLECTOR
  A. ANALYZE

• Question 79:

  Select one incorrect statement about the SQL COPY command.

  A. It copies data between a file and a table.
  B. You can output the row data of a table to a specified client side file.
  C. Copies row data from a file into a table.
  D. Only the superuser can specify the output file name.
  E. The delimiter that separates column data can be changed from the default TAB character.
  B. You can output the row data of a table to a specified client side file.

• Question 80:

  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.

  A. UPDATE email = NULL FROM customer WHERE id
  B. UPDATE customer SET email IS NULL WHERE id < 10001;
  C. UPDATE customer SET email = NULL WHERE id
  D. DELETE FROM customer.email WHERE id < 10001;
  E. UPDATE FROM customer SET email = NULL WHERE id
  C. UPDATE customer SET email = NULL WHERE id