Oracle sql Interview questions 3

1) CREATE TABLE atest (a VARCHAR2 (20))

INSERT INTO atest
  VALUES   ('a')

ALTER TABLE atest
MODIFY a Number;

Is it right statement?

Answer No:

You can't modify the data type if the data is available in the table

2)

create table atest(a number,sal number)

create view atest_v as select a,sal,(sal*.1) comm from atest

insert into atest values(10,1000);

then what is the value of comm?

Value of the comm is :100,it automatically calculate the comm as per definition

3)for the same q2

update emp set comm=1000 where a=10;

then what is the value of sal?

salary remains 1000

4)create table atestv as select * from atest_v;

update atest_v set sal=800 where a=10,

commit;

then what is the value of sal in the atestv?

800

5)create table emptest

( a number primary key) how many indexes will be created?

One
Unique Index will be created

6)Can I update rowid?

No

7)

CREATE OR REPLACE FUNCTION afun1 (a NUMBER)

   RETURN NUMBER

IS

   b   NUMBER;

   c number;

BEGIN

c:=a*10;

   b := afun1(c);

   RETURN b;

END afun1;

what is the output ?

Need to justify your answer?

It will give compile time error as afunc is not available 

8)CREATE OR REPLACE FUNCTION afun1 (a IN NUMBER)
   RETURN NUMBER
IS
BEGIN
   RETURN 10;

   DBMS_OUTPUT.putline (a * 10);
END afun1;

what is the output?

Ans:10(Return )

if you give
select afun1(10) from dual;
it will show only value 10

9)what is sequence;

I am using sequence in emp table

ex:

insert into emp values(emp_s.nextval,'test');

if update empno is the sequence value will be incremented by the same value?

No,It wont' incremented

<!--[if gte mso 9]> E4E4 Normal E4E4 2 2 2015-02-10T19:06:00Z 2015-02-10T19:06:00Z 2 175 999 8 2 1172 12.00 <![endif]

     when  you create a table as ‘create table xyz as select * from abc’- what all are copied from the table xyz?

a)      All constraints

b)      Only primary keys

c)      Only foreign keys

d)     Only not null constraints 

   

      When a table  is dropped , what happens to the view defined on that table?

a)      View is automatically dropped

b)      View is not dropped

c)      View becomes invalid

d)     None of above

 which data dictionary view has source code of stored procedures

a)      USER_PROCEDURES

b)   USER_SOURCE

c)      USER_OBJECTS

d)     None

  what is datatype of Global variables

a)      number

b)      character

c)      both a & b

d)     None

What is a Cartesian product?

What is the default ordering of an ORDER BY clause in a SELECT statement?

Can I Update From Another Table ?

 How can I get the definition of a view ?

Difference between group functions and single row functions

Difference between TRUNCATE and DELETE 

What is difference between SUBSTR and INSTR 

Which is more faster - IN or EXISTS?

 

 

create table atest(a varchar2(20)) insert  into atest values('a'); alter table atest modify a Number; is it right statement? 2) create table atest(a number,sal number) create view atest_v as select a,sal,(sal*.1) comm from atest insert into atest values(10,1000); then what is the value of comm? 3)for the same q2 update emp set comm=1000 where a=10; then what is the value of sal? 4)create table atestv as select * from atest_v; update atest_v set sal=800 where a=10, commit; then what is the value of sal in the atestv? 5)create table emptest ( a number primary key) how many indexes will be created? 6)Can I update rowid? 7)CREATE OR REPLACE FUNCTION afun1 (a NUMBER)    RETURN NUMBER IS    b   NUMBER;    c number; BEGIN c:=a*10;    b := afun1(c);    RETURN b; END afun1; what is the ouput ? Need to justify your answer? 8)CREATE OR REPLACE FUNCTION afun1 (a IN NUMBER)    RETURN NUMBER IS BEGIN    RETURN 10;    DBMS_OUTPUT.putline (a * 10); END afun1; what is the output? 9)what is sequence; I am using sequence in emp table ex: insert into emp values(emp_s.nextval,'test'); if update empno is the sequence value will be incremented by the same value?