1. What is an UTL_FILE.What are different procedures and functions associated with it?


2. What is OCI. What are its uses?


3. How you open and close a cursor variable.Why it is required?


4. What WHERE CURRENT OF clause does in a cursor?


5. What a SELECT FOR UPDATE cursor represent.[ANSWER]SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.


6. Difference between NO DATA FOUND and %NOTFOUND


7. What is a cursor for loop?


8. What is the purpose of a cluster?


9. Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?


10. What is SQL Tuning?
11. What is a deadlock?


12. Display Odd/ Even number of records


13. Display the records between two range? er


14. What is a pseudo column. Give some examples?

15. What is a OUTER JOIN?


16. When do you use WHERE clause and when do you use HAVING clause?


17. How you will avoid duplicating records in a query?


18. Can a primary key contain more than one columns?


19. What is difference between UNIQUE and PRIMARY KEY constraints?


20. What are various privileges that a user can grant to another user?
21. What is a view ?


22. What is difference between Rename and Alias?


23. What are different Oracle database objects?


24. Oracle long datatype


25. Redundancy of data is many times benificial, Justify the statement.


26. How centralized DBMS differs from distributed DBMS .


27. Display the emploee records who joins the department before their manager?


28. Dispaly employee records who gets more salary than the average salary in their department?


29. Write a query to display employee records having same salary?


30. Can you use a reference cursor as an input parameter in a procedure with out declaring it explicitly?
31. Write a query to display alternate records from the employee table?


32. Create a query that displays the employees' last names and commision amounts.If an employee does not earn commmission, put "No Commission." Label the column COMM.


33. How to add positive and negative numbers in a single column and output them to two different columns


34. why order by clause maintains column number values instead of column names ?


35. How to get the row values as comma separated by a query


36. How many codd's rules are supporting by the oracle?.Plz clarify this.


37. Oracle Insert statement execution


38. Difference between database triggers and form triggers?


39. What is difference between a formal and an actual parameter?


40. Question is been asked in the interview., I have table Department with details likeDeptId Dname Dlocation10 Finance Del20 Sales Mum30 Marketing BloreThe output should be in this format 10 20 30 Finance Sales Marketing Del Mum BloreThe query I need in SQL not using any transformation.Can anyone help me out in this ?
41. Difference between Optimizer=ALL_ROWS and Optimizer=CHOOSE


42. Difference Between Hash Join & Merge Join


43. Oracle : Table Level and Column Level constraints


44. Difference between an implicit & an explicit cursor.
45. Explain about SUPPORTING TIME-CONSTRINED SQL queries in ORACLE?


46. Find out nth highest salary from emp table


47. How do i get Greatest salaray of dept group from emp and dept?we have to tables EMP and DEPT. for exp: dept 10 have 10000,dept 20 have 5000 and dept 30 have 15000 salaries, i need dept 30 group salary(means sum of that group is 15000).


48. Difference between procedure and function.


49. you have EMP and dept table .how do you get the entire department and corresponding employee details and the department which are not alloted it?what is referential integrity constraint?wat is triggers,stored procedures,stored functions explain with examples


50. How you will avoid your query from using indexes?
51. maxvalue.sql Select the Nth Highest value from a table


52. minvalue.sql Select the Nth lowest value from a table


53. What is the Hirerachy of SQL?


54. sql general queries like1.find the two minimum salaries among table


55. Write query for the following questions1. sql query for selecting alternate rows in a table2. sql query for deleting alternate rows in a table


56. how to find number of user in the database.


57. How to see the existing constraints in a table?


58. List all the employees who have atleast one person reporting to themList the employee details if and only if more than 10 employees are presently in department 10


59. How do you view the last record added to a table?
60 in a table a column is having value like 'Steven Kovy'. without using concat() function and concatenate operator "||" how to display like 'Kovy Steven'
61. how to get the prime number rows from table ie like1,3,5,7,11


62. What is the data type of the column of Dual table?


63. Differentiate between TRUNCATE and DELETE


64. How do you find the numbert of rows in a Table ?


65. What is the difference between ROWNUM and ROWID


66. Explain what is mutation and what is mutating table and how this mutation problem is solved in a table


67. Why is insert faster than delete?Which indexing technique works best for Oracle based database?


68. What is difference between SUBSTR and INSTR?


69. How will you delete duplicating rows from a base table?


70. What is a cursor?


72. Is it possible to index a table through stored procedure? If Yes, How?


73. difference between decode and case.in which case we are using case and in which case we are using decode?with an example.


74. What is difference between SQL and SQL*PLUS?


75. Can one use dynamic SQL statements from PL/SQL?How can I use a variable for the columns/tables in the select statements?


76. How to give select privilege on all the objects owned by an user(say user1) to another user say(user2) using a single sql statement?


77. what is a reverse key index and it's real time usage


78. SESSION 1 SESSION 2 ---------------- -------------------- DELETE FROM EMP; SELECT * FROM EMP;What will be the result?


79. What is the difference between SQL standard joins and Oracle propreitary joins


80. explain the differences between cost-based optimizer and Rule-based optimizer ? Why DB2, Sybase is cost based and SQL, Oracle are Rule Based Optimisers ?
81. what is rep cursor?


82. Which is more faster - IN or EXISTS?


83. is there any way we can change the column name in a table


84. What are different modes of parameters used in functions and procedures?


85. What are the privileges that view does not have as compared to normal table?


86. How to get first 5 Records then next 5 records till end of row count in SQL -Oracle


87. is there any chance to remove a column of a table


88. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?


89. Which datatype is used for storing graphics and images?


90. What are the advantages and disadvantages of View?
91. how do you generate prime numbers in sql not in plsql


92. Can the default values be assigned to actual parameters?


93. how to select the recently UPDATED records from the table?


94. I have a table with a column city. The values are “delhi” and “hyderabad”. I need to update Hyderabad with delhi and delhi with Hyderabad. Define a query for that


95. how find to find column value without knowing table name


96. what is the differences between Oracle8i and 9i?


97. Is it possible to update Views? If yes, How, If Not, Why?


98. There are 10 rows in Table A and 0 Rows in table B. Select * from A,BHow many rows will be retrieved.


99. Display the number value in Words?


100. What is Local Index and Global Index in Partition Table?What is Prefix Index and Nonprefix Index in table?
101. What is output of this SQL where current value of sequence is 3?Select seq.curval,seq.Nextval,seq.Nextval from dual ? Give reason?


102. Update statement in SET clause subquery is allowed or not?


103. What is difference between Cartesian Join & Cross Join?


104. How to compare the total salary of all employee with a individual employee salary for a particular department.


105. How to retrieving the data from 11th column to n th column in a table.
106. What is Complex View?Where we can use?


107 There is a table Table_A contains 4 feilds col1, col2, col3, col4 having values a, b, c, d as values respectively. What will be the code if someone wants to update a with b, b with c, c with d and d with a?


108. Suppose there are two fields in table(Employee) say Name and Salary and there are in total 100 records in table. Now my query gives the name of an employee whose salary is 10th among 100 salaries. this query should be solve in SQL not in PL/SQL


109. What is the Difference between stored procedures and external procedures?


110. what is difference between sub query and correlated sub query, Give the example for this.
111. What is Cartesian product in the SQL?


112. How do you create our own schema? which privileges are required?


113. how to choose between a procedure and a function?


114. what is the difference between oracle,sql and sql server


115. How to find Nth largest or Nth smallest data from oracle table, for ex..5th highest salary from employees


116. How many max No. of triggers I can create in a table?


117. How does an index work? I know it uses rowid but want to know the exact process.


118. Can cursor variables be stored in PL/SQL tables.If yes how. If not why?


119. There is a string 120000 12 0 .125 , how you will find the position of the decimal place?


120. what is an inline view?
121. can anybody provide me some common and normal scenario based questions during interveiw.


122how to delete set of records at a time where all the records having same values , except the Rowid differs?

123. What is the difference between single quote (') and double quote(") in relates to using in SQL. When do you use 'xxx' and "xxx"? what is the difference both of them?

124. When i write the following program, i am getting error like: DECLARE vInHandle UTL_FILE.file_type; vNewLine VARCHAR2(250); BEGIN vInHandle := UTL_FILE.Fopen ('D:', 'new.txt', 'R'); LOOP BEGIN UTL_FILE.get_line(vInHandle, vNewLine); dbms_output.put_line(vNewLine); END; END LOOP; UTL_FILE.fclose(vInHandle); END; Error:-DECLARE*ERROR at line 1:ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 18 O

125. What are various constraints used in SQL?

126. How to eliminate duplicate rows from a table leaving one from the duplicates?

127. In the real world, where do we use Savepoint command.

128. Why statistics are different in oracle9i and oracle 10g?

129. what is the internal structure of database and how sql is related with it?

130. on index why u need indexing ? where that is stroed and what u mean by schema object?for what purpose we are using view
131. What is difference between Varchar2 and Nvarchar2?

132. where we use nested tables in our real time

133. 1) how many number of tables we can join in join concepts?is there any limitation?i think its not...2) since joins and subqueries are used for same purpose , why we have two things.is there any need

134. How to find errors of PROCEDURE at run time( not complie time) ?

135. What are ORACLE PRECOMPILERS?

136. SQL Queries- Say for Eg.Two Tables - EMP and DeptEMP Tables consist of Empno, Deptno, Mgrid, Empname, LocationDept Table Consist of Deptno, Deptname.1. Write a query to select Department name for which there is more that 20 employees in a specific Department. List the deptname and no of employees.Deptno in Emp table is the primary key for deptno column in Dept Table.2. Write a query to select Manager name for each employee. MGRID column in the emp table is the empid of the Manager. There shou

137. In Oracle10g sqlplus, how do you find the tables that have been dropped?

138. how to get 1,4,7,10.....rows from a table

139. How do we restrict the insert/ updates on the foreign column in a table if that table is in different instance that the table with Primary Key. Say for Example Emp and Dept Table. We have deptno in both tables and Deptno in Emp table is the Primary Key and other is foreign Key. How do we control what values we insert in Dept table if both the tables are not in the same instance.

140. what is the result ofselect * from table1, table2there is a common column between them but no where caluse has been specified
141. Give is the maximum prime number which can be fit into 8-bit Memory

142. Can you use a commit statement within a database trigger?

143. Explain psudo column Label with an example

144. Can a function take OUT parameters. If not why?

145. What are various joins used while writing SUBQUERIES?

146. There is a % sign in one field of a column. What will be the query to find it?

147. difference between case expresssion and case statement

148. What is use of a cursor variable? How it is defined?

149. Difference between HASH JOIN And MERGE JOIN

150. Can data be inserted/deleted/updated from CLOB,BLOB,NCLOB columns.If not why?

151. What should be the return type for a cursor variable.Can we use a scalar data type as return type?

152. How you were passing cursor variables in PL/SQL 2.2?

153. What is syntax for dropping a procedure and a function .Are these operations possible?

154. What is the meaning of cost, card, Bytes?