General PostgreSQL MCQs

1. PostgreSQL can be used from just about any major programming language, including C, C++, Perl, Python, Java, Tcl, and PHP.

2. The value NULL, in database terminology, means?

3. With table inheritance, not null and check constraints are inherited.

4. What do you call the application that makes requests of the PostgreSQL server?

5. What is the wrapper around the SQL command CREATE DATABASE?

6. When retrieving data in a particular table, we use the_____________ statement.

7. PostgreSQL is:

8. PostgreSQL is

9. PostgreSQL has many modern features including:

10. PostgreSQL can be installed?

11. PostgreSQL runs on:

12. With PostgreSQL, you can access data by

13. Triggers can be configured to execute when which of the following operations are performed:

14. We add data to PostgreSQL by using which statement?

15. The heart of SQL is the __________ statement.

16. The most common method to get data into a table is to use what command?

17. In PostgreSQL, a named collection of tables is called what?

18. What command tells PostgreSQL that all of the changes you made to a database should become permanent?

19. The SQL condition for pattern matching is?

20. ____________ allow us to define formally in the database how different tables relate to each other.

21. Which of the following best describes a role:

22. The basic psql command to list tables is?

23. VACUUM FULL shrinks indexes, optimizing database performance.

24. WAL stands for:

25. Deadlocks occur when:

26. When using a SELECT statement on a table, or group of tables, those resources are locked exclusively.

27. Tablespaces:

28. If you don’t specify ASC or DESC, PostgreSQL will assume you want to see results:

29. A tablespace is the place where database objects are stored on disk.

30. When you want to use a join between columns that are in the same table, you use what type of join?

31. Within a table, a single column may be encrypted.

32. ALTER TABLE may be issued while a VACUUM process is running.

33. The core PostgreSQL source code includes what interfaces?

34. A VACUUM operation is used:

35. PostgreSQL is so lightweight that performance and reliability are not affected by hardware.

36. Unless you specify NOT NULL, PostgreSQL will assume that a column is:

37. To describe a table in PostgreSQL which of the following is correct:

38. Query trees can be viewed in the server logs as long as which of the following configuration parameters are enabled?

39. A meta-command always begins with what?

40. To create a database in PostgreSQL, you must have the special CREATEDB privilege or

41. What does the following statement do? CREATE INDEX lower_title_idx ON books ((lower(title)));

42. What command allows you to edit PostgreSQL queries in your favorite editor ?

43. To prevent transaction wraparound, a VACUUM operation should be run on every table no less than once every:

44. Asynchronous Commits:

45. The rule system:

46. Only the administrator can make use of tablespaces.

47. What command turns on timing?

48. When looking at 'ps' output on a unix system, you see the following: postgres 1016 0.1 2.4 6532 3080 pts/1 SN 13:19 0:00 postgres: tgl regression [local] idle in transaction What does 'idle in transaction' mean?

49. Advisory locks are allocated out of a shared memory pool whose size is defined by the configuration variables....

50. This is used to determine how text is stored and sorted within PostgreSQL?

51. True or False? Dynamic Tracing is enabled by default at compile time.

52. Which statement is true about PostgreSQL data types?

53. PostgreSQL supports Index Only Scans.

54. What does MCV stand for?

55. To restore a PostgreSQL backup created with pg_dump, the following may be used:

56. In order to echo all input from script, you use the ________ psql command.

57. By default, in what subdirectory of the database data directory are WAL logs contained?

58. What is 'index bloat'?

59. Which is NOT true of array indexes?

60. Bob works for StegaCorp. His workstation's IP address is 10.5.34.8. He needs access to a database called 'partners' directly from his workstation. Which of the following is the correct entry in pg_hba.conf?

61. What are the join strategies available to the postgreSQL planner when a SELECT query contains two or more relations?

62. To copy a database from server1 to server2, you might use which of the following:

63. To increase server performance, automated CHECKPOINT operations should be setup in cron or Task Scheduler.

64. Which of the following is not a valid integer array?

65. What is the difference between DO 'some code...' and EXECUTE 'some code...' statements?

66. Locks are recorded in:

67. When restoring a database backed up with pg_dump, it's generally a good idea to enable WAL.

68. Hash indexes are not crash-safe

69. True or false: When a table is created which uses a table name as a column type, not null constraints on the column type's table definition are honored by the including table.

70. The syntax to view the indexes of an existing postgreSQL table is:

71. Which statement is not true about a PostgreSQL domain?

72. The extension used for data encryption/decryption within PostgreSQL is:

73. What is a TOAST file?

74. To create a database that supports UTF-8, the following command can be used:

75. PostgreSQL allows you to implement table inheritance. This should be defined with the special keyword INHERITIS in the table design.

76. Which of the following is NOT a feature of user defined functions?

77. WAL segment size is determined:

78. The __________ database model has the advantage of being able to quickly discover all of the records of one type that are related to a specific record of another type by following the pointers from the starting record.

79. When identifying rows uniquely, we use__________keys.

80. With table inheritance child tables inherit primary and foreign key definitions from their parents

81. To enable continuous archiving, all you have to do is set archive_mode to 'on' in postgresql.conf

82. Can deferrable constraints be deferred by a trigger?

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

84. PostgreSQL triggers can be written in C directly.

85. Which index types are supported by PostgreSQL?

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

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

88. Which kind of index can be declared unique?

89. Which authentication methods are supported by PostgreSQL?

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

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

92. Consider the following query:

Create table foo (bar varchar);

What will be the size limit of the bar?

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

94. The following statement will retrieve the second element of the array column products in table store_products.

SELECT products[1] FROM store_products;

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

96. What kind of triggers are offered by PostgreSQL?

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

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

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

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

101. What library is used by PostgreSQL for encryption?

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

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

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

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

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

107. How will you rank text search results?

108. Does PostgreSQL support SSL?

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

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

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

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

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

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

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

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

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

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

119. What is the ~ operator?

120. What is true regarding file system backup?

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

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

123. SELECT 'infinity'::timestamp;

Will this statement produce an error?

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

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

126. Which will retrieve the number of values stored in an array column?

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

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

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

130. In the following list, which operators do a case insensitive string matching?

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

132. Which PostgreSQL version added the enum datatype?

133. Are the contents of the pg_autovacuum system catalog saved when pg_dumpall is used to backup the database?

134. Which index types support multicolumn indexes?

135. Command used to import a backup made with pg_dumpall > file.dmp?

136. How can you configure PostgreSQL autovacuum?

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

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

139. What is the difference between tokens and lexemes?

140. Which will create a table special_products which is a child of the table store_products?

141. Which will create a table with a multidimensional array as second column?

142. Which authentication methods are supported by PostgreSQL?

143. Which index types are supported by PostgreSQL?

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

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

146. Which index types support multicolumn indexes?

147. What kind of triggers are offered by PostgreSQL?

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

149. How can you configure PostgreSQL autovacuum?

150. What library is used by PostgreSQL for encryption?

151. Are the contents of the pg_autovacuum system catalog saved when pg_dumpall is used to backup the database?

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

153. Can deferrable constraints be deferred by a trigger?

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

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

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

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

158.

Consider the following query:

Create table foo (bar varchar);

What will be the size limit of the bar?

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

160. What is true regarding file system backup?

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

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

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

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

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

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

167. Which PostgreSQL version added the enum datatype?

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

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

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

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

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

173.

The following statement will retrieve the second element of the array column products in table store_products.

SELECT products[1] FROM store_products;

174.

SELECT 'infinity'::timestamp;

Will this statement produce an error?

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

176. Which kind of index can be declared unique?

177.

SELECT rtrim('foobar', 'abr');

The result of this statement is foo.

178.

SELECT !!3;

What output will this statement give?

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

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

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

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

183.

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?

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

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

186. How will you rank text search results?

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

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

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

190. Does PostgreSQL support SSL?

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

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

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

194. What is the ~ operator?

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

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

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

198.

What command will correctly restore a backup made with the following command?

pg_dump -Fc dbname > filename

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

200. PostgreSQL triggers can be written in C directly.

201.

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?

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

203. What is the default variable for the PROMPT3?

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

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

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

207. Choose the correct statement regarding WAL in PostgreSQL:

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

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

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

211.

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?

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

213. What is a cluster?

214. Is function overloading available in PL/pgSQL?

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

216. There is a column c1 in the table t to which a primary key pk is to be added. What will be the correct syntax?

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

218. Which statements hold true for Partition Tables?

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

220. The primary key indexing technique does not allow:

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

222. Which of the following is correct regarding VACUUM?

223.

What does the following update statement do?

Update OrderTable set OrderDiscount=OrderDiscount*1.10

224. Which of the following statements is correct?

225. Which method should be used to drop the master table if its primary key is being referenced by a foreign key in some other table?

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

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

228.

What does the pg_dump command do?

pg_dump CustomerDatabase

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

230. Choose the correct statement:

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

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

233.

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:

234.

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';

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

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

237. Point out the incorrect statement regarding group functions:

238.

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

239. Which of the following are not DCL operations?

240.

What do you understand by the following PL/pgSQL declaration?

c_phone customer.contact_no%TYPE;

241.

What is wrong in this query:

Select * from Orders where OrderID=(select OrderID from OrderItems where ItemQty>50)

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

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

244.

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

245. Which of the following is not a single value function?

246.

What is the correct order of clauses in the select statement?

1        select

2        order by

3        where

4        having

5        group by

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

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

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

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

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

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

253. Premium Corp. uses alphanumeric characters for EmployeeID which contains 7 digits prefixed with 3 characters. The 3 characters describe the department code. You need to show the department code and the Employee number separately, where department code should be in capital letters. Which function(s) will you have to use for this?

254.

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

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

256. Which of the following are supported by PostgreSQL?

257.

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:

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

259. 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:

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

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

262.

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

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

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

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

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

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

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

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

270.

What is the error in the following query if the students table contains several records?

       select name from students where name =

     (select name from students order by name);

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

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

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

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

275. Choose the correct statements for a trigger function:

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

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

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

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

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

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

282.

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?

283.

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

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

285.

Will the following function compile and execute?

DECLARE

   intValue int4;

BEGIN

   intValue := 20 * 20;

   return intValue;

END;

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

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

288.

What do you infer from the following declarations?

1. arrayA type[]

2. arrayB type[][]

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

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

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

292. There is a table t upon which a primary key constraint by the name pk is applied. What will be the correct syntax to drop the constraint?

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

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

295. When should sub queries be used?