|
Oracle Interview Questions and Answers
What should be the return type for a cursor
variable. Can we use a scalar data type as return type?
The return type for a cursor must be a record type.It
can be declared explicitly as a user-defined or %ROWTYPE
can be used. eg TYPE t_studentsref IS REF CURSOR RETURN
students%ROWTYPE
What are different Oracle database objects?
-TABLES
-VIEWS
-INDEXES
-SYNONYMS
-SEQUENCES
-TABLESPACES etc
What is difference between SUBSTR and INSTR?
SUBSTR returns a specified portion of a string eg
SUBSTR('BCDEF',4) output BCDE INSTR provides character
position in which a pattern is found in a string. eg
INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
Display the number value in Words?
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- ----------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like, Rs. Three Thousand
only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| '
only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- -----------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL
language interface and reporting tool. Its a command
line tool that allows user to type SQL commands to be
executed directly against an Oracle database. SQL is a
language used to query the relational
database(DML,DCL,DDL). SQL*PLUS commands are used to
format query result, Set options, Edit SQL commands and
PL/SQL.
What are various joins used while writing SUBQUERIES?
Self join-Its a join foreign key of a table references
the same table. Outer Join--Its a join condition used
where One can query all the rows of one of the tables in
the join condition even though they don't satisfy the
join condition.
Equi-join--Its a join condition that retrieves rows from
one or more tables in which one or more columns in one
table are equal to one or more columns in the second
table.
What a SELECT FOR UPDATE cursor represent.?
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.
What are various privileges that a user can grant to
another user?
-SELECT
-CONNECT
-RESOURCES
Display the records between two range?
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto minus select
rowid from emp where rownum<&Start);
minvalue.sql Select the Nth lowest value from a table?
select level, min('col_name') from my_table where
level = '&n' connect by prior ('col_name') < 'col_name')
group by level;
Example:
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second lowest salary:
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level
What is difference between Rename and Alias?
Rename is a permanent name given to a table or column
whereas Alias is a temporary name given to a table or
column which do not exist once the SQL statement is
executed.
Difference between an implicit & an explicit cursor.?
only one row. However,queries that return more than one
row you must declare an explicit cursor or use a cursor
FOR loop. Explicit cursor is a cursor in which the
cursor name is explicitly assigned to a SELECT statement
via the CURSOR...IS statement. An implicit cursor is
used for all SQL statements Declare, Open, Fetch, Close.
An explicit cursors are used to process multirow SELECT
statements An implicit cursor is used to process INSERT,
UPDATE, DELETE and single row SELECT. .INTO statements.
What is a OUTER JOIN?
Outer Join--Its a join condition used where you can
query all the rows of one of the tables in the join
condition even though they don’t satisfy the join
condition.
What is a cursor?
Oracle uses work area to execute SQL statements and
store processing information PL/SQL construct called a
cursor lets you name a work area and access its stored
information A cursor is a mechanism used to fetch more
than one row in a Pl/SQl block.
What is the purpose of a cluster?
Oracle does not allow a user to specifically locate
tables, since that is a part of the function of the
RDBMS. However, for the purpose of increasing
performance, oracle allows a developer to create a
CLUSTER. A CLUSTER provides a means for storing data
from different tables together for faster retrieval than
if the table placement were left to the RDBMS.
What is OCI. What are its uses?
Oracle Call Interface is a method of accesing database
from a 3GL program. Uses--No precompiler is required,PL/SQL
blocks are executed like other DML statements.
The OCI library provides
--functions to parse SQL statemets
--bind input variables
--bind output variables
--execute statements
--fetch the results
How you open and close a cursor variable. Why it is
required?
OPEN cursor variable FOR SELECT...Statement
CLOSE cursor variable In order to associate a cursor
variable with a particular SELECT statement OPEN syntax
is used. In order to free the resources used for the
query CLOSE statement is used.
Display Odd/ Even number of records?
Odd number of records:
select * from emp where (rowid,1) in (select rowid,
mod(rownum,2) from emp);
Output:-
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid,
mod(rownum,2) from emp)
Output:-
2
4
6
What are various constraints used in SQL?
-NULL
-NOT NULL
-CHECK
-DEFAULT
Can cursor variables be stored in PL/SQL tables. If yes
how. If not why?
No, a cursor variable points a row which cannot be
stored in a two-dimensional PL/SQL table.
Difference between NO DATA FOUND and %NOTFOUND?
NO DATA FOUND is an exception raised only for the
SELECT....INTO statements when the where clause of the
querydoes not match any rows. When the where clause of
the explicit cursor does not match any rows the %NOTFOUND
attribute is set to TRUE instead.
Can you use a commit statement within a database
trigger?
No
What WHERE CURRENT OF clause does in a cursor?
LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
FHKO;;;;;;;;;SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;
There is a string 120000 12 0 .125 , how you will find
the position of the decimal place?
INSTR('120000 12 0 .125',1,'.')
output 13
What are different modes of parameters used in functions
and procedures?
-IN -OUT -INOUT
How you were passing cursor variables in PL/SQL 2.2?
In PL/SQL 2.2 cursor variables cannot be declared in a
package.This is because the storage for a cursor
variable has to be allocated using Pro*C or OCI with
version 2.2, the only means of passing a cursor variable
to a PL/SQL block is via bind variable or a procedure
parameter.
When do you use WHERE clause and when do you use HAVING
clause?
HAVING clause is used when you want to specify a
condition for a group function and it is written after
GROUP BY clause. The WHERE clause is used when you want
to specify a condition for columns, single row functions
except group functions and it is written before GROUP BY
clause if it is used.
Difference between procedure and function.?
Functions are named PL/SQL blocks that return a value
and can be called with arguments procedure a named block
that can be called with parameter. A procedure all is a
PL/SQL statement by itself, while a Function call is
called as part of an expression.
Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a
Boolean value whereas IN returns a value.
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 35
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
