PGCES-02 PostgreSQL CE 8 Silver Certification Practice Test

psql generated the following error message:

psql: could not connect to server: Connection was refused

Is the server running on host "server.example.com" and accepting

PostgreSQL CE PGCES-02 Exam

BrainDumps.

TCP/IP connections on port 5432?

Select two reasons that are NOT the cause of this error.

A. Host "server.example.com" does not exist.

B. The PostgreSQL server is not running on "server.example.com".

C. The PostgreSQL server is not accepting TCP/IP connections on "server.example.com".

D. The PostgreSQL server is running on a port other than 5432 on "server.example.com".

E. The username and/or password are incorrect

Options are :

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

Answer : a,b

Select two incorrect statements about the function of the information schema.

A. It consists of a group of views included in a schema called "information_schema".

B. Information on objects defined in a database can be referenced.

C. The number of tables defined in a database can be confirmed.

D. Administrator privileges are needed to reference the information schema.

E. In order to enable the information schema, "information_schema = true" must be set in

postgresql.conf

Options are :

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

Answer : d,e

Select two correct statements from below concerning the ANALYZE command.

A. It renews the statistical information of the table content.

B. It takes some time to execute, but it does not lock the table.

C. If the FULL option is used when executing, the size of the file can be reduced.

D. If ANALYZE is insufficient, the most efficient search plan will not be selected for queries.

E. If ANALYZE is not used at all, there are times when it becomes impossible to see any of the

data.

Options are :

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

Answer : a,d

Select one incorrect statement about the SQL COPY command.

Options are :

  • Only the superuser can specify the output file name.
  • Copies row data from a file into a table.
  • The delimiter that separates column data can be changed from the default TAB character.
  • You can output the row data of a table to a specified client side file. (Correct)
  • It copies data between a file and a table.

Answer : You can output the row data of a table to a specified client side file.

Select a correct SQL command to change existing user "george"'s password to "foobar".

Options are :

  • ALTER USER george CHANGE PASSWORD 'foobar';
  • SET USER george PASSWORD TO 'foobar
  • ALTER USER george WITH PASSWORD 'foobar';ALTER USER george CHANGE PASSWORD 'foobar'; (Correct)
  • SET USER george ALTER PASSWORD 'foobar';
  • ALTER USER george SET PASSWORD 'foobar

Answer : ALTER USER george WITH PASSWORD 'foobar';ALTER USER george CHANGE PASSWORD 'foobar';

Select one incorrect statement from the below about a database cluster.

Options are :

  • When a database cluster is created, a database superuser is registered using the username of the OS at the time of creation unless otherwise designated.
  • Special databases called template0 and template1 are created in a database cluster by default.
  • It is possible to have multiple databases within a database cluster
  • A database cluster is created using initdb command.
  • Each host can have only one database cluster (Correct)

Answer : Each host can have only one database cluster

Select one SQL statement that will cause an error.

Options are :

  • SELECT current_database();
  • SELECT version (Correct)
  • SELECT current_date;
  • SELECT current_user;
  • SELECT current_timestamp;

Answer : SELECT version

Select two suitable statements regarding a postmaster process.

A. A postmaster process waits for client connection requests.

B. A postmaster process receives and processes database queries.

C. A postmaster process creates a child process which processes the given queries.

D. A postmaster process collects statistical information.

E. A postmaster process is created for each client connection.

Options are :

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

Answer : a,c

What does the following command do? Choose one incorrect statement from the selection below.

$ pg_dumpall -U postgres > 20060601.bak

Options are :

  • Backup all the databases in a database cluster.
  • You can access databases while the backup is in process.
  • Backup with plain-text format.
  • Backup postgresql.conf along with databases. (Correct)
  • Backup user and group information along with databases.

Answer : Backup postgresql.conf along with databases.

Select two commands below from which privileges cannot be changed by the GRANT and REVOKE statements.

A. SELECT

B. VACUUM

C. DELETE

D. TRIGGER

E. DROP

Options are :

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

Answer : b,e

Select the most suitable statement about the creation of a new database

Options are :

  • Only the OS superuser (root) can create databases.
  • The target directory is specified by the environment variable PGDATA or the -D parameter when creating a database
  • Only one database can be used at the same time even if two or more databases are created
  • You can set the character encoding when creating a new database. (Correct)
  • Only a PostgreSQL superuser is authorized to create a new database. (Incorrect)

Answer : You can set the character encoding when creating a new database.

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

Options are :

  • The "pg_ctl stop" command stops postmaster.
  • None
  • It is not possible to boot up the postmaster process by directly executing the postmaster command. (Correct)
  • postmaster is a server process that receives connections from clients
  • One postmaster process controls one database cluster.
  • The "pg_ctl start" command boots up postmaster.

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

What does the following command do? Choose two incorrect statements from the selection below.

Note: $ is the command prompt.

$ pg_restore -U postgres -d database1 database1.dump

A. This command restores the database database1 from the file database1.dump.

B. This command connects to a database as the user 'postgres'.

C. This command can not be executed unless the postmaster is running.

D. This command can not restore large objects.

E. This command must be executed under the condition where the database database1 does

not exist.

Options are :

  • C,D
  • D,E (Correct)
  • A,E
  • A,B

Answer : D,E

Select one incorrect statement concerning changes from PostgreSQL version 7.4 to 8.0.

Options are :

  • Two-phase commit function was added. (Correct)
  • CSV mode was added to the copy command
  • SAVEPOINT function was added.
  • Point-In-Time Recovery function was added.
  • The shared buffer control algorithm was improved

Answer : Two-phase commit function was added.

Based on the relationship of columns within a table, select the most suitable description that

shows that column A is dependent on column B.

Options are :

  • When the value in column B is changed, the corresponding value of column A also must be changed.
  • The value in column B is uniquely determined when a value in column A is selected
  • The value in column A is uniquely determined when a value in column B is selected (Correct)
  • As long as column B exists, the amount of information will not decrease even if column A is deleted.
  • When the value in column A is changed, the corresponding value in column B also must be changed.

Answer : The value in column A is uniquely determined when a value in column B is selected

Select one incorrect statement about the command shown below. Note: $ is the command prompt.

$ dropuser -U admin foo

Options are :

  • The same process can be performed using the SQL command "DROP USER".
  • If there is a database owned by foo, an error will occur.
  • The user admin is removing the user foo.
  • If admin doesn't have the superuser privilege, an error will occur.
  • If admin is not the owner of foo, an error will occur. (Correct)

Answer : If admin is not the owner of foo, an error will occur.

Select two incorrect statements concerning the system catalog

A. It stores the object definition information for tables and columns.

B. There may be changes to specification by major version upgrades.

C. It is accessible as a table; however, it is output in binary format so it can not be read as is.

D. It stores the internal information of the database management system.

E. It is defined based on the standard SQL specification

Options are :

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

Answer : c,e

Select one false statement about the benefits of using database management systems from

below.

Options are :

  • You can separate the data storage method from the application
  • You can separate the data search method from the application.
  • None of the above
  • You can share data more easily on systems consisting of multiple computers.
  • You can separate the data display method from the application (Correct)
  • You can reduce the programming workload of programming for managing data.

Answer : You can separate the data display method from the application

Select two incorrect statements related to the command below. Note: $ is the command prompt.

$ psql -U foo -c "COPY company TO stdout;" bar

A. If the company table is not readable, an error occurs.

B. The content of the company table is written into a file called 'stdout'.

C. The content of the company table is output in TAB delimited format.

D. An error occurs unless the user foo has administrator privileges.

E. Connects to the database bar.

Options are :

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

Answer : b,d

I would like to check the privileges on the "items" table in psql. Select the most appropriate

command.

Options are :

  • \d items
  • \a items
  • \t items
  • \p items
  • \z items (Correct)

Answer : \z items

The following question concerns the use of multibyte characters in PostgreSQL.

Select two correct items about character encoding in PostgreSQL.

A. "./configure --enable-multibyte" must be designated at time of build.

B. When the database cluster is initialized, the or --multibyte option must be specified.

C. Character encoding can be set on a per database basis.

D. Only a single character encoding can be specified for each database cluster.

E. Different character encodings can be specified for server and clients.

Options are :

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

Answer : c,e

I would like to restore the database cluster from the "db1.dump" backup file.

Select the correct command from below. (Note: "postgres" is the superuser)

Options are :

  • pg_restore -U postgres -f db1.dump db1
  • psql -U postgres -f db1.dump db1 (Correct)
  • pg_dump --restore db1 < db1.dump
  • pg_resetxlog -U postgres db1 < db1.dump
  • pg_dump -U postgres --restore db1 < db1.dump

Answer : psql -U postgres -f db1.dump db1

Select the most appropriate statement about the initdb command.

Options are :

  • The directory designated by the environment variable PGDATA must exist
  • It can not be executed by an OS administrator-level user (root user). (Correct)
  • When "auto" is designated as the value for the environment variable PGDATA, a directory is automatically set up.
  • It can only be executed by the user who installed PostgreSQL.
  • Unless the environment variable PGDATA is set, it can not be executed

Answer : It can not be executed by an OS administrator-level user (root user).

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

below.

Note: $ is the command prompt.

$ pg_dump postgres > pgsql

Options are :

  • Backs up the database postgres and writes an error message to the file pgsql.
  • Outputs all of the content of the database postgres to the screen using the user pgsql.
  • Writes a backup of the database postgres to the file pgsql. (Correct)
  • Writes a backup of the entire database cluster using user postgres to the file pgsql.
  • Writes a backup of the entire database cluster to the file postgres and writes an error message to the file pgsql.

Answer : Writes a backup of the database postgres to the file pgsql.

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

Options are :

  • SHOW version
  • SELECT version;c
  • \server_version
  • SELECT version(); (Correct)
  • SHOW server;

Answer : SELECT version();

I want to restore data from a text format backup file foo.dump. Select an appropriate command.

Options are :

  • psql -f foo.dump foo (Correct)
  • pg_dump -R foo < foo.dump
  • pg_restore -d foo foo.dump
  • pg_restore -f foo.dump foo
  • createdb foo < foo.dump

Answer : psql -f foo.dump foo

What phenomenon occurs if PostgreSQL is used without performing VACUUM ?

Select two appropriate descriptions from those below.

A. Performance is reduced.

B. It gradually gets to the point where connections are denied.

C. The physical size of the database increases considerably in size.

D. Only SELECT queries will be accepted.

E. An e-mail prompting the administrator to perform VACUUM is sent from PostgreSQL

Options are :

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

Answer : a,c

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions