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

• Question 31:

  The table "score" is defined as follows: gid | score -----+------1 | 70 1 | 60 2 | 100 3 | 80 3 | 50 The following query was executed. Select the correct result value. SELECT score FROM score ORDER BY gid DESC, score ASC LIMIT 1;

  A. 50

  B. 60

  C. 70

  D. 80

  E. 100

  Correct Answer: A

• Question 32:

  A set of tables are defined as follows: t1 t2 How many rows are returned by executing the following SQL statement? SELECT * FROM t1 LEFT OUTER JOIN t2 USING (id);

  A. 2 rows

  B. 3 rows

  C. 4 rows

  D. 5 rows

  E. 6 rows

  Correct Answer: C

• Question 33:

  Table t1 is defined as follows:

  CREATE TABLE t1 (value VARCHAR(5));

  A set of SQL statements were executed in the following order. Select the number of rows that table "t1"

  has after execution.

  BEGIN;

  INSERT INTO t1 VALUES ('A'); SAVEPOINT sp;

  INSERT INTO t1 VALUES ('B'); ROLLBACK TO sp;

  INSERT INTO t1 VALUES ('C'); COMMIT;

  A. 1 row

  B. 2 rows

  C. 3 rows

  D. 4 rows

  E. 0 rows

  Correct Answer: B

• Question 34:

  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);

  Correct Answer: BC

• Question 35:

  The table "t1" is defined below.

  The column "id" for table "t1" is of INTEGER type.

  id | name

  ----+-----------1 | mammoth

  2 | tortoise

  3 | coelacanth

  The following SQL statements were executed. Select the correct statement about the execution result.

  BEGIN;

  DECLARE c SCROLL CURSOR FOR SELECT name FROM t1 ORDER BY id; MOVE FORWARD 2

  FROM c;

  FETCH FORWARD ALL FROM c; COMMIT;

  A. The number of rows returned by the FETCH statement is 0.

  B. The number of rows returned by the FETCH statement is 1.

  C. The number of rows returned by the FETCH statement is 2.

  D. The number of rows returned by the FETCH statement is 3.

  E. An error occurs part way through.

  Correct Answer: B

• Question 36:

  Select two incorrect statements regarding large objects.

  A. A large object is generated by 'CREATE LARGE OBJECT'.

  B. A large object is added by 'INSERT'.

  C. One large object is able to handle up to 2GB of data.

  D. Binary data cannot be used unless declared as a large object.

  E. An OID is used to identify a large object.

  Correct Answer: CE

• Question 37:

  From the SQL commands below, select one that is generally classified as "DDL".

  A. START TRANSACTION

  B. CREATE TABLE

  C. SELECT

  D. INSERT

  E. DELETE

  Correct Answer: B

• Question 38:

  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.

  Correct Answer: E

• Question 39:

  A set of tables are defined as follows: t1 t2 How many rows are returned by executing the following SQL statement? SELECT * FROM t1 WHERE EXISTS (SELECT name FROM t2 WHERE t1.id = t2.id);

  A. 0 rows

  B. 2 rows

  C. 3 rows

  D. 5 rows

  E. 6 rows

  Correct Answer: B

• Question 40:

  SQL statements were executed in the following order.

  CREATE TABLE book (

  id VARCHAR(21), title TEXT NOT NULL, price INT,

  UNIQUE (id), CHECK (price > 0)

  );

  INSERT INTO book VALUES ('4-12345-678-9', 'SQL book', 2300); --(1) INSERT INTO book (title, price)

  VALUES ('PostgreSQL', 3000); --(2) UPDATE book SET id = '4-12345-678-9' WHERE id IS NULL; --(3)

  DELETE FROM book WHERE price < 0; --(4)

  While executing, select the first location that generates an error.

  A. (1)

  B. (2)

  C. (3)

  D. (4)

  E. No error is generated.

  Correct Answer: C