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

• Question 21:

  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.

  Correct Answer: C

• Question 22:

  Select two incorrect statements concerning the BOOLEAN type in PostgreSQL.

  A. BOOLEAN is an alias of the INTEGER type in PostgreSQL.

  B. BOOLEAN only takes either NULL, TRUE, or FALSE.

  C. You can use the characters 't' or 'f' as a value for the BOOLEAN type.

  D. You can use the TRUE or FALSE keywords as a value for the BOOLEAN type.

  E. If the INTEGER value of '0' is inserted into a BOOLEAN column, it will be treated as FALSE.

  Correct Answer: AE

• Question 23:

  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.

  Correct Answer: C

• Question 24:

  Select one statement which will cause a syntax error.

  A. SELECT (SELECT item FROM sale WHERE id = 1);

  B. SELECT * FROM (SELECT * FROM customer);

  C. SELECT * FROM item WHERE cid IN (SELECT cid FROM customer);

  D. SELECT * FROM sale WHERE name IN (SELECT name FROM names);

  E. SELECT * FROM sale WHERE cid = ANY (SELECT cid FROM customer);

  Correct Answer: B

• Question 25:

  Select two incorrect descriptions regarding the following SQL statements. CREATE TABLE cities ( name

  text, population

  float

  );

  CREATE TABLE capitals (

  state char(2)

  ) INHERITS (cities);

  A. Defines the tables called "cities" and "capitals".

  B. "capitals" inherits "cities".

  C. Searching "capitals" also searches rows in "cities".

  D. The columns "name" and "population" are also defined in "capitals".

  E. The second SQL statement results in an error, since the 'INHERITS' keyword is no longer available.

  Correct Answer: CE

• Question 26:

  Select two SQL statements which abort a transaction.

  A. END

  B. ROLLBACK

  C. TRUNCATE

  D. ABORT

  E. DROP TRANSTACTION

  Correct Answer: BD

• Question 27:

  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: UPDATE custom SET email = '' FROM custom c WHERE custom.introducer = c.id; UPDATE custom SET introducer = NULL WHERE introducer NOT IN (SELECT id FROM custom); DELETE FROM custom WHERE id = 2 OR introducer = 2; 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: A

• Question 28:

  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 HAVING count(*) = 2;

  A. 0 rows

  B. 1 row

  C. 2 rows

  D. 3 rows

  E. 4 rows

  Correct Answer: C

• Question 29:

  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.

  Correct Answer: DE

• Question 30:

  A set of tables are defined as follows: t1 t2 How many rows are returned by executing the following SQL statement? SELECT t1.name FROM t1 CROSS JOIN t2;

  A. 0 rows

  B. 2 rows

  C. 3 rows

  D. 5 rows

  E. 6 rows

  Correct Answer: E