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

PostgreSQL-CE PGCES-02 Online Questions & Answers

• Question 91:

  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;

  A. 1 row
  B. 2 rows
  C. 3 rows
  D. 4 rows
  E. 5 rows
  C. 3 rows

• Question 92:

  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
  E. 6 rows

• Question 93:

  Table "t1" is defined below.

  Table "t1" has a column "id" of type INTEGER, and a column "name" of type TEXT. t1:

  The following SQL is executed while client "A" is connected. BEGIN; SELECT * FROM t1 WHERE id = 2 FOR UPDATE; SELECT * FROM t1 WHERE id = 1 FOR UPDATE; -- (*)

  While the second 'SELECT' statement, shown with (*), is being executed, a separate client "B" connects and executes the following SQL.

  Select the correct statement about the execution results. UPDATE t1 SET name = 'turtle' WHERE id = 2;

  Note: the default transaction isolation level is set to "read committed".

  A. The update process for client "B" is blocked until the current connection for client "A" is finished.
  B. The update process for client "B" is blocked until the current transaction for client "A" is finished.
  C. The 'UPDATE' process for client "B" proceeds regardless of the condition of client "A".
  D. The process of client "B" immediately generates an error.
  E. The processes for both clients are blocked, and an error stating that a deadlock has been detected is generated.
  B. The update process for client "B" is blocked until the current transaction for client "A" is finished.

• Question 94:

  The following SQL statements were executed using psql. Select the appropriate statement about the result. LISTEN sign_v;

  BEGIN;

  NOTIFY sign_v; COMMIT; LISTEN sign_v;

  A. At the point that 'NOTIFY sign_v' is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output.
  B. At the point that 'COMMIT' is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output.
  C. At the point that 'SELECT * FROM pg_user;" is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output.
  D. When 'LISTEN sign_v' is executed for the second time, a message that starts with "Asynchronous notification 'sign_v' received" is output.
  E. The message "Asynchronous notification 'sign_v' received" is not received while in this connection.
  B. At the point that 'COMMIT' is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output.

• Question 95:

  Select two commands used to check the syntax of the ALTER TABLE statement in psql.

  A. \h ALTER TABLE
  B. \h ALTER
  C. \ ALTER TABLE
  D. \ ALTER
  E. HELP ALTER TABLE;
  A. \h ALTER TABLE
  B. \h ALTER

• Question 96:

  Choose the most suitable statement about user management of PostgreSQL. Note: the version of PostgreSQL is 8.0.

  A. Usernames not registered in the operating system cannot be registered as PostgreSQL users.
  B. To create a new user for PostgreSQL, the "newuser" command is used.
  C. PostgreSQL cannot be in a state where multiple users exist at the same time.
  D. If you create a user that has permission to create other users, that user will become a superuser that is not subject to any access restriction checks.
  E. To delete an existing user for PostgreSQL, "deleteuser" command is used.
  D. If you create a user that has permission to create other users, that user will become a superuser that is not subject to any access restriction checks.

• Question 97:

  Select two incorrect statements concerning the system catalog.

  A. It stores the object definition information for tables and columns.
  B. There may be changes to specification by major version upgrades.
  C. It is accessible as a table; however, it is output in binary format so it can not be read as is.
  D. It stores the internal information of the database management system.
  E. It is defined based on the standard SQL specification.
  C. It is accessible as a table; however, it is output in binary format so it can not be read as is.
  E. It is defined based on the standard SQL specification.

• Question 98:

  Select one option which cannot be specified using createdb.

  A. Database locale
  B. Character encoding
  C. Host name
  D. Database owner
  E. Template database
  A. Database locale

• Question 99:

  Select two appropriate statements from below about the following SQL statements: CREATE FUNCTION myfunc(INTEGER) RETURNS text LANGUAGE plpgsql STRICT AS ' DECLARE x ALIAS FOR $1; r text := ''default''; BEGIN IF x > 100 THEN SELECT INTO r data FROM mytable WHERE id = x; END IF; RETURN r; END;';

  A. An error is generated unless the plpgsql language is registered in the database beforehand.
  B. The execution results of SELECT myfunc(-123) differs based on the content of "mytable".
  C. When SELECT myfunc(123) is executed an error occurs.
  D. When SELECT myfunc(NULL) is executed an error occurs.
  E. When SELECT myfunc (0) is executed the text "default" is returned.
  A. An error is generated unless the plpgsql language is registered in the database beforehand.
  E. When SELECT myfunc (0) is executed the text "default" is returned.

• Question 100:

  Select one incorrect statement concerning the relational data model.

  A. It expresses the real world in a collection of 2-dimensional tables called a relation.
  B. It is a model based on set theory.
  C. It has a logical structure independent of physical data structure.
  D. It is made up of a multiple stage hierarchy where each of the set elements has parent child relationships.
  E. It is a model that was proposed by E.F. Codd in 1970.
  D. It is made up of a multiple stage hierarchy where each of the set elements has parent child relationships.