Tuesday, June 24, 2014

PL/SQL Interview Questions and Answers

1. The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

2. Operators used in SELECT statements.
= Equal
<> or != Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern

3. SELECT statements:
SELECT column_name(s) FROM table_name
SELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column


5. The INSERT INTO Statements:

6. The Update Statement:
UPDATE table_name SET column_name = new_value WHERE column_name = some_value
7. The Delete Statements:
DELETE FROM table_name WHERE column_name = some_value
Delete All Rows:
DELETE FROM table_name or DELETE * FROM table_name

8. Sort the Rows:



11. What is the use of CASCADE CONSTRAINTS?

12. Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol.


15. What is the difference between TRUNCATE and DELETE commands?
< p TRUNCATE. with and DELETE used be can clause WHERE back. rolled cannot operation TRUNCATE but back, Hence command. DML is whereas command DDL>

17. State true or false. EXISTS, SOME, ANY are operators in SQL?

18. What will be the output of the following query?
19. What does the following query do?

20. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?

21. Which command executes the contents of a specified file?
START or @.

sal = 11000, comm = 1000.


24. What command is used to get back the privileges offered by the GRANT command?


27. What operator performs pattern matching?
LIKE operator.

It is used to drop constraints specified on the table.

29. What operator tests column for the absence of data?
IS NULL operator.


Data Definition Language (DDL).

33. What is the use of DESC in SQL?
Explanation :


Will the outputs of the above two commands differ?

37. What are the wildcards used for pattern matching.?


39. What's an SQL injection?
40. What is difference between TRUNCATE & DELETE

Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
42. What is the sub-query?

43. What is correlated sub-query?

Retrieves rows in hierarchical order eg.
select empno, ename from emp where.

45. Difference between SUBSTR and INSTR?
SUBSTR (String1 n, m)

INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates.

47. What is ROWID?

Using ROWID.
49. What is an integrity constraint?

50. What is referential integrity constraint?

51. What is the usage of SAVEPOINTS?


53. What are the data types allowed in a table?

CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.



57. Where the integrity constraints are stored in data dictionary?
The integrity constraints are stored in USER_CONSTRAINTS.

58. How will you activate/deactivate integrity constraints?
It won't, Because SYSDATE format contains time attached with it.

60. What is a database link?


60.What is CYCLE/NO CYCLE in a Sequence?

61. What are the advantages of VIEW?
- To hide complexity of a query.
- To hide complexity of calculations.


64. Which of the following statements is true about implicit cursors?
2. Developers should use implicit cursors with great care.
4. Implicit cursors are no longer a feature in Oracle.

65. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.

1. Use employee.lname%type.
2. Use employee.lname%rowtype.
4. Declare it to be type LONG.

67. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype

1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.

70. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.

71. What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values

72. Read the following code:10. CREATE OR REPLACE PROCEDURE find_cpt
12. IS
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
73. Read the following code:22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
25. {additional code}
26. END;


1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)

1. Only one
2. All that apply
3. All referenced
4. None


78. Read the following code:CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS

v_yearly_budget NUMBER;

SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;

RETURN v_yearly_budget;
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER

32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
34. UPDATE theater
35. SET name = v_name
36. WHERE id = v_theater_id;
37. END update_theater;
79. When invoking this procedure, you encounter the error: ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error code directly.

80. Read the following code: 40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
43. v_budget := get_budget(11);
44. IF v_budget < 30000
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END;

You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.

81. Which procedure can be used to create a customized error message? 1. RAISE_ERROR

82. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger? 1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;

83. Examine this database trigger 52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
58. END IF;
59. END;

1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt
84. Examine this function: 61. CREATE OR REPLACE FUNCTION set_budget
62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
64. UPDATE studio
65. SET yearly_budget = v_new_budget
WHERE id = v_studio_id;



Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.

85. Under which circumstance must you recompile the package body after recompiling the package specification? 1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs

86. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed? 1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger

87. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus? 1. DBMS_DISPLAY 2. DBMS_OUTPUT 3. DBMS_LIST 4. DBMS_DESCRIBE

88. What occurs if a procedure or function terminates with failure without being handled? 1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
89. Examine this code 71. BEGIN
72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
73. END;

For this code to be successful, what must be true?



91 How to implement ISNUMERIC function in SQL *Plus ?
Method 1:



Method 2:

select instr(translate('wwww',
FROM dual;

92 How to Select last N records from a Table?

Here N = 10

The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.

Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno

What are steps required tuning this query to improve its performance?

-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO

-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:

FROM ter.ter_master a
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;

93 What is the difference between Truncate and Delete interms of Referential Integrity?
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it only checks for the existence (and status) of another foreign key Pointing to the table. If one exists and is enabled, then you will get The following error. This is true even if you do the child tables first.
You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.

94. What does preemptive in preemptive multitasking mean ?
Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.

95. What does the OLTP stands for ?
OLTP stands for On Line Transaction Processing

96. What is the most important requirement for OLTP ?
OLTP requires real time response.

97. In a client server environment, what would be the major work that the client deals with ?
The client deals with the user interface part of the system.

98. Why is the most of the processing done at the sever ?
To reduce the network traffic and for application sharing and implementing business rules.

99. What does teh term upsizing refer to ?
Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing.

100. What does one do when one is rightsizing ?
With rightsizing, one would move applications to the most appropriate server platforms.

101. What does the term downsizing refer to ?
A host based application is re-engineered to run in smaller or LAN based environment.

102. What is event trigger ?
An event trigger, a segment of code which is associated with each event and is fired when the event occurs.

103. Why do stored procedures reduce network traffic ?
When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
104. What are the types of processes that a server runs ?
Foreground process and Background process.

105. What is a event handler ?
An event handler is a routine that is written to respond to a particular event.

106. What is an integrity constraint ?
An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table.

107. What are the various uses of database triggers ?
Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.

108. What is a transaction ?
A transaction is a set of operations that begin when the first DML is issued and end when a commit or rollback is issued. BEGIN COMMIT/ROLLBACK are the boundries of a transaction.

109. Why are the integrity constraints preferred to database triggers ?
Because it is easier to define an integrity constraint than a database trigger.

110. Why is it better to use an integrity constraint to validate data in a table than to use a stored procedure ?
Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.

111. What are the three components of a client server model ?
A Client,
A Server and
A Network/Communication software.

112. What are the advantages of client/server model ?
Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.

113. What are the disadvantages of the client/server model ?
Heterogeneity of the system results in reduced reliablity. May not be suitable for all applications. Managing and tuning networks becomes difficult.

114. What are the different topologies available for network ?

115. What is the first work of Client process ?
A client process at first establishes connection with the Server.
115. What are the responsibilities of a Server ?
1. Manage resources optimally across multiple clients.
2. Controlling database access and security.
3. Protecting the databse and recovering it from crashes.
4. Enforcing integrity rules globally.

116. In a Client/Server context, what does API (Application Programming Interface) refer to ?
An API, in a Client/Server context, is a specification of a set of functions for communication between the client and the server.

117. Give some examples of standard API??s ?
Open Database Connectivity (ODBC),
Integrated Database Application Programming Interface (IDAPI),

118. What is the main advantage of developing an application using an API ?
The application can be connected to any back end server that is supported by the API.

119. What is the main disadvantage of developing an application using an API ?
The application cannot use any special features of the backend server.

120. Why is an event driven program referred to a passive program ?
Because an event driven program is always waiting for something to happen before processing.

120. What are the four types of events ?
1. System Events.
2. Control Events
3. User Events
4. Other Events.

121. What is the difference between file server and a database server ?
A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output.

122. What is inheritance ?
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.

123. What are the two components of ODBC ?
1. An ODBC manager/administrator and
2. ODBC driver.
124. What is the function of a ODBC manager ?
The ODBC Manager manages all the data sources that exists in the system.

125. What is the function of a ODBC Driver ?
The ODBC Driver allows the developer to talk to the back end database.

126. What description of a data source is required for ODBC ?
The name of the DBMS, the location of the source and the database dependent information.

127. How is a connection establised by ODBC ?
ODBC uses the description of the datasource available in the ODBC.INI file to load the required drivers to access that particular back end database.  

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect