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

PostgreSQL-CE PGCES-02 Online Questions & Answers

• Question 61:

  A pg_hba.conf file is set up as follows. local all all md5 host all all 127.0.0.1/32 md5

  host all all 172.16.1.0/24 md5

  When user foo connects to database bar from host IP address 172.16.1.2, I would like password verification to not be performed.

  Select one appropriate line for the new pg_hba.conf file.

  A. Add "host foo bar 172.16.1.2/32 trust" to the first row.
  B. Add "host bar foo 172.16.1.2/32 trust" to the first row.
  C. Add "host foo bar 172.16.1.2/32 trust" to the last row.
  D. Add "host bar foo 172.16.1.2/32 trust" to the last row.
  E. The settings are fine as is.
  B. Add "host bar foo 172.16.1.2/32 trust" to the first row.

• Question 62:

  Select two correct statements that describe what occurs on the client side when the following command is executed.

  pg_ctl -m smart stop

  A. Clients will not be able to make new connections.
  B. The running transaction for a connected client is rolled back and the connection is disconnected forcibly.
  C. Connections are terminated after the currently running transactions have finished.
  D. The processes of currently connected clients are processed normally.
  E. Connected clients receive a message saying that the server will soon be shutdown.
  A. Clients will not be able to make new connections.
  D. The processes of currently connected clients are processed normally.

• Question 63:

  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
  D. psql -U postgres -f db1.dump db1

• Question 64:

  SQL statements were executed in the following order: CREATE TABLE fmaster (id INTEGER PRIMARY KEY, name TEXT);

  CREATE TABLE ftrans

  (id INTEGER REFERENCES fmaster (id), stat INTEGER, date DATE); INSERT INTO fmaster VALUES (1, 'itemA');

  INSERT INTO ftrans VALUES (1, 1, CURRENT_DATE);

  Select the two SQL statements that will generate an error when executed next.

  A. DROP TABLE ftrans;
  B. INSERT INTO fmaster VALUES (1, 'itemB');
  C. DELETE FROM fmaster;
  D. UPDATE fmaster SET name = NULL;
  E. INSERT INTO ftrans VALUES (1, 2, NULL);
  B. INSERT INTO fmaster VALUES (1, 'itemB');
  C. DELETE FROM fmaster;

• Question 65:

  A table is defined as follows: CREATE TABLE t (id INT, val TEXT); Select two correct statements from below about the function "get_head" defined below.

  CREATE FUNCTION get_head(BOOLEAN)

  RETURNS TEXT LANGUAGE sql CALLED ON NULL INPUT

  AS 'SELECT val FROM t WHERE $1 OR id > 0 ORDER BY id LIMIT 1;';

  A. This function is defined using PL/pgSQL.
  B. There are cases where this function returns multiple lines.
  C. When NULL is passed for the argument and the function is executed, NULL is returned.
  D. Even if this function is passed the same parameter value and executed multiple times, the returned values will not necessarily also be the same value.
  E. If a function with the same name and with type BOOLEAN as the parameter is already defined, an error occurs.
  D. Even if this function is passed the same parameter value and executed multiple times, the returned values will not necessarily also be the same value.
  E. If a function with the same name and with type BOOLEAN as the parameter is already defined, an error occurs.

• Question 66:

  Select two correct statements about the command shown below. Note: $ is the command prompt. $ pg_ctl reload

  A. The command forces the content of pg_hba.conf to be re-read into PostgreSQL server process.
  B. The command temporarily stops the PostgreSQL server process and restart it.
  C. The command re-reads the postgresql.conf details into the PostgreSQL server process and changes the values of any configuration parameters that can be changed.
  D. The command forces the content of the database cluster to be re-read into PostgreSQL server process.
  E. The command causes a recovery to be performed from a standard backup file in the PostgreSQL server process.
  A. The command forces the content of pg_hba.conf to be re-read into PostgreSQL server process.
  C. The command re-reads the postgresql.conf details into the PostgreSQL server process and changes the values of any configuration parameters that can be changed.

• Question 67:

  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 id AS data, data FROM sample1;
  B. SELECT id, id FROM sample1;
  C. SELECT s1.id, s2.id FROM sample1 AS s1, sample1 AS s2;
  D. SELECT s1.id, s2.id FROM sample1 s1, sample2 s2;
  E. SELECT s1.id, s2.data FROM sample1 s1, sample2 s2;
  E. SELECT s1.id, s2.data FROM sample1 s1, sample2 s2;

• Question 68:

  The table "foo" is defined as follows:

  CREATE TABLE foo (bar TEXT);

  Next, four SQL statements were executed in the following order.

  INSERT INTO foo VALUES ('bar'); -------- (1)

  ALTER TABLE foo ADD COLUMN c1 TEXT; ---- (2)

  ALTER TABLE foo ADD UNIQUE (c1); ------- (3)

  ALTER TABLE foo DROP COLUMN bar; ------- (4)

  Select the correct statement from those below.

  A. An error occurs when executing the (1) SQL statement.
  B. An error occurs when executing the (2) SQL statement.
  C. An error occurs when executing the (3) SQL statement.
  D. An error occurs when executing the (4) SQL statement.
  E. No error is generated.
  E. No error is generated.

• Question 69:

  I want to restore data from a text format backup file foo.dump. Select an appropriate command.

  A. pg_dump -R foo < foo.dump
  B. pg_restore -d foo foo.dump
  C. pg_restore -f foo.dump foo
  D. psql -f foo.dump foo
  E. createdb foo < foo.dump
  D. psql -f foo.dump foo

• Question 70:

  The tables "t1" and "t2" are defined below. Tables "t1" and "t2" have columns "id" that are of INTEGER type, and columns "name" that are of TEXT type. t1 t2 The following SQL command was executed. Select the number of rows in the result. SELECT

  *

  FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;

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