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

PostgreSQL-CE PGCES-02 Online Questions & Answers

• Question 41:

  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
  D. 5 rows

• Question 42:

  Select one incorrect statement concerning the following SQL statement. CREATE OR REPLACE VIEW sales_view AS SELECT * FROM sales_table ORDER BY sales_date DESC LIMIT 10;

  A. Defines the view called "sales_view".
  B. Replaces "sales_view" if it already exists.
  C. When you 'SELECT' the "sales_view", it displays the first 10 records from the "sales_table" sorted by the "sales_date" column in descending order.
  D. Some errors occur when "SELECT * FROM sales_table" is executed after the view is defined.
  E. You can confirm that the "sales_view" has been added by querying the view called "pg_views".
  D. Some errors occur when "SELECT * FROM sales_table" is executed after the view is defined.

• Question 43:

  Select the most appropriate setting to output the log messages of the database to syslog.

  A. "syslog = true" in postgresql.conf.
  B. "log_destination = 0" in postgresql.conf.
  C. "log_destination = 1" in postgresql.conf.
  D. "log_destination = 2" in postgresql.conf.
  E. "log_destination = syslog" in postgresql.conf.
  E. "log_destination = syslog" in postgresql.conf.

• Question 44:

  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 45:

  Select one incorrect description about changing the settings of PostgreSQL during operation.

  A. The current value of a parameter can be confirmed using the SHOW command.
  B. All of the current settings can be displayed using the SHOW ALL command.
  C. Changes that can be made using the SET command have higher priority than ones in postgresql.conf.
  D. Values set by a superuser using the SET command are valid for different connections made later.
  E. There are parameters that can not be set using the SET command
  D. Values set by a superuser using the SET command are valid for different connections made later.

• Question 46:

  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.
  B. A newly created table is empty and has 0 rows.
  E. The SQL 'CREATE TABLE' statement is used to create a new table.

• Question 47:

  Select an incorrect statement regarding prepared statements, and 'PREPARE' / 'EXECUTE' commands.

  A. 'PREPARE'/'EXECUTE' is mainly used to optimize performance.
  B. 'PREPARE' creates a plan for the prepared statement.
  C. 'PREPARE' can only specify 'SELECT' as a prepared statement.
  D. 'EXECUTE' executes the plan defined by 'PREPARE'.
  E. 'DEALLOCATE' deallocates prepared statements.
  C. 'PREPARE' can only specify 'SELECT' as a prepared statement.

• Question 48:

  SQL statements were executed in the following order.

  CREATE TABLE book (

  id VARCHAR(21), title TEXT NOT NULL, price INT,

  UNIQUE (id), CHECK (price > 0)

  );

  INSERT INTO book VALUES ('4-12345-678-9', 'SQL book', 2300); --(1) INSERT INTO book (title, price) VALUES ('PostgreSQL', 3000); --(2) UPDATE book SET id = '4-12345-678-9' WHERE id IS NULL; --(3) DELETE FROM book WHERE

  price < 0; --(4)

  While executing, select the first location that generates an error.

  A. (1)
  B. (2)
  C. (3)
  D. (4)
  E. No error is generated.
  C. (3)

• Question 49:

  You want to set a constraint so that the "item_id" in the "sales" table will always have a value that already exists as "id" in the "item_master" table. Select the correct SQL statement to fill in the underlined blank of the "sales" table. Definitions:

  CREATE TABLE item_master ( id INTEGER PRIMARY KEY, name TEXT );

  CREATE TABLE sales ( sales_id INTEGER, item_id INTEGER, num INTEGER,

  );

  A. FOREIGN KEY (id) REFERENCES item_master (item_id)
  B. FOREIGN KEY (item_id) REFERENCES item_master (id)
  C. REFERENCES item_master (item_id)
  D. REFERENCES item_master (id)
  E. REFERENCES item_master (id) TO item_id
  B. FOREIGN KEY (item_id) REFERENCES item_master (id)

• Question 50:

  You have just added an option "listen_addresses = 'localhost'" in postgresql.conf. When will this setting take effect?

  A. This change will take effect as soon as postgresql.conf is saved.
  B. This change will take effect by executing "pg_ctl reload".
  C. This change will take effect by executing "pg_ctl restart".
  D. This change will take effect after rebooting the OS, because the new option is recorded as an OS parameter.
  E. This setting is invalid unless the change is made while postmaster is stopped.
  C. This change will take effect by executing "pg_ctl restart".