PostgreSQL CE 8 Silver (PGCES-02) Free Practice Test

Question 1

Table "t1" is defined as follows:
CREATE TABLE t1 (value VARCHAR(5));
A set of SQL statements were executed in the following order.Select thenumber of rows that
table "t1" has after execution.
BEGIN;
INSERT INTO t1 VALUES ('AA');
SAVEPOINT point1;
INSERT INTO t1 VALUES ('BB');
SAVEPOINT point2;
INSERT INTO t1 VALUES ('CC');
ROLLBACK TO point1;
INSERT INTO t1 VALUES ('DD');
END;

A. 3 rows

B. 1 row

C. 4 rows

D. 0 rows

E. 2 rows

Correct Answer: E

Question 2

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. 3 rows

B. 1 row

C. 4 rows

D. 0 rows

E. 2 rows

Correct Answer: E

Question 3

Given the following two table definitions, select one SQL statement which will cause an error.
CREATE TABLE sample1 (id INTEGER, data TEXT);
CREATE TABLE sample2 (id INTEGER);

A. SELECT id, id FROM sample1;

B. SELECT id AS data, data FROM sample1;

C. SELECT s1.id, s2.data FROM sample1 s1, sample2 s2;

D. SELECT s1.id, s2.idFROM sample1 AS s1, sample1 AS s2;

E. SELECT s1.id, s2.idFROM sample1 s1, sample2 s2;

Correct Answer: C

Question 4

You want to set a constraint so that the "item_id" in the "sales" table will always have a value that
already exists as "id" in the "item_master" table. Select the correct SQL statement to fill in the
underlined blank of the "sales" table. Definitions:
CREATE TABLE item_master (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE sales (
sales_id INTEGER,
item_id INTEGER,
num INTEGER,
);

A. REFERENCESitem_master (item_id)

B. REFERENCESitem_master (id) TO item_id

C. FOREIGN KEY (item_id) REFERENCES item_master (id)

D. FOREIGN KEY (id) REFERENCESitem_master (item_id)

E. REFERENCESitem_master (id)

Correct Answer: C

Question 5

Based on the relationship of columns within a table, select the most suitable description that
shows that column A is dependent on column B.

A. When the value in column A is changed, the corresponding value in column B also must be changed.

B. When the value in column B is changed, the corresponding value of column A also must be changed.

C. The value in column B is uniquely determined when a value in column A is selected.

D. The value in column A is uniquely determined when a value in column B is selected.

E. As long as column B exists, the amount of information will not decrease even if column A is deleted.

Correct Answer: D

Question 6

Select the correct result generated by execution of the following SQL statements: CREATE
TABLE log (id int, message TEXT, logtime TIMESTAMP);
CREATE TABLE log_01 () INHERITS (log);
INSERT INTO log_01 VALUES (1, 'error', CURRENT_TIMESTAMP); SELECT * FROM log;

A. Second 'CREATE TABLE' generates a syntax error.

B. 'INSERT' statement generates an error stating that the number of columns and values do not match".

C. 'SELECT' statement returns 0 rows, and exits successfully.

D. 'SELECT' statement returns 1 row, and exits successfully.

E. First 'CREATE TABLE' generates a syntax error.

Correct Answer: D

Question 7

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

A. PostgreSQL cannot be in a state where multiple users exist at the same time.

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

C. To delete an existing user for PostgreSQL, "deleteuser" command is used.

D. To create a new user for PostgreSQL, the "newuser" command is used.

E. Usernames not registered in the operating system cannot be registered as PostgreSQL users.

Correct Answer: B

Question 8

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. 70

B. 50

C. 100

D. 80

E. 60

Correct Answer: B

Question 9

Select the most suitable statement about the creation of a new database.

A. You can set the character encoding when creating a new database.

B. The target directory is specified by the environment variable PGDATA or the -D parameter when creating a database.

C. Only a PostgreSQL superuser is authorized to create a new database.

D. Only the OS superuser (root) can create databases.

E. Only one database can be used at the same time even if two or more databases are created.

Correct Answer: A

Question 10

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" proceeds regardless of the condition of client "A".

B. The update process for client "B" is blocked until the current connection for client "A" is finished.

C. The update process for client "B" is blocked until the current transaction for client "A" is finished.

D. The processes for both clients are blocked, and an error stating that a deadlock has been detected is generated.

E. The process of client "B" immediately generates an error.

Correct Answer: C

Welcome to TestSimulate

PostgreSQL-CE PostgreSQL CE 8 Silver (PGCES-02) Free Practice Test