PostgreSQL-CE PostgreSQL-CE Certifications PGCES-02 Questions & Answers

• Question 81:

  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;

  Correct Answer: D

• Question 82:

  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.

  Correct Answer: C

• Question 83:

  The table "tbl" is defined below such that it could only store non-negative integers in the column "nn". Select the keyword that is applicable for the underlined blank. CREATE natural_number AS DECIMAL CHECK (VALUE >= 0); CREATE TABLE tbl(nn natural_number);

  A. VIEW

  B. SCHEMA

  C. RULE

  D. TYPE

  E. DOMAIN

  Correct Answer: E

• Question 84:

  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 <= 10000;

  B. UPDATE customer SET email IS NULL WHERE id < 10001;

  C. UPDATE customer SET email = NULL WHERE id <= 10000;

  D. DELETE FROM customer.email WHERE id < 10001;

  E. UPDATE FROM customer SET email = NULL WHERE id <= 10000;

  Correct Answer: C

• Question 85:

  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

  Correct Answer: B

• Question 86:

  You want to delete rows in the "product" table which include the value '2004' in the "name" field. Select the correct statement to achieve this task.

  A. DELETE product WHERE name ~ '2004';

  B. DELETE product WHERE contain(name, '2004');

  C. DELETE FROM product WHERE name IN '2004';

  D. DELETE FROM product WHERE name LIKE '%2004%';

  E. DELETE FROM product WHERE name SIMILAR TO '2004';

  Correct Answer: D

• Question 87:

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

  Correct Answer: BE

• Question 88:

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

  SELECT i FROM sample GROUP BY i;

  A. 1 row

  B. 2 rows

  C. 3 rows

  D. 4 rows

  E. 5 rows

  Correct Answer: C

• Question 89:

  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

  Correct Answer: B

• Question 90:

  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.

  Correct Answer: E