UpWork Postgre SQL RDBMS Test Question and Answers

Postgre SQL RDBMS UpWork Test Answers

1. Which authentication methods are supported by PostgreSQL?

Answers:

1. Trust
2. PAM
3. LDAP
4. Radius
5. Password

2. Which index types are supported by PostgreSQL?

Answers:

1. A-tree
2. B-tree
3. GiST
4. Hash
5. GIN
6. None of the above

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

Answers:

1. SQL dump
2. File system level backup
3. Real cluste
4. Continuous archiving
5. None of the above

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

Answers:

1. alte
2. insert
3. drop
4. update
5. delete

5. Which index types support multicolumn indexes?

Answers:

1. -tree
2. GiST
3. GIN
4. Hash
5. None of the above

6. What kind of triggers are offered by PostgreSQL?

Answers:

1. Per-row triggers
2. Per-table triggers
3. Per-database triggers
4. Per-statement triggers

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

Answers:

1. SELECT text(1) AS cast_integer;
2. SELECT CAST(1, text) as cast_integer;
3. SELECT 1::text AS cast_integer;
4. SELECT 1 TO text AS cast_integer;
5. SELECT CAST(1 AS text) AS cast_integer;

8. How can you configure PostgreSQL autovacuum?

Answers:

1. With the ALTER autovacuum policy; query in psql
2. By editing pg_hba.conf
3. By editing postgresql.conf
4. By running the autovacuum command line tool
5. None of the above

9. What library is used by PostgreSQL for encryption?

Answers:

1. GnuTLS
2. OpenSSL
3. OpenLDAP
4. OpenTLS
5. 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:

1. Yes
2. No

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

Answers:

1. Using ipfilte
2. Using UNIX sockets
3. Using TCP port 0
4. Using an empty pg_hba.conf
5. None of the above

12. Can deferrable constraints be deferred by a trigger?

Answers:

1. Yes
2. No

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

Answers:

1. With created
2. With initd
3. With createdbs
4. With postmaste
5. None of the above

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

Answers:

1. PostgreSQL does not support TCP
2. By changing “port” in postgresql.conf
3. By issuing UPDATE config SET port = <newvalue
4. TCP port cannot be changed
5. 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:

1. create table foo (id int serial primary key auto, name varchar(255));
2. create table foo (id int auto_increment primary key, name varchar(255));
3. create table foo (id auto_increment primary key, name varchar(255));
4. create table foo (id serial primary key, name varchar(255));
5. None of the above

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

Answers:

1. select * from pg_functions;
2. df
3. ? functions
4. select * from pg_procedures;
5. None of the above

17. Consider the following query:

Create table foo (bar varchar);

What will be the size limit of the bar?

Answers:

1. 256
2. 1024
3. 1
4. No limit (It will be equivalent to the text)
5. None of the above

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

Answers:

1. 5000
2. 541
3. 5432
4. 63
5. None of the above

19. What is true regarding file system backup?

Answers:

1. It can only be used to backup the whole database
2. The database must be running for the backup to take place
3. To restore a file system backup, pg_restore should be used
4. None of the above
5. All of the above

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

Answers:

1. to_tsvecto
2. plainto_tsquery
3. to_tsquery
4. strip
5. ts_rewrite
6. plainto_tsvecto

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

Answers:

1. alter table foo alter bar set not null;
2. alter table foo alter bar avoid null;
3. alter table foo alter bar forbid null;
4. alter table foo alter bar add not null;
5. None of the above

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

Answers:

1. to_tsvector () normalizes the string while ::tsvector does not
2. to_tsvector () can be used in select statements, while ::tsvector cannot
3. They are equivalent
4. None of the above

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

Answers:

1. 8bytes
2. 2bytes
3. 16bytes
4. 4bytes
5. 64bytes

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

Answers:

1. True
2. False

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

Answers:

1. ts_highlight
2. ts_tag
3. ts_headline
4. ts_select
5. None of the above

26. Which PostgreSQL version added the enum datatype?

Answers:

1. 8.0
2. 8.1
3. 7.4
4. 8.3
5. 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:

1. *
2. $
3. %
4. &
5. None of the above

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

Answers:

1. Lexemes
2. Tokens
3. Integers
4. Sets
5. Enum

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

Answers:

1. pg_restore file.dmp
2. psql -f file.dmp
3. pg_restoreall file.dmp
4. postgre –restore -f file.dmp
5. None of the above

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

Answers:

1. SELECT now()::timestamp;
2. SELECT now()::int;
3. SELECT now()::varchar;
4. SELECT now()::char;
5. None

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

Answers:

1. True
2. 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:

1. True
2. False

33. SELECT ‘infinity’::timestamp;

Will this statement produce an error?

Answers:

1. Yes
2. No

34. What is the difference between tokens and lexemes?

Answers:

1. Tokens are always in upper case
2. Lexemes are normalized
3. A lexeme is a string while a token is an intege
4. Lexemes are always in upper case
5. None of the above

35. Which kind of index can be declared unique?

Answers:

1. Hash
2. A-tree
3. B-tree
4. GIN
5. GiST

36. SELECT rtrim(‘foobar’, ‘abr’);

The result of this statement is foo.

Answers:

1. True
2. False

37. SELECT !!3;

What output will this statement give?

Answers:

1. true
2. 3
3. 6
4. The statement is invalid.
5. 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:

1. True
2. False

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

Answers:

1. True
2. False

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

Answers:

1. create table foo (bar int array);
2. create table foo (bar integer[]);
3. create table foo (bar array int);
4. create table foo (bar[] int);
5. None of the above

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

Answers:

1. zero
2. noon
3. midnight
4. allballs
5. 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:

1. insert into example (a, b, c) values (1, 2, 3), (1, 2, 4);
2. insert into example (a, b, c) values (1, 2, 3), (3, 3, 3);
3. insert into example (a, b, c) values (1, 1, 1), (3, 3, 3);
4. insert into example (a, b, c) values (1, 2, 3), (1, 4, 3);
5. None will cause an error

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

Answers:

1. CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[[]]);
2. CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[][]);
3. CREATE TABLE favorite_books (customer_id integer, themes_and_titles text{2});
4. 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:

1. You cannot
2. Leave a superuser connected all the time
3. Set superuser_reserved_connections in postgresql.conf
4. Kill postmaster and connect to it
5. None of the above

45. How will you rank text search results?

Answers:

1. With the ORDER BY operator
2. With the ts_rank function
3. Search results are automatically ranked
4. Search results cannot be ranked
5. 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:

1. CREATE TABLE special_products (quality int) INHERITS store_products;
2. CREATE TABLE special_products (quality int) EXTENDS store_products;
3. CREATE TABLE special_products (quality int) EXPANDS store_products;
4. CREATE TABLE special_products (quality int) FROM store_products;
5. CREATE TABLE special_products (quality int) WITH PARENT store_products;

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

Answers:

1. SELECT INTO products_backup FROM special_products;
2. SELECT FROM special_products * INTO products_backup;
3. SELECT * INTO products_backup LIKE special_products;
4. SELECT * INTO products_backup FROM special_products;
5. 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:

1. SELECT array_dims(products) FROM store_products;
2. SELECT products[] FROM store_products;
3. SELECT dimensions(products) FROM store_products;
4. SELECT array_dim(products) FROM store_products;
5. SELECT count(products) FROM store_products;

49. Does PostgreSQL support SSL?

Answers:

1. Yes
2. No

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

Answers:

1. File synchronization will be deactivated
2. fsync is not a valid configuration option
3. PostgreSQL will not enforce write ahead log flush
4. It will turn off fast synchronization
5. 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:

1. UPDATE store_products ONLY SET name = ‘Wine’ WHERE id = 2;
2. UPDATE store_products SET name= ‘Wine’ WHERE id = 2;
3. UPDATE ONLY store_products SET name = ‘Wine’ WHERE id = 2;
4. UPDATE JUST store_products SET name = ‘Wine’ WHERE id = 2;
5. 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:

1. 4 numeric values with up to 3 digits to the right of the decimal point.
2. A numeric value with up to 7 digits in total, 3 of which can be to the right of the decimal point.
3. A numeric value with up to 4 digits in total, 3 of which can be to the right of the decimal point.
4. 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:

1. POSIX regular expression match operator
2. XOR operator
3. NOT operator
4. Home directory ENV variable
5. None of the above

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

Answers:

1. Java
2. PHP
3. C
4. Ruby
5. Perl

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

Answers:

1. By ID
2. By insertion date
3. By name
4. The ordering is unknown if not specified
5. None of the above

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

Answers:

1. True
2. False

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

Answers:

1. psql -f filename dbname
2. psql -Fc filename dbname
3. pg_restore filename dbname
4. pg_restore -d dbname filename
5. None of the above

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

Answers:

1. SELECT random() * FROM tab LIMIT 1;
2. SELECT * FROM tab ORDER BY random() LIMIT 1;
3. SELECT * FROM tab RANDOM LIMIT 1 ;
4. SELECT RANDOM 1 from tab;
5. None of the above

59. PostgreSQL triggers can be written in C directly.

Answers:

1. True
2. False