PGCES-02 Exam Training Practice Tests (PostgreSQL-CE PostgreSQL CE 8 Silver)]

Question 1

Select one statement which will cause a syntax error.

A. SELECT * FROM item
WHERE cid IN (SELECT cid FROM customer); B. SELECT * FROM (SELECT * FROM customer); C. SELECT * FROM sale
WHERE cid = ANY (SELECT cid FROM customer); D. SELECT * FROM sale
WHERE name IN (SELECT name FROM names); E. SELECT (SELECT item FROM sale WHERE id = 1);

Correct Answer: B

Question 2

What happens if an SQL statement syntax error occurs while a transaction is running? Select the
correct action from below.

A. The transaction continues. B. The connection is terminated. C. The "postmaster" process is terminated. D. The transaction is aborted and a new transaction is started automatically. E. The transaction is stopped and you cannot issue any SQL commands other than a command to end the transaction.

Correct Answer: E

Question 3

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. The message "Asynchronous notification 'sign_v' received" is not received while in this connection. B. When 'LISTEN sign_v' is executed for the second time, 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. At the point that 'NOTIFY sign_v' is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output. E. At the point that 'COMMIT' is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output.

Correct Answer: E

Question 4

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 HAVING count(*) = 2;

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

Correct Answer: A

Question 5

I would like to check the privileges on the "items" table in psql. Select the most appropriate command.

A. \t items B. \z items C. \p items D. \a items E. \d items

Correct Answer: B

Question 6

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. When SELECTmyfunc(NULL) is executed an error occurs. B. When SELECTmyfunc(123) is executed an error occurs. C. An error is generated unless theplpgsql language is registered in the database beforehand. D. When SELECTmyfunc (0) is executed the text "default" is returned. E. The execution results of SELECTmyfunc(-123) differs based on the content of "mytable".

Correct Answer: C,D

Question 7

Which psql command do you need to execute to display the list of tables in the currently connected database?

A. \dt B. SELECT * FROMpg_table_list; C. \dt "database_name" D. \dT E. SHOW tables;

Correct Answer: A

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