Test answers for PostgreSQL RDBMS Test 2015

59 Answered Test Questions:

1. Which authentication methods are supported by PostgreSQL?

Answers:

• Trust

• PAM

• LDAP

• Radius

• Password

2. Which index types are supported by PostgreSQL?

Answers:

• A-tree

• B-tree

• GiST

• Hash

• GIN

• None of the above

3. Out of the following backup approaches, which ones are applicable to PostgreSQL?

Answers:

• SQL dump

• File system level backup

• Real cluste

• Continuous archiving

• None of the above

4. In the following operation, which ones can trigger a trigger?

Answers:

• alte

• insert

• drop

• update

• delete

5. Which index types support multicolumn indexes?

Answers:

• B-tree

• GiST

• GIN

• Hash

• None of the above

6. What kind of triggers are offered by PostgreSQL?

Answers:

• Per-row triggers

• Per-table triggers

• Per-database triggers

• Per-statement triggers

7. Which of the following statements will cast the integer value 1 to type text?

Answers:

• SELECT text(1) AS cast_integer;

• SELECT CAST(1, text) as cast_integer;

• SELECT 1::text AS cast_integer;

• SELECT 1 TO text AS cast_integer;

• SELECT CAST(1 AS text) AS cast_integer;

8. How can you configure PostgreSQL autovacuum?

Answers:

• With the ALTER autovacuum policy; query in psql

• By editing pg_hba.conf

• By editing postgresql.conf

• By running the autovacuum command line tool

• None of the above

9. What library is used by PostgreSQL for encryption?

Answers:

• GnuTLS

• OpenSSL

• OpenLDAP

• OpenTLS

• None of the above

10. Are the contents of the pg_autovacuum system catalog saved when pg_dumpall is used to backup the database?

Answers:

• Yes

• No

11. On a UNIX system, what is the best way to prevent all non-local connections to the postmaster?

Answers:

• Using ipfilte

• Using UNIX sockets

• Using TCP port 0

• Using an empty pg_hba.conf

• None of the above

12. Can deferrable constraints be deferred by a trigger?

Answers:

• Yes

• No

13. After a PostgreSQL installation, how will you create the database cluster?

Answers:

• With created

• With initd

• With createdbs

• With postmaste

• None of the above

14. How will you change the TCP port which PostgreSQL will listen to?

Answers:

• PostgreSQL does not support TCP

• By changing "port" in postgresql.conf

• By issuing UPDATE config SET port = <newvalue

• TCP port cannot be changed

• None of the above

15. Which of the following queries will create a table with two fields, "id" and "name" with "id" as an auto incrementing primary key?

Answers:

• create table foo (id int serial primary key auto, name varchar(255));

• create table foo (id int auto_increment primary key, name varchar(255));

• create table foo (id auto_increment primary key, name varchar(255));

• create table foo (id serial primary key, name varchar(255));

• None of the above

16. How will you list the available functions from psql?

Answers:

• select * from pg_functions;

• \df

• \? functions

• select * from pg_procedures;

• None of the above

17. Consider the following query:

Create table foo (bar varchar);

What will be the size limit of the bar?

Answers:

• 256

• 1024

• 1

• No limit (It will be equivalent to the text)

• None of the above

18. What is the well known port number for the postgresql database service?

Answers:

• 5000

• 541

• 5432

• 63

• None of the above

19. What is true regarding file system backup?

Answers:

• It can only be used to backup the whole database

• The database must be running for the backup to take place

• To restore a file system backup, pg_restore should be used

• None of the above

• All of the above

20. Which one of the following text search functions does not exist?

Answers:

• to_tsvecto

• plainto_tsquery

• to_tsquery

• strip

• ts_rewrite

• plainto_tsvecto

21. How do you alter a column to forbid null values?

Answers:

• alter table foo alter bar set not null;

• alter table foo alter bar avoid null;

• alter table foo alter bar forbid null;

• alter table foo alter bar add not null;

• None of the above

22. What is the difference between to_tsvector() and ::tsvector ?

Answers:

• to_tsvector () normalizes the string while ::tsvector does not

• to_tsvector () can be used in select statements, while ::tsvector cannot

• They are equivalent

• None of the above

23. What is the storage size of an integer on a 64bit system?

Answers:

• 8bytes

• 2bytes

• 16bytes

• 4bytes

• 64bytes

24. While creating a trigger, the function it will call may be created after it and attached to it.

Answers:

• True

• False

25. Which function should be used to highlight the results?

Answers:

• ts_highlight

• ts_tag

• ts_headline

• ts_select

• None of the above

26. Which PostgreSQL version added the enum datatype?

Answers:

• 8.0

• 8.1

• 7.4

• 8.3

• PostgreSQL doesn't have an enum type.

27. When using LIKE to compare strings, what is the wildcard operator (operator which matches zero or more characters)?

Answers:

• *

• $

• %

• &

• None of the above

28. For proper results, which of the following should contain a tsvector?

Answers:

• Lexemes

• Tokens

• Integers

• Sets

• Enum

29. What is the command used to import a backup made with pg_dumpall > file.dmp?

Answers:

• pg_restore file.dmp

• psql -f file.dmp

• pg_restoreall file.dmp

• postgre --restore -f file.dmp

• None of the above

30. Which of the following statements will produce an error?

Answers:

• SELECT now()::timestamp;

• SELECT now()::int;

• SELECT now()::varchar;

• SELECT now()::char;

• None

31. To backup a database, the postmaster daemon must be halted.

Answers:

• True

• False

32. The following statement will retrieve the second element of the array column products in table store_products.

SELECT products[1] FROM store_products;

Answers:

• True

• False

33. SELECT 'infinity'::timestamp;

Will this statement produce an error?

Answers:

• Yes

• No

34. What is the difference between tokens and lexemes?

Answers:

• Tokens are always in upper case

• Lexemes are normalized

• A lexeme is a string while a token is an intege

• Lexemes are always in upper case

• None of the above

35. Which kind of index can be declared unique?

Answers:

• Hash

• A-tree

• B-tree

• GIN

• GiST

36. SELECT rtrim('foobar', 'abr');

The result of this statement is foo.

Answers:

• True

• False

37. SELECT !!3;

What output will this statement give?

Answers:

• true

• 3

• 6

• The statement is invalid.

• None of the above.

38. An ISO-8601 time may be entered into a table using the numeric format 012411 instead of 01:24:11.

Answers:

• True

• False

39. While creating a table with a field of the serial type, a sequence will be created.

Answers:

• True

• False

40. How do you create a table with a field of the int array type?

Answers:

• create table foo (bar int array);

• create table foo (bar integer[]);

• create table foo (bar array int);

• create table foo (bar[] int);

• None of the above

41. What is the name of the special time input with value 00:00:00.00 UTC?

Answers:

• zero

• noon

• midnight

• allballs

• None of the above

42. Consider the following empty table:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

Which of the following inserts will cause an error?

Answers:

• insert into example (a, b, c) values (1, 2, 3), (1, 2, 4);

• insert into example (a, b, c) values (1, 2, 3), (3, 3, 3);

• insert into example (a, b, c) values (1, 1, 1), (3, 3, 3);

• insert into example (a, b, c) values (1, 2, 3), (1, 4, 3);

• None will cause an error

43. Which of the following statements will create a table with a multidimensional array as second column?

Answers:

• CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[[]]);

• CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[][]);

• CREATE TABLE favorite_books (customer_id integer, themes_and_titles text{2});

• CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[text]);

44. If max_connections is 10 and 10 connections are currently active, how can you be sure the superuser will be available to connect?

Answers:

• You cannot

• Leave a superuser connected all the time

• Set superuser_reserved_connections in postgresql.conf

• Kill postmaster and connect to it

• None of the above

45. How will you rank text search results?

Answers:

• With the ORDER BY operator

• With the ts_rank function

• Search results are automatically ranked

• Search results cannot be ranked

• None of the above

46. Which of the following statements will create a table special_products which is a child of the table store_products?

Answers:

• CREATE TABLE special_products (quality int) INHERITS store_products;

• CREATE TABLE special_products (quality int) EXTENDS store_products;

• CREATE TABLE special_products (quality int) EXPANDS store_products;

• CREATE TABLE special_products (quality int) FROM store_products;

• CREATE TABLE special_products (quality int) WITH PARENT store_products;

47. Which of the following statements will create a table?

Answers:

• SELECT INTO products_backup FROM special_products;

• SELECT FROM special_products * INTO products_backup;

• SELECT * INTO products_backup LIKE special_products;

• SELECT * INTO products_backup FROM special_products;

• SELECT COPY special_products INTO products_backup;

48. Which of the following statements will retrieve the number of values stored in an array column?

Answers:

• SELECT array_dims(products) FROM store_products;

• SELECT products[] FROM store_products;

• SELECT dimensions(products) FROM store_products;

• SELECT array_dim(products) FROM store_products;

• SELECT count(products) FROM store_products;

49. Does PostgreSQL support SSL?

Answers:

• Yes

• No

50. What is the effect of turning fsync off in postgresql.conf?

Answers:

• File synchronization will be deactivated

• fsync is not a valid configuration option

• PostgreSQL will not enforce write ahead log flush

• It will turn off fast synchronization

• None of the above

51. Given a table special_products that inherits from a table store_products, which of the following statements will modify store_products only without affecting its child table?

Answers:

• UPDATE store_products ONLY SET name = 'Wine' WHERE id = 2;

• UPDATE store_products SET name= 'Wine' WHERE id = 2;

• UPDATE ONLY store_products SET name = 'Wine' WHERE id = 2;

• UPDATE JUST store_products SET name = 'Wine' WHERE id = 2;

• UPDATE store_products NOT special_products SET name = 'Wine' WHERE id = 2;

52. What can be stored in a column of type decimal(4,3)?

Answers:

• 4 numeric values with up to 3 digits to the right of the decimal point.

• A numeric value with up to 7 digits in total, 3 of which can be to the right of the decimal point.

• A numeric value with up to 4 digits in total, 3 of which can be to the right of the decimal point.

• A numeric value with at least 4 digits, 3 of which must be to the right of the decimal point.

53. What is the ~ operator?

Answers:

• POSIX regular expression match operator

• XOR operator

• NOT operator

• Home directory ENV variable

• None of the above

54. What interfaces are available in the base distribution of PostgreSQL?

Answers:

• Java

• PHP

• C

• Ruby

• Perl

55. What is the default ordering when ORDER BY is not specified?

Answers:

• By ID

• By insertion date

• By name

• The ordering is unknown if not specified

• None of the above

56. A table can have only one primary key column.

Answers:

• True

• False

57. What command will correctly restore a backup made with the following command?
pg_dump -Fc dbname > filename

Answers:

• psql -f filename dbname

• psql -Fc filename dbname

• pg_restore filename dbname

• pg_restore -d dbname filename

• None of the above

58. How do you select a single random row from a table?

Answers:

• SELECT random() * FROM tab LIMIT 1;

• SELECT * FROM tab ORDER BY random() LIMIT 1;

• SELECT * FROM tab RANDOM LIMIT 1 ;

• SELECT RANDOM 1 from tab;

• None of the above

59. PostgreSQL triggers can be written in C directly.

Answers:

• True

• False