PostgreSQL Quiz

Posted on: | By: – Comments Off

 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
Answer : A

 

 

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
Answer : C

 

 

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
Answer : B

 

 

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
Answer : A

 

 

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
Answer : A

 

 

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
Answer : C

 

 

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
Answer : B

 

 

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.
Answer : D

 

 

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
Answer : C

 

 

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

  1. Import
  2. Imp
  3. Copy
  4. \Import
Answer : C

 

 

11) which catalog that stores data about all available databases

  1. Pg_database
  2. Pg_DB
  3. catalog_db
  4. postgres_db
Answer : A

 

 

12) which command shows all databases in the current folder

  1. \list
  2. \l
  3. \db
  4. \show
Answer : B

 

 

13) how to execute a function in Postgresql

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

 

 

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
Answer : A

 

 

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
Answer : B

 

 

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
Answer : D

 

 

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
Answer : A

 

 

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
Answer : D

 

 

19) NULLIF(20,10) returns

  1. NULL
  2. 20
  3. 10
  4. expression is invalid
Answer : B

 

 

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
Answer : B

 

 

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

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

 

 

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

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

 

 

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
Answer : A

 

 

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

  1. TRUE
  2. FALSE
Answer : B

 

 

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
Answer : A

 

 

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
Answer : C

 

 

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
Answer : B

 

 

28) Which privilege is needed to create a Database?

  1. createdb
  2. dbcreate
  3. Createdatabase
  4. Databasecreate
Answer : A

 

 

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
Answer : B

 

 

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
Answer : C

 

 

) a number(3) := 100;

Answer :

 

 

) b number(3) := 200;

Answer :

 

 

) BEGIN

Answer :

 

 

) IF( a = 100 ) THEN

Answer :

 

 

) IF( b <> 200 ) THEN

Answer :

 

 

) dbms_output.put_line(b);

Answer :

 

 

) END IF;

Answer :

 

 

) END IF;

Answer :

 

 

) dbms_output.put_line(a);

Answer :

 

 

) END;

Answer :

 

 

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

  1. plpgsql
  2. C
  3. internal
  4. cobol
Answer : D

 

 

32) which function can be used with any datatypes?

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

 

 

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
Answer : A

 

 

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
Answer : B

 

 

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
Answer : C

 

 

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

  1. ALL
  2. PUBLIC
  3. EVERYONE
  4. ALL USERS
Answer : B

 

 

37) Which is not a valid privilege?

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

 

 

38) SELECT CURRENT_TIME command returns?

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

 

 

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
Answer : A

 

 

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
Answer : B

 

 

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
Answer : C

 

 

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
Answer : D

 

 

43) In Indexes, GIST stands for _______________

  1. Generalized search index
  2. General search index
  3. Generaized index
  4. Geneal index
Answer : A

 

 

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
Answer : C

 

 

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

  1. INSERT
  2. UPDATE
  3. DELETE
  4. INSTEAD OF
Answer : D

 

 

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

  1. INSERT
  2. UPDATE
  3. DELETE
  4. INSTEAD OF
Answer : D

 

 

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
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
Answer : C

 

 

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

Answer :

 

 

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

Answer :

 

 

) CREATE FUNCTION get_head(BOOLEAN)

Answer :

 

 

) RETURNS TEXT LANGUAGE sql CALLED ON NULL INPUT

Answer :

 

 

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

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
Answer : D

 

 

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

Answer :

 

 

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

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

 

 

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
Answer : D

 

 

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.
Answer : A

 

 

) selection below.

Answer :

 

 

)

Answer :

 

 

) Note: $ is the command prompt.

Answer :

 

 

) $ pg_dump postgres > pgsql

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
Answer : D

 

 

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.
Answer : C

 

 

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.
Answer : B

 

 

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

Answer :

 

 

) test=# ANALYZE foo;

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
Answer : D

 

 

) command.

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’;
Answer : D

 

 

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.
Answer : D

 

 

) Note: $ is the command prompt.

Answer :

 

 

) $ dropuser -U admin foo

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.
Answer : B

 

 

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

  1. RULE
  2. TRIGGER
  3. VIEW
  4. FUNCTION
Answer : A

 



Related Articles

Advanced Manufacturing Process Quiz

Jenkins Quiz

Jenkins Quiz