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?
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?
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?
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?
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?
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[][]
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?
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?
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;
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?
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?
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?
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?
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?
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?
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?
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?
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?
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:
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?
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?
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?
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?
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:
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?
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?
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?
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:
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?
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?
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
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?
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:
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:
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?
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:
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?
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?
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:
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?
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?
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?
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?
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?
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?
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
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?
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?
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?
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)
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;
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?
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
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:
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?
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?
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';
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:
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?
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?
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:
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?
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
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?
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?
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?
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
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?
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?
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:
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?
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?
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?
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?
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?
Answer
Correct Answer:
True
Note: This Question is unanswered, help us to find answer for this one
77. What is a cluster?
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?
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?
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?
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?
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?
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:
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?
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?
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?
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?
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?
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?
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.
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?
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
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.
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?
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?
Answer
Correct Answer:
C
Note: This Question is unanswered, help us to find answer for this one
96. What is the ~ operator?
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)?
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?
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?
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?
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?
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?
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?