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

• Question 121:

  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;

  Correct Answer: E

• Question 122:

  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.

  Correct Answer: BE

• Question 123:

  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

  Correct Answer: D

• Question 124:

  Select three SQL statements which return NULL.

  A. SELECT 0 = NULL;

  B. SELECT NULL != NULL;

  C. SELECT NULL IS NULL;

  D. SELECT NULL;

  E. SELECT 'null'::TEXT;

  Correct Answer: ABD

• Question 125:

  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

  Correct Answer: C

• Question 126:

  The "sample" table consists of the following data: How many rows are returned by executing the following SQL statement? SELECT * FROM sample WHERE v ~ 'ab';

  A. 0 rows

  B. 1 row

  C. 2 rows

  D. 3 rows

  E. 4 rows

  Correct Answer: C

• Question 127:

  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.

  Correct Answer: D

• Question 128:

  The "animal" table consists of the following data:Select the correct result returned by executing the following SQL statement: SELECT name FROM animal ORDER BY weight DESC LIMIT 2 OFFSET 1;

  A. A syntax error will occur.

  Correct Answer: A

• Question 129:

  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

  Correct Answer: B

• Question 130:

  The following SQL statements were executed using psql. Select the appropriate statement about the

  result. LISTEN sign_v;

  BEGIN;

  NOTIFY sign_v; COMMIT; LISTEN sign_v;

  A. At the point that 'NOTIFY sign_v' is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output.

  B. At the point that 'COMMIT' is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output.

  C. At the point that 'SELECT * FROM pg_user;" is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output.

  D. When 'LISTEN sign_v' is executed for the second time, a message that starts with "Asynchronous notification 'sign_v' received" is output.

  E. The message "Asynchronous notification 'sign_v' received" is not received while in this connection.

  Correct Answer: B