PGCES-02 Online Practice Questions and Answers

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.

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 | [email protected] | 1 3 | [email protected] | 2 4 | [email protected] | 2 Three SQL statements were executed in the following order: INSERT INTO custom SELECT max(id) + 1, '[email protected]', 4 FROM custom; UPDATE custom SET introducer = 999 WHERE email = '[email protected]'; DELETE FROM custom WHERE introducer NOT IN (SELECT id FROM custom); Select the number of rows in the "custom" table after the execution.

A. 0 rows

B. 1 row

C. 2 rows

D. 3 rows

E. 4 rows

I would like to set the default character encoding for the client to Unicode. Select the most appropriate configuration parameter in postgresql.conf from those below.

A. backend_encoding = UNICODE

B. frontend_encoding = UNICODE

C. client_encoding = UNICODE

D. default_encoding = UTF8

E. encoding = UTF8

Select the correct command to collect and save the statistical information of a table.

A. ANALYZE

B. CLUSTER

C. REINDEX

D. STATISTIC COLLECTION

E. STATISTIC COLLECTOR

I would like to enable all users to SELECT the "item" table. Select the most appropriate SQL statement from below.

A. GRANT public SELECT ON item;

B. GRANT SELECT ON item TO public;

C. REVOKE 'r' ON item TO public;

D. REVOKE READ ON item TO public;

E. REVOKE public SELECT ON item;

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

A. '10 day'::timestamp

B. '10 day'::interval

C. 10::day

D. 8640000::time

E. age(8640000)

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. FOREIGN KEY (id) REFERENCES item_master (item_id)

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

C. REFERENCES item_master (item_id)

D. REFERENCES item_master (id)

E. REFERENCES item_master (id) TO item_id

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.

A. DELETE product WHERE name ~ '2004';

B. DELETE product WHERE contain(name, '2004');

C. DELETE FROM product WHERE name IN '2004';

D. DELETE FROM product WHERE name LIKE '%2004%';

E. DELETE FROM product WHERE name SIMILAR TO '2004';

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

A. If any row exists in the "record" table, no change can be made to the "master" table.

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

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

D. The "record" table cannot have duplicate "id"s.

E. These SQL statements are invalid; no constraints are created.

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.

A. CREATE INDEX t1_idx ON t1 (id, upper(name));

B. CREATE INDEX t1_idx ON t1 USING HASH (id);

C. CREATE INDEX t1_idx ON t1 (name);

D. ALTER TABLE ADD INDEX ON t1 (id, upper(name));

E. ALTER TABLE ADD INDEX ON t1 (id, name);

Select an appropriate command to check the PostgreSQL version in psql.

A. \server_version

B. SELECT version;

C. SELECT version();

D. SHOW version;

E. SHOW server;

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;

A. Defines the view called "sales_view".

B. Replaces "sales_view" if it already exists.

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.

D. Some errors occur when "SELECT * FROM sales_table" is executed after the view is defined.

E. You can confirm that the "sales_view" has been added by querying the view called "pg_views".

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.

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

A. syslog = true

B. log_connections = true

C. log_authorization = true

D. log_hostname = true

E. log_min_level = log

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, 0.1