PGCES-02 PostgreSQL CE 8 Silver Certification

Select two suitable statements regarding the data types of PostgreSQL.

A. One field can handle up to 1GB of data.

B. 'n' in CHARACTER(n) represents the number of bytes.

C. Only the INTEGER type can be declared as an array.

D. There is a non-standard PostgreSQL data type, called Geometric data type, which handles 2-

dimensional data.

E. A large object data type can be used to store data of unlimited size.

Options are :

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

Answer : a,d

Select two suitable statements about major version upgrades of PostgreSQL from below.

A. You can use the databases of the old major version.

B. To use the data from the old version, you only need to replace the program.

C. To use the data from the old version, you need to conduct a backup and restore.

D. There is a possibility of configuration parameter changes after major version upgrades.

E. Upgrade scripts can be executed while the old version of PostgreSQL is running.

Options are :

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

Answer : c,d

Select an incorrect statement regarding the following SQL statement. Note that "user_view" is a

view.

CREATE OR REPLACE RULE rule_1 AS ON UPDATE TO user_view DO INSTEAD NOTHING;

Options are :

  • It is defining a rule "rule_1".
  • It will replace "rule_1" if it already exists.
  • 'DROP RULE rule_1 ON user_view' deletes the above definition
  • Executing 'UPDATE user_view' will no longer output errors.
  • When executing 'UPDATE user_view', data is updated in the table that is the origin of the view. (Correct)

Answer : When executing 'UPDATE user_view', data is updated in the table that is the origin of the view.

Select two incorrect statements regarding 'DOMAIN'.

A. When defining a domain, you can add a default value and constraints to the original data.

B. Domain is a namespace existing between databases and objects such as tables.

C. A domain is created by 'CREATE DOMAIN'.

D. A domain can be used as a column type when defining a table.

E. To define a domain, both input and output functions are required.

Options are :

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

Answer : b,e

PostgreSQL can use an index to access a table. Select two incorrect statements about indexes.

A. An index is created by 'CREATE INDEX', and deleted by 'DROP INDEX'.

B. By using an index effectively, searching and sorting performs faster.

C. There are B-tree, Hash, R-tree and GiST index types.

D. By creating an index, performance always improves.

E. Creating an unused index does not affect the performance of a database at all.

Options are :

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

Answer : d,e

The following is the result of executing the createlang command which is installed with

PostgreSQL.

$ createlang -U postgres --list mydb

Procedural Languages

Name | Trusted?

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

plpgsql | yes

Select two correct statements from below.

A. The procedural language plpgsql is installed in the database mydb using the above command.

B. The procedural language plpgsql can be used in the database mydb.

C. plpgsql is a trusted language, so it can execute the OS commands on the server side.

D. plpgsql is a trusted language, so it can read/write OS files on the server side.

E. plpgsql is a safe language with restricted operations.

Options are :

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

Answer : d,e

Select two suitable statements regarding creating a new table.

A. There is no upper limit to the number of columns in a table.

B. A newly created table is empty and has 0 rows

C. You can only use alphabetic characters for a table name.

D. The row name must be within 16 characters.

E. The SQL 'CREATE TABLE' statement is used to create a new table.

Options are :

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

Answer : b,e

You want to delete rows in the "product" table which include the value '2004' in the "name" field.

Select the correct statement to achieve this task.

Options are :

  • DELETE FROM product WHERE name LIKE '%2004%'; (Correct)
  • DELETE product WHERE contain(name, '2004');
  • DELETE FROM product WHERE name SIMILAR TO '2004';
  • DELETE product WHERE name ~ '2004';
  • DELETE FROM product WHERE name IN '2004';

Answer : DELETE FROM product WHERE name LIKE '%2004%';

The table "t1" is defined by the following SQL statement: CREATE TABLE t1 (id integer, name

varchar(20));

You want to increase the execution speed of the SQL statement below:

SELECT id, name FROM t1 WHERE id < 123 AND upper(name) = 'MAMMOTH'; Select the most

suitable SQL statement to create an index.

Options are :

  • ALTER TABLE ADD INDEX ON t1 (id, name);
  • ALTER TABLE ADD INDEX ON t1 (id, upper(name));
  • CREATE INDEX t1_idx ON t1 (name);
  • CREATE INDEX t1_idx ON t1 USING HASH (id);
  • CREATE INDEX t1_idx ON t1 (id, upper(name)); (Correct)

Answer : CREATE INDEX t1_idx ON t1 (id, upper(name));

In the "customer" table, you want to change the "email" values which have an "id" value of 10000

or less, to NULL. Select the correct SQL statement to achieve this task.

Options are :

  • UPDATE customer SET email = NULL WHERE id <= 10000; (Correct)
  • DELETE FROM customer.email WHERE id < 10001;
  • UPDATE email = NULL FROM customer WHERE id <= 10000;
  • UPDATE customer SET email IS NULL WHERE id < 10001
  • UPDATE FROM customer SET email = NULL WHERE id <= 10000;

Answer : UPDATE customer SET email = NULL WHERE id <= 10000;

A table is defined as below. Select the most suitable description about the foreign key constraint.

CREATE TABLE master (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE record (id INTEGER REFERENCES master (id), count INTEGER);

Options are :

  • If the "record" table contains a row with an "id", the corresponding "id" row in the "master" table cannot be deleted. (Correct)
  • These SQL statements are invalid; no constraints are created
  • The "record" table cannot have duplicate "id"s.
  • If any row exists in the "record" table, no change can be made to the "master" table
  • If the "record" table contains a row with an "id", no change can be made at all to the corresponding "id" row in the "master" table.

Answer : If the "record" table contains a row with an "id", the corresponding "id" row in the "master" table cannot be deleted.

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.

Options are :

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

Answer : a,e

You want to create a cursor that will SELECT the "customer" table. The created cursor must be

able to move in any direction and reference data even after the end of the transaction.

Select one answer containing the correct keyword(s) to fill in the underlined blank below.

DECLARE cursor1 __________ FOR SELECT * FROM customer;

Options are :

  • NO SCROLL CURSOR WITH HOLD
  • CURSOR
  • SCROLL CURSOR WITH HOLD (Correct)
  • INSENSITIVE CURSOR
  • CURSOR WITHOUT HOLD

Answer : SCROLL CURSOR WITH HOLD

The tables "s1" and "s2" are defined below.

The column "id" for tables "s1" and "s2" is of INTEGER type. The column "enable" for table "s1" is

of

BOOLEAN type, and the column "name" for table "s2" is of TEXT type.

s1:

id | enable

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

1 | t

2 | f

s2:

id | name

----+------

1 | post

2 | gre

3 | SQL

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

FROM s2 WHERE id IN (SELECT id FROM s1);

Options are :

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

Answer : 2row

What happens if an SQL statement syntax error occurs while a transaction is running? Select the

correct action from below.

Options are :

  • The transaction is stopped and you cannot issue any SQL commands other than a command to end the transaction (Correct)
  • The transaction is aborted and a new transaction is started automatically
  • The "postmaster" process is terminated
  • The transaction continues
  • The connection is terminated.

Answer : The transaction is stopped and you cannot issue any SQL commands other than a command to end the transaction

The "sample" table consists of the data below. The column "x" is of type INTEGER. How many

rows are returned by executing the following SQL statement?

SELECT 6 / x FROM sample

WHERE CASE WHEN x = 0 THEN FALSE ELSE TRUE END;

Options are :

  • ERROR division by zero" and no rows are returned.
  • 0 rows with no errors
  • 3 rows
  • 2 rows (Correct)
  • 1 row

Answer : 2 rows

Which normal form has the constraint that there must be no tables with duplicate column values in

the same row?

Options are :

  • Third normal form
  • Boyce/Codd normal form
  • Fourth normal form
  • Second normal form
  • First normal form (Correct)

Answer : First normal form

There is a table "tb1" that has a column "c1" defined as type TEXT. The following SQL is executed

while client "A" is connected.

BEGIN;

LOCK TABLE tb1 IN ACCESS EXCLUSIVE MODE; SELECT * FROM tb1;

While the above 'SELECT' statement is being executed, client "B" connects to the same database

and executes the following SQL.

Select two correct statements describing the behavior of PostgreSQL.

INSERT INTO tb1 (c1) VALUES ('new line');

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

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

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

C. The process for client "B" will be deleted regardless of the condition of client "A".

D. The process of client "B" will affect the SELECT result of client "A".

E. The process of client "B" will not affect the SELECT result of client "A".

Options are :

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

Answer : b,e

The following SQL defines an INSERT with respect to item_view.

Select the keyword that is applicable in the underlined blank.

CREATE _______ foo AS ON INSERT TO item_view

DO INSTEAD INSERT INTO item_table VALUES (NEW.id, NEW.itemname);

Options are :

  • VIEW
  • TRIGGER
  • CONSTRAINT
  • FUNCTION
  • RULE (Correct)

Answer : RULE

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

Tables "t1" and "t2" have columns "id" that are of INTEGER type, and columns "name" that are of

TEXT type.

t1

t2

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

FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;

Options are :

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

Answer : 3rows

Select the correct SQL statement to define a new data type.

Options are :

  • CREATE TYPE (Correct)
  • CREATE OPERATOR
  • CREATE FUNCTION
  • CREATE DATABASE
  • CREATE CLASS

Answer : CREATE TYPE

Select the SQL command that must be executed prior to executing the EXECUTE command.

Options are :

  • PREPARE (Correct)
  • CREATE FUNCTION
  • DECLARE
  • ALLOCATE
  • LOAD

Answer : PREPARE

table and view are defined as follows:

CREATE TABLE item (id INT, name TEXT, description TEXT);

CREATE VIEW item_simple AS SELECT id, name FROM item;

A set of SQL statements were executed in the order below. Select the most appropriate statement

concerning the execution results.

BEGIN;

SELECT * FROM item_simple;

INSERT INTO item_simple VALUES (1, 'item_name_1');

UPDATE item_simple SET name = 'item_name_2' WHERE id = 1; DELETE FROM item_simple;

END;

Options are :

  • An error is generated at the point the SELECT statement is executed
  • No error is generated
  • An error is generated at the point the INSERT statement is executed. (Correct)
  • An error is generated at the point the UPDATE statement is executed.
  • An error is generated at the point the DELETE statement is executed

Answer : An error is generated at the point the INSERT statement is executed.

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 s1.id FROM sample1 s1;
  • SELECT data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = 1 AND s2.id = 2;
  • SELECT s1.id, s1.data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = s2.id;
  • SELECT id, data FROM sample1 AS s1, sample2 AS s2 WHERE s1.id = s2.id; (Correct)
  • SELECT s1.id FROM sample1 AS s1;

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

Select two suitable statements about the BSD license from below.

A. If you make changes to the source code, feedback must be sent to the original developers.

B. It is used by open source software.

C. User registration is required.

D. It is defined by Free Software Foundation, Inc. (FSF).

E. Software under the BSD license can be incorporated with any programs provided that the

copyright notice appears in all copies.

Options are :

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

Answer : a,e

The tables "t1" and "t2" are defined in the same way (they have the same data types and column

names). You want to select rows in "t1" which are not in "t2".

Select a correct keyword to fill in the blank below.

SELECT * FROM t1 ______ SELECT * FROM t2;

Options are :

  • INTERSECT ALL
  • EXCEPT (Correct)
  • NAND
  • INTERSECT
  • UNION

Answer : EXCEPT

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;

Options are :

  • SELECT' statement returns 1 row, and exits successfully. (Correct)
  • 'SELECT' statement returns 0 rows, and exits successfully.
  • Second 'CREATE TABLE' generates a syntax error.
  • 'INSERT' statement generates an error stating that the number of columns and values do not match".
  • First 'CREATE TABLE' generates a syntax error.

Answer : SELECT' statement returns 1 row, and exits successfully.

The present time is noon of July 7th, 2007, and the result of the following

SQL sentence was '2007-07-17 12:00:00'.

Select the correct expression to fill in the blank below.

SELECT CURRENT_TIMESTAMP::timestamp + ________________ ;

Options are :

  • age(8640000 (Correct)
  • '10 day'::timestamp
  • '10 day'::interval
  • 8640000::time
  • 10::day

Answer : age(8640000

Select one incorrect description regarding the following SQL statement defining a function.

CREATE OR REPLACE FUNCTION get_file_list(TEXT, BOOLEAN)

RETURNS SETOF TEXT LANGUAGE C STRICT

SECURITY DEFINER AS 'myfuncs.so';

Options are :

  • This function may be defined in 'myfuncs.so'.
  • If this function is called with a NULL parameter, it will return 0 when executed
  • This function operates with the authority of the user who executed it. (Correct)
  • This function can return multiple rows.
  • This SQL statement defines a function written in the C language.

Answer : This function operates with the authority of the user who executed it.

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;

Options are :

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

Answer : 3row

The table "tbl" is defined below such that it could only store non-negative integers in the column

"nn".

Select the keyword that is applicable for the underlined blank.

CREATE _______ natural_number AS DECIMAL CHECK (VALUE >= 0); CREATE TABLE tbl(nn

natural_number);

Options are :

  • DOMAIN (Correct)
  • SCHEMA
  • VIEW
  • TYPE
  • RULE

Answer : DOMAIN

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,

);

Options are :

  • REFERENCES item_master (item_id)
  • REFERENCES item_master (id)
  • REFERENCES item_master (id) TO item_id
  • FOREIGN KEY (id) REFERENCES item_master (item_id)
  • FOREIGN KEY (item_id) REFERENCES item_master (id) (Correct)

Answer : FOREIGN KEY (item_id) REFERENCES item_master (id)

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions