QUESTION
NO: 1
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.
Answer:
A,E
QUESTION
NO: 2
Select two transaction
isolation levels supported in PostgreSQL.
A. DIRTY READ
B. READ COMMITTED
C. REPEATABLE READ
D. PHANTOM READ
E. SERIALIZABLE
Answer:
B,E
QUESTION
NO: 3
PostgreSQL can use an
index to access a table. Select two incorrect statements about indexes.
A. An index is created
by 'CREATE INDEX', and deleted by 'DROP INDEX'.
B. By using an index
effectively, searching and sorting performs faster.
C. There are B-tree,
Hash, R-tree and GiST index types.
D. By creating an
index,performance always improves.
E. Creating an unused
index does not affect the performance of a database at all.
Answer:
D,E
QUESTION
NO: 4
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.
Answer: B,E
QUESTION NO: 5
Select two suitable statements
regarding the data types of PostgreSQL.
A. One field can handle up to 1GB
of data.
B. 'n'in CHARACTER(n) represents
the number of bytes.
C. Only the INTEGER type can be
declared as an array.
D. There is a non-standard
PostgreSQL data type, called Geometric data type, which handles 2-
dimensional data.
E. A large object data type can be
used to store data of unlimited size.
Answer: A,D
QUESTION NO: 6
The table "score" is
defined as follows:
gid | score
-----+-------
1 | 70
1 | 60
2 | 100
3 | 80
3 | 50
The following query was executed.
Select the number of rows in the result. SELECT gid,
max(score) FROM score
GROUP BY gid HAVING max(score) >
60;
A. 1 row
B. 2 rows
C. 3 rows
D. 4 rows
E. 5 rows
Answer: C
QUESTION NO: 7
Table "t1" is
defined as follows:
CREATE TABLE t1 (value
VARCHAR(5));
A set of SQL statements
were executed in the following order.Select thenumber of rows that
table "t1"
has after execution.
BEGIN;
INSERT INTO t1 VALUES
('AA');
SAVEPOINT point1;
INSERT INTO t1 VALUES
('BB');
SAVEPOINT point2;
INSERT INTO t1 VALUES
('CC');
ROLLBACK TO point1;
INSERT INTO t1 VALUES
('DD');
END;
A. 1 row
B. 2 rows
C. 3 rows
D. 4 rows
E. 0 rows
Answer:
B
QUESTION
NO: 8
Select two suitable
statements about sequences.
A. A sequence always
returns a 4-byte INTEGER type value, so the maximum value is
2147483647.
B. A sequence is
defined by 'CREATE SEQUENCE', and deleted by 'DROP SEQUENCE'.
C. Although the
"nextval" function is called during a transaction, it will have no
effect if that
transaction is rolled
back.
D. A sequence always
generates 0 or consecutive positive numbers.
E. A sequence number
can be set by calling the "setval" function.
Answer:
B,E
QUESTION
NO: 9
The "sample"
table consists of the following data:
How many rows are
returned by executing the following SQL statement? SELECT DISTINCT ON
(data) * FROM sample;
A. 2 rows
B. 3 rows
C. 4 rows D. 5 rows
E. 6 rows
Answer:
B
QUESTION
NO: 10
The following SQL statements
were executed using psql.
Select the appropriate
statement about the result.
LISTEN sign_v;
BEGIN;
NOTIFY sign_v;
COMMIT;
LISTEN sign_v;
A. At the point that
'NOTIFY sign_v' is executed, a message that starts with "Asynchronous
notification 'sign_v'
received" is output.
B. At the point that
'COMMIT' is executed, a message that starts with "Asynchronous
notification
'sign_v' received"
is output.
C. At the point that
'SELECT * FROM pg_user;" is executed, a message that starts with
"Asynchronous notification
'sign_v' received" is output.
D. When 'LISTEN sign_v'
is executed for the second time, a message that starts with
"Asynchronous
notification 'sign_v' received" is output.
E. The message
"Asynchronous notification 'sign_v' received" is not received while
in this
connection.
Answer:
B
QUESTION
NO: 11
Select the correct SQL
statement which concatenates strings 'ABC' and 'abc' to form 'ABCabc'.
A. SELECT'ABC' . 'abc';
B. SELECTcat('ABC',
'abc') FROM pg_operator;
C. SELECT 'ABC' +
'abc';
D. SELECT 'ABC' + 'abc'
FROM pg_operator;
E. SELECT 'ABC' ||
'abc';
Answer:
E
QUESTION
NO: 12
Select two correct
descriptions about views.
A. A view is created by
'DECLARE VIEW', and deleted by 'DROP VIEW'.
B. A view is a virtual
table which does not exist.
C. A view is created to
simplify complicated queries.
D. You can create a
view with the same name as already existing tables.
E. A view only exists
while the postmaster is running, and is deleted when the postmaster stops.
Answer:
B,C
QUESTION
NO: 13
Table "t1" is
defined below.
Table "t1"
has a column "id" of type INTEGER, and a column "name" of
type TEXT.
t1:
The following SQL is
executed while client "A" is connected. BEGIN;
SELECT * FROM t1 WHERE
id = 2 FOR UPDATE;
SELECT * FROM t1 WHERE
id = 1 FOR UPDATE; -- (*)
While the second
'SELECT' statement, shown with (*), is being executed, a separate client
"B"
connects and executes
the following SQL.
Select the correct
statement about the execution results.
UPDATE t1 SET name =
'turtle' WHERE id = 2;
Note: the default transaction
isolation level is set to "read committed".
A. The update process
for client "B" is blocked until the current connection for client
"A" is
finished.
B. The update process
for client "B" is blocked until the current transaction for client
"A" is
finished.
C. The 'UPDATE' process
for client "B" proceeds regardless of the condition of client
"A".
D. The process of
client "B" immediately generates an error.
E. The processes for
both clients are blocked, and an error stating that a deadlock has been
detected is generated.
Answer:
B
QUESTION
NO: 14
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 two SQL
statements that will generate an error when executed next.
A. INSERT INTOftrans
VALUES (1, 1, CURRENT_DATE);
B. INSERT INTOftrans
VALUES (2, 1, '2007-07-07');
C. UPDATEfmaster SET
name = 'itemAX' WHERE id = 1;
D. UPDATEfmaster SET id
= 100 WHERE id = 1;
E. UPDATEftrans SET id
= 200 WHERE id = 1;
Answer:
A,C
QUESTION
NO: 15
Select three SQL
statements which return NULL.
A. SELECT 0 = NULL;
B. SELECTNULL != NULL;
C. SELECT NULL IS NULL;
D. SELECT NULL;
E. SELECT 'null'::TEXT;
Answer:
A,B,D
QUESTION
NO: 16
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 | aaa@example.com | 1
3 | bbb@example.com | 2
4 | ccc@example.com | 2
Three SQL statements
were executed in the following order: INSERT INTO custom
SELECT max(id) + 1,
'ddd@example.com', 4 FROM custom;
UPDATE custom SET introducer
= 999
WHERE email =
'bbb@example.com';
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
Answer:
C
QUESTION
NO: 17
The "sample"
table consists of the following data:
How many rows are
returned by executing the following SQL statement? SELECT * FROM
sample WHERE v ~ 'ab';
A. 0 rows
B. 1 row
C. 2 rows
D. 3 rows
E. 4 rows
Answer:
C
QUESTION
NO: 18
Select an incorrect statement
regarding the following SQL statement. Note that "user_view" is a
view.
CREATE OR REPLACE RULE
rule_1 AS ON UPDATE TO user_view DO INSTEAD NOTHING;
A. It is defining a
rule "rule_1".
B. It will replace
"rule_1" if it already exists.
C. Executing 'UPDATE
user_view' will no longer output errors.
D. When executing
'UPDATE user_view', data is updated in the table that is the origin of the
view.
E. 'DROP RULE rule_1 ON
user_view' deletes the above definition.
Answer:
D
QUESTION
NO: 19
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.
Answer:
A
QUESTION
NO: 20
Four SQL statements
were executed in the following order. CREATE TABLE foo (bar INT);
ALTER TABLE foo ALTER
bar TYPE BIGINT;
ALTER TABLE foo ADD baz
VARCHAR(5);
ALTER TABLE foo DROP
bar;
Select two SQL
statements that generate an error when executed.
A. INSERT INTOfoo
VALUES ('12345');
B. INSERT INTOfoo
VALUES ('5000000000');
C. INSERT INTOfoo
VALUES ('ABC');
D. INSERT INTOfoo
VALUES (2000000000);
E. INSERT INTOfoo
VALUES (NULL);
Answer:
B,D
QUESTION
NO: 21
A table named
"sample" is defined as below. Select two statements which will generate
a
constraint error.
CREATE TABLE sample (i
INTEGER PRIMARY KEY,j INTEGER,
CHECK ( i > 0 AND j
< 0 ));
A. INSERT INTO sample
VALUES (1, 0);
B. INSERT INTO sample
VALUES (2, -2);
C. INSERT INTO sample
VALUES (3, NULL);
D. INSERT INTO sample
VALUES (NULL, -4);
E. INSERT INTO sample
VALUES (5, -5);
Answer:
A,D
QUESTION
NO: 22
The following is the
result of executing the createlang command which is installed with
PostgreSQL.
$ createlang -U
postgres --list mydb
Procedural Languages
Name | Trusted?
---------+----------
plpgsql | yes
Select two correct
statements from below.
A. The procedural
languageplpgsql is installed in the database mydb using the above command.
B. The procedural
languageplpgsql can be used in the database mydb.
C. plpgsql is a trusted
language, so it can execute the OS commands on the server side.
D. plpgsql is a trusted
language, so it can read/write OS files on the server side.
E. plpgsql is a safe
language with restricted operations.
Answer:
B,E
QUESTION
NO: 23
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);
A. SELECT id AS data,
data FROM sample1;
B. SELECT id, id FROM
sample1;
C. SELECT s1.id,
s2.idFROM sample1 AS s1, sample1 AS s2;
D. SELECT s1.id,
s2.idFROM sample1 s1, sample2 s2;
E. SELECT s1.id,
s2.data FROM sample1 s1, sample2 s2;
Answer:
E
QUESTION
NO: 24
Select two suitable
statements regarding creating a new table.
A. There is no upper
limit to the number of columns in a table.
B. A newly created
table is empty and has 0 rows.
C. You can only use
alphabetic characters for a table name.
D. The row name must be
within 16 characters.
E. The SQL 'CREATE
TABLE' statement is used to create a new table.
Answer:
B,E
QUESTION
NO: 25
The tables
"t1" and "t2" are defined below.
The tables
"t1" and "t2" have columns "id" which are type of
INTEGER and column "name"s which
are type of TEXT.
t1
t2
The following SQL
command was executed. Select the number of rows in the result. SELECT *
FROM t1 NATURAL FULL
OUTER JOIN t2;
A. 2 rows
B. 3 rows
C. 4 rows
D. 5 rows
E. 6 rows
Answer:
D
QUESTION
NO: 26
Select two suitable
statements about major version upgrades of PostgreSQL from below.
A. You can use the
databases of the old major version.
B. To use the data from
the old version, you only need to replace the program.
C. To use the data from
the old version, you need to conduct a backup and restore.
D. There is a
possibility of configuration parameter changes after major version upgrades.
E. Upgrade scripts can
be executed while the old version ofPostgreSQL is running.
Answer:
C,D
QUESTION
NO: 27
Select one incorrect
statement concerning the relational data model.
A. It expresses the
real world in a collection of 2-dimensional tables called a relation.
B. It is a model based
on set theory.
C. It has a logical
structure independent of physical data structure.
D. It is made up of a
multiple stage hierarchy where each of the set elements has parent child
relationships.
E. It is a model that
was proposed by E.F. Codd in 1970.
Answer:
D
QUESTION
NO: 28
Select two incorrect
statements concerning PostgreSQL license.
A. It can be used
freely.
B. It can be duplicated
freely.
C. It can be freely
redistributed.
D. Developers are
responsible for its maintenance support.
E. Developers are only
responsible for handling its crucial faults.
Answer:
D,E
QUESTION
NO: 29
Select the most
suitable statement about PostgreSQL from below.
A. PostgreSQL is a
card-type database management system.
B. PostgreSQL is a
hierarchical database management system.
C. PostgreSQL is a
network-type database management system.
D. PostgreSQL is an XML
database management system.
E. PostgreSQL is a
relational database management system.
Answer:
E
QUESTION
NO: 30
Select the most
suitable statement about the PostgreSQL license from below.
A. PostgreSQL is
distributed under the GPL license.
B. PostgreSQL is
distributed under the PostgreSQL license.
C. PostgreSQL is
distributed under the LGPL license.
D. PostgreSQL is
distributed under the BSD license.
E. PostgreSQL is
distributed under the X11(MIT) license.
Answer:
D
QUESTION
NO: 31
Select one incorrect
statement regarding psql.
A. psql is an
interactive SQL interpreter that enables a user to enter queries.
B. For system security,
only thePostgreSQL administrator account is allowed to use psql.
C. "psql -l"
displays a database list.
D. "psql -U
jan" will connect to the database called jan.
E. Commands that begin
with a backslash are processed internally inpsql.
Answer:
B
QUESTION
NO: 32
I would like to insert
the contents of the text file users.dat into the table t1 using psql. Contents
of
text file users.dat:
Definition of table t1:
CREATE TABLE t1 (uname
TEXT, pass TEXT, id INTEGER); Select the most appropriate input
from those below.
A. \copy t1 FROM
users.dat WITH DELIMITER ':'
B. \copy t1 TO
users.dat WITH DELIMITER ':'
C. INSERT INTO t1 FROM
file('users.dat');
D. INSERT INTO t1SELECT
uname, pass, id FROM file('users.dat');
E. \insert t1 FROM
users.dat WITH DELIMITER ':';
Answer:
A
QUESTION
NO: 33
Select one correct
statement about the createlang command.
A. It is a command for
setting the locale for a database.
B. It is a command for
registering a procedural language into a database.
C. It is a command for
creating a user defined function.
D. It is a command for
adding message catalogs.
E. It is a command
thathas been discarded for version 8 and later.
Answer:
B
QUESTION
NO: 34
What does the following
command do? Select two correct descriptions regarding this SQL
statement.
SELECT * FROM
information_schema.tables;
A. Requests a list of
defined tables in the currently connected database.
B. Requests a list of
defined tables in the "information_schema" database.
C. Requests the
contents of the "tables" table in the "information_schema"
database.
D. Requests the
contents of the "tables" table in the "information_schema"
schema.
E. Requests a list of
defined tables in the "information_schema" schema.
14
Answer:
A,D
QUESTION
NO: 35
Select two correct
statements that describe what occurs on the client side when the following
command is executed.
pg_ctl -m smart stop
A. Clients will not be
able to make new connections.
B. The running
transaction for a connected client is rolled back and the connection is
disconnected forcibly.
C. Connections are
terminated after the currently running transactions have finished.
D. The processes of
currently connected clients are processed normally.
E. Connected clients
receive a message saying that the server will soon be shutdown.
Answer:
A,D
QUESTION
NO: 36
Select two correct
statements about the command shown below. Note: $ is the command prompt.
$ pg_ctl reload
A. The command forces
the content of pg_hba.conf to be re-read into PostgreSQL server
process.
B. The command
temporarily stops the PostgreSQL server process and restart it.
C. The command re-reads
the postgresql.conf details into the PostgreSQL server process and
changes the values of
any configuration parameters that can be changed.
D. The command forces
the content of the database cluster to be re-read into PostgreSQL
server process.
E. The command causes a
recovery to be performed from a standard backup file in the
PostgreSQLserver
process.
Answer:
A,C
QUESTION
NO: 37
PostgreSQL can be used from just about any major programming
language, including C, C++, Perl, Python, Java, Tcl, and PHP.
A. True
B. FALSE
Answer: A
A. True
B. FALSE
Answer: A
QUESTION
NO: 38
A meta-command always begins with what?
A. Forwardslash character (/)
B. Dollar sign ($)
C. Backslash character (\)
D. Question mark (?)
Answer: C
A. Forwardslash character (/)
B. Dollar sign ($)
C. Backslash character (\)
D. Question mark (?)
Answer: C
QUESTION NO: 39
When you want to use a join between columns that are in the same
table, you use what type of join?
A. union
B. right outer
C. left outer
D. self
Answer: D
A. union
B. right outer
C. left outer
D. self
Answer: D
QUESTION NO: 40
True or False? VACUUM FULL shrinks indexes, optimizing database
performance.
A. True – This was addressed in version 9.0
B. False – This was addressed in version 9.0
Answer: A
A. True – This was addressed in version 9.0
B. False – This was addressed in version 9.0
Answer: A
QUESTION NO: 41
True or False? When using a SELECT statement on a table, or group
of tables, those resources are locked exclusively.
A. False
B. True
Answer: A
A. False
B. True
Answer: A
QUESTION NO: 42
What do you call the application that makes requests of the
PostgreSQL server?
A. Workstation
B. Client
C. Thin Client
D. Interface
Answer: B
A. Workstation
B. Client
C. Thin Client
D. Interface
Answer: B
QUESTION NO: 43
Which of the following best describes a role:
A. A server’s purpose within a cluster.
B. The purpose of a particular database.
C. A cluster’s purpose.
D. A template for authorization to various database objects.
Answer: D
A. A server’s purpose within a cluster.
B. The purpose of a particular database.
C. A cluster’s purpose.
D. A template for authorization to various database objects.
Answer: D
QUESTION NO: 44
The most common method to get data into a table is to use what
command?
A. Insert
B. Write
C. Execute
D. Send
Answer: A
A. Insert
B. Write
C. Execute
D. Send
Answer: A
QUESTION NO: 45
The basic psql command to list tables is?
A. \do
B. \dT
C. \h
D. \dt
Answer: D
A. \do
B. \dT
C. \h
D. \dt
Answer: D
QUESTION NO: 46
In PostgreSQL, a named collection of tables is called what?
A. Trigger
B. View
C. Diagram
D. Schema
Answer: D
A. Trigger
B. View
C. Diagram
D. Schema
Answer: D
QUESTION NO: 47
The heart of SQL is the __________ statement.
A. GROUP BY
B. INSERT INTO
C. SELECT
D. VALUES
Answer: C
A. GROUP BY
B. INSERT INTO
C. SELECT
D. VALUES
Answer: C
QUESTION NO: 48
PostgreSQL is
A. a relational database management system.
B. a hierarchical database management system.
C. a network-type database management system.
D. an XML database management system.
Answer: A
A. a relational database management system.
B. a hierarchical database management system.
C. a network-type database management system.
D. an XML database management system.
Answer: A
QUESTION NO: 49
PostgreSQL is:
A. An open-source SMTP server.
B. A NoSQL solution.
C. Enterprise-class proprietary software developed at Bell Labs, with a basic set of features.
D. An open-source ORDBMS developed at UC Berkley, which supports many modern features.
Answer: D
A. An open-source SMTP server.
B. A NoSQL solution.
C. Enterprise-class proprietary software developed at Bell Labs, with a basic set of features.
D. An open-source ORDBMS developed at UC Berkley, which supports many modern features.
Answer: D
QUESTION NO: 50
When retrieving data in a particular table, we use
the_____________ statement.
A. \dt
B. ORDER BY
C. SELECT FROM
D. \i
Answer: C
A. \dt
B. ORDER BY
C. SELECT FROM
D. \i
Answer: C
QUESTION NO: 51
We add data to PostgreSQL by using which statement?
A. INSERT
B. ADD
C. UPDATE
D. SELECT
Answer: A
A. INSERT
B. ADD
C. UPDATE
D. SELECT
Answer: A
QUESTION NO: 52
PostgreSQL used what model of communication?
A. Client/Server
B. Network
C. Peer-to-Peer
D. Push Model
Answer: A
A. Client/Server
B. Network
C. Peer-to-Peer
D. Push Model
Answer: A
QUESTION NO: 53
With PostgreSQL, you can access data by
A. Use function calls (APIs) to prepare and execute SQL statements, scan result sets, and perform updates from a large variety of different programming languages.
B. Use a command-line application to execute SQL statements
C. All of these
D. Embed SQL directly into your application
Answer: C
A. Use function calls (APIs) to prepare and execute SQL statements, scan result sets, and perform updates from a large variety of different programming languages.
B. Use a command-line application to execute SQL statements
C. All of these
D. Embed SQL directly into your application
Answer: C
QUESTION NO: 54
PostgreSQL has many modern features including:
A. Complex SQL queries
B. SQL Sub-selects
C. All of the above
D. Views
Answer: C
A. Complex SQL queries
B. SQL Sub-selects
C. All of the above
D. Views
Answer: C
QUESTION NO: 55
The SQL condition for pattern matching is?
A. IN
B. LIKE
C. DISTINCT
D. BETWEEN
Answer: B
A. IN
B. LIKE
C. DISTINCT
D. BETWEEN
Answer: B
QUESTION NO: 56
What is the wrapper around the SQL command CREATE DATABASE?
A. newdb
B. add_DB
C. NEW_DB
D. createdb
Answer: D
A. newdb
B. add_DB
C. NEW_DB
D. createdb
Answer: D
QUESTION NO: 57
PostgreSQL runs on:
A. all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.
B. on all UNIX versions except Solaris.
C. Linux and Windows only.
D. Windows only
Answer: A
A. all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.
B. on all UNIX versions except Solaris.
C. Linux and Windows only.
D. Windows only
Answer: A
QUESTION NO: 58
What command tells PostgreSQL that all of the changes you made to
a database should become permanent?
A. Apply
B. Execute
C. Commit
D. Send
Answer: C
A. Apply
B. Execute
C. Commit
D. Send
Answer: C
QUESTION NO: 59
PostgreSQL can be installed?
A. on a Windows computer via the PostgreSQL installer
B. from Linux binaries
C. All of these
D. from the source code
Answer: C
A. on a Windows computer via the PostgreSQL installer
B. from Linux binaries
C. All of these
D. from the source code
Answer: C
QUESTION NO: 60
____________ allow us to define formally in the database how
different tables relate to each other.
A. Views
B. temporary tables
C. Foreign Key Constraints
D. table management
Answer: C
A. Views
B. temporary tables
C. Foreign Key Constraints
D. table management
Answer: C
QUESTION NO: 61
Triggers can be configured to execute when which of the following
operations are performed:
A. All of the above
B. INSERT statements
C. UPDATE statements
D. DELETE statements
Answer: A
A. All of the above
B. INSERT statements
C. UPDATE statements
D. DELETE statements
Answer: A
QUESTION NO: 62
WAL stands for:
A. Write Ahead Log
B. Write Ahead List
C. Write Anywhere List
D. Write Anywhere Logging
Answer: A
A. Write Ahead Log
B. Write Ahead List
C. Write Anywhere List
D. Write Anywhere Logging
Answer: A
QUESTION NO: 63
Deadlocks occur when:
A. Two users are connected to the same database at the same time.
B. Two transactions hold exclusive locks on resources they both need.
C. Two users try to access the same table at the same time.
D. Data is being written to the same cell by separate processes at the same time.
Answer: B
A. Two users are connected to the same database at the same time.
B. Two transactions hold exclusive locks on resources they both need.
C. Two users try to access the same table at the same time.
D. Data is being written to the same cell by separate processes at the same time.
Answer: B
QUESTION NO: 64
Tablespaces:
A. Allow an administrator to designate specific locations in a file system where database objects can be stored.
B. Are a fancy name for table.
C. Are the amount of disk space a table is using.
D. Are the width, or number of columns, of a particular table.
Answer: A
A. Allow an administrator to designate specific locations in a file system where database objects can be stored.
B. Are a fancy name for table.
C. Are the amount of disk space a table is using.
D. Are the width, or number of columns, of a particular table.
Answer: A
QUESTION NO: 65
If you don’t specify ASC or DESC, PostgreSQL will assume you want
to see results:
A. in ascending order
B. grouped together by field type
C. in a random order
D. in descending order
Answer: A
A. in ascending order
B. grouped together by field type
C. in a random order
D. in descending order
Answer: A
QUESTION NO: 66
True or false: With table inheritance, not null and check
constraints are inherited.
A. False
B. True
Answer: B
A. False
B. True
Answer: B
QUESTION NO: 67
True or False: A tablespace is the place where database objects
are stored on disk.
A. False
B. True
Answer: B
A. False
B. True
Answer: B
QUESTION NO: 68
True or False? ALTER TABLE may be issued while a VACUUM process is
running.
A. False
B. True
Answer: A
A. False
B. True
Answer: A
QUESTION NO: 69
True or False? PostgreSQL is so lightweight that performance and
reliability are not affected by hardware.
A. False
B. True
Answer: A
A. False
B. True
Answer: A
QUESTION NO: 70
A VACUUM operation is used:
A. To recover or reuse disk space occupied by updated or deleted rows.
B. To protect against loss of very old data due to transaction ID wraparound.
C. To update data statistics used by the PostgreSQL query planner.
D. All of these
Answer: D
A. To recover or reuse disk space occupied by updated or deleted rows.
B. To protect against loss of very old data due to transaction ID wraparound.
C. To update data statistics used by the PostgreSQL query planner.
D. All of these
Answer: D
QUESTION NO: 71
True or False? Within a table, a single column may be encrypted.
A. True
B. False
Answer: A
A. True
B. False
Answer: A
QUESTION NO: 72
The core PostgreSQL source code includes what interfaces?
A. Ruby and PHP interfaces
B. C++ and Java interfaces
C. The C and embedded C interfaces
D. VB and VB.NET interfaces
Answer: C
A. Ruby and PHP interfaces
B. C++ and Java interfaces
C. The C and embedded C interfaces
D. VB and VB.NET interfaces
Answer: C
QUESTION NO: 73
Unless you specify NOT NULL, PostgreSQL will assume that a column
is:
A. Required
B. Optional
C. Integers
D. Text
Answer: B
A. Required
B. Optional
C. Integers
D. Text
Answer: B
QUESTION NO: 74
Query trees can be viewed in the server logs as long as which of
the following configuration parameters are enabled?
A. debug_print_parse
B. debug_print_plan
C. All of these
D. debug_print_rewritten
Answer: C
A. debug_print_parse
B. debug_print_plan
C. All of these
D. debug_print_rewritten
Answer: C
QUESTION NO: 75
To describe a table in PostgreSQL which of the following is
correct:
A. psql> \D table_name
B. psql> describe table_name
C. psql> DESCRIBE table_name
D. psql> \d table_name
Answer: D
A. psql> \D table_name
B. psql> describe table_name
C. psql> DESCRIBE table_name
D. psql> \d table_name
Answer: D
QUESTION NO: 76
The value NULL, in database terminology, means?
A. All of these
B. The value is undetermined at this time
C. The value is undetermined
D. The value is not relevant for this particular row.
Answer: A
A. All of these
B. The value is undetermined at this time
C. The value is undetermined
D. The value is not relevant for this particular row.
Answer: A
QUESTION NO: 77
To create a database in PostgreSQL, you must have the special
CREATEDB privilege or
A. be an admin
B. have a script do it.
C. be a superuser.
D. the special CREATETBL privilege
Answer: C
A. be an admin
B. have a script do it.
C. be a superuser.
D. the special CREATETBL privilege
Answer: C
QUESTION NO: 78
What does the following statement do? CREATE INDEX lower_title_idx
ON books ((lower(title)));
A. Modifies an index in place to be lowercase
B. Creates a new index with a special operator class ‘lower’ for case insensitive comparisons.
C. Creates an index for efficient case-insensitive searches on the titles column within the books table
D. Nothing, it’s invalid SQL
E. Creates a non-write-locking index
Answer: C
A. Modifies an index in place to be lowercase
B. Creates a new index with a special operator class ‘lower’ for case insensitive comparisons.
C. Creates an index for efficient case-insensitive searches on the titles column within the books table
D. Nothing, it’s invalid SQL
E. Creates a non-write-locking index
Answer: C
QUESTION NO: 79
What command allows you to edit PostgreSQL queries in your favorite
editor ?
A. \ed
B. edit sql #go
C. \e
D. \edit
Answer: C
A. \ed
B. edit sql #go
C. \e
D. \edit
Answer: C
QUESTION NO: 80
To prevent transaction wraparound, a VACUUM operation should be
run on every table no less than once every:
A. 2 billion transactions
B. 5 billion transactions
C. 4 billion transactions
D. 3 billion transactions
Answer: A
A. 2 billion transactions
B. 5 billion transactions
C. 4 billion transactions
D. 3 billion transactions
Answer: A
QUESTION NO: 81
The rule system:
A. Operates between the parser and the planner.
B. None of these
C. Takes the output of the parser, one query tree, and the user-defined rewrite rules, which are also query trees with some extra information, and creates zero or more query trees as result.
D. All of these
Answer: D
A. Operates between the parser and the planner.
B. None of these
C. Takes the output of the parser, one query tree, and the user-defined rewrite rules, which are also query trees with some extra information, and creates zero or more query trees as result.
D. All of these
Answer: D
QUESTION NO: 82
Asynchronous Commits:
A. None of these
B. All of these
C. Allow transactions to complete more quickly
D. May cause recent transactions to be lost
Answer: B
A. None of these
B. All of these
C. Allow transactions to complete more quickly
D. May cause recent transactions to be lost
Answer: B
QUESTION NO: 83
When looking at ‘ps’ output on a unix system, you see the
following: postgres 1016 0.1 2.4 6532 3080 pts/1 SN 13:19 0:00 postgres: tgl
regression [local] idle in transaction What does “idle in transaction” mean?
A. The server is idle, awaiting a client connection.
B. A transaction is possibly hung.
C. A user issued an IDLE statement to the database.
D. A client is connected, and the server is awaiting input.
Answer: D
A. The server is idle, awaiting a client connection.
B. A transaction is possibly hung.
C. A user issued an IDLE statement to the database.
D. A client is connected, and the server is awaiting input.
Answer: D
QUESTION NO: 84
True or False? Only the administrator can make use of tablespaces.
A. True
B. False
Answer: B
A. True
B. False
Answer: B
QUESTION NO: 85
This is used to determine how text is stored and sorted within
PostgreSQL?
A. Collations
B. Index
C. Data Types
D. Database
Answer: A
A. Collations
B. Index
C. Data Types
D. Database
Answer: A
QUESTION NO: 86
Which statement is true about PostgreSQL data types?
A. A large object data type can be used to store data of unlimited size.
B. Only the INTEGER type can be declared as an array.
C. n’in CHARACTER(n) represents the number of bytes.
D. There is a non-standard PostgreSQL data type, called Geometric data type, which handles 2-dimensional data.
Answer: D
A. A large object data type can be used to store data of unlimited size.
B. Only the INTEGER type can be declared as an array.
C. n’in CHARACTER(n) represents the number of bytes.
D. There is a non-standard PostgreSQL data type, called Geometric data type, which handles 2-dimensional data.
Answer: D
QUESTION NO: 87
What command turns on timing?
A. \on timing
B. \start time
C. \time start
D. \timing
Answer: D
A. \on timing
B. \start time
C. \time start
D. \timing
Answer: D
QUESTION NO: 88
Advisory locks are allocated out of a shared memory pool whose
size is defined by the configuration variables….
A. All of these
B. None of these
C. max_connections
D. max_locks_per_transaction
Answer: A
A. All of these
B. None of these
C. max_connections
D. max_locks_per_transaction
Answer: A
QUESTION NO: 89
To restore a PostgreSQL backup created with pg_dump, the following
may be used:
A. $ psql -F database_dump.psql database_name
B. $ psql -R database_dump.psql database_name
C. $ psql -f database_dump.psql database_name
D. $ psql -r database_dump.psql database_name
Answer: C
A. $ psql -F database_dump.psql database_name
B. $ psql -R database_dump.psql database_name
C. $ psql -f database_dump.psql database_name
D. $ psql -r database_dump.psql database_name
Answer: C
QUESTION NO: 90
In order to echo all input from script, you use the ________ psql
command.
A. -a
B. -A
C. -l
D. -E
Answer: A
A. -a
B. -A
C. -l
D. -E
Answer: A
QUESTION NO: 91
True or False? Dynamic Tracing is enabled by default at compile
time.
A. False
B. True
Answer: A
A. False
B. True
Answer: A
QUESTION NO: 92
Bob works for StegaCorp. His workstation’s IP address is
10.5.34.8. He needs access to a database called “partners” directly from his
workstation. Which of the following is the correct entry in pg_hba.conf?
A. host partners bob 10.5.34.8/24 krb5
B. host partners bob 10.5.34.8/32 kerb5
C. host partners bob 10.5.34.8 kerb5
D. host partners bob 10.5.34.8/32 krb5
Answer: D
A. host partners bob 10.5.34.8/24 krb5
B. host partners bob 10.5.34.8/32 kerb5
C. host partners bob 10.5.34.8 kerb5
D. host partners bob 10.5.34.8/32 krb5
Answer: D
QUESTION NO: 93
What does MCV stand for?
A. Massive Checkpoint Vault
B. Most Common Values
C. Most Common Variables
D. Many Common Variables
Answer: B
A. Massive Checkpoint Vault
B. Most Common Values
C. Most Common Variables
D. Many Common Variables
Answer: B
QUESTION NO: 94
By default, in what subdirectory of the database data directory
are WAL logs contained?
A. pg-xlog
B. pg-log
C. pg_log
D. pg_xlog
Answer: D
A. pg-xlog
B. pg-log
C. pg_log
D. pg_xlog
Answer: D
QUESTION NO: 95
Which is NOT true of array indexes?
A. Partial functional indexes of arrays are supported on Btree, GiST, and GIN indexes.
B. By default PostgreSQL indexes arrays so that inner elements can be searched.
C. GIN indexes can search for arrays containing specific elements.
D. BTree indexes can search for array elements as long as the match begins with the beginning of the array.
E. GiST indexes can be used to select arrays for specific values.
Answer: B
A. Partial functional indexes of arrays are supported on Btree, GiST, and GIN indexes.
B. By default PostgreSQL indexes arrays so that inner elements can be searched.
C. GIN indexes can search for arrays containing specific elements.
D. BTree indexes can search for array elements as long as the match begins with the beginning of the array.
E. GiST indexes can be used to select arrays for specific values.
Answer: B
QUESTION NO: 96
True or False? PostgreSQL supports Index Only Scans.
A. False
B. True
Answer: B
A. False
B. True
Answer: B
QUESTION NO: 97
What is “index bloat”?
A. Index filling up with keys.
B. No-longer-needed keys in an index aren’t reclaimed, therefore increasing space required to store an index, as well as time it takes to scan.
C. Indexing inefficiently, like choosing to index timestamps in a table.
D. Indexing too many tables, resulting in inefficient database performance.
Answer: B
A. Index filling up with keys.
B. No-longer-needed keys in an index aren’t reclaimed, therefore increasing space required to store an index, as well as time it takes to scan.
C. Indexing inefficiently, like choosing to index timestamps in a table.
D. Indexing too many tables, resulting in inefficient database performance.
Answer: B
QUESTION NO: 98
What are the join strategies available to the postgreSQL planner
when a SELECT query contains two or more relations?
A. Nested Loop Join, Merge Join, Hash Join
B. MERGE, JOIN
C. INNER JOIN, OUTER JOIN
D. MERGE, INNER JOIN, OUTER JOIN
Answer: A
A. Nested Loop Join, Merge Join, Hash Join
B. MERGE, JOIN
C. INNER JOIN, OUTER JOIN
D. MERGE, INNER JOIN, OUTER JOIN
Answer: A
QUESTION NO: 99
To copy a database from server1 to server2, you might use which of
the following:
A. pg_mv -h server1 database | pgsql -h server2 database
B. pg_dump -h server1 database > pgsql -h server2 database
C. pg_copy -h server1 database | psql -h server2 database
D. pg_dump -h server1 database | psql -h server2 database
Answer: D
A. pg_mv -h server1 database | pgsql -h server2 database
B. pg_dump -h server1 database > pgsql -h server2 database
C. pg_copy -h server1 database | psql -h server2 database
D. pg_dump -h server1 database | psql -h server2 database
Answer: D
QUESTION NO: 100
Which of the following is not a valid integer array?
A. ‘{}’
B. ‘{1,3,4,5,{6,7}}’
C. ‘{{1,2},{1,3},{1,4},{2,5}}’
D. ‘{1,3,4,5,6,7}’
E. ‘{{1,2,3,4,5,6}}’
Answer: B
A. ‘{}’
B. ‘{1,3,4,5,{6,7}}’
C. ‘{{1,2},{1,3},{1,4},{2,5}}’
D. ‘{1,3,4,5,6,7}’
E. ‘{{1,2,3,4,5,6}}’
Answer: B
QUESTION NO: 101
What is the difference between DO ‘some code…’ and EXECUTE ‘some
code…’ statements?
A. DO lets you execute some plPgSql code without saving it to database and EXECUTE lets you execute DDL or DML only
B. EXECUTE statement executes only prepared code, and DO can execute without PREPARE statement
C. No difference – they both need a preparation for execution
D. DO prepares a statement and EXECUTE executes it
E. No difference – they both can execute a code without preparation
Answer: A
A. DO lets you execute some plPgSql code without saving it to database and EXECUTE lets you execute DDL or DML only
B. EXECUTE statement executes only prepared code, and DO can execute without PREPARE statement
C. No difference – they both need a preparation for execution
D. DO prepares a statement and EXECUTE executes it
E. No difference – they both can execute a code without preparation
Answer: A
QUESTION NO: 102
True or False? To increase server performance, automated
CHECKPOINT operations should be setup in cron or Task Scheduler.
A. True
B. False
Answer: B
A. True
B. False
Answer: B
QUESTION NO: 103
The syntax to view the indexes of an existing postgreSQL table is:
A. # index database_name.table_name
B. # \d table_name
C. # \i table_name
D. # index table_name
Answer: B
A. # index database_name.table_name
B. # \d table_name
C. # \i table_name
D. # index table_name
Answer: B
QUESTION NO: 104
Locks are recorded in:
A. pg_lock system logs
B. pg_locks system view
C. pg_locks system logs
D. pg_lock system table
Answer: B
A. pg_lock system logs
B. pg_locks system view
C. pg_locks system logs
D. pg_lock system table
Answer: B
QUESTION NO: 105
True or false? Hash indexes are not crash-safe
A. True
B. False
Answer: B
A. True
B. False
Answer: B
QUESTION NO: 106
True or False? When restoring a database backed up with pg_dump,
it’s generally a good idea to enable WAL.
A. True
B. False
Answer: B
A. True
B. False
Answer: B
QUESTION NO: 107
True or false: When a table is created which uses a table name as
a column type, not null constraints on the column type’s table definition are
honored by the including table.
A. False
B. True
Answer: A
A. False
B. True
Answer: A
QUESTION NO: 108
Which statement is not true about a PostgreSQL domain?
A. A domain is created by ‘CREATE DOMAIN’.
B. A domain can be used as a column type when defining a table.
C. Domain is a namespace existing between databases and objects such as tables.
D. When defining a domain, you can add a default value and constraints to the original data.
Answer: C
A. A domain is created by ‘CREATE DOMAIN’.
B. A domain can be used as a column type when defining a table.
C. Domain is a namespace existing between databases and objects such as tables.
D. When defining a domain, you can add a default value and constraints to the original data.
Answer: C
QUESTION NO: 109
The extension used for data encryption/decryption within
PostgreSQL is:
A. crypto
B. pgcrypt
C. pgcrypto
D. pgencrypt
Answer: C
A. crypto
B. pgcrypt
C. pgcrypto
D. pgencrypt
Answer: C
QUESTION NO: 110
Which of the following is NOT a feature of user defined functions?
A. They can be written in various different languages
B. Functions marked IMMUTABLE can have their output indexed
C. They can return multiple result sets via refcursors
D. They can perform most database management tasks
E. They can initiate subtransactions
Answer: E
A. They can be written in various different languages
B. Functions marked IMMUTABLE can have their output indexed
C. They can return multiple result sets via refcursors
D. They can perform most database management tasks
E. They can initiate subtransactions
Answer: E
QUESTION NO: 111
To create a database that supports UTF-8, the following command
can be used:
A. createdb -E UTF-8 -O user database_name
B. createdb -E UTF8 -O user database_name
C. createdb -C UTF8 database_name
D. createdb -C UTF8 -O user database_name
Answer: B
A. createdb -E UTF-8 -O user database_name
B. createdb -E UTF8 -O user database_name
C. createdb -C UTF8 database_name
D. createdb -C UTF8 -O user database_name
Answer: B
QUESTION NO: 112
What is a TOAST file?
A. A list of clients not allowed to connect to the database.
B. A file storing data that was unable to be written to the database, and will be expunged once the server shuts down.
C. A file containing values too wide to fit comfortably in the main table
D. A file containining transactions which were unsuccessfully completed due to errors.
Answer: C
A. A list of clients not allowed to connect to the database.
B. A file storing data that was unable to be written to the database, and will be expunged once the server shuts down.
C. A file containing values too wide to fit comfortably in the main table
D. A file containining transactions which were unsuccessfully completed due to errors.
Answer: C
QUESTION NO: 113
True or False: PostgreSQL allows you to implement table
inheritance. This should be defined with the special keyword INHERITIS in the
table design.
A. False
B. True
Answer: A
A. False
B. True
Answer: A
QUESTION NO: 114
WAL segment size is determined:
A. By the configure script at compile time
B. By the wal_segment_size configuration parameter in postgresql.conf
C. By the administrator at runtime, or through the startup script.
D. It is statically set within the source code.
Answer: A
A. By the configure script at compile time
B. By the wal_segment_size configuration parameter in postgresql.conf
C. By the administrator at runtime, or through the startup script.
D. It is statically set within the source code.
Answer: A
QUESTION NO: 115
The __________ database model has the advantage of being able to
quickly discover all of the records of one type that are related to a specific
record of another type by following the pointers from the starting record.
A. relational
B. network
C. hierarchical
D. structured
Answer: B
A. relational
B. network
C. hierarchical
D. structured
Answer: B
QUESTION NO: 116
When identifying rows uniquely, we use__________keys.
A. command
B. surrogate
C. standard
D. unique
Answer: B
A. command
B. surrogate
C. standard
D. unique
Answer: B
QUESTION NO: 117
True or false: With table inheritance child tables inherit primary
and foreign key definitions from their parents
A. False
B. True
Answer: A
A. False
B. True
Answer: A
QUESTION NO: 118
True or False? To enable continuous archiving, all you have to do is set archive_mode to ‘on’ in postgresql.conf
True or False? To enable continuous archiving, all you have to do is set archive_mode to ‘on’ in postgresql.conf
A. True
B. False
Answer: B
B. False
Answer: B
No comments:
Post a Comment