Eplislon Questions for Interview:
1. Introduce yourself.
2. Tell me about your PL/SQL experience. What have you used to do the coding so far?
3. What was your responsibility there?
4. What are the projects you have worked with so far?
5. What is the longest Package you have written so far? How many lines did it consist of? What was the logic
of the Package?
6. What are the procedures and functions you wrote there? Give me some of their names. Can you
remember the syntax/coding of some of the procedures and functions you constructed there? What was
the purpose/logic of those/that procedure and the function?
7. Suppose, I will insert 1,000,000 rows in a table. I want my PL/SQL block to commit after inserting 1000
rows. How can I use this logic in PL/SQL?
Answer:
declare
vcounter number:=0;
begin
for i in 1..1000000 loop
insert into test1 values(i);
vcounter:= vcounter+sql%rowcount;
if vcounter >=10000 then
commit;
vcounter:=0;
end if;
end loop;
end;
8. What is integrity constraint?
Answer: integrity constraint ensures that the data in the table in database meet/confirm a defined
set of correctness and quality.
What are the names of the Oracle Integrity Constraint?
Answer: Not Null ,Primary Key ,Foreign Key ,Check ,Unique
9. What is Check Constraint?
Answer: A check constraint allows to state a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used.
The following table allows only numbers that are between 0 and 100 in the column a;
create table ri_check_1 (
a number check (a between 0 and 100),
b number
);
Check constraints can be added after a table had been created:
alter table ri_check_1
add constraint ch_b check (b > 50);
ref:http://www.adp-gmbh.ch/ora/misc/integrity_constraints.html
10. What is rollback?
11. What is View?
What is updateable View?
Answer: we cant do the operatons of insert delete and update is called non updatable views or read oly views
whereas we can insert delete and update operations can done in updatable views with some conditions
(should be derived from base table and should not contain derived columns,
views should not contain groupby having and distinct clauses at the outer most level,
views should include all the not null columns of the base table)
ref.: http://www.coolinterview.com/interview/34123/
Example: create OR REPLACE view emp_dept_v as
select ename,empno, job, dEPT.DEPTNO, DNAME
from emp, dept
where emp.deptno=dept.deptno
INSERT INTO EMP_DEPT_V VALUES('N',1111,'J',11,'D')
-- unable to insert
CREATE OR REPLACE TRIGGER T_EMP_DEPT_V
INSTEAD OF INSERT ON EMP_DEPT_V
BEGIN
INSERT INTO EMP (ename,empno, job)
VALUES (:NEW.ename,:NEW.empno, :NEW.job);
END;
INSERT INTO EMP_DEPT_V VALUES('N',1111,'J',11,'D')
--data inserted
What is the difference between Views and Snapshots?
A snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data between distributed databases.
A snapshot is a table that contains the
results of a query of one or more tables or views, often located on
a remote database.
In Oracle 9i instead of snapshot we can use meterialized views which can act as snapshot.
Diff between view & snapshot is view is not a table its a virtual table to join one or more table or to prevent access from users on complete data on table. whenever we will access the view that brings the data from tables only not from view(it wont store any kind of data).
but the snapshot itself store the query result data. usually we will use to get remote data to replicate the changes made at remote site.
http://www.geekinterview.com/question_details/20813
Can we use View to update a table?
12. What is group by function? What is having clause? Why do we use having clause? What is the sequence
of the key words/clauses of a SQL Statement with a Group by Function? Does Having clause come after
Group by clause or before Group by clause?
13. What are the differences between Truncate, Drop and Delete? I want to Truncate 50 rows in a table
keeping the rest of the rows, give me the syntax.
14. What is Rowid?
15. What is rownum? Why do we use Top N Analysis?
16. Suppose there are duplicate rows in a table. How can we delete the duplicate rows keeping only the
distinct rows?
17. What is trigger? How many types of triggers are there? What is the syntax of creating a Trigger? How
many different triggers can be used for a table? In which type of trigger both old value and new value can
be inserted?
18. What is Cursor? How many Cursors are there? What is Implicit Cursor? What is Explicit Cursor? What are
the Cursors attributes? How do we use Cursor attributes? Write a PL/SQL block where you will use an
Explicit Cursor?
19. What is Ref Cursor? What is the use of a Ref Cursor? Write a PL/SQL block using Ref Cursor.
20. What is loop? What is the difference between For Loop and While loop? In what section of a PL/SQL block
do we use Loop? How do we use loop in a Cursor? How does a Looping works?
21. What PL/SQL block do we have? What are the differences between a Procedure and a Function? Can a
Function return more than one value? What do we have to do to make a Function return more than one
value?
22. What is %rowtype and what is %type? Which is a good % type?
23. What is Package? What is the difference between a stored procedure and a Package? What are Public
Construct and Private Construct in a Package? What are the differences between them? Suppose a
package has no compilation error but while trying to call it from the Front end, user gets a
Message, “Object not defined”. What may be the cause of the problem? How can we handle it?
24. What is Collections? What are the Collections we have?
25. What is Varray? How do you populate a Varray?
26. What are PL/SQL tables? Can we have multiple columns in a PL/SQL tables?
27. Can we handle an error in a PL/SQL block? What is an Exception? How many types of Exceptions are
there? How can we trap/handle an Exception in a PL/SQL block? Give us syntax where you will trap
an Exception in an Exception Handling section. How can we give different message to the end users in
Exceptions? How will you raise a user defined exception and handle it? How can we store the errors in an
Error_log table? What is Get_error in Oracle?
28. How can we see the last day of a month? Or, which built in Function is used to see the last day of a
month? How can we see the current date? How can we see the current time? Provide the syntax.
29. What are the differences between Unique Index and Unique Constraint? What are the differences
between Unique Index and Primary Key Constraint?
30. What are nvl and nvl2? What are the differences? Give us syntax for each.
31. What is an Errorlog table?
Answer:
32. What is Varchar2? What is the maximum length Varchar2?
Answer:
33. What is DBMS_OUTPUT.PUT_LINE?
Answer:
34. What is partition? How we can use Partition? Why do we use Partition on a table?
Answer:
35. How I can write something into a file using Oracle?
Answer:
36. They have given us the image of five tables with some data and asked various questions on these tables.
Answer:
CEO Questions for Interview:
37. What is your level of PL/SQL? How much do you work on PL/SQL? How much would you give yourself out of ten at PL/SQL?
Answer:
38. What is your current responsibility at the place you are working?
Answer:
39. What is your career goal?
Answer:
40. What is ‘agile’?
Answer: Agile software development is a group of software development methods based on iterative and incremental development.
Agile is a conceptual framework generally centered on iterative and incremental delivery of working software, driven by the customer.
Agile methods break tasks into small increments with minimal planning and do not directly involve long-term planning.
Iterations are short time frames (timeboxes) that typically last from one to four weeks.
Each iteration involves a cross functional team working in all functions:
planning, requirements analysis, design, coding, unit testing, and acceptance testing.
41. Do you know any Linux shell programming?
42. Which are the blocks in PL/SQL?
Answer: A PL/SQL Block consists of three sections:
The Declaration section (optional).
The Execution section (mandatory).
The Exception (or Error) Handling section (optional).
43. What is multiple declared block?
A block can be (recursively) nested, that is, a block can contain other blocks, which in turn can again contain other blocks asf.
declare
variable declarations
begin
sql statements
declare
variable declarations
begin
sql statements
exception
exception handler
end;
further-sql-statements
exception
exception handler
end;
44. What is an Exception block? What happens when an exception takes place? What are the
boundaries of an Exception Section? Can we have multiple Exception section in a PL/SQL block?
45. What are the types of error handling? What are the differences between explicit and implicit error
handling?
46. What data type would you use for Pie?
47. What are the data types related to numbers? What is the difference between number and
integer?
48. What is float? What is the difference between float and number?
49. What is rowid? Can different tables have similar rowid?
50. What is an index? Suppose there are 15 columns in a table. Can we use index on every column
of a table? What will happen if we use index for every column of the table?
51. What is Error Processing/Trapping or Exception Handling?
52. How many types of loops do we have? How would you use Exception in a Loop?
53. How do you handle an exception a while loop and continue process?
54. Give an example of while loop.
55. What is Cursor? Why do we use a Cursor? How do we use a cursor in a PL/SQL block?
56. What is implicit Cursor? What is explicit cursor? What are the cursor attributes we have?
57. What are the differences between a For and While loop?
58. What is commit?
59. What is Substr?
60. What is the difference between function and procedure? How does a procedure return a value?
Answer:
61. How does a function return a value? How does a function return multiple values? How does
procedure return multiple values? How can we return multiple values from a function? Like if I
want to get three different values (first_name, last_name, and age)from a function, what should I
do? Can we use out parameter in a function?
Answer: we can but it is not a good programming practice.
62. What is varray? Can we hold multiple data in that varray?
Answer:
63. What is trigger? Why is a trigger executed? Can we use triggers anywhere except at DML level?
Answer:
64. How can we use triggers in insert in various ways?
Answer:
65. What is a package?
Answer:A package is a collection of PL/SQL elements like Cursors, Variables (scalars, records, tables, etc.) and constants,
Exception names and pragmas for associating an error number with an exception,
PL/SQL table and record TYPE statements, Procedures and functions
Why should we use package? What are the advantages of a package?
Advantages of package:
-----------------------
A. Modularity
- Encapsulate related constructs.
B. Easier Application Design
- Code and compile specification and body separately.
C. Hiding Information
- Only the declarations in the pacakge specification
are visible and accessible to application.
- Private constructs in the package body ar
1. Introduce yourself.
2. Tell me about your PL/SQL experience. What have you used to do the coding so far?
3. What was your responsibility there?
4. What are the projects you have worked with so far?
5. What is the longest Package you have written so far? How many lines did it consist of? What was the logic
of the Package?
6. What are the procedures and functions you wrote there? Give me some of their names. Can you
remember the syntax/coding of some of the procedures and functions you constructed there? What was
the purpose/logic of those/that procedure and the function?
7. Suppose, I will insert 1,000,000 rows in a table. I want my PL/SQL block to commit after inserting 1000
rows. How can I use this logic in PL/SQL?
Answer:
declare
vcounter number:=0;
begin
for i in 1..1000000 loop
insert into test1 values(i);
vcounter:= vcounter+sql%rowcount;
if vcounter >=10000 then
commit;
vcounter:=0;
end if;
end loop;
end;
8. What is integrity constraint?
Answer: integrity constraint ensures that the data in the table in database meet/confirm a defined
set of correctness and quality.
What are the names of the Oracle Integrity Constraint?
Answer: Not Null ,Primary Key ,Foreign Key ,Check ,Unique
9. What is Check Constraint?
Answer: A check constraint allows to state a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used.
The following table allows only numbers that are between 0 and 100 in the column a;
create table ri_check_1 (
a number check (a between 0 and 100),
b number
);
Check constraints can be added after a table had been created:
alter table ri_check_1
add constraint ch_b check (b > 50);
ref:http://www.adp-gmbh.ch/ora/misc/integrity_constraints.html
10. What is rollback?
11. What is View?
What is updateable View?
Answer: we cant do the operatons of insert delete and update is called non updatable views or read oly views
whereas we can insert delete and update operations can done in updatable views with some conditions
(should be derived from base table and should not contain derived columns,
views should not contain groupby having and distinct clauses at the outer most level,
views should include all the not null columns of the base table)
ref.: http://www.coolinterview.com/interview/34123/
Example: create OR REPLACE view emp_dept_v as
select ename,empno, job, dEPT.DEPTNO, DNAME
from emp, dept
where emp.deptno=dept.deptno
INSERT INTO EMP_DEPT_V VALUES('N',1111,'J',11,'D')
-- unable to insert
CREATE OR REPLACE TRIGGER T_EMP_DEPT_V
INSTEAD OF INSERT ON EMP_DEPT_V
BEGIN
INSERT INTO EMP (ename,empno, job)
VALUES (:NEW.ename,:NEW.empno, :NEW.job);
END;
INSERT INTO EMP_DEPT_V VALUES('N',1111,'J',11,'D')
--data inserted
What is the difference between Views and Snapshots?
A snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data between distributed databases.
A snapshot is a table that contains the
results of a query of one or more tables or views, often located on
a remote database.
In Oracle 9i instead of snapshot we can use meterialized views which can act as snapshot.
Diff between view & snapshot is view is not a table its a virtual table to join one or more table or to prevent access from users on complete data on table. whenever we will access the view that brings the data from tables only not from view(it wont store any kind of data).
but the snapshot itself store the query result data. usually we will use to get remote data to replicate the changes made at remote site.
http://www.geekinterview.com/question_details/20813
Can we use View to update a table?
12. What is group by function? What is having clause? Why do we use having clause? What is the sequence
of the key words/clauses of a SQL Statement with a Group by Function? Does Having clause come after
Group by clause or before Group by clause?
13. What are the differences between Truncate, Drop and Delete? I want to Truncate 50 rows in a table
keeping the rest of the rows, give me the syntax.
14. What is Rowid?
15. What is rownum? Why do we use Top N Analysis?
16. Suppose there are duplicate rows in a table. How can we delete the duplicate rows keeping only the
distinct rows?
17. What is trigger? How many types of triggers are there? What is the syntax of creating a Trigger? How
many different triggers can be used for a table? In which type of trigger both old value and new value can
be inserted?
18. What is Cursor? How many Cursors are there? What is Implicit Cursor? What is Explicit Cursor? What are
the Cursors attributes? How do we use Cursor attributes? Write a PL/SQL block where you will use an
Explicit Cursor?
19. What is Ref Cursor? What is the use of a Ref Cursor? Write a PL/SQL block using Ref Cursor.
20. What is loop? What is the difference between For Loop and While loop? In what section of a PL/SQL block
do we use Loop? How do we use loop in a Cursor? How does a Looping works?
21. What PL/SQL block do we have? What are the differences between a Procedure and a Function? Can a
Function return more than one value? What do we have to do to make a Function return more than one
value?
22. What is %rowtype and what is %type? Which is a good % type?
23. What is Package? What is the difference between a stored procedure and a Package? What are Public
Construct and Private Construct in a Package? What are the differences between them? Suppose a
package has no compilation error but while trying to call it from the Front end, user gets a
Message, “Object not defined”. What may be the cause of the problem? How can we handle it?
24. What is Collections? What are the Collections we have?
25. What is Varray? How do you populate a Varray?
26. What are PL/SQL tables? Can we have multiple columns in a PL/SQL tables?
27. Can we handle an error in a PL/SQL block? What is an Exception? How many types of Exceptions are
there? How can we trap/handle an Exception in a PL/SQL block? Give us syntax where you will trap
an Exception in an Exception Handling section. How can we give different message to the end users in
Exceptions? How will you raise a user defined exception and handle it? How can we store the errors in an
Error_log table? What is Get_error in Oracle?
28. How can we see the last day of a month? Or, which built in Function is used to see the last day of a
month? How can we see the current date? How can we see the current time? Provide the syntax.
29. What are the differences between Unique Index and Unique Constraint? What are the differences
between Unique Index and Primary Key Constraint?
30. What are nvl and nvl2? What are the differences? Give us syntax for each.
31. What is an Errorlog table?
Answer:
32. What is Varchar2? What is the maximum length Varchar2?
Answer:
33. What is DBMS_OUTPUT.PUT_LINE?
Answer:
34. What is partition? How we can use Partition? Why do we use Partition on a table?
Answer:
35. How I can write something into a file using Oracle?
Answer:
36. They have given us the image of five tables with some data and asked various questions on these tables.
Answer:
CEO Questions for Interview:
37. What is your level of PL/SQL? How much do you work on PL/SQL? How much would you give yourself out of ten at PL/SQL?
Answer:
38. What is your current responsibility at the place you are working?
Answer:
39. What is your career goal?
Answer:
40. What is ‘agile’?
Answer: Agile software development is a group of software development methods based on iterative and incremental development.
Agile is a conceptual framework generally centered on iterative and incremental delivery of working software, driven by the customer.
Agile methods break tasks into small increments with minimal planning and do not directly involve long-term planning.
Iterations are short time frames (timeboxes) that typically last from one to four weeks.
Each iteration involves a cross functional team working in all functions:
planning, requirements analysis, design, coding, unit testing, and acceptance testing.
41. Do you know any Linux shell programming?
42. Which are the blocks in PL/SQL?
Answer: A PL/SQL Block consists of three sections:
The Declaration section (optional).
The Execution section (mandatory).
The Exception (or Error) Handling section (optional).
43. What is multiple declared block?
A block can be (recursively) nested, that is, a block can contain other blocks, which in turn can again contain other blocks asf.
declare
variable declarations
begin
sql statements
declare
variable declarations
begin
sql statements
exception
exception handler
end;
further-sql-statements
exception
exception handler
end;
44. What is an Exception block? What happens when an exception takes place? What are the
boundaries of an Exception Section? Can we have multiple Exception section in a PL/SQL block?
45. What are the types of error handling? What are the differences between explicit and implicit error
handling?
46. What data type would you use for Pie?
47. What are the data types related to numbers? What is the difference between number and
integer?
48. What is float? What is the difference between float and number?
49. What is rowid? Can different tables have similar rowid?
50. What is an index? Suppose there are 15 columns in a table. Can we use index on every column
of a table? What will happen if we use index for every column of the table?
51. What is Error Processing/Trapping or Exception Handling?
52. How many types of loops do we have? How would you use Exception in a Loop?
53. How do you handle an exception a while loop and continue process?
54. Give an example of while loop.
55. What is Cursor? Why do we use a Cursor? How do we use a cursor in a PL/SQL block?
56. What is implicit Cursor? What is explicit cursor? What are the cursor attributes we have?
57. What are the differences between a For and While loop?
58. What is commit?
59. What is Substr?
60. What is the difference between function and procedure? How does a procedure return a value?
Answer:
61. How does a function return a value? How does a function return multiple values? How does
procedure return multiple values? How can we return multiple values from a function? Like if I
want to get three different values (first_name, last_name, and age)from a function, what should I
do? Can we use out parameter in a function?
Answer: we can but it is not a good programming practice.
62. What is varray? Can we hold multiple data in that varray?
Answer:
63. What is trigger? Why is a trigger executed? Can we use triggers anywhere except at DML level?
Answer:
64. How can we use triggers in insert in various ways?
Answer:
65. What is a package?
Answer:A package is a collection of PL/SQL elements like Cursors, Variables (scalars, records, tables, etc.) and constants,
Exception names and pragmas for associating an error number with an exception,
PL/SQL table and record TYPE statements, Procedures and functions
Why should we use package? What are the advantages of a package?
Advantages of package:
-----------------------
A. Modularity
- Encapsulate related constructs.
B. Easier Application Design
- Code and compile specification and body separately.
C. Hiding Information
- Only the declarations in the pacakge specification
are visible and accessible to application.
- Private constructs in the package body ar