PostgreSQL Multiple Choice Questions:-
Click Here —-> PostgreSQL Interview Questions
1. PostgreSQL can be used from just about any major programming language, including C, C++, Perl, Python, Java, Tcl, and PHP.
A. True
B. FALSE
Ans: A
2. A meta-command always begins with what?
A. The forward slash character (/)
B. A dollar sign ($)
C. Backslash character ()
D. Question mark (?)
Ans: C
3. 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
Ans: D
4. 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
Ans: A
5. True or False? When using a SELECT statement on a table, or group of tables, those resources are locked exclusively.
A. False
B. True
Ans: A
6. What do you call the application that makes requests of the PostgreSQL server?
A. Workstation
B. Client
C. Thin Client
D. Interface
Ans: B
7. 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.
Ans: D
8. The most common method to get data into a table is to use what command?
A. Insert
B. Write
C. Execute
D. Send
Ans: A
9. The basic SQL command to list tables is?
A. do
B. DT
C. h
D. DT
Ans: D
10. In PostgreSQL, a named collection of tables is called what?
A. Trigger
B. View
C. Diagram
D. Schema
Ans: D
11. The heart of SQL is the __________ statement.
A. GROUP BY
B. INSERT INTO
C. SELECT
D. VALUES
Ans: C
12. 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.
Ans: A
13. 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.
Ans: D
14. When retrieving data in a particular table, we use the_____________ statement.
A. DT
B. ORDER BY
C. SELECT FROM
D. i
Ans: C
15. We add data to PostgreSQL by using which statement?
A. INSERT
B. ADD
C. UPDATE
D. SELECT
Ans: A
16. PostgreSQL used what model of communication?
A. Client/Server
B. Network
C. Peer-to-Peer
D. Push Model
Ans: A
17. 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
Ans: C
18. PostgreSQL has many modern features including:
A. Complex SQL queries
B. SQL Sub-selects
C. All of the above
D. Views
Ans: C
19. The SQL condition for pattern matching is?
A. IN
B. LIKE
C. DISTINCT
D. BETWEEN
Ans: B
20. What is the wrapper around the SQL command CREATE DATABASE?
A. newdb
B. add_DB
C. NEW_DB
D. created
Ans: D
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
Ans: A
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
Ans: C
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
Ans: C
____________ 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
Ans: C
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
Ans: A
WAL stands for:
A. Write Ahead Log
B. Write Ahead List
C. Write Anywhere List
D. Write Anywhere Logging
Ans: A
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.
Ans: B
Tablespaces:
A. Allow an administrator to designate specific locations in a file system where database objects can be stored.
B. Area fancy name for a table.
C. Are the amount of disk space a table is using.
D. Are the width, or the number of columns, of a particular table.
Ans: A
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
Ans: A
True or false: With table inheritance, not null and check constraints are inherited.
A. False
B. True
Ans: B
True or False: A tablespace is a place where database objects are stored on disk.
A. False
B. True
Ans: B
True or False? ALTER TABLE may be issued while a VACUUM process is running.
A. False
B. True
Ans: A
True or False? PostgreSQL is so lightweight that performance and reliability are not affected by hardware.
A. False
B. True
Ans: 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
Ans: D
True or False? Within a table, a single column may be encrypted.
A. True
B. False
Ans: A
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
Ans: C
Unless you specify NOT NULL, PostgreSQL will assume that a column is:
A. Required
B. Optional
C. Integers
D. Text
Ans: B
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
Ans: C
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
Ans: D
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.
Ans: A
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
Ans: C
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
Ans: C
What command allows you to edit PostgreSQL queries in your favorite editor?
A. ed
B. edit SQL #go
C. e
D. edit
Ans: C
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
Ans: A
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 a result.
D. All of these
Ans: D
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
Ans: B
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.
Ans: D
True or False? Only the administrator can make use of tablespaces.
A. True
B. False
Ans: B
This is used to determine how text is stored and sorted within PostgreSQL?
A. Collations
B. Index
C. Data Types
D. Database
Ans: A
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. 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.
Ans: D
What command turns on timing?
A. on timing
B. start time
C. time start
D. timing
Ans: D
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
Ans: A
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
Ans: C
In order to echo all input from the script, you use the ________ psql command.
A. -a
B. -A
C. -l
D. -E
Ans: A
True or False? Dynamic Tracing is enabled by default at compile time.
A. False
B. True
Ans: A
Bob works for StataCorp. 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
Ans: D
What does MCV stand for?
A. Massive Checkpoint Vault
B. Most Common Values
C. Most Common Variables
D. Many Common Variables
Ans: B
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
Ans: D
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.
Ans: B
True or False? PostgreSQL supports Index Only Scans.
A. False
B. True
Ans: B
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 the 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.
Ans: B
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
Ans: A
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
Ans: D
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}}’
Ans: B
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 a 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 preparation for execution
D. DO prepares a statement and EXECUTE executes it
E. No difference – they both can execute code without preparation
Ans: A
True or False? To increase server performance, automated CHECKPOINT operations should be set up in corn or Task Scheduler.
A. True
B. False
Ans: B
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
Ans: B
Locks are recorded in:
A. pg_lock system logs
B. pg_locks system view
C. pg_locks system logs
D. pg_lock system table
Ans: B
True or false? Hash indexes are not crash-safe
A. True
B. False
Ans: B
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
Ans: B
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 is honored by the including table.
A. False
B. True
Ans: A
90. 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. A 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.
Ans: C
91. The extension used for data encryption/decryption within PostgreSQL is:
A. crypto
B. pgcrypt
C. pgcrypto
D. pgencrypt
Ans: C
92. 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 precursors
D. They can perform most database management tasks
E. They can initiate subtransactions
Ans: E
93. 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
Ans: B
94. 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 containing transactions which were unsuccessfully completed due to errors.
Ans: C
95. True or False: PostgreSQL allows you to implement table inheritance. This should be defined with the special keyword INHERITS in the table design.
A. False
B. True
Ans: A
96. 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.
Ans: A
97. 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
Ans: B
98. When identifying rows uniquely, we use__________keys.
A. command
B. surrogate
C. standard
D. unique
Ans: B
99. True or false: With table inheritance, child tables inherit primary and foreign key definitions from their parents
A. False
B. True
Ans: A
100. 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
Ans: B