PGCES-02 PostgreSQL CE 8 Silver Certification Exam

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: UPDATE custom SET email = ''

FROM custom c

WHERE custom.introducer = c.id;

UPDATE custom SET introducer = NULL

WHERE introducer NOT IN (SELECT id FROM custom);

DELETE FROM custom WHERE id = 2 OR introducer = 2;

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

Options are :

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

Answer : 0 rows

SQL statements were executed in the following order. CREATE TABLE book (

id VARCHAR(21), title TEXT NOT NULL, price INT, UNIQUE (id), CHECK (price > 0)

);

INSERT INTO book VALUES ('4-12345-678-9', 'SQL book', 2300); --(1) INSERT INTO book (title,

price) VALUES ('PostgreSQL', 3000); --(2) UPDATE book SET id = '4-12345-678-9' WHERE id IS

NULL; --(3)

DELETE FROM book WHERE price < 0; --(4)

While executing, select the first location that generates an error.

Options are :

  • No error is generated
  • (3) (Correct)
  • (4)
  • (2)
  • (1)

Answer : (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);

Options are :

  • SELECT data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = 1 AND s2.id = 2;
  • SELECT id, data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = s2.id; (Correct)
  • SELECT s1.id, s1.data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = s2.id;
  • SELECT s1.id FROM sample1 AS s1;
  • SELECT s1.id FROM sample1 s1;

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

The table "t1" is defined below.

The column "id" for table "t1" is of INTEGER type.

id | name

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

1 | mammoth

2 | tortoise

3 | coelacanth

The following SQL statements were executed. Select the correct statement about the execution

result.

BEGIN;

DECLARE c SCROLL CURSOR FOR SELECT name FROM t1 ORDER BY id;

MOVE FORWARD 2 FROM c;

FETCH FORWARD ALL FROM c;

COMMIT;

Options are :

  • An error occurs part way through.
  • The number of rows returned by the FETCH statement is 0.
  • The number of rows returned by the FETCH statement is 3.
  • The number of rows returned by the FETCH statement is 1. (Correct)
  • The number of rows returned by the FETCH statement is 2.

Answer : The number of rows returned by the FETCH statement is 1.

The table "foo" is defined as follows:

CREATE TABLE foo (bar TEXT);

Next, four SQL statements were executed in the following order. INSERT INTO foo VALUES

('bar'); -------- (1)

ALTER TABLE foo ADD COLUMN c1 TEXT; ---- (2)

ALTER TABLE foo ADD UNIQUE (c1); ------- (3)

ALTER TABLE foo DROP COLUMN bar; ------- (4)

Select the correct statement from those below.

Options are :

  • An error occurs when executing the (1) SQL statement
  • An error occurs when executing the (3) SQL statement
  • No error is generated (Correct)
  • An error occurs when executing the (2) SQL statement.
  • An error occurs when executing the (4) SQL statement

Answer : No error is generated

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

Options are :

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

Answer : 2rows

A set of tables are defined as follows:

t1

t2

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

SELECT * FROM t1 LEFT OUTER JOIN t2 USING (id);

Options are :

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

Answer : 4ows

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 the two SQL statements that will generate an error when executed next.

A. DROP TABLE ftrans;

B. INSERT INTO fmaster VALUES (1, 'itemB');

C. DELETE FROM fmaster;

D. UPDATE fmaster SET name = NULL;

E. INSERT INTO ftrans VALUES (1, 2, NULL);

Options are :

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

Answer : b,c

Select two incorrect statements regarding 'TRIGGER'.

A. When UPDATE is executed to the table, the specified function can be called.

B. When INSERT is executed to the table, the specified function can be called.

C. When SELECT is executed to the table, the specified function can be called.

D. A trigger can be set up to call a specified function before or after the event occurs.

E. A corresponding rule is automatically created when a trigger is created.

Options are :

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

Answer : c,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 correct result value.

SELECT score FROM score ORDER BY gid DESC, score ASC LIMIT 1;

Options are :

  • 60
  • 80
  • 100
  • 70
  • 50 (Correct)

Answer : 50

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 with the following

content on the client side.

1,Pgsql \,inc.

2,Postgres Co. \,Ltd

3,SQL Company

Options are :

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

Answer : \copy company TO 'company.txt' DELIMITER ','

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

Options are :

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

Answer : a,e

Select one statement which will cause a syntax error.

Options are :

  • SELECT * FROM (SELECT * FROM customer); (Correct)
  • SELECT (SELECT item FROM sale WHERE id = 1);
  • SELECT * FROM sale WHERE cid = ANY (SELECT cid FROM customer);
  • SELECT * FROM sale WHERE name IN (SELECT name FROM names);
  • SELECT * FROM item WHERE cid IN (SELECT cid FROM customer);

Answer : SELECT * FROM (SELECT * FROM customer);

From the SQL commands below, select one that is generally classified as "DDL".

Options are :

  • INSERT
  • START TRANSACTION
  • SELECT
  • CREATE TABLE (Correct)
  • DELETE

Answer : CREATE TABLE

 Select one incorrect statement about schemas.

Options are :

  • 'SELECT current_schema();' returns the current schema.
  • A schema is the name space for a database object.
  • One user cannot own multiple schemas (Correct)
  • DROP SCHEMA' deletes a schema
  • A schema is the name space for a database object.

Answer : One user cannot own multiple schemas

Select one incorrect statement concerning the following SQL statement. CREATE OR REPLACE

VIEW sales_view

AS SELECT * FROM sales_table ORDER BY sales_date DESC LIMIT 10;


Options are :

  • You can confirm that the "sales_view" has been added by querying the view called "pg_views".
  • C. When you 'SELECT' the "sales_view", it displays the first 10 records from the "sales_table" sorted by the "sales_date" column in descending order
  • Defines the view called "sales_view".
  • Replaces "sales_view" if it already exists.
  • Some errors occur when "SELECT * FROM sales_table" is executed after the view is defined (Correct)

Answer : Some errors occur when "SELECT * FROM sales_table" is executed after the view is defined

A set of tables are defined as follows:

t1

t2

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

SELECT * FROM t1 UNION ALL SELECT * FROM t2;

Options are :

  • 2 rows
  • 4 rows
  • An error will occur.
  • 3 rows
  • 5 rows (Correct)

Answer : 5 rows

A table is defined as follows:

CREATE TABLE t (id INT, val TEXT);

Select two correct statements from below about the function "get_head" defined below. CREATE

FUNCTION get_head(BOOLEAN)

RETURNS TEXT LANGUAGE sql CALLED ON NULL INPUT

AS 'SELECT val FROM t WHERE $1 OR id > 0 ORDER BY id LIMIT 1;';

A. This function is defined using PL/pgSQL.

B. There are cases where this function returns multiple lines.

C. When NULL is passed for the argument and the function is executed, NULL is returned.

D. Even if this function is passed the same parameter value and executed multiple times, the

returned values will not necessarily also be the same value.

E. If a function with the same name and with type BOOLEAN as the parameter is already

defined, an error occurs.

Options are :

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

Answer : d,e

Select two incorrect descriptions regarding the following SQL statements. CREATE TABLE cities (

name text,

population float );

CREATE TABLE capitals (

state char(2)

) INHERITS (cities);

A. Defines the tables called "cities" and "capitals".

B. "capitals" inherits "cities".

C. Searching "capitals" also searches rows in "cities".

D. The columns "name" and "population" are also defined in "capitals".

E. The second SQL statement results in an error, since the 'INHERITS' keyword is no longer

available.

Options are :

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

Answer : c,e

Select an incorrect statement regarding prepared statements, and 'PREPARE' / 'EXECUTE'

commands.

Options are :

  • 'PREPARE' can only specify 'SELECT' as a prepared statement. (Correct)
  • PREPARE' creates a plan for the prepared statement
  • 'DEALLOCATE' deallocates prepared statements.
  • 'PREPARE'/'EXECUTE' is mainly used to optimize performance
  • 'EXECUTE' executes the plan defined by 'PREPARE'.

Answer : 'PREPARE' can only specify 'SELECT' as a prepared statement.

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 ('A');

SAVEPOINT sp;


INSERT INTO t1 VALUES ('B');

ROLLBACK TO sp;

INSERT INTO t1 VALUES ('C');

COMMIT;

Options are :

  • 4row
  • 5row
  • 3row (Correct)
  • 0row
  • 1 row

Answer : 3row

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;

Options are :

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

Answer : 2 rows

Select two correct statements about the command shown below. Note: $ is the command prompt.

$ vacuumdb -az

A. Recovers unused areas from all of the databases.

B. Collects statistical information related to the table content for all of the databases.

C. Processes the job equivalent of the VACUUM FULL command for all of the databases.

D. Processes the job equivalent of the VACUUM VERBOSE command for all of the databases.

E. The database can not be accessed until this command is finished.

Options are :

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

Answer : a,b

Select two suitable statements about postgresql.conf configuration.

A. A line that starts with ! (exclamation mark) is interpreted as a comment.

B. You can have different parameters for the same option to configure each database

differently.

C. The timing of when a change in any value is reflected is different depending on the

configuration parameter.

D. All options have no default values. Therefore, all of them must be set specifically and

thoroughly.

E. As a boolean value, any of the following can be used: TRUE, FALSE, ON, OFF, YES, NO, 1,

0.

Options are :

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

Answer : c,e

I would like to be able to save log entries as shown below. Select a correct configuration setting

from statements below.

LOG: connection received: host=[local] port=

LOG: connection authorized: user=postgres database=test


Options are :

  • syslog = true
  • log_min_level = log
  • log_hostname = true
  • log_connections = true (Correct)
  • log_authorization = true

Answer : log_connections = true

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;

Options are :

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

Answer : a,b

A pg_hba.conf file is set up as follows.

local all all md5

host all all 127.0.0.1/32 md5

host all all 172.16.1.0/24 md5

When user foo connects to database bar from host IP address 172.16.1.2, I would like password

verification to not be performed.

Select one appropriate line for the new pg_hba.conf file.

Options are :

  • Add "host foo bar 172.16.1.2/32 trust" to the first row.
  • Add "host foo bar 172.16.1.2/32 trust" to the last row.
  • The settings are fine as is.
  • Add "host bar foo 172.16.1.2/32 trust" to the first row. (Correct)
  • Add "host bar foo 172.16.1.2/32 trust" to the last row

Answer : Add "host bar foo 172.16.1.2/32 trust" to the first row.

Select two statements that the command below DOES NOT do.

Note: $ is the command prompt.

$ pg_dump -b -F c b > d

A. Backs up the "b" database to the "c" file, and stores error messages to the "d" file.

B. Backs up the "b" database to the "d" file.

C. Backs up large objects.

D. Backs up the "c" database to the "d" file as the "c" user.

E. Creates a backup, which is restorable with the pg_restore command.

Options are :

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

Answer : a,d

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions