Oracle PL/SQL Frequently asked questions

Sl.No Category Complexity Questions
1 Performance tuning Simple What are the types of optimizer?
A: Rule-based and Cost-based optimizers
2 Performance tuning Medium Which optimizer mode is the default one?
A: Optimizer takes the rule based approach as default.
3 Performance tuning Medium What is explain plan?
A:The sequence of steps the optimizer chooses to execute the SELECT, UPDATE, INSERT, DELETE statements.
4 Performance tuning Complex Can you force the optimizer to choose the optimal execution plan?
A:Yes. By using hints option.
5 Performance tuning Medium How will you tune the following query?
Select empid, ename, dno from emp where dno in(select deptno from dept)
A: select empid, ename,dno from emp, dept where dno=deptno
6 Performance tuning Simple Which is faster? Using IN or EXISTS?
A: EXISTS is faster compared to IN.
7 Joins Simple What are the types of joins? What are they?
A:Simple join, Outer join, Self join
Simple join returns the common rows between two or more tables.
Outer join returns the matching rows from one, and all the rows from the other table, with the unmatched rows marked as NULL.
Self join is joining a table to itself.
8 Queries Simple How will you fetch the first 10 records of a table?
A: select * from emp where rownum<11
9 Fundamentals Simple How many types of SQL statements are there in Oracle?
A: DDL - create, alter, drop
DML - select, insert, update, delete
TCL - commit, savepoint, rollback
DCL – Grant, Revoke
10 PL/SQL Simple What are cursors?
A: A cursor is a handle ( name or a pointer) for the memory associated with a specific statement. A cursor is basically an area allocated by Oracle for executing the SQL Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
11 PL/SQL Simple What are attributes of cursor?
%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
12 Queries Medium How will you identify the duplicates in a particular column?
A:Select col1, count(*) from table1 group by col1 having count(*)>1
13 PL/SQL Medium What are packages? Advantages of packages?
A:Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents.
14 PL/SQL Simple How many types of parameters can be passed to a stored procedure?
A:3 types: IN, OUT, INOUT
15 PL/SQL Simple Difference between Stored procedure and functions
A: Function returns a value to the calling block whereas the Stored procedure does not.
16 PL/SQL Simple What are Database Triggers and Stored Procedures
A: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table.
17 Partition Medium What are partitions?
A: Partitions are segments of a logical table split by range of key column values.
18 Partition Complex Syntax of creating a partition
Create table A(a varchar(1), b numeric(10))
Partition by range(b)
(partition p1 values less than 10,
Partition p2 values less than 20,
Partition p3 values less than maxvalue)
19 Normalization Simple What is ER model?
A: It is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects.
20 PL/SQL Simple How many types of Exceptions are there
There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
21 Locks Medium How many types of Locks are there? What are they?
A: Shared, Share Update, Exclusive
22 Normalization Medium You are asked to model a many-many relationship given the students and teachers data. How many tables would you require to implement the same?
A: 3
23 Queries Medium What is the difference between a sub query and a correlated sub query?
A: Subquery is a query inside a query, where the sub query gets executed only once.
In a correlated sub query, the sub query gets executed once for each row processed by the parent query.
24 Fundamentals Simple What are the two types of synonyms? What is the difference between the two?
A: Private and Public.
Private synonyms are created by the database user and is available only to the user, where as public synonym is created by the database administrator and is available to all users.
25 PL/SQL Simple How many types of Exceptions are there? What are they?
A: There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
26 PL/SQL Complex Can you execute DDL statements inside a PL/SQL block? How?
A: Yes. By using 'execute immediate' clause.
27 Queries Medium What are embedded SQLs?
A: Sql Statements embedded in a high level language such as 'C' are called embedded sqls.
28 Fundamentals Medium Table Emp has 3 records:
A
B
C
Sequence of 4 sql stmts are executed:
Delete emp where val='A'
Alter table emp modify(val varchar2(5))
Delete emp where val='B'
Rollback
What is the status of the table emp?
A: Table emp has 2 records B & C.
29 PL/SQL Complex What are mutating tables?
A: Mutating table is a table that is currently being modified by insert update or delete statement causing a trigger to fire that again tries to modify the same table
30 Fundamentals Simple What is the difference between deleting and truncating of tables
A: Deleting a table will remove the rows from the table but space consumed by these rows is not released. But truncating a table deletes it completely and release the space consumed by it too.
31 Fundamentals Simple Name a few pseudocolumns.
A: NEXTVAL, CURRVAL, ROWNUM etc..
32 Queries Medium What is 'on delete cascade' option?
A: When a key value is deleted, if you want to also delete the references of the key, go for on delete cascade option.
33 Fundamentals Medium How many columns can table have?
A: The number of columns in a table can range from 1 to 254.
34 Database connection Medium What is tnsnames.ora?
A tnsnames.ora file provides the ability to reference oracle databases by a simple alias.
35 Performance tuning Complex How do you generate statistics of a particular table involved in a query?
A: Analyze table compute statistics
36 PL/SQL Complex What are PL/SQL tables?
A: PL/SQL tables are not stored in the database, and is usually created inside a PL/SQL block with only one column.
37 Database connection Medium What is Oracle SID? Where do you set that?
A: If Oracle is used in a UNIX server, then Oracle SID is set in the .profile file. It denotes the database.
38 Queries Medium How do u implement the If statement in the Select Statement?
A: Using DECODE
39 Queries Simple How can you fetch the uncommon rows from the result set of 2 queries?
A: Using MINUS operator
40 Conversion functions Medium What is the difference between to_char() & to_number() function/
A: To_char converts a number or date to a char.
To_number converts a char or varchar to a number.
41 What are inline views? Views
42 What are the advantages of force views? Views
43 What type of DBMS is supported by Oracle 8i? Fundamentals
44 What are materialized views? Views
45 What are the rules that are to be followed while inserting data into views? Views
46 Can you insert records into materialized views? Views
47 What is the difference between a snapshot and a materialized view? Fundamentals
48 What is the advantage of using a clustered index? Index
49 What are the enhancements on Views in Oracle 8i? Views
50 What are the new types of triggers added in Oracle 8i? Triggers
51 Executing the query on a million records performance. Index
How will you tune it?
Select *, revenue-cost from tableA
52 Difference between Bitmap and B-Tree indices. Index
53 Which feature in Oracle 8i supports incremental updates? Summary tables
54 Hash and composite partitioning techniques Partition
55 What are the constraints imposed on creating a column of 'LONG' datatype? Datatypes
56 What happens when you use a 'for update of clause' in a select stmt? Locks
57 How are data stored in a nested table? Nested tables
58 What are REF cursors? PL/SQL
59 What is a star query? Give eg. Queries
60 There are 2 exceptions in a PL/SQL block. When will both the exceptions get executed? (Twisted) PL/SQL
61 When do you use 'connect by prior' in a query? Queries to display records hierarchy tree level
62 What is the difference between a translate and a replace of a string? Egs? String functions
63 What happens when you modify and re-compile a procedure? Procedure
64 What are bind variables? PL/SQL
65 How will you find the byte size of a particular column? Numeric functions
66 'Select sysdate from dual' returns 5 records. What's the implication? Fundamentals
67 How will you fetch the age of a person, given his DOB column in a table, using a simple query? Date functions