PGCES-02 PostgreSQL CE 8 Silver Certification Test

Select two incorrect statements about the Point-In-Time Recovery (PITR) from below.

A. This is a backup method integrating a physical backup and a transaction log (WAL).

B. It is necessary to stop the database server to perform a backup for the first time.

C. Updated data is continuously saved.

D. A restore can be performed to any arbitrary point in time since the starting point of PITR.

E. A backup can only be performed on a per-database basis.

Options are :

  • a,b
  • b,e (Correct)
  • d,e
  • c,d

Answer : b,e

What does the following command do? Select the correct description from below. Note: "text=#" is

the command prompt for psql.

test=# ANALYZE foo;

Options are :

  • Collects statistical information related to the content of the database foo
  • The command does not generate an error; however, it does not do anything either.
  • Outputs statistical information related to the content of the table foo.
  • Collects statistical information related to the content of the table foo (Correct)
  • Collects statistical information related to the content of the database test

Answer : Collects statistical information related to the content of the table foo

It is possible to backup a database cluster by copying the entire data directory. Select two

suitable descriptions regarding this backup method.

A. The backup data will be a text file consisting of SQL statements.

B. The database server must be stopped prior to the backup.

C. The "pg_restore" command is used to restore the database.

D. "psql" is used to restore the database.

E. You can use standard tools like "tar" and "rsync" to backup files and directories.

Options are :

  • d,e
  • b,e (Correct)
  • a,b
  • b,c
  • c,d

Answer : b,e

The following table called 'company' is defined as follows:

id | name

----+------------------

1 | Pgsql,inc.

2 | Postgres Co.,Ltd

3 | SQL Company.

Select the most appropriate psql command for generating a text file company.txt on the client side

with the following content:

1,"Pgsql,inc."

2,"Postgres Co.,Ltd"

3,SQL Company.

Options are :

  • \copy company TO 'company.txt' DELIMITER ','
  • \copy company TO "company.txt"
  • \copy company TO 'company.txt' WITH DELIMITER AS ','
  • \copy company TO company.txt CSV (Correct)
  • \copy company TO 'company.txt' WITH ','
  • None

Answer : \copy company TO company.txt CSV

Select the most suitable statement about PostgreSQL from below.

Options are :

  • PostgreSQL is shareware.
  • There are PostgreSQL GPL license versions and commercial license versions
  • You can use PostgreSQL for free; however, the source code is not open to the public
  • You need to be pre-registered to use PostgreSQL
  • PostgreSQL can be used by everyone free of charge for any purpose, be it private, commercial, or academic (Correct)

Answer : PostgreSQL can be used by everyone free of charge for any purpose, be it private, commercial, or academic

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

connected database?

Options are :

  • \dt "database_name"
  • \dT
  • \dt (Correct)
  • SELECT * FROM pg_table_list;
  • SHOW tables;

Answer : \dt

Select one incorrect description about changing the settings of PostgreSQL during operation.

Options are :

  • There are parameters that can not be set using the SET command
  • Values set by a superuser using the SET command are valid for different connections made later. (Correct)
  • All of the current settings can be displayed using the SHOW ALL command.
  • The current value of a parameter can be confirmed using the SHOW command.
  • Changes that can be made using the SET command have higher priority than ones in postgresql.conf.

Answer : Values set by a superuser using the SET command are valid for different connections made later.

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

Options are :

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

Answer : The authentication settings in the file are evaluated from the top line to the bottom line

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

Options are :

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

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

Select the correct SQL statement which concatenates strings 'ABC' and 'abc' to form 'ABCabc

Options are :

  • SELECT cat('ABC', 'abc') FROM pg_operator;
  • SELECT 'ABC' + 'abc';
  • SELECT 'ABC' || 'abc'; (Correct)
  • SELECT 'ABC' + 'abc' FROM pg_operator;
  • SELECT 'ABC' . 'abc';

Answer : SELECT 'ABC' || 'abc';

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 )

);

A. INSERT INTO sample VALUES (1, 0);

B. INSERT INTO sample VALUES (2, -2);

C. INSERT INTO sample VALUES (3, NULL);

D. INSERT INTO sample VALUES (NULL, -4);

E. INSERT INTO sample VALUES (5, -5);

Options are :

  • b,c
  • d,e
  • a,b
  • a,d (Correct)

Answer : a,d

The "sample" table consists of the following data:

How many rows are returned by executing the following SQL statement? SELECT DISTINCT ON

(data) * FROM sample;

Options are :

  • 4 rows D. 5 rows
  • 6 rows
  • 2 rows
  • 3 rows (Correct)

Answer : 3 rows

The table "custom" is defined below.

The "id" column and "introducer" column are of INTEGER type, and the "email" column is of TEXT

type.

id | email| introducer

----+-----------------+------------

2 | aaa@example.com | 1

3 | bbb@example.com | 2

4 | ccc@example.com | 2

Three SQL statements were executed in the following order: INSERT INTO custom

SELECT max(id) + 1, 'ddd@example.com', 4 FROM custom;

UPDATE custom SET introducer = 999

WHERE email = 'bbb@example.com';

DELETE FROM custom

WHERE introducer NOT IN (SELECT id FROM custom);

Select the number of rows in the "custom" table after the execution.

Options are :

  • 2 rows (Correct)
  • 0 rows
  • 4 rows
  • 3 rows
  • 1 row

Answer : 2 rows

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);

Options are :

  • SELECT id AS data, data FROM sample1;
  • D. SELECT s1.id, s2.id FROM sample1 s1, sample2 s2
  • SELECT s1.id, s2.id FROM sample1 AS s1, sample1 AS s2;
  • SELECT s1.id, s2.data FROM sample1 s1, sample2 s2; (Correct)
  • SELECT id, id FROM sample1;

Answer : SELECT s1.id, s2.data FROM sample1 s1, sample2 s2;

SQL statements were executed in the following order:

CREATE TABLE fmaster

(id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE ftrans

(id INTEGER REFERENCES fmaster (id), stat INTEGER, date DATE);

INSERT INTO fmaster VALUES (1, 'itemA');

INSERT INTO ftrans VALUES (1, 1, CURRENT_DATE);

Select two SQL statements that will generate an error when executed next.

A. INSERT INTO ftrans VALUES (1, 1, CURRENT_DATE);

B. INSERT INTO ftrans VALUES (2, 1, '2007-07-07');

C. UPDATE fmaster SET name = 'itemAX' WHERE id = 1;

D. UPDATE fmaster SET id = 100 WHERE id = 1;

E. UPDATE ftrans SET id = 200 WHERE id = 1;

Options are :

  • b,c
  • c,d
  • a,b
  • d,e
  • a,c (Correct)

Answer : a,c

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;

Options are :

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

Answer : At the point that 'COMMIT' is executed, a message that starts with "Asynchronous notification 'sign_v' received" is output

Select two correct descriptions about views.

A. A view is created by 'DECLARE VIEW', and deleted by 'DROP VIEW'.

B. A view is a virtual table which does not exist.

C. A view is created to simplify complicated queries.

D. You can create a view with the same name as already existing tables.

E. A view only exists while the postmaster is running, and is deleted when the postmaster stops.

Options are :

  • b,c (Correct)
  • d,e
  • b,d
  • a,b

Answer : b,c

Four SQL statements were executed in the following order. CREATE TABLE foo (bar INT);

ALTER TABLE foo ALTER bar TYPE BIGINT;

ALTER TABLE foo ADD baz VARCHAR(5);

ALTER TABLE foo DROP bar;

Select two SQL statements that generate an error when executed.

A. INSERT INTO foo VALUES ('12345');

B. INSERT INTO foo VALUES ('5000000000');

C. INSERT INTO foo VALUES ('ABC');

D. INSERT INTO foo VALUES (2000000000);

E. INSERT INTO foo VALUES (NULL);

Options are :

  • c,d
  • a,b (Correct)
  • d,e
  • b,c

Answer : a,b

Select two suitable statements about sequences.

A. A sequence always returns a 4-byte INTEGER type value, so the maximum value is

2147483647.

B. A sequence is defined by 'CREATE SEQUENCE', and deleted by 'DROP SEQUENCE'.

C. Although the "nextval" function is called during a transaction, it will have no effect if that

transaction is rolled back.

D. A sequence always generates 0 or consecutive positive numbers.

E. A sequence number can be set by calling the "setval" function

Options are :

  • c,d
  • d,e
  • b,e (Correct)
  • a,b

Answer : b,e

Table "t1" is defined as follows:

CREATE TABLE t1 (value VARCHAR(5));

A set of SQL statements were executed in the following order. Select the number 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');

Options are :

  • 2 rows (Correct)
  • 1 row
  • 0 rows
  • 3 rows
  • 4 rows

Answer : 2 rows

Select two suitable statements regarding the following SQL statement:

CREATE TRIGGER trigger_1 AFTER UPDATE ON sales FOR EACH ROW EXECUTE

PROCEDURE write_log();

A. It is defining a trigger "trigger_1".

B. Every time 'UPDATE' is executed on the "sales" table, the "write_log" function is called once.

C. The "write_log" function is called before 'UPDATE' takes place.

D. 'UPDATE' is not executed if "write_log" returns NULL.

E. 'DROP TRIGGER trigger_1 ON sales;' deletes the defined trigger.

Options are :

  • a,e (Correct)
  • a,b
  • b,c
  • c,d

Answer : a,e

Select three SQL statements which return NULL.

A. SELECT 0 = NULL;

B. SELECT NULL != NULL;

C. SELECT NULL IS NULL;

D. SELECT NULL;

E. SELECT 'null'::TEXT;

Options are :

  • a,b,d (Correct)
  • a,b,c
  • b,c,d
  • c,d,e

Answer : a,b,d

The "sample" table consists of the following data:

How many rows are returned by executing the following SQL statement? SELECT * FROM

sample WHERE v ~ 'ab';

Options are :

  • 0 rows
  • 4 rows
  • 2 rows (Correct)
  • 3 rows
  • 1 row

Answer : 2 rows

The tables "t1" and "t2" are defined below.

The tables "t1" and "t2" have columns "id" which are type of INTEGER and column "name"s which

are type of TEXT.

t1

t2

The following SQL command was executed. Select the number of rows in the result. SELECT *

FROM t1 NATURAL FULL OUTER JOIN t2;

Options are :

  • 2 rows
  • 3rows
  • 6 rows
  • 5 rows (Correct)
  • 4 rows

Answer : 5 rows

 Select two transaction isolation levels supported in PostgreSQL.

A. DIRTY READ

B. READ COMMITTED

C. REPEATABLE READ

D. PHANTOM READ

E. SERIALIZABLE

Options are :

  • d,e
  • c,d
  • a,b
  • b,e (Correct)
  • c,b

Answer : b,e

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 number of rows in the result. SELECT gid,

max(score) FROM score

GROUP BY gid HAVING max(score) > 60;

Options are :

  • 5 rows
  • 2 rows
  • 1 row
  • 3 rows (Correct)
  • 4 rows

Answer : 3 rows

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions