Question { Saama Tech, 32243 }

What is difference between CHAR and VARCHAR2?What is the
maximum SIZE allowed for each type?

Answer

Varchar2: The max. allowed length is 4000 byte default 0
i.e. nothing. This is variable length datatype. it will
take only same space as value stored.

Char: Max. permitable length 2000 byte. Default length 0.
It will store value blank padded to right side till
full length as declared.

Ex. SQL> create table fg (d varchar2);
create table fg (d varchar2)
*
ERROR at line 1:
ORA-00906: missing left parenthesis


SQL> create table fg (d char);

Table created.

SQL> desc fg
Name Null? Type
------------------------------- -------- ----
D CHAR(1)
SQL> create table cher_chk (col1 char(12));

Table created.

SQL> insert into cher_chk values ('raj');

1 row created.
SQL> select length(col1) from cher_chk
2 /

LENGTH(COL1)
------------
12

Question { Oracle, 12620 }

declare
l1 number := null;
l2 number :=null;
begin
if l1=l2 then message('equal');
else
if l1<>l2 then message('not equal');
else
message('else');
end if;
end if;
end;
What will be the output ?

Answer

In term of oracle block should be like as........

SQL> declare
2 l1 number := null;
3 l2 number :=null;
4 begin
5 if l1=l2 then
6 dbms_output.put_line ('equal');
7 elsif l1<>l2 then
8 dbms_output.put_line ('not equal');
9 else
10 dbms_output.put_line ('else');
11 end if;
12 end;
13 /
else

PL/SQL procedure successfully completed.

Answer is as shown at end of block ELSE.
because u can't compare a null value to other null.

Question { Microsoft, 7923 }

Types of joins?

Answer

(1) Inner or Equei join
(2) Outer or no - equei join
(a) left outer
(b) right outer
(c) full outer
(3) Cartision or cross join
(4) Self join
(5) Natural join

Question { TCS, 63603 }

Difference between sub query and nested query ?

Answer

Main Diff. b/w Subquery & Nested Subquery:

The query inside a query is known as a subquery.
When we have another query again query inside subquery then
it is known as nested subquery.

Question { 5939 }

can i write pl/sql code in a package

Answer

Yes we can write pl/sql code directly in package. This
process is known as package initialisation. In this we can
write the code direct in package body in the end of body.

Question { 9261 }

Delete the emps whose salaries are lowest sals of their own
dept.

Answer

delete from emp where (deptno,nvl(comm,0)) in (select
deptno,min(nvl(comm,0)) from emp
group by deptno)

Question { Wipro, 11834 }

what is clustered index?why is it created?

Answer

Clustring is a method for storing the tables in same block
that are related.
Cluster index are used to achive this object.

Question { Microsoft, 12964 }

what is trigger?

Answer

A trigger is a DB object like proc. or func. stored in data
dictonary. It will fire implicitly with event on which it
written.

Main use of a trigger are

-- we can take backup of data.
-- Can be used for auditing purpose.
-- For applying complex bussiness rule.
Etc.

Question { Microsoft, 7540 }

can we execute trigger normally at the desired time?

Answer

No, it's not possible to shedule timing for a trigger.

In this situation we can shedule a job.

Question { 22959 }

What is magic table?

Answer

In oracle Dual is a magic table.

Question { Oracle, 16983 }

there are 2 variables called x and y ,x contains 1,2 and y
contains 3,4 we have to swap the values from x to y and y
to x with out using dummy variables and it can be done only
by using a single statement ? how?

Answer

declare
x number:= 1;
y number:= 2;
begin
x := x + y;
dbms_output.put_line (x); -- output 3;
y := x - y;
dbms_output.put_line (y); -- output 1;
x := x - y;
dbms_output.put_line (x); -- output 2;
end;
/

Question { iFlex, 28101 }

what is difference between procedure and function,
procedure and trigger?

Answer

Diff. B/W Procudure & Func......
(1) Proc. may or may not return value or more than one.
but func. must return a single value.
(2) We can call a func. in a select stmt. but func. should
only with IN parameter.
But we can't call a procedure inside a selct stmt.
because calling a procedure itself a PL/SQL block.

There is no too much diff. b/w proc. & func. but the thing
is that when we are looking to get back a single value we
should use func. in case of more than one then we should go
for proc.

Diff. b/w proc. & trigger..

(1) We have to call a proc. explicitly but trigger will
fire implicitly with assosiated event.

Question { Wipro, 15366 }

Give an example of any procedure.

Answer

We can write a procedure with or w/o using parameters.

For procedure the simplest ex. could be like as

SQL> create or replace procedure tr ---calling procedure
2 as
3 begin
4 in_emp; -- Called procedure
5 end;
6 /

in this procedure i am calling another procedure inside
that.

Question { iFlex, 38204 }

what are the advantages of package?

Answer

The main purpose for using a packege is grouping the
related objects like proc., func. etc.

For ex. we can create one package for payroll of HRMS system
that can be used for storing related proc, func. & other DB
objects.

Question { 8484 }

what is a view?

Answer

A view is a logical db object which is created for data
security, data hiding etc. In database onle views
defination stored.

Syntax: create or replace view name_vw as select col1,
col2, ......... from table1, table2 where ....;

Types: (1) simple view
(2) complex view
(3) read only view
(4) force view
(5) with check option