PGCES-02 Online Practice Questions and Answers

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.

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

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

A. RULE

B. VIEW

C. TRIGGER

D. FUNCTION

E. CONSTRAINT

What does the following command do? Choose the most appropriate statement from the selection below.

Note: $ is the command prompt.

$ pg_dump postgres > pgsql

A. Writes a backup of the database postgres to the file pgsql.

B. Writes a backup of the entire database cluster using user postgres to the file pgsql.

C. Backs up the database postgres and writes an error message to the file pgsql.

D. Writes a backup of the entire database cluster to the file postgres and writes an error message to the file pgsql.

E. Outputs all of the content of the database postgres to the screen using the user pgsql.

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

A. 0 rows

B. 2 rows

C. 3 rows

D. 5 rows

E. 6 rows

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

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.

Select one incorrect statement about schemas.

A. A schema is the name space for a database object.

B. A new schema is created by 'CREATE SCHEMA'.

C. One user cannot own multiple schemas.

D. 'SELECT current_schema();' returns the current schema.

E. 'DROP SCHEMA' deletes a schema.

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

A. 'PREPARE'/'EXECUTE' is mainly used to optimize performance.

B. 'PREPARE' creates a plan for the prepared statement.

C. 'PREPARE' can only specify 'SELECT' as a prepared statement.

D. 'EXECUTE' executes the plan defined by 'PREPARE'.

E. 'DEALLOCATE' deallocates prepared statements.

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.