PGCES-02 PostgreSQL CE 8 Silver Certification Practice

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)

A set of tables are defined as follows:

t1

t2

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

SELECT t1.name FROM t1 CROSS JOIN t2;

Options are :

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

Answer : 6 rows

Select two incorrect statements regarding large objects.

A. A large object is generated by 'CREATE LARGE OBJECT'.

B. A large object is added by 'INSERT'.

C. One large object is able to handle up to 2GB of data.

D. Binary data cannot be used unless declared as a large object.

E. An OID is used to identify a large object.

Options are :

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

Answer : c,e

Select two SQL statements which abort a transaction.

A. END

B. ROLLBACK

C. TRUNCATE

D. ABORT

E. DROP TRANSTACTION

Options are :

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

Answer : b,d

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions