PostgreSQL Quiz

PostgreSQL Quiz : This PostgreSQL Intermediate Quiz contains set of 60 PostgreSQL Quiz which will help to clear any Mcqs exam which is designed for Intermediate.

1) Default indexing alogorithm in postgresql

1. B-Tree
2. Hash
3. Gist
4. Brin

Show Answer

 

 

2) which index alogithm is best suited for indexing columns and expressions that contain an array, JSON, JSONB, etc

1. Brin
2. Gist
3. Gin
4. Hash

Show Answer

 

 

3) which type of indexing alogithm is used if a query used Eqlaity operation for searching rows

1. Gin
2. Hash
3. Gist
4. Brin

Show Answer

 

 

4) CREATE TABLESPACE fastspace LOCATION ‘/ssd1/postgresql/data’. What is true

1. The location specified must be existing directory
2. the directory will get created if doesnot exists
3. the command will throw an error
4. Location is an Optional keywod

Show Answer

 

 

5) Tablespaces

1. Allow an administrator to designate specific locations in a file system where database objects can be stored.
2. Are a fancy name for table.
3. Are the amount of disk space a table is using
4. Are the width, or number of columns, of a particular table

Show Answer

 

 

6) To restore a PostgreSQL backup created with pg_dump, the following may be used:

1. $ psql -F database_dump.psql database_name
2. $ psql -R database_dump.psql database_name
3. $ psql -f database_dump.psql database_name
4. $ psql -r database_dump.psql database_name

Show Answer

 

 

7) which tool is provided by postgres to help you backup databases easily and effectively.

1. PG_BACKUP
2. PG_DUMP
3. BACKUP
4. DUMP

Show Answer

 

 

8) Which of the following best describes a role

1. A server’s purpose within a cluster
2. The purpose of a particular database.
3. A cluster’s purpose.
4. A template for authorization to various database objects.

Show Answer

 

 

9) To create a database in PostgreSQL, you must have the special CREATEDB privilege or

1. be an admin
2. have a script do it
3. be a superuser.
4. the special CREATETBL privilege

Show Answer

 

 

10) ___________command is used to import the contents of a .csv file into portgres table

1. Import
2. Imp
3. Copy
4. \Import

Show Answer

 

 

11) which catalog that stores data about all available databases

1. Pg_database
2. Pg_DB
3. catalog_db
4. postgres_db

Show Answer

 

 

12) which command shows all databases in the current folder

1. \list
2. \l
3. \db
4. \show

Show Answer

 

 

13) how to execute a function in Postgresql

1. Call <funcname>
2. Select <funcname>
3. execute <funcname>
4. go <funcname>

Show Answer

 

 

14) when we have more than one function having the  same name,  but arguments are different , we call them as

1. Overloaded function
2. Recursive Function
3. Overriding function
4. repetable function

Show Answer

 

 

15) Which type of oop statement executes a block of statements until a condition evaluates to false

1. Basic
2. While
3. For
4. Do-While

Show Answer

 

 

16) which statement about FOR Loop is not true

1. PostgreSQL creates an integer variable loop_counter that exists only inside the loop
2. the from and to are expressions that specify the lower and upper bound of the range
3. the expression following the BY clause specifies the iteration step
4. For loop is an infinite loop

Show Answer

 

 

17) 1.  declare a cursor.                                                                                   2.  Open the cursor                                                                                     3. fetch the records

1. 1,2,3,4
2. 3,2,1,4
3. 2,1,3,4
4. 3,1,2,4

Show Answer

 

 

18) which function accepts an unlimited number of arguments and returns the first argument that is not null.

1. Nullif
2. IfNull
3. IsNull
4. Coalesce

Show Answer

 

 

19) NULLIF(20,10) returns

1. NULL
2. 20
3. 10
4. expression is invalid

Show Answer

 

 

20) SELECT CAST(‘10.2’ as DOUBLE);

1. converts the value into Double
2. throws error as DOUBLE datatype does not exists
3. throws error as syntax is invalid
4. FROM clause is mandatory

Show Answer

 

 

21) Which statements execute a sequence of statements multiple times?

1. EXIT
2. LOOP
3. Both A and B
4. Neither A not B

Show Answer

 

 

22) Which statements are used to control a cursor variable?

1. OPEN-FOR
2. FETCH
3. CLOSE
4. All of the Above

Show Answer

 

 

23) Which loop statement executes a block of statements until a condition evaluates to false

1. WHILE
2. FOR
3. LOOP
4. None of the Above

Show Answer

 

 

24) The CLOSE statement enables a cursor variable and makes the associated result set undefined

1. TRUE
2. FALSE

Show Answer

 

 

25) In which of the following, do you specify the same parameters in the same order as they are declared in the procedure?

1. Positional Notation
2. Names Notation
3. Mixed Notation
4. All of the Above

Show Answer

 

 

26) what is true about  PL/pgSQL constants?

1. the constants make the code more readable
2. The constants reduce the maintenance efforts
3. Both A and B
4. Neither A not B

Show Answer

 

 

27) Because they should not contain SQL manipulation statements, what kind of trigger is typically used to enforce complex integrity constraints?

1. AFTER ROW triggers.
2. BEFORE ROW triggers.
3. INSTEAD OF triggers.
4. None of the above

Show Answer

 

 

28) Which privilege is needed to create a Database?

1. createdb
2. dbcreate
3. Createdatabase
4. Databasecreate

Show Answer

 

 

29) which is the correct command to restore from the dump file created using PG_DUMP command

1. psql dbname > infile
2. psql dbname < infile
3. psql infile > dbname
4. psql infile < dbname

Show Answer

 

 

30) What will be the output of the below code?                    DECLARE

1. 200
2. 200 and 100
3. 100
4. this code has a syntax error , so there wont be any output

Show Answer

 

 

) a number(3) := 100;

Show Answer

 

 

) b number(3) := 200;

Show Answer

 

 

) BEGIN

Show Answer

 

 

) IF( a = 100 ) THEN

Show Answer

 

 

) IF( b <> 200 ) THEN

Show Answer

 

 

) dbms_output.put_line(b);

Show Answer

 

 

) END IF;

Show Answer

 

 

) END IF;

Show Answer

 

 

) dbms_output.put_line(a);

Show Answer

 

 

) END;

Show Answer

 

 

31) Which is NOT the valid languages in which Function can be implemented?

1. plpgsql
2. C
3. internal
4. cobol

Show Answer

 

 

32) which function can be used with any datatypes?

1. SUM
2. Avg
3. Count
4. None of the Above

Show Answer

 

 

33) which is a valid command to grant all privileges on a table COMPANY to the user “manisha”

1. GRANT ALL ON COMPANY TO manisha
2. GRANT manisha all on COMPANY
3. GRANT ALL COMPANY TO manisha
4. GRANT COMPANY TO manisha

Show Answer

 

 

34) when you issue the command “DROP USER manisha” , what message appears on the screen?

1. USER DROPPED
2. DROP ROLE
3. ROLE DROPPED
4. DROP USER

Show Answer

 

 

35) which is a valid command to revoke  all privileges on a table COMPANY  from the user “manisha”

1. REVOKE COMPANY FROM manisha
2. REVOKE ALL ON COMPANY TO manisha
3. REVOKE ALL ON COMPANY FROM manisha
4. REVOKE ALL COMPANY FROM manisha

Show Answer

 

 

36) Which Keyword should be used to grant permission to all users?

1. ALL
2. PUBLIC
3. EVERYONE
4. ALL USERS

Show Answer

 

 

37) Which is not a valid privilege?

1. INSERT
2. UPDATE
3. DELETE
4. None of the Above

Show Answer

 

 

38) SELECT CURRENT_TIME command returns?

1. Current time
2. Current time with timezone
3. Current timezone
4. This is not a valid command

Show Answer

 

 

39) SELECT CURRENT_DATE command returns?

1. Current date
2. Current date with timezone
3. Current date, time and timezone
4. This is not a valid command

Show Answer

 

 

40) SELECT date_part(‘day’, TIMESTAMP ‘2001-02-16 20:38:40’) returns?

1. 16-02-2001
2. 16
3. ‘2001-02-16 20:38:40’
4. This is not a valid command

Show Answer

 

 

41) Which command is used to back up all the databases in one single go?

1. pg_dump
2. pgdump
3. pg_dumpall
4. pgdumpall

Show Answer

 

 

42) Which type of index is designed for handling cases where the items to be indexed are composite values

1. B-TREE
2. HASH
3. GIST
4. GIN

Show Answer

 

 

43) In Indexes, GIST stands for _______________

1. Generalized search index
2. General search index
3. Generaized index
4. Geneal index

Show Answer

 

 

44) Which is the valid command for reindexing  an ITEMS table?

1. TABLE item REINDEX
2. TABLE REINDEX item
3. REINDEX TABLE item
4. REINDEX item

Show Answer

 

 

45) Which type of trigger is used on a View?

1. INSERT
2. UPDATE
3. DELETE
4. INSTEAD OF

Show Answer

 

 

46) Which type of trigger can only be defined a Row level trigger?

1. INSERT
2. UPDATE
3. DELETE
4. INSTEAD OF

Show Answer

 

 

47) In which scenario, FULL BACKUP is not recommended?

1. When dealing with a lot of data
2. When dealing with small amount of Data
3. When Periodical backup is needed
4. E

Show Answer

 

 

48) A table is defined as follows:

1. There are cases where this function returns multiple lines.
2. When NULL is passed for the argument and the function is executed, NULL is returned.
3. If a function with the same name and with type BOOLEAN as the parameter is already defined, an error occurs.
4. This function is defined using PL/pgSQL

Show Answer

 

 

) CREATE TABLE t (id INT, val TEXT);

Show Answer

 

 

) Select is correct statements from below about the function ‘get_head’ defined below.

Show Answer

 

 

) CREATE FUNCTION get_head(BOOLEAN)

Show Answer

 

 

) RETURNS TEXT LANGUAGE sql CALLED ON NULL INPUT

Show Answer

 

 

) AS ‘SELECT val FROM t WHERE $1 OR id > 0 ORDER BY id LIMIT 1;’;

Show Answer

 

 

49) I would like to restore the database cluster from the ‘db1.dump’ backup file.

1. pg_dump –restore db1 < db1.dump
2. pg_resetxlog -U postgres db1 < db1.dump
3. pg_restore -U postgres -f db1.dump db1
4. psql -U postgres -f db1.dump db1

Show Answer

 

 

) Select the correct command from below. (Note: ‘postgres’ is the superuser)

Show Answer

 

 

50) Select one option which cannot be specified using createdb.

1. Database locale
2. Host name
3. Character encoding
4. Database owner

Show Answer

 

 

51) Select the correct command to collect and save the statistical information of a table.

1. STATISTIC COLLECTION
2. REINDEX
3. STATISTIC COLLECTOR
4. ANALYZE

Show Answer

 

 

52) What does the following command do? Choose the most appropriate statement from the

1. Writes a backup of the database postgres to the file pgsql.
2. Writes a backup of the entire database cluster using user postgres to the file pgsql.
3. Writes a backup of the entire database cluster to the file postgres and writes an error message to the file pgsql.
4. Backs up the database postgres and writes an error message to the file pgsql.

Show Answer

 

 

) selection below.

Show Answer

 

 

)

Show Answer

 

 

) Note: $ is the command prompt.

Show Answer

 

 

) $ pg_dump postgres > pgsql

Show Answer

 

 

53) Select suitable statements regarding the pg_dump command.

1. pg_dump is an SQL command.
2. Only the user who executed initdb can execute pg_dump.
3. pg_dump can create a backup while postmaster is stopped.
4. pg_dump can create a backup of a specified table

Show Answer

 

 

54) PostgreSQL can use an index to access a table. Select an incorrect statements about indexes.

1. An index is created by ‘CREATE INDEX’, and deleted by ‘DROP INDEX’.
2. By using an index effectively, searching and sorting performs faster.
3. Creating an unused index does not affect the performance of a database at all.
4. There are B-tree, Hash, GIN and GiST index types.

Show Answer

 

 

55) What does the following command do? Select the correct description from below.

1. Collects statistical information related to the content of the database foo.
2. Collects statistical information related to the content of the table foo.
3. Collects statistical information related to the content of the database test.
4. Outputs statistical information related to the content of the table foo.

Show Answer

 

 

) Note: ‘text=#’ is the command prompt for psql.

Show Answer

 

 

) test=# ANALYZE foo;

Show Answer

 

 

56) I would like to check the privileges on the ‘items’ table in psql. Select the most appropriate

1. \t items
2. \p items
3. \d items
4. \z items

Show Answer

 

 

) command.

Show Answer

 

 

57) Select a correct SQL command to change existing user ‘george”s password to ‘michael’.

1. ALTER USER george CHANGE PASSWORD ‘michael’;
2. ALTER USER george SET PASSWORD ‘michael’;
3. ALTER USER george ALTER PASSWORD ‘michael’;
4. ALTER USER george WITH PASSWORD ‘michael’;

Show Answer

 

 

58) Select one incorrect statement about the command shown below.

1. If there is a database owned by foo, an error will occur.
2. If admin doesn’t have the superuser privilege, an error will occur.
3. The user admin is removing the user foo.
4. If admin is not the owner of foo, an error will occur.

Show Answer

 

 

) Note: $ is the command prompt.

Show Answer

 

 

) $ dropuser -U admin foo

Show Answer

 

 

59) It is possible to backup a database cluster by copying the entire data directory. Select suitable descriptions regarding this backup method

1. The backup data will be a text file consisting of SQL statements.
2. The database server must be stopped prior to the backup.
3. The ‘pg_restore’ command is used to restore the database.
4. psql’ is used to restore the database.

Show Answer

 

 

60) The following SQL defines an INSERT with respect to item_view.

1. RULE
2. TRIGGER
3. VIEW
4. FUNCTION

Show Answer