PostgreSQL-CE PGCES-02 Online Practice Questions and Exam Preparation

PostgreSQL-CE PGCES-02 Online Questions & Answers

• Question 21:

  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
  B. 2 rows

• Question 22:

  A sequence has the following definition: CREATE SEQUENCE seq1 CACHE 10 CYCLE; Select the value that is returned by executing the following SQL. SELECT nextval('seq1');

  B. 1
  C. 10
  D. 11
  E. -10
  B. 1

• Question 23:

  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.
  A. BOOLEAN is an alias of the INTEGER type in PostgreSQL.
  E. If the INTEGER value of '0' is inserted into a BOOLEAN column, it will be treated as FALSE.

• Question 24:

  Select the most suitable statement regarding PostgreSQL's pg_hba.conf configuration file.

  A. You can use any number of authentication types on a single line.
  B. You cannot set different authentications per user.
  C. You can set different authentications per table.
  D. The authentication settings in the file are evaluated from the top line to the bottom line.
  E. krb5 authentication can only be used for local connections.
  D. The authentication settings in the file are evaluated from the top line to the bottom line.

• Question 25:

  Configuration file pg_hda.conf is set as below on a host currently running PostgreSQL. local all all trust host all 192.168.1.0/24 reject host all all 192.168.0.0/16 trust Select a host IP address which is authorized to connect to this database via

  the network. Note:

  INET domain socket communication is available.

  A. 127.0.0.1
  B. 192.168.1.1
  C. 192.168.0.1
  D. 192.168.1.0
  E. None can be connected.
  C. 192.168.0.1

• Question 26:

  Select an appropriate command to check the PostgreSQL version in psql.

  A. \server_version
  B. SELECT version;
  C. SELECT version();
  D. SHOW version;
  E. SHOW server;
  C. SELECT version();

• Question 27:

  Which normal form has the constraint that there must be no tables with duplicate column values in the same row?

  A. First normal form
  B. Second normal form
  C. Third normal form
  D. Boyce/Codd normal form
  E. Fourth normal form
  A. First normal form

• Question 28:

  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);
  B. SELECT * FROM (SELECT * FROM customer);

• Question 29:

  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);
  A. CREATE INDEX t1_idx ON t1 (id, upper(name));

• Question 30:

  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
  A. RULE