Do you know that? biggest challenge for 41.8% freelancers is to find clients next

Contact Email: teammmrem@yahoo.com

<< All Upwork (oDesk) and Elance Tests << Elance test answers << IT & Programming category

Test answers for PostgreSQL 2020

(82, CL) Last updated: February 12
Elance • IT & Programming
Questions and answers are regularly updated. Answered and not answered tests questions.

This helps getting job: Hundreds of (cover letter examples , interview questions , profile samples ) • Earn on Upwork (oDesk)
Job assistance: jobs popularityfreelance rates

Popular test answers: HTML, .Net, CSS, English, SEO, Photoshop, iOS, Java, Android, Ruby, Python, JavaScript

See all 6 tests answers updated

Find Upwork (oDesk) and Elance test answers on this website:



Collapse | Expand

82 Answered Test Questions:

1. PostgreSQL can be used from just about any major programming language, including C, C++, Perl, Python, Java, Tcl, and PHP.

Answers:

• True

• FALSE

2. The value NULL, in database terminology, means?

Answers:

• The value is not relevant for this particular row.

• The value is undetermined

• All of these

• The value is undetermined at this time

3. Tablespaces:

Answers:

• Are a fancy name for table.

• Are the amount of disk space a table is using.

• Allow an administrator to designate specific locations in a file system where database objects can be stored.

• Are the width, or number of columns, of a particular table.

4. A VACUUM operation is used:

Answers:

• To protect against loss of very old data due to transaction ID wraparound.

• All of these

• To update data statistics used by the PostgreSQL query planner.

• To recover or reuse disk space occupied by updated or deleted rows.

5. When you want to use a join between columns that are in the same table, you use what type of join?

Answers:

• left outer

• union

• self

• right outer

6. True or False? ALTER TABLE may be issued while a VACUUM process is running.

Answers:

• True

• False

7. True or False? When using a SELECT statement on a table, or group of tables, those resources are locked exclusively.

Answers:

• True

• False

8. True or false:  With table inheritance, not null and check constraints are inherited.

Answers:

• False

• True

9. If you don’t specify ASC or DESC, PostgreSQL will assume you want to see results:

Answers:

• in a random order

• in descending order

• grouped together by field type

• in ascending order

10. Deadlocks occur when:

Answers:

• Data is being written to the same cell by separate processes at the same time.

• Two users try to access the same table at the same time.

• Two users are connected to the same database at the same time.

• Two transactions hold exclusive locks on resources they both need.

11. True or False? VACUUM FULL shrinks indexes, optimizing database performance.

Answers:

• True - This was addressed in version 9.0

• False - This was addressed in version 9.0

12. WAL stands for:

Answers:

• Write Ahead List

• Write Anywhere Logging

• Write Ahead Log

• Write Anywhere List

13. Which of the following best describes a role:

Answers:

• The purpose of a particular database.

• A template for authorization to various database objects.

• A cluster's purpose.

• A server's purpose within a cluster.

14. What do you call the application that makes requests of the PostgreSQL server?

Answers:

• Interface

• Client

• Thin Client

• Workstation

15. The basic psql command to list tables is?

Answers:

• \dt

• \do

• \dT

• \h

16. In PostgreSQL, a named collection of tables is called what?

Answers:

• Diagram

• Schema

• View

• Trigger

17. What command tells PostgreSQL that all of the changes you made to a database should become permanent?

Answers:

• Send

• Apply

• Execute

• Commit

18. What is the wrapper around the SQL command CREATE DATABASE?

Answers:

• createdb

• NEW_DB

• add_DB

• newdb

19. ____________ allow us to define formally in the database how different tables relate to each other.

Answers:

• temporary tables

• Foreign Key Constraints

• table management

• Views

20. The SQL condition for pattern matching is?

Answers:

• LIKE

• BETWEEN

• DISTINCT

• IN

21. The most common method to get data into a table is to use what command?

Answers:

• Write

• Execute

• Insert

• Send

22. PostgreSQL is:

Answers:

• Enterprise-class proprietary software developed at Bell Labs, with a basic set of features.

• An open-source ORDBMS developed at UC Berkley, which supports many modern features.

• An open-source SMTP server.

• A NoSQL solution.

23. PostgreSQL is

Answers:

• a relational database management system.

• a network-type database management system.

• an XML database management system.

• a hierarchical database management system.

24. With PostgreSQL, you can access data by

Answers:

• Embed SQL directly into your application

• Use a command-line application to execute SQL statements

• All of these

• Use function calls (APIs) to prepare and execute SQL statements, scan result sets, and perform updates from a large variety of different programming languages.

25. When retrieving data in a particular table, we use the_____________ statement.

Answers:

• \dt

• SELECT FROM

• ORDER BY

• \i<filename>

26. The heart of SQL is the __________ statement.

Answers:

• GROUP BY

• VALUES

• INSERT INTO

• SELECT

27. We add data to PostgreSQL by using which statement?

Answers:

• UPDATE

• SELECT

• ADD

• INSERT

28. PostgreSQL has many modern features including:

Answers:

• SQL Sub-selects

• Views

• Complex SQL queries

• All of the above

29. PostgreSQL used what model of communication?

Answers:

• Client/Server

• Peer-to-Peer

• Push Model

• Network

30. PostgreSQL runs on:

Answers:

• on all UNIX versions except Solaris.

• Windows only

• Linux and Windows only.

• all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.

31. PostgreSQL can be installed?

Answers:

• from the source code

• All of these

• from Linux binaries

• on a Windows computer via the PostgreSQL installer

32. Triggers can be configured to execute when which of the following operations are performed:

Answers:

• INSERT statements

• All of the above

• UPDATE statements

• DELETE statements

33. True or False? Within a table, a single column may be encrypted.

Answers:

• True

• False

34. The core PostgreSQL source code includes what interfaces?

Answers:

• C++ and Java interfaces

• Ruby and PHP interfaces

• The C and embedded C interfaces

• VB and VB.NET interfaces

35. True or False? PostgreSQL is so lightweight that performance and reliability are not affected by hardware.

Answers:

• True

• False

36. True or False: A tablespace is the place where database objects are stored on disk.

Answers:

• True

• False

37. Unless you specify NOT NULL, PostgreSQL will assume that a column is:

Answers:

• Integers

• Optional

• Required

• Text

38. Query trees can be viewed in the server logs as long as which of the following configuration parameters are enabled?

Answers:

• debug_print_rewritten

• debug_print_plan

• All of these

• debug_print_parse

39. To create a database in PostgreSQL, you must have the special CREATEDB privilege or

Answers:

• be an admin

• the special CREATETBL privilege

• be a superuser.

• have a script do it.

40. A meta-command always begins with what?

Answers:

• Question mark (?)

• Dollar sign ($)

• Forwardslash character (/)

• Backslash character (\)

41. To describe a table in PostgreSQL which of the following is correct:

Answers:

• psql> describe table_name

• psql> DESCRIBE table_name

• psql> \d table_name

• psql> \D table_name

42. What does the following statement do?  CREATE INDEX lower_title_idx ON books ((lower(title)));

Answers:

• Creates a non-write-locking index

• Creates an index for efficient case-insensitive searches on the titles column within the books table

• Creates a new index with a special operator class 'lower' for case insensitive comparisons.

• Modifies an index in place to be lowercase

• Nothing, it's invalid SQL

43. What command allows you to edit PostgreSQL queries in your favorite editor ?

Answers:

• edit sql #go

• \e

• \ed

• \edit

44. To prevent transaction wraparound, a VACUUM operation should be run on every table no less than once every:

Answers:

• 3 billion transactions

• 4 billion transactions

• 5 billion transactions

• 2 billion transactions

45. The rule system:

Answers:

• All of these

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

• Operates between the parser and the planner.

• None of these

46. Asynchronous Commits:

Answers:

• May cause recent transactions to be lost

• Allow transactions to complete more quickly

• All of these

• None of these

47. This is used to determine how text is stored and sorted within PostgreSQL?

Answers:

• Collations

• Database

• Index

• Data Types

48. What command turns on timing?

Answers:

• \time start

• \timing

• \start time

• \on timing

49. 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?

Answers:

• The server is idle, awaiting a client connection.

• A user issued an IDLE statement to the database.

• A client is connected, and the server is awaiting input.

• A transaction is possibly hung.

50. True or False? Only the administrator can make use of tablespaces.

Answers:

• False

• True

51. Advisory locks are allocated out of a shared memory pool whose size is defined by the configuration variables....

Answers:

• All of these

• max_connections

• None of these

• max_locks_per_transaction

52. What does MCV stand for?

Answers:

• Most Common Variables

• Massive Checkpoint Vault

• Many Common Variables

• Most Common Values

53. In order to echo all input from script, you use the ________ psql command.

Answers:

• -A

• -E

• -a

• -l

54. Which is NOT true of array indexes?

Answers:

• By default PostgreSQL indexes arrays so that inner elements can be searched.

• GiST indexes can be used to select arrays for specific values.

• Partial functional indexes of arrays are supported on Btree, GiST, and GIN indexes.

• BTree indexes can search for array elements as long as the match begins with the beginning of the array.

• GIN indexes can search for arrays containing specific elements.

55. Which statement is true about PostgreSQL data types?

Answers:

• n'in CHARACTER(n) represents the number of bytes.

• Only the INTEGER type can be declared as an array.

• A large object data type can be used to store data of unlimited size.

• There is a non-standard PostgreSQL data type, called Geometric data type, which handles 2-dimensional data.

56. True or False? Dynamic Tracing is enabled by default at compile time.

Answers:

• True

• False

57. What is "index bloat"?

Answers:

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

• Indexing too many tables, resulting in inefficient database performance.

• Indexing inefficiently, like choosing to index timestamps in a table.

• Index filling up with keys.

58. To copy a database from server1 to server2, you might use which of the following:

Answers:

• pg_dump -h server1 database | psql -h server2 database

• pg_mv -h server1 database | pgsql -h server2 database

• pg_copy -h server1 database | psql -h server2 database

• pg_dump -h server1 database > pgsql -h server2 database

59. To restore a PostgreSQL backup created with pg_dump, the following may be used:

Answers:

• $ psql -r database_dump.psql database_name

• $ psql -F database_dump.psql database_name

• $ psql -f database_dump.psql database_name

• $ psql -R database_dump.psql database_name

60. What is the difference between DO 'some code...' and EXECUTE 'some code...' statements?

Answers:

• No difference - they both can execute a code without preparation

• DO lets you execute some plPgSql code without saving it to database and EXECUTE lets you execute DDL or DML only

• DO prepares a statement and EXECUTE executes it

• No difference - they both need a preparation for execution

• EXECUTE statement executes only prepared code, and DO can execute without PREPARE statement

61. By default, in what subdirectory of the database data directory are WAL logs contained?

Answers:

• pg-xlog

• pg_xlog

• pg_log

• pg-log

62. The syntax to view the indexes of an existing postgreSQL table is:

Answers:

• # index table_name

• # \i table_name

• # \d table_name

• # index database_name.table_name

63. 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?

Answers:

• host partners bob 10.5.34.8/32 kerb5

• host partners bob 10.5.34.8 kerb5

• host partners bob 10.5.34.8/24 krb5

• host partners bob 10.5.34.8/32 krb5

64. True or False? PostgreSQL supports Index Only Scans.

Answers:

• True

• False

65. Which of the following is not a valid integer array?

Answers:

• '{{1,2,3,4,5,6}}'

• '{{1,2},{1,3},{1,4},{2,5}}'

• '{1,3,4,5,{6,7}}'

• '{1,3,4,5,6,7}'

• '{}'

66. What are the join strategies available to the postgreSQL planner when a SELECT query contains two or more relations?

Answers:

• Nested Loop Join, Merge Join, Hash Join

• MERGE, INNER JOIN, OUTER JOIN

• INNER JOIN, OUTER JOIN

• MERGE, JOIN

67. Locks are recorded in:

Answers:

• pg_locks system view

• pg_lock system table

• pg_lock system logs

• pg_locks system logs

68. True or False? To increase server performance, automated CHECKPOINT operations should be setup in cron or Task Scheduler.

Answers:

• True

• False

69. Which of the following is NOT a feature of user defined functions?

Answers:

• They can return multiple result sets via refcursors

• They can be written in various different languages

• They can perform most database management tasks

• They can initiate subtransactions

• Functions marked IMMUTABLE can have their output indexed

70. The extension used for data encryption/decryption within PostgreSQL is:

Answers:

• pgcrypto

• crypto

• pgcrypt

• pgencrypt

71. Which statement is not true about a PostgreSQL domain?

Answers:

• A domain is created by 'CREATE DOMAIN'.

• When defining a domain, you can add a default value and constraints to the original data.

• Domain is a namespace existing between databases and objects such as tables.

• A domain can be used as a column type when defining a table.

72. True or False? When restoring a database backed up with pg_dump, it's generally a good idea to enable WAL.

Answers:

• False

• True

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

Answers:

• False

• True

74. To create a database that supports UTF-8, the following command can be used:

Answers:

• createdb -E UTF8 -O user database_name

• createdb -C UTF8 database_name

• createdb -E UTF-8 -O user database_name

• createdb -C UTF8 -O user database_name

75. True or false? Hash indexes are not crash-safe

Answers:

• False

• True

76. True or False: PostgreSQL allows you to implement table inheritance. This should be defined with the special keyword INHERITIS in the table design.

Answers:

• False

• True

77. WAL segment size is determined:

Answers:

• By the administrator at runtime, or through the startup script.

• By the wal_segment_size configuration parameter in postgresql.conf

• It is statically set within the source code.

• By the configure script at compile time

78. What is a TOAST file?

Answers:

• A file storing data that was unable to be written to the database, and will be expunged once the server shuts down.

• A file containing values too wide to fit comfortably in the main table

• A file containining transactions which were unsuccessfully completed due to errors.

• A list of clients not allowed to connect to the database.

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

Answers:

• network

• hierarchical

• structured

• relational

80. When identifying rows uniquely, we use__________keys.

Answers:

• command

• unique

• standard

• surrogate

81. True or false:  With table inheritance child tables inherit primary and foreign key definitions from their parents

Answers:

• True

• False

82. True or False? To enable continuous archiving, all you have to do is set archive_mode to 'on' in postgresql.conf

Answers:

• False

• True