PostgreSQL PGCES-02 PostgreSQL CE 8 Silver Exam Practice Test Questions

• Question 1

  Select the correct SQL statement that records the space occupied by deleted or updated rows for later reuse, and also updates statistics.

  • VACUUM
  • VACUUM ANALYZE
  • EXPLAIN
  • EXPLAIN ANALYZE
  • NOTIFY

  Show Answer Answer: B Next

• Question 2

  A table named "sample" is defined as below. Select two statements which will generate a constraint error. CREATE TABLE sample ( i INTEGER PRIMARY KEY, j INTEGER,CHECK ( i > 0 AND j < 0 ) );

  • INSERT INTO sample VALUES (1, 0);
  • INSERT INTO sample VALUES (2, -2);
  • INSERT INTO sample VALUES (3, NULL);
  • INSERT INTO sample VALUES (NULL, -4);
  • INSERT INTO sample VALUES (5, -5);

  Show Answer Answer: A, ,D Next

• Question 3

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

  • START TRANSACTION
  • CREATE TABLE
  • SELECT
  • INSERT
  • DELETE

  Show Answer Answer: B Next

• Question 4

  Select one incorrect statement regarding psql.

  • Psql is an interactive SQL interpreter that enables a user to enter queries.
  • For system security, only the PostgreSQL administrator account is allowed to use psql.
  • "psql -l" displays a database list.
  • "psql -U jan" will connect to the database called jan.
  • Commands that begin with a backslash are processed internally in psql.

  Show Answer Answer: B Next

• Question 5

  The table "t1" is defined by the following SQL statement: CREATE TABLE t1 (id integer, namevarchar(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.

  • CREATE INDEX t1_idx ON t1 (id, upper(name));
  • CREATE INDEX t1_idx ON t1 USING HASH (id);
  • CREATE INDEX t1_idx ON t1 (name);
  • ALTER TABLE ADD INDEX ON t1 (id, upper(name));
  • ALTER TABLE ADD INDEX ON t1 (id, name);

  Show Answer Answer: A Next