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

• Question 71:

  The table "t1" is defined by the following SQL statement: CREATE TABLE t1 (id integer, name varchar

  (20));

  You want to increase the execution speed of the SQL statement below:

  SELECT id, name FROM t1 WHERE id < 123 AND upper(name) = 'MAMMOTH'; Select the most suitable

  SQL statement to create an index.

  A. CREATE INDEX t1_idx ON t1 (id, upper(name));

  B. CREATE INDEX t1_idx ON t1 USING HASH (id);

  C. CREATE INDEX t1_idx ON t1 (name);

  D. ALTER TABLE ADD INDEX ON t1 (id, upper(name));

  E. ALTER TABLE ADD INDEX ON t1 (id, name);

  Correct Answer: A

• Question 72:

  The tables "t1" and "t2" are defined in the same way (they have the same data types and column names). You want to select rows in "t1" which are not in "t2". Select a correct keyword to fill in the blank below. SELECT * FROM t1 SELECT * FROM t2;

  A. EXCEPT

  B. UNION

  C. NAND

  D. INTERSECT

  E. INTERSECT ALL

  Correct Answer: A

• Question 73:

  Select one incorrect description regarding the following SQL statement defining a function. CREATE OR REPLACE FUNCTION get_file_list(TEXT, BOOLEAN) RETURNS SETOF TEXT LANGUAGE C STRICT SECURITY DEFINER AS 'myfuncs.so';

  A. This function may be defined in 'myfuncs.so'.

  B. This function can return multiple rows.

  C. This SQL statement defines a function written in the C language.

  D. If this function is called with a NULL parameter, it will return 0 when executed.

  E. This function operates with the authority of the user who executed it.

  Correct Answer: E

• Question 74:

  The following SQL defines an INSERT with respect to item_view. Select the keyword that is applicable in the underlined blank. CREATE foo AS ON INSERT TO item_view DO INSTEAD INSERT INTO item_table VALUES (NEW.id, NEW.itemname);

  A. RULE

  B. VIEW

  C. TRIGGER

  D. FUNCTION

  E. CONSTRAINT

  Correct Answer: A

• Question 75:

  The tables "s1" and "s2" are defined below. The column "id" for tables "s1" and "s2" is of INTEGER type. The column "enable" for table "s1" is of BOOLEAN type, and the column "name" for table "s2" is of TEXT type. s1: id | enable ----+-------1 | t 2 | f s2: id | name ----+-----1 | post 2 | gre 3 | SQL The following SQL was executed. Select the correct number of rows in the result. SELECT * FROM s2 WHERE id IN (SELECT id FROM s1);

  A. 1 row

  B. 2 rows

  C. 3 rows

  D. 4 rows

  E. 5 rows

  Correct Answer: B

• Question 76:

  A table is defined as below. Select the most suitable description about the foreign key constraint. CREATE TABLE master (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE record (id INTEGER REFERENCES master (id), count INTEGER);

  A. If any row exists in the "record" table, no change can be made to the "master" table.

  B. If the "record" table contains a row with an "id", no change can be made at all to the corresponding "id" row in the "master" table.

  C. If the "record" table contains a row with an "id", the corresponding "id" row in the "master" table cannot be deleted.

  D. The "record" table cannot have duplicate "id"s.

  E. These SQL statements are invalid; no constraints are created.

  Correct Answer: C

• Question 77:

  You want to create a cursor that will SELECT the "customer" table. The created cursor must be able to move in any direction and reference data even after the end of the transaction. Select one answer containing the correct keyword(s) to fill in the underlined blank below. DECLARE cursor1 FOR SELECT * FROM customer;

  A. CURSOR

  B. SCROLL CURSOR WITH HOLD

  C. INSENSITIVE CURSOR

  D. NO SCROLL CURSOR WITH HOLD

  E. CURSOR WITHOUT HOLD

  Correct Answer: B

• Question 78:

  Select the correct result generated by execution of the following SQL statements: CREATE TABLE log (id

  int, message TEXT, logtime TIMESTAMP); CREATE TABLE log_01 () INHERITS (log);

  INSERT INTO log_01 VALUES (1, 'error', CURRENT_TIMESTAMP); SELECT * FROM log;

  A. First 'CREATE TABLE' generates a syntax error.

  B. Second 'CREATE TABLE' generates a syntax error.

  C. 'INSERT' statement generates an error stating that the number of columns and values do not match".

  D. 'SELECT' statement returns 0 rows, and exits successfully.

  E. 'SELECT' statement returns 1 row, and exits successfully.

  Correct Answer: E

• Question 79:

  The "sample" table consists of the data below. The column "x" is of type INTEGER. How many rows are returned by executing the following SQL statement? SELECT 6 / x FROM sample WHERE CASE WHEN x = 0 THEN FALSE ELSE TRUE END;

  A. "ERROR division by zero" and no rows are returned.

  B. 0 rows with no errors.

  C. 1 row

  D. 2 rows

  E. 3 rows

  Correct Answer: D

• Question 80:

  Select the SQL command that must be executed prior to executing the EXECUTE command.

  A. CREATE FUNCTION

  B. PREPARE

  C. DECLARE

  D. LOAD

  E. ALLOCATE

  Correct Answer: B