Oracle Interview Questoins - Part 1

1)How to display row number with record

Select rownum, ename from emp;

2)How to view version information in oracle

select * from v$version

3)Find 2nd higest sal in emp

select min(sal) from emp a
where 1 = (select count(*) from emp b where a.sal < b.sal) ;

4)Delete duplicate rows from table

delete from T1
where rowid <> ( select max(rowid)
from t1 b
where b.col1 = t1.col1
and b.col2 = t1.col2 )

5)How to select a row using index

Specify the index column in the WHERE clause

6)Difference between Primary Key and Unique Key

Primary Key : Doesn’t allow nulls
Unique Key : Allows nulls

Primary key : By default creates cluster index on the
column
Unique Key : Creates noncluster index

7) What is self Join

Self join joins a table to itself

8)Is it possible for a table to have more than one foreign key

A table can have any number of foreign keys, but it can have only one primary key

9)How to display number value in words

select t_amount, (to_char(to_date(t_amount,'j'), 'jsp')) from transaction

10)Difference between Delete, Truncate, and Drop table

Delete and Truncate will leave the structure of the table.
Drop will remove the structure also

-- Truncate is fast as compared to Delete. Delete will generate undo information,in case of rollback, but Truncate will not

-- Cannot specify 'where' in Truncate, can specify 'Where' in Delete

11)what is %rowtype

associates a variable to an entire table row

12)what is %type

associates a variable to a single column type

13)what is sqlcode

returns the current database error number
all the numbers are negative except, NO_DATA_FOUND,which is positive +100

14) what is sqlerrm

returns textual error message

15)what is %isopen

Check if an cursor is open

16)How to Show current user

show user

17)How to change prompt

set sqlprompt "whatever>"

18) if a column has "date" data type, and you want NA to be printed if null

select nvl(to_char(t_cas_send_date),'na') from table_name

19)Give some cursor attributes

cursor_name%found
cursor_name%notfound
cursor_name%rowcount
cursor_name%isopen

20)Get Nth highest salary

select distinct (a.sal)
from emp a
where &n = (select count (distinct (b.sal))
from emp b
where a.sal <= b.sal);

21)Get Odd number of records

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

22)Get Even number of records

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

23) Which date function returns numeric value

months_between

24)Give some plsql exception

Too_many_rows,
No_Data_Found,
Value_Error,
Zero_Error,
Others

25) plsql Cursor Exception

Cursor_Already_Open,
Invalid_Cursor