Question { TCS, 76027 }

find out the second highest salary?

Answer

select sal from(select sal,rank() over(order by sal desc)
rnk from emp) e
where e.rnk=2;
(OR)
select rownum,sal from emp group by sal,rownum
having rownum=2;
(OR)
select rn,sal from( select rownum rn,sal from emp group by
sal,rownum order by sal desc)
where rn=2;

Question { Infosys, 7227 }

How can we manage the gap in a primary key column created
by a sequence?
Ex:a company has empno as primary key generated by a
sequence and some employees leaves in between.What is the
best way to manage this gap?

Answer

yes...yes........ you can do it simply by updating that
table using rownum yar. see my example below.
SQL> create table test(sno number(2),sname varchar2(20));

Table created.

now if you have the data in the table like follows.

SQL> select * from test;

SNO SNAME
---------- --------------------
1 sdf
3 sdf
6 ewr
7 rt
90 drt

now just do the following simple statement.


SQL> update test set sno=rownum;

5 rows updated.


now here is the o/p for eliminating the gap generated by the
sequence.

SQL> select * from test;

SNO SNAME
---------- --------------------
1 sdf
2 sdf
3 ewr
4 rt
5 drt
then do the commit. thats all....................
now enjoy with this coding


suppose if you want to allocate the with any kind of series
of values for updating the table just follow the below
procedure

SQL> update test set sno=sql.nextval;

NOTE:sql is the sequence.

this statement will updates ur table with
what ever you given with the start with value and it ll
increment by which value you had given in the increment by
clause.

Question { Secon, 4135 }

can a table has a column that has only view data and in
other columns we can change data?

Answer

ya,we can create trigger on that table
like as follows
CREATE OR REPLACE TRIGGER key_gen
BEFORE INSERT ON
FOR EACH ROW
DECLARE
v_num NUMBER(5);
BEGIN
SELECT seq.nextval INTO v_num from dual;
:new.id:=SAM||LPAD(v_num,3,0);
END;

the structure like as follows
(id VARCHAR2(20),
name VARCHAR2(15)
)
now you can just add the records like as follows
INSERT INTO
(
name
)
VALUES
(
'&Name'
);
then trigger will fires and automatically it will inserts
into that table with out our knowledge.

NOTE:here "seq" is forward sequence.if it start with 1 and
incremented by 1 then the output will be like as follows

select * from

id name

SAM001 TV
SAM002 LCD

Is This Answer Correct ?

0 Yes

1 No

Question { Polaris, 4470 }

How to select oracle sequence from different schema and

How to select oracle sequence from different Databases.

Explain with example

Answer

How to select oracle sequence from different schema

ANS:
a. SCHEMANAME.SEQUENCENAME.CURRVAL.
b. SCHEMANAME.SEQUENCENAME.NEXTVAL

How to select oracle sequence from different Databases

ANS:

a. SCHEMANAME.SEQUENCENAME.CURRVAL@DBLINK
b. SCHEMANAME.SEQUENCENAME.NEXTVAL@DBLINIK

Thanks,
Hari

Question { IBM, 2870 }

How can one skip any number of rows while loading the DB
tables with SQL Loader? Similarly how can one skip any
column?

Answer

LOAD DATA
INFILE 'Fullalbum.csv'
truncate
INTO TABLE DP_fa
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
field2 FILLER,--for skipping the first column
sno,
performance_ky,
performance_nm,
isrc,
track_media_ky,
song_nm,
full_album,
country_nm,
isrc_country_cd
)

here first column will be skipped.

if you want to skip number of rows do the following
in cmd prompt
enter the commands like...
sqlldr /@
cotrol='' skip=n that you want to skip>

Thanks,
Harikanth.

Is This Answer Correct ?

0 Yes

0 No

Question { Microsoft, 5088 }

There is a big table with "n" of rows and 40 + columns .It
doesn't have primary key.How do you select the primary key.
In other words how do you get the duplicate records.

Answer

Suppose Table name is Dup_Del with id as a column(consists
10,20,30,10,10,30,20)then

select * from Dup_Del
where rowid not in(select * from
(select min(rowid) from Dup_Del
group by id
);
the above query will give you the duplicate records. if you
want unique records then

select * from Dup_Del
where rowid in(select * from
(select min(rowid) from Dup_Del
group by id
);