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

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

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

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

8.

What do you infer from the following declarations?

1. arrayA type[]

2. arrayB type[][]

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

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

11.

Will the following function compile and execute?

DECLARE

   intValue int4;

BEGIN

   intValue := 20 * 20;

   return intValue;

END;

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

13.

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

14.

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?

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

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

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

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

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

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

21. Choose the correct statements for a trigger function:

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

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

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

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

26.

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

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

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

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

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

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

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

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

34.

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

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

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

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

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

39.

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:

40. Which of the following are supported by PostgreSQL?

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

42.

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

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

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

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

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

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

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

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

50.

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

1        select

2        order by

3        where

4        having

5        group by

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

52.

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

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

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

55.

What is wrong in this query:

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

56.

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

c_phone customer.contact_no%TYPE;

57. Which of the following are not DCL operations?

58.

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

59. Point out the incorrect statement regarding group functions:

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

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

62.

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

63.

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. Which of the following statements regarding ordinary views are incorrect?

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

66. Choose the correct statement:

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

68.

What does the pg_dump command do?

pg_dump CustomerDatabase

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

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

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

72. Which of the following statements is correct?

73.

What does the following update statement do?

Update OrderTable set OrderDiscount=OrderDiscount*1.10

74. Which of the following is correct regarding VACUUM?

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

76. The primary key indexing technique does not allow:

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

78. Which statements hold true for Partition Tables?

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

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

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

82. Is function overloading available in PL/pgSQL?

83. What is a cluster?

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

85.

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?

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

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

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

89. Choose the correct statement regarding WAL in PostgreSQL:

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

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

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

93. What is the default variable for the PROMPT3?

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

95.

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?

96. PostgreSQL triggers can be written in C directly.

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

98.

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

pg_dump -Fc dbname > filename

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

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