> > PostgreSQL MCQs > Basic PostgreSQL MCQs

Basic PostgreSQL MCQ

1. When should sub queries be used?

To define the set of rows to be inserted in a table

To define the set of rows to be included in a view

To define one or more values to be assigned to existing rows

To provide values for conditions in the Where clause

All of the above are correct

Answer

Correct Answer: To define the set of rows to be included in a view

Note: This Question is unanswered, help us to find answer for this one

2. Can you define variables in PostgreSQL pl/pgSQL whose value cannot be null?

No, you cannot

Yes, you can use 'NOT NULL' after datatype

Yes, you can use 'NON NULLABLE' after datatype

Yes, that is the default case, for setting it to hold null values 'NULLABLE' is used after datatype

Answer

Correct Answer: Yes, you can use 'NOT NULL' after datatype

Note: This Question is unanswered, help us to find answer for this one

3. Which of the following holds true for functions in PostgreSQL?

The parameters are positional

Functions may be overloaded

A variation of the SQL99 CREATE FUNCTION command is supported

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

4. Which of the following is not defined in the PL/pgSQL?

%Type

%Coltype

%Rowtype

ALIAS

Answer

Correct Answer: %Coltype

Note: This Question is unanswered, help us to find answer for this one

5. What is a rollback of transactions normally used for?

To recover from the transaction failure

To update the transaction

To retrieve old records

None of the above

Answer

Correct Answer: To recover from the transaction failure

Note: This Question is unanswered, help us to find answer for this one

6. Which character function should be used to return a specified portion of a character string?

CONCAT

LENGTH

SUBSTR

INITCAP

Answer

Correct Answer: SUBSTR

Note: This Question is unanswered, help us to find answer for this one

7.
What do you infer from the following declarations?
1. arrayA type[]
2. arrayB type[][]

arrayA is a single dimension array variable

arrayB is a multi dimension array variable

arrayB declaration is not valid

Answer

Correct Answer:

arrayA is a single dimension array variable

arrayB is a multi dimension array variable

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

8. Which of the following help PostgreSQL avoid unnecessary locking of records?

Multi Lock

MCVR

MVCC

MVSQL

Answer

Correct Answer: MVCC

Note: This Question is unanswered, help us to find answer for this one

9. Which of the following trigger function variables is not defined?

TG_NARGS

NEW

TG_NAME

TG_WHERE

OLD

None of the above

Answer

Correct Answer: TG_WHERE

Note: This Question is unanswered, help us to find answer for this one

10.
Will the following function compile and execute?
DECLARE
intValue int4;
BEGIN
intValue := 20 * 20;
return intValue;
END;

It will not compile

It will compile but will give a runtime error

It will compile but will not produce any output

It will compile and will produce 400 as output

Answer

Correct Answer:

It will compile and will produce 400 as output

Note: This Question is unanswered, help us to find answer for this one

11. If entity x is existence-dependent on entity y, then what is x called?

Dominant entity

Subordinate entity

Primary entity

Secondary entity

Answer

Correct Answer: Subordinate entity

Note: This Question is unanswered, help us to find answer for this one

12.
You want to manipulate some value in the database upon updation in a trigger function. Which line of the code would be appropriate?

IF TG_RELID = ''UPDATE'' THEN

--Modify the database value

END IF;

IF TG_NAME = ''UPDATE'' THEN

--Modify the database value

END IF;

IF TG_OP = ''UPDATE'' THEN

--Modify the database value

END IF;

IF TG_ARGV = ''UPDATE'' THEN

--Modify the database value

END IF;

Answer

Correct Answer:

IF TG_OP = ''UPDATE'' THEN

--Modify the database value

END IF;

Note: This Question is unanswered, help us to find answer for this one

13.
Consider the following structure of the students table:
rollno number(4)
name varchar(20)
course varchar(20)
What will be the query to display the courses in which the number of students enrolled is more than 5?

Select course from students where count(course) > 5;

Select course from students where count(*) > 5 group by course;

Select course from students group by course;

Select course from students group by course having count(*) > 5;

Answer

Correct Answer:

Select course from students group by course having count(*) > 5;

Note: This Question is unanswered, help us to find answer for this one

14. Which of the following files controls the host based authentication in PostgreSQL?

pg_host_auth.conf

pghba.conf

pg_hba.conf

pghostauth.conf

Answer

Correct Answer: pg_hba.conf

Note: This Question is unanswered, help us to find answer for this one

15. Which of the following statements are incorrect regarding referential integrity?

A foreign key can refer to a primary key

A foreign key can refer to a unique key

The on delete cascade clause will work only if there is a reference to a primary key

The referred key can either be in the same table or in some other table

A foreign key can be composite

Referential integrity can only be applied while creating the table

Answer

Correct Answer: The on delete cascade clause will work only if there is a reference to a primary key
Referential integrity can only be applied while creating the table

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

16. Which of the following statements are true for ordinary views?

A view is stored in the data dictionary

Views provide a more secure way of data retrieval

Views are actual tables and store data at another location

All of the above are true

Answer

Correct Answer: A view is stored in the data dictionary
Views provide a more secure way of data retrieval

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

17. Which of the following variable declarations is not correct?

cust_id INTEGER;

meter_name VARCHAR(30);

net_amt FLOAT(2);

update_time timestamp;

Answer

Correct Answer: net_amt FLOAT(2);

Note: This Question is unanswered, help us to find answer for this one

18. You have defined a function "Calculate()" in the template1 database. What will happen when you create a new database "ManagementDB" there?

The function will be moved to the new database

The function will be cloned to the new database

The function will remain with the template database only

Any of the above can happen

Answer

Correct Answer: The function will be cloned to the new database

Note: This Question is unanswered, help us to find answer for this one

19. Which of the following values is not defined to indicate the level of the raise event?

DEBUG

ERROR

NOTICE

EXCEPTION

Answer

Correct Answer: ERROR

Note: This Question is unanswered, help us to find answer for this one

20. Choose the correct statements for a trigger function:

It is created using the CREATE FUNCTION command

It should be defined as accepting single arguments

It returns a value of integer datatype

It returns a value of triger or opaque datatype

Answer

Correct Answer: It is created using the CREATE FUNCTION command
It returns a value of triger or opaque datatype

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

21. Which of the following are valid in a declare block?

intCustomerId int4 := 67;

intCustomerId int4 = 67;

intCustomerId int4;

intCustomerId:= 67

Answer

Correct Answer: intCustomerId int4 := 67;
intCustomerId int4;

intCustomerId int4;

intCustomerId:= 67

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

22. Which of the following clauses are not allowed in a single row sub-query?

From

Where

Group by

Having

Order by

None of above

Answer

Correct Answer: None of above

Note: This Question is unanswered, help us to find answer for this one

23. Which of the following functionalities is supported by the pg_ctl script?

status

start

stop

restart

invoke

all of the above

Answer

Correct Answer: status
start
stop
restart

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

24. There are two tables A and B. You are retrieving data from both tables where all rows from table B and only matching rows from table A should be displayed. Which type of join will you apply between tables A and B?

Inner join

Left outer join

Right outer join

Self join

Answer

Correct Answer: Right outer join

Note: This Question is unanswered, help us to find answer for this one

25. Every Boyce-Codd Normal Form(BCNF) is in:

First Normal Form

Second Normal Form

3.5 Normal Form

None of the above

Answer

Correct Answer: 3.5 Normal Form

Note: This Question is unanswered, help us to find answer for this one

26. Which of the following geometric types is not defined in PostgreSQL?

path

box

hexagon

polygon

Answer

Correct Answer: hexagon

Note: This Question is unanswered, help us to find answer for this one

27. Which of the following can be used to uniquely identify a row?

Primary Key

Unique Key

Foreign Key

All of the above

Answer

Correct Answer: Primary Key

Note: This Question is unanswered, help us to find answer for this one

28. Which of the following copy commands will work in PostgreSQL?

COPY

\copy

COPY .. TO

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

29. Data validation can be implemented at the definition stage through:

Check constraints with specified values

Referential constraints, by creating a foreign key for another table

Default value of a column

Null constraints

Answer

Correct Answer: Check constraints with specified values
Default value of a column

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

30. Which of the following date and time constants are not defined in PostgreSQL?

today

current

epoch

current_day

yesterday

Answer

Correct Answer: current_day

Note: This Question is unanswered, help us to find answer for this one

31. Which of the following cannot be used as the operator name while creating a new operator?

! @ # %

+ - * /

< > = ~

^ & | `

? $

None of the above

Answer

Correct Answer: None of the above

Note: This Question is unanswered, help us to find answer for this one

32.
The names of those departments where there are more than 100 employees have to be displayed. Given two relations, employees and departments, which query should be used?
Employee
---------
Empno
Employeename
Salary
Deptno
Department
---------
Deptno
Departname

Select departname from department where deptno in (select deptno

from employee group by deptno having count(*) > 100);

Select departname from department where deptno in (select count(*)

from employee group by deptno where count(*) > 100);

Select departname from department where count(deptno) > 100;

Select departname from department where deptno in (select count(*)

from employee where count(*) > 100);

Answer

Correct Answer:

Select departname from department where deptno in (select deptno

from employee group by deptno having count(*) > 100);

Note: This Question is unanswered, help us to find answer for this one

33. Which of the following statements are correct with regard to PostgreSQL?

PostgreSQL does not support embedded procedural language

PostgreSQL supports native language PL/pgSQL

PostgreSQL supports the core SQL99 specification

PostgreSQL supports SQL92 joins

Answer

Correct Answer: PostgreSQL supports native language PL/pgSQL
PostgreSQL supports SQL92 joins

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

34. In which order are primary queries and their sub-queries interpreted:

prime query followed by sub query followed by sub sub query and so on

lowest sub query followed by sub query followed by prime query

The whole query is interpreted at one time

There is no fixed sequence of interpretation of a query

Answer

Correct Answer: lowest sub query followed by sub query followed by prime query

Note: This Question is unanswered, help us to find answer for this one

35. Select the appropriate query for the Products table when data should be primarily ordered by ProductGroup. ProductGroup should be displayed in ascending order and CurrentStock should be in descending order:

Select * from Products order by CurrentStock,ProductGroup

Select * from Products order by CurrentStock DESC,ProductGroup

Select * from Products order by ProductGroup,CurrentStock

Select * from Products order by ProductGroup,CurrentStock DESC

None of the above

Answer

Correct Answer: Select * from Products order by ProductGroup,CurrentStock DESC

Note: This Question is unanswered, help us to find answer for this one

36. Which of the following programming structures is not available in PL/pgSQL?

IF THEN ELSE

LOOP

WHILE

DO WHILE

FOR

Answer

Correct Answer: DO WHILE

Note: This Question is unanswered, help us to find answer for this one

37.
Food Cart Accounting System (FOCAS) is maintaining products in the products table, and wants to see the products which are 50 or more numbers far from the minimum stock limit. The structure of the Products table is:
ProductID
ProductName
CurrentStock
MinimumStock
Two possible queries are:
Statement 1: select * from products where currentStock>MinimumStock+50
Statement 2: select * from products where currentStock-50>MinimumStock
Select an option which is more suitable for these queries:

Only statement 1 is correct

Only statement 2 is correct

Both statements 1 and 2 are correct

Both statements 1 and 2 are incorrect

Answer

Correct Answer:

Both statements 1 and 2 are correct

Note: This Question is unanswered, help us to find answer for this one

38. Which of the following are supported by PostgreSQL?

Multi version concurrency control transactions

Multi-user support

Declarative SQL queries

Query optimization

Answer

Correct Answer: Multi version concurrency control transactions
Multi-user support
Declarative SQL queries
Query optimization

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

39. What is the first step for manually installing PL/pgSQL in PostgreSQL?

Use 'CREATE FUNCTION' to create the procedural call handler

Use 'CREATE LANGUAGE' SQL command

Either one of the above can be used

Answer

Correct Answer: Use 'CREATE FUNCTION' to create the procedural call handler

Note: This Question is unanswered, help us to find answer for this one

40.
A handler named 'plpgsqlHandle()' is created for installing PL/pgSQL. For creating the handler on 'SwineDB' database you would issue a command:

SwineDB=# CREATE LANGUAGE 'pl/pgsql' HANDLER plpgsqlHandle

SwineDB-# LANCOMPILER 'Install PL/pgSQL';

CREATE

SwineDB=# CREATE LANGUAGE 'pl/pgsql' EXECUTE plpgsqlHandle

SwineDB-# LANCOMPILER 'Install PL/pgSQL';

CREATE

SwineDB=# CREATE LANGUAGE 'plpgsql' HANDLER plpgsqlHandle

SwineDB-# LANCOMPILER 'Install PL/pgSQL';

CREATE

SwineDB=# CREATE LANGUAGE 'plpgsql' EXECUTE plpgsqlHandle

SwineDB-# LANCOMPILER 'Install PL/pgSQL';

CREATE

Answer

Correct Answer:

SwineDB=# CREATE LANGUAGE 'plpgsql' HANDLER plpgsqlHandle

SwineDB-# LANCOMPILER 'Install PL/pgSQL';

CREATE

Note: This Question is unanswered, help us to find answer for this one

41. Perfect Services provides financial services. You need to display data from the pers table for joining_date from '1/1/2005' to '31/12/2005' and the job should be for Analyst or Clerk or Salesman. Which select statement will you use?

select * from Pers where joining_date from '1/1/2005' to '31/12/2005', job='Analyst' or 'Clerk' or 'Salesman'

select * from Pers where joining_date between '1/1/2005' to '31/12/2005', job='Analyst' or job='Clerk' or job='Salesman'

select * from Pers where joining_date between '1/1/2005' and '31/12/2005' and (job='Analyst' or job='Clerk' or job='Salesman')

None of the above

Answer

Correct Answer: select * from Pers where joining_date between '1/1/2005' and '31/12/2005' and (job='Analyst' or job='Clerk' or job='Salesman')

Note: This Question is unanswered, help us to find answer for this one

42. Which of the following keywords can be used to ignore the return data of a function?

NORETURN

PERFORM

IGNORE

There is no such keyword

Answer

Correct Answer: PERFORM

Note: This Question is unanswered, help us to find answer for this one

43. Which of the following statements is correct with regard to PostgreSQL?

It is an Object Oriented Database Management System

It is an Object Relational Database Management System

It is a Relational Database Management System

It is an Object Based Database Management System

Answer

Correct Answer: It is an Object Relational Database Management System

Note: This Question is unanswered, help us to find answer for this one

44. How can data be accessed by users who do not have direct access to the tables?

By creating views

By creating triggers

By creating stored procedures

None of the above

Answer

Correct Answer: By creating views
By creating stored procedures

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

45. Which of the following statements is not correct about creating a new operator?

Name and functionname clause must be specified

LEFTARG or RIGHTARG must be defined

Overloading is not possible in operators

COMMUTATOR attribute must be specified

Answer

Correct Answer: Name and functionname clause must be specified
COMMUTATOR attribute must be specified

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

46. In which of the following ways can a value in an array column be modified?

Element modification

Columnar modification

Slice modification

Complete modification

Answer

Correct Answer: Element modification
Slice modification
Complete modification

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

47.
What is the correct order of clauses in the select statement?
1 select
2 order by
3 where
4 having
5 group by

1,2,3,4,5

1,3,5,4,2

1,3,5,2,4

1,3,2,5,4

1,3,2,4,5

1,5,2,3,4

1,4,2,3,5

1,4,3,2,5

Answer

Correct Answer:

1,3,5,4,2

Note: This Question is unanswered, help us to find answer for this one

48.
Which of the following holds true when creating the function for a PL/pgSQL call handler, and installing PL/pgSQL in the default directory?

CREATE FUNCTION plpgsqlHandle()

RETURNS PLQUE

AS '/usr/local/pgsql/lib/plpgsql.so'

LANGUAGE 'C';

CREATE FUNCTION plpgsqlHandle()

RETURNS PLQUE

AS '/pgsql/lib/plpgsql.so'

LANGUAGE 'C';

CREATE FUNCTION plpgsqlHandle()

RETURNS OPAQUE

AS '/pgsql/lib/plpgsql.so'

LANGUAGE 'C';

CREATE FUNCTION plpgsqlHandle()

RETURNS OPAQUE

AS '/usr/local/pgsql/lib/plpgsql.so'

LANGUAGE 'C';

Answer

Correct Answer:

CREATE FUNCTION plpgsqlHandle()

RETURNS OPAQUE

AS '/usr/local/pgsql/lib/plpgsql.so'

LANGUAGE 'C';

Note: This Question is unanswered, help us to find answer for this one

49. Which clause should be used to display the rows of a table in ascending order of a particular column?

Where

Order By

Group By

Having

First Group By and then Having

Between

Answer

Correct Answer: Order By

Note: This Question is unanswered, help us to find answer for this one

50. A wholesale merchant shop needs a report about the sale where total sale of the day is more than $50,000. Which of the following will fulfill this requirement?

select * from orders where sum(amount) > 50000

select * from orders where sum(amount) > 50000 order by OrderDate

select * from orders group by OrderDate where sum(amount) > 50000

select * from orders group by OrderDate having sum(amount)>50000

None of above

Answer

Correct Answer: None of above

Note: This Question is unanswered, help us to find answer for this one

51.
What is wrong in this query:
Select * from Orders where OrderID=(select OrderID from OrderItems where ItemQty>50)

In a sub query, * should be used instead of OrderID

The sub query can return more than one row, so, = should be replaced with in

The sub query should be used first

None of the above

Answer

Correct Answer:

The sub query can return more than one row, so, = should be replaced with in

Note: This Question is unanswered, help us to find answer for this one

52.
What do you understand by the following PL/pgSQL declaration?
c_phone customer.contact_no%TYPE;

This is declaring a new variable c_phone with undefined type

This is declaring a new variable c_phone with same type as contact_no column

This is declaring a new variable c_phone with variable data type

None of the above is correct

Answer

Correct Answer:

This is declaring a new variable c_phone with same type as contact_no column

Note: This Question is unanswered, help us to find answer for this one

53. Which of the following are not DCL operations?

Insert

Grant

Delete

Update

Revoke

Commit

Rollback

Answer

Correct Answer: Insert
Delete
Update

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

54.
What do you infer from the following two lines?
1. host all 192.168.1.10 255.255.255.255 reject
2. host all 127.0.0.1 255.255.255.255 trust

Line 1 is a valid host based client config entry

Line 2 is a valid host based client config entry

Line 1 and Line 2 both are valid host based client config entries

Both Line 1 and Line 2 are invalid host based client config entries

Answer

Correct Answer:

Line 1 and Line 2 both are valid host based client config entries

Note: This Question is unanswered, help us to find answer for this one

55. Point out the incorrect statement regarding group functions:

Group functions act on a group of rows

Group functions return one result for all the rows operated upon

Group functions ignore the null values

Stddev and variance are examples of group functions

One cannot combine group and single value functions in a query

Sum is not a group function

Answer

Correct Answer: Group functions act on a group of rows
Group functions return one result for all the rows operated upon
Group functions ignore the null values

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

56. Which of the following is true about PostgreSQL clients?

The graphical client application is psql

The command line driven client is PgAccess

psql is the command line PostgreSQL client and typically installed by default

PgAccess installation requires specification of the with-pgtcl option

Answer

Correct Answer: psql is the command line PostgreSQL client and typically installed by default

Note: This Question is unanswered, help us to find answer for this one

57. Which of the following holds true when you have defined a function with "isstrict" attribute?

This will allow you to pre-evaluate a call to a function

This will not allow you to pre-evaluate a call to a function if one of its arguments is NULL

The function will always return a NULL value if more than one of its arguments is NULL

The function will always return a NULL value whenever any of its arguments is NULL

Answer

Correct Answer: The function will always return a NULL value whenever any of its arguments is NULL

Note: This Question is unanswered, help us to find answer for this one

58.
What would happen on compiling and running the following function?
CREATE FUNCTION loop_function (integer) RETURNS integer AS '
DECLARE
result integer;
BEGIN
result := $1;
LOOP
result := result * result;
EXIT WHEN result >= 15000;
END LOOP;
RETURN result;
END;
' LANGUAGE 'plpgsql';

The function will not compile as the for loop defined is not syntactically correct

The function will not compile if the return parameter defined is not correct

The function will compile but will not give any output

The function will compile and will produce numeric output on running

Answer

Correct Answer:

The function will compile and will produce numeric output on running

Note: This Question is unanswered, help us to find answer for this one

59.
You want to update the last modified timestamp in the orders table. The correct way to do this in a PL/pgSQL function, when the parameter integer order_id is passed, would be:

DECLARE

order_id ALIAS FOR $1;

mytimestamp timestamp;

BEGIN

mytimestamp := ''now'';

UPDATE orders SET orderid=order_id, lastmodified = mytimestamp;

RETURN mytimestamp;

END;

' LANGUAGE 'plpgsql';

DECLARE

order_id ALIAS FOR $1;

mytimestamp timestamp;

BEGIN

mytimestamp := 'now';

UPDATE orders SET orderid=order_id, lastmodified = mytimestamp;

RETURN mytimestamp;

END;

' LANGUAGE 'plpgsql';

DECLARE

order_id ALIAS FOR $1;

BEGIN

mytimestamp := ''now'';

UPDATE orders SET orderid=order_id, lastmodified = ''now'';

RETURN mytimestamp;

END;

' LANGUAGE 'plpgsql';

DECLARE

order_id ALIAS FOR $1;

BEGIN

mytimestamp := ''now'';

UPDATE orders SET orderid=order_id, lastmodified = 'now';

RETURN mytimestamp;

END;

' LANGUAGE 'plpgsql';

Answer

Correct Answer:

DECLARE

order_id ALIAS FOR $1;

mytimestamp timestamp;

BEGIN

mytimestamp := ''now'';

UPDATE orders SET orderid=order_id, lastmodified = mytimestamp;

RETURN mytimestamp;

END;

' LANGUAGE 'plpgsql';

Note: This Question is unanswered, help us to find answer for this one

60. Which of the following statements regarding ordinary views are incorrect?

A view is like a window through which data in tables can be viewed or changed

A view is derived from another table

A view cannot be derived from another view

A view is stored as a select statement only

A view has no data of its own

A view is another name for a table

Answer

Correct Answer: A view is like a window through which data in tables can be viewed or changed
A view is stored as a select statement only

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

61. Which of the following security features is inbuilt in PostgreSQL?

Stunnel

SSL on compiling with -with-ssl

SSH/OpenSSH

None of the above

Answer

Correct Answer: SSL on compiling with -with-ssl

Note: This Question is unanswered, help us to find answer for this one

62. Choose the correct statement:

All PL/pgSQL expressions in a function, except dynamic queries, are only prepared once during the lifetime of the PostgreSQL backend process

All PL/pgSQL expressions in a function are only prepared once during the lifetime of the PostgreSQL backend process

All PL/pgSQL expressions in a function are prepared for each execution time during the lifetime of the PostgreSQL backend process

None of the above is correct

Answer

Correct Answer: All PL/pgSQL expressions in a function, except dynamic queries, are only prepared once during the lifetime of the PostgreSQL backend process

Note: This Question is unanswered, help us to find answer for this one

63. Which of the following options can be used to create and initialize a new database cluster within your file system?

Initdbcluster

createcluster

initdb

createdb_cluster

Answer

Correct Answer: initdb

Note: This Question is unanswered, help us to find answer for this one

64.
What does the pg_dump command do?
pg_dump CustomerDatabase

It copies the database to specified location

It creates a list of SQL commands used to create db from scratch

None of the above

Answer

Correct Answer:

It creates a list of SQL commands used to create db from scratch

Note: This Question is unanswered, help us to find answer for this one

65. What does the RAISE statement do in PL/pgSQL?

It logs the errors and messages to the logging utility

It displays the errors and messages to stderr

Both of the above

None of the above

Answer

Correct Answer: Both of the above

Note: This Question is unanswered, help us to find answer for this one

66. Which of the following techniques can be used to obtain a result which is based on comparing one row of a table with another row of the same table?

Equi-join

Inner Join

Outer Join

Self Join

Correlated Subquery

Answer

Correct Answer: Self Join
Correlated Subquery

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

67. Which of the following statements is correct?

PostgreSQL is written in C

PostgreSQL can dynamically load compiled code for C on the fly

All the users are allowed to use CREATE FUNCTION to link to a C function

All of the above

Answer

Correct Answer: PostgreSQL is written in C
PostgreSQL can dynamically load compiled code for C on the fly

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

68.
What does the following update statement do?
Update OrderTable set OrderDiscount=OrderDiscount*1.10

It increases OrderDiscount of the first row by 10%

It increases OrderDiscount of all rows by 10%

It does nothing without the where clause

It generates an error due to the lack of a where clause

Answer

Correct Answer:

It increases OrderDiscount of all rows by 10%

Note: This Question is unanswered, help us to find answer for this one

69. Which of the following is correct regarding VACUUM?

It deletes the temporary data and recovers the disk space

The table identifier must be provided

A VACUUM statement without arguments will clean up each table in the presently connected database

All of the above

Answer

Correct Answer: It deletes the temporary data and recovers the disk space
A VACUUM statement without arguments will clean up each table in the presently connected database

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

70. For which of the following languages does PostgreSQL provide an API interface?

Python and Perl

C/C++ and Java

PHP and Ruby

Small talk

Answer

Correct Answer: Python and Perl
C/C++ and Java
PHP and Ruby

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

71. The primary key indexing technique does not allow:

Duplicate data in a field

Multiple attributes

Sets of relations

Many to Many relation

Answer

Correct Answer: Duplicate data in a field

Note: This Question is unanswered, help us to find answer for this one

72. Which of the following functions is not available in PostgreSQL?

intfrombit

bittoint4

to_number

to_timestamp

Answer

Correct Answer: intfrombit

Note: This Question is unanswered, help us to find answer for this one

73. Which statements hold true for Partition Tables?

A table may be split into several independent pieces

All pieces must have the same size

A partition may be analyzed and exported

A partition cannot be truncated

When one partition goes offline, other partitions still remain available

Answer

Correct Answer: A table may be split into several independent pieces
A partition may be analyzed and exported

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

74. Which of the following is not an SQL operator?

Between .. and ..

Is null

Having

Not in

Answer

Correct Answer: Having

Note: This Question is unanswered, help us to find answer for this one

75. Which component of a DBMS verifies the syntax of the users query?

Parser

The database manager

Query optimization

Database administrator

Answer

Correct Answer: Parser

Note: This Question is unanswered, help us to find answer for this one

76. Is function overloading available in PL/pgSQL?

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

77. What is a cluster?

A group of users

A group of indexes

A method that reorganizes the table on the disc to increase performance

A compressed datafile

A segment of a partitioned table

A group of constraints

A group of databases

A group of roles

Answer

Correct Answer: A method that reorganizes the table on the disc to increase performance

Note: This Question is unanswered, help us to find answer for this one

78. Which of the following is not true regarding single row functions?

They act on each row returned in the query

They return one result per row

They expect one or more user arguments

They cannot be nested

They can be used in the where and order by clauses

Answer

Correct Answer: They cannot be nested

Note: This Question is unanswered, help us to find answer for this one

79.
Examine the following query:
Create table Person
(EmpNo Number(4) not null,
EName Char not null,
Join_dt Date not null,
Pay Number)
Which of the following field(s) are created correctly?

EmpNo

EName

Join_dt

Pay

Answer

Correct Answer:

EName

Join_dt

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

80. Normalization divides tables in a more useful and meaningful manner. Which statement is correct for the FIRST NORMAL FORM?

It includes only tables that do not have composite primary keys

It must have data stored in a two-dimensional table with no repeating groups

Every non-key column is non transitively dependent upon its primary key

None of the above

Answer

Correct Answer: It must have data stored in a two-dimensional table with no repeating groups

Note: This Question is unanswered, help us to find answer for this one

81. A PL/pgSQL code block is defined with DECLARE, BEGIN and END. How many such sub blocks can be nested within a block?

None

Unlimited

Answer

Correct Answer: Unlimited

Note: This Question is unanswered, help us to find answer for this one

82. Which of the following date function(s) are invalid?

NEXT_DAY

NEXT_MONTH

MONTHS_BETWEEN

DAYS_BETWEEN

None of above

Answer

Correct Answer: None of above

Note: This Question is unanswered, help us to find answer for this one

83. Choose the correct statement regarding WAL in PostgreSQL:

It increases the reliability of the database

It logs the entries to the database automatically

It is detrimental for database performance

There is no such term defined in PostgreSQL

Answer

Correct Answer: It increases the reliability of the database

Note: This Question is unanswered, help us to find answer for this one

84. Which of the following holds true if you have installed PL/pgSQL in the PgDatabase?

For all the subsequent databases that are created with PgDatabase as their template, the user must install PL/pgSQL

All the subsequent databases that are created with PgDatabase as their template, will have PL/pgSQL installed

For all the subsequent databases that are created with PgDatabase as their template, the user may or may not install PL/pgSQL

None of the above are correct

Answer

Correct Answer: All the subsequent databases that are created with PgDatabase as their template, will have PL/pgSQL installed

Note: This Question is unanswered, help us to find answer for this one

85. Which of the following is correct for the postmaster -n debugging option?

It stops the postmaster from re-initializing shared data structures

It allows a developer to collect a core dump from each backend process manually

It causes the postmaster to use the SIGSTOP signal to stop backend processes

None of the above

Answer

Correct Answer: It stops the postmaster from re-initializing shared data structures

Note: This Question is unanswered, help us to find answer for this one

86. What is the default location of the standard elog?

/var/log/messages

$PGDATA/serverlog

Any of the above

None of the above

Answer

Correct Answer: None of the above

Note: This Question is unanswered, help us to find answer for this one

87. What is the default variable for the PROMPT3?

'>>> '

'>> '

'%/%R%# '

Answer

Correct Answer: '>> '

Note: This Question is unanswered, help us to find answer for this one

88. Which of the following is correct with regard to Password Authentication?

pg_shadow table stores the password as plain text

pg_user table stores the password as encrypted text

Only superusers have access to the system password storing table

All of the above

Answer

Correct Answer: pg_shadow table stores the password as plain text
Only superusers have access to the system password storing table

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

89.
A steel production company has two sales outlets. Both outlets are maintaining their data separately in servers SVA and SVD. Both outlets use the same structure for the Sales table. Which method will you use to create a combined sales report for both the outlets?

Select * from SVA.Sales join SVD.Sales

Select * from SVA.Sales union all SVD.Sales

select * from SVA.Sales,SVD.Sales

None of the above

Answer

Correct Answer:

Select * from SVA.Sales union all SVD.Sales

Note: This Question is unanswered, help us to find answer for this one

90. PostgreSQL triggers can be written in C directly.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: SELECT * FROM tab ORDER BY random() LIMIT 1;

Note: This Question is unanswered, help us to find answer for this one

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

psql -f filename dbname

psql -Fc filename dbname

pg_restore filename dbname

pg_restore -d dbname filename

None of the above

Answer

Correct Answer:

psql -f filename dbname

Note: This Question is unanswered, help us to find answer for this one

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

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

By ID

By insertion date

By name

The ordering is unknown if not specified

None of the above

Answer

Correct Answer: The ordering is unknown if not specified

Note: This Question is unanswered, help us to find answer for this one

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

Java

PHP

Ruby

Perl

Answer

Correct Answer: C

Note: This Question is unanswered, help us to find answer for this one

96. What is the ~ operator?

POSIX regular expression match operator

XOR operator

NOT operator

Home directory ENV variable

None of the above

Answer

Correct Answer: POSIX regular expression match operator

Note: This Question is unanswered, help us to find answer for this one

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

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.

Answer

Correct Answer: 4 numeric values with up to 3 digits to the right of the decimal point.

Note: This Question is unanswered, help us to find answer for this one

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

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;

Answer

Correct Answer: UPDATE ONLY store_products SET name = 'Wine' WHERE id = 2;

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: File synchronization will be deactivated

Note: This Question is unanswered, help us to find answer for this one

100. Does PostgreSQL support SSL?

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

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

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;

Answer

Correct Answer: SELECT array_dims(products) FROM store_products;

Note: This Question is unanswered, help us to find answer for this one

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

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;

Answer

Correct Answer: SELECT * INTO products_backup FROM special_products;

Note: This Question is unanswered, help us to find answer for this one

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

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;

Answer

Correct Answer: CREATE TABLE special_products (quality int) INHERITS store_products;

Note: This Question is unanswered, help us to find answer for this one

104. How will you rank text search results?

With the ORDER BY operator

With the ts_rank function

Search results are automatically ranked

Search results cannot be ranked

None of the above

Answer

Correct Answer: With the ts_rank function

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: Set superuser_reserved_connections in postgresql.conf

Note: This Question is unanswered, help us to find answer for this one

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

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]);

Answer

Correct Answer: CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[][]);

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer:

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

Note: This Question is unanswered, help us to find answer for this one

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

zero

noon

midnight

allballs

None of the above

Answer

Correct Answer: None of the above

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: create table foo (bar integer[]);

Note: This Question is unanswered, help us to find answer for this one

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

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

112.
SELECT !!3;
What output will this statement give?

true

The statement is invalid.

None of the above.

Answer

Correct Answer:

Note: This Question is unanswered, help us to find answer for this one

113.
SELECT rtrim('foobar', 'abr');
The result of this statement is foo.

True

False

Answer

Correct Answer:

True

Note: This Question is unanswered, help us to find answer for this one

114. Which kind of index can be declared unique?

Hash

A-tree

B-tree

GIN

GiST

Answer

Correct Answer: Hash

Note: This Question is unanswered, help us to find answer for this one

115. What i s the difference between tokens and lexemes?

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

Answer

Correct Answer: A lexeme is a string while a token is an intege

Note: This Question is unanswered, help us to find answer for this one

116.
The following statement will retrieve the second element of the array column products in table store_products.
SELECT products[1] FROM store_products;

True

False

Answer

Correct Answer:

True

Note: This Question is unanswered, help us to find answer for this one

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

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

SELECT now()::timestamp;

SELECT now()::int;

SELECT now()::varchar;

SELECT now()::char;

None

Answer

Correct Answer: SELECT now()::int;

Note: This Question is unanswered, help us to find answer for this one

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

pg_restore file.dmp

psql -f file.dmp

pg_restoreall file.dmp

postgre --restore -f file.dmp

None of the above

Answer

Correct Answer: psql -f file.dmp

Note: This Question is unanswered, help us to find answer for this one

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

Lexemes

Tokens

Integers

Sets

Enum

Answer

Correct Answer: Lexemes

Note: This Question is unanswered, help us to find answer for this one

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

None of the above

Answer

Correct Answer: *

Note: This Question is unanswered, help us to find answer for this one

122. Which PostgreSQL version added the enum datatype?

8.0

8.1

7.4

8.3

PostgreSQL doesn't have an enum type.

Answer

Correct Answer: 8.0

Note: This Question is unanswered, help us to find answer for this one

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

ts_highlight

ts_tag

ts_headline

ts_select

None of the above

Answer

Correct Answer: ts_highlight

Note: This Question is unanswered, help us to find answer for this one

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

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

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

8bytes

2bytes

16bytes

4bytes

64bytes

Answer

Correct Answer: 4bytes

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

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

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: None of the above

Note: This Question is unanswered, help us to find answer for this one

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

to_tsvecto

plainto_tsquery

to_tsquery

strip

ts_rewrite

plainto_tsvecto

Answer

Correct Answer: plainto_tsvecto

Note: This Question is unanswered, help us to find answer for this one

129. What is true regarding file system backup?

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

Answer

Correct Answer: It can only be used to backup the whole database

Note: This Question is unanswered, help us to find answer for this one

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

5000

541

5432

None of the above

Answer

Correct Answer: 5432

Note: This Question is unanswered, help us to find answer for this one

131.
Consider the following query:
Create table foo (bar varchar);
What will be the size limit of the bar?

256

1024

No limit (It will be equivalent to the text)

None of the above

Answer

Correct Answer:

No limit (It will be equivalent to the text)

Note: This Question is unanswered, help us to find answer for this one

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

select * from pg_functions;

\df

\? functions

select * from pg_procedures;

None of the above

Answer

Correct Answer: \df

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: create table foo (id serial primary key, name varchar(255));

Note: This Question is unanswered, help us to find answer for this one

PostgreSQL Subjects

More Resources

PostgreSQL MCQ

PostgreSQL Skill Assessment

PostgreSQL PDF

Related MCQs

Programming Aptitude MCQs

Programming Fundamental and Principle MCQs

Programming Languages MCQs

Programming Logic and Design MCQs

Programming with C MCQs

Basic PostgreSQL MCQ

1. When should sub queries be used?

2. Can you define variables in PostgreSQL pl/pgSQL whose value cannot be null?

3. Which of the following holds true for functions in PostgreSQL?

4. Which of the following is not defined in the PL/pgSQL?

5. What is a rollback of transactions normally used for?

6. Which character function should be used to return a specified portion of a character string?

7. What do you infer from the following declarations?1. arrayA type[]2. arrayB type[][]

8. Which of the following help PostgreSQL avoid unnecessary locking of records?

9. Which of the following trigger function variables is not defined?

10. Will the following function compile and execute?DECLARE intValue int4;BEGIN intValue := 20 * 20; return intValue;END;

11. If entity x is existence-dependent on entity y, then what is x called?

12. You want to manipulate some value in the database upon updation in a trigger function. Which line of the code would be appropriate?

13. Consider the following structure of the students table: rollno number(4) name varchar(20) course varchar(20)What will be the query to display the courses in which the number of students enrolled is more than 5?

14. Which of the following files controls the host based authentication in PostgreSQL?

15. Which of the following statements are incorrect regarding referential integrity?

16. Which of the following statements are true for ordinary views?

17. Which of the following variable declarations is not correct?

18. You have defined a function "Calculate()" in the template1 database. What will happen when you create a new database "ManagementDB" there?

19. Which of the following values is not defined to indicate the level of the raise event?

20. Choose the correct statements for a trigger function:

21. Which of the following are valid in a declare block?

22. Which of the following clauses are not allowed in a single row sub-query?

23. Which of the following functionalities is supported by the pg_ctl script?

24. There are two tables A and B. You are retrieving data from both tables where all rows from table B and only matching rows from table A should be displayed. Which type of join will you apply between tables A and B?

25. Every Boyce-Codd Normal Form(BCNF) is in:

26. Which of the following geometric types is not defined in PostgreSQL?

27. Which of the following can be used to uniquely identify a row?

28. Which of the following copy commands will work in PostgreSQL?

29. Data validation can be implemented at the definition stage through:

30. Which of the following date and time constants are not defined in PostgreSQL?

31. Which of the following cannot be used as the operator name while creating a new operator?

32. The names of those departments where there are more than 100 employees have to be displayed. Given two relations, employees and departments, which query should be used?Employee---------EmpnoEmployeenameSalaryDeptnoDepartment---------DeptnoDepartname

33. Which of the following statements are correct with regard to PostgreSQL?

34. In which order are primary queries and their sub-queries interpreted:

35. Select the appropriate query for the Products table when data should be primarily ordered by ProductGroup. ProductGroup should be displayed in ascending order and CurrentStock should be in descending order:

36. Which of the following programming structures is not available in PL/pgSQL?

38. Which of the following are supported by PostgreSQL?

39. What is the first step for manually installing PL/pgSQL in PostgreSQL?

40. A handler named 'plpgsqlHandle()' is created for installing PL/pgSQL. For creating the handler on 'SwineDB' database you would issue a command:

41. Perfect Services provides financial services. You need to display data from the pers table for joining_date from '1/1/2005' to '31/12/2005' and the job should be for Analyst or Clerk or Salesman. Which select statement will you use?

42. Which of the following keywords can be used to ignore the return data of a function?

43. Which of the following statements is correct with regard to PostgreSQL?

44. How can data be accessed by users who do not have direct access to the tables?

45. Which of the following statements is not correct about creating a new operator?

46. In which of the following ways can a value in an array column be modified?

47. What is the correct order of clauses in the select statement?1 select2 order by3 where4 having5 group by

48. Which of the following holds true when creating the function for a PL/pgSQL call handler, and installing PL/pgSQL in the default directory?

49. Which clause should be used to display the rows of a table in ascending order of a particular column?

50. A wholesale merchant shop needs a report about the sale where total sale of the day is more than $50,000. Which of the following will fulfill this requirement?

51. What is wrong in this query:Select * from Orders where OrderID=(select OrderID from OrderItems where ItemQty>50)

52. What do you understand by the following PL/pgSQL declaration?c_phone customer.contact_no%TYPE;

53. Which of the following are not DCL operations?

54. What do you infer from the following two lines?1. host all 192.168.1.10 255.255.255.255 reject2. host all 127.0.0.1 255.255.255.255 trust

55. Point out the incorrect statement regarding group functions:

56. Which of the following is true about PostgreSQL clients?

57. Which of the following holds true when you have defined a function with "isstrict" attribute?

58. What would happen on compiling and running the following function?CREATE FUNCTION loop_function (integer) RETURNS integer AS ' DECLARE result integer; BEGIN result := $1; LOOP result := result * result; EXIT WHEN result >= 15000; END LOOP; RETURN result; END;' LANGUAGE 'plpgsql';

59. You want to update the last modified timestamp in the orders table. The correct way to do this in a PL/pgSQL function, when the parameter integer order_id is passed, would be:

60. Which of the following statements regarding ordinary views are incorrect?

61. Which of the following security features is inbuilt in PostgreSQL?

62. Choose the correct statement:

63. Which of the following options can be used to create and initialize a new database cluster within your file system?

64. What does the pg_dump command do?pg_dump CustomerDatabase

65. What does the RAISE statement do in PL/pgSQL?

66. Which of the following techniques can be used to obtain a result which is based on comparing one row of a table with another row of the same table?

67. Which of the following statements is correct?

68. What does the following update statement do?Update OrderTable set OrderDiscount=OrderDiscount*1.10

69. Which of the following is correct regarding VACUUM?

70. For which of the following languages does PostgreSQL provide an API interface?

71. The primary key indexing technique does not allow:

72. Which of the following functions is not available in PostgreSQL?

73. Which statements hold true for Partition Tables?

74. Which of the following is not an SQL operator?

75. Which component of a DBMS verifies the syntax of the users query?

76. Is function overloading available in PL/pgSQL?

77. What is a cluster?

78. Which of the following is not true regarding single row functions?

79. Examine the following query:Create table Person(EmpNo Number(4) not null,EName Char not null,Join_dt Date not null,Pay Number)Which of the following field(s) are created correctly?

80. Normalization divides tables in a more useful and meaningful manner. Which statement is correct for the FIRST NORMAL FORM?

7.
What do you infer from the following declarations?
1. arrayA type[]
2. arrayB type[][]

10.
Will the following function compile and execute?
DECLARE
intValue int4;
BEGIN
intValue := 20 * 20;
return intValue;
END;

12.
You want to manipulate some value in the database upon updation in a trigger function. Which line of the code would be appropriate?

13.
Consider the following structure of the students table:
rollno number(4)
name varchar(20)
course varchar(20)
What will be the query to display the courses in which the number of students enrolled is more than 5?

32.
The names of those departments where there are more than 100 employees have to be displayed. Given two relations, employees and departments, which query should be used?
Employee
---------
Empno
Employeename
Salary
Deptno
Department
---------
Deptno
Departname

40.
A handler named 'plpgsqlHandle()' is created for installing PL/pgSQL. For creating the handler on 'SwineDB' database you would issue a command:

47.
What is the correct order of clauses in the select statement?
1 select
2 order by
3 where
4 having
5 group by

48.
Which of the following holds true when creating the function for a PL/pgSQL call handler, and installing PL/pgSQL in the default directory?

51.
What is wrong in this query:
Select * from Orders where OrderID=(select OrderID from OrderItems where ItemQty>50)

52.
What do you understand by the following PL/pgSQL declaration?
c_phone customer.contact_no%TYPE;

54.
What do you infer from the following two lines?
1. host all 192.168.1.10 255.255.255.255 reject
2. host all 127.0.0.1 255.255.255.255 trust

58.
What would happen on compiling and running the following function?
CREATE FUNCTION loop_function (integer) RETURNS integer AS '
DECLARE
result integer;
BEGIN
result := $1;
LOOP
result := result * result;
EXIT WHEN result >= 15000;
END LOOP;
RETURN result;
END;
' LANGUAGE 'plpgsql';

59.
You want to update the last modified timestamp in the orders table. The correct way to do this in a PL/pgSQL function, when the parameter integer order_id is passed, would be:

64.
What does the pg_dump command do?
pg_dump CustomerDatabase

68.
What does the following update statement do?
Update OrderTable set OrderDiscount=OrderDiscount*1.10

79.
Examine the following query:
Create table Person
(EmpNo Number(4) not null,
EName Char not null,
Join_dt Date not null,
Pay Number)
Which of the following field(s) are created correctly?

89.
A steel production company has two sales outlets. Both outlets are maintaining their data separately in servers SVA and SVD. Both outlets use the same structure for the Sales table. Which method will you use to create a combined sales report for both the outlets?

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

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

112.
SELECT !!3;
What output will this statement give?

113.
SELECT rtrim('foobar', 'abr');
The result of this statement is foo.

116.
The following statement will retrieve the second element of the array column products in table store_products.
SELECT products[1] FROM store_products;

131.
Consider the following query:
Create table foo (bar varchar);
What will be the size limit of the bar?