PGCES-02 Online Practice Questions and Answers

The "animal" table consists of the following data:Select the correct result returned by executing the following SQL statement: SELECT name FROM animal ORDER BY weight DESC LIMIT 2 OFFSET 1;

A. A syntax error will occur.

Select two suitable statements regarding the pg_dump command.

A. pg_dump is an SQL command.

B. Only the user who executed initdb can execute pg_dump.

C. pg_dump can create a backup while postmaster is running.

D. pg_dump can create a backup while postmaster is stopped.

E. pg_dump can create a backup of a specified table.

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

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.

What does the following command do? Select the correct description from below. Note:

"text=#" is the command prompt for psql.

test=# ANALYZE foo;

A. Collects statistical information related to the content of the database foo.

B. Collects statistical information related to the content of the table foo.

C. Collects statistical information related to the content of the database test.

D. Outputs statistical information related to the content of the table foo.

E. The command does not generate an error; however, it does not do anything either.

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.

The following are statements related to the postmaster. Select one statement that is incorrect.

A. postmaster is a server process that receives connections from clients.

B. The "pg_ctl start" command boots up postmaster.

C. It is not possible to boot up the postmaster process by directly executing the postmaster command.

D. One postmaster process controls one database cluster.

E. The "pg_ctl stop" command stops postmaster.

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.

A. (1)

B. (2)

C. (3)

D. (4)

E. No error is generated.

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.