ajit kumar nayak


{ City } bangalore
< Country > india
* Profession * software engineer
User No # 103755
Total Questions Posted # 7
Total Answers Posted # 19

Total Answers Posted for My Questions # 7
Total Views for My Questions # 20383

Users Marked my Answers as Correct # 17
Users Marked my Answers as Wrong # 0
Questions / { ajit kumar nayak }
Questions Answers Category Views Company eMail

I am creating an index on Emp table Empno column,if u using this indexed column in ur SELECT stmt. where clause,then how do u know that yr index will be working or nor? Thanks Advance...

 3 SQL PLSQL 5459

I m giving Source, Destination and Age. Write a procedure or function, it will give to u this source to destination tickets are available or not, if available then check this person is senior citizen or not,if this person is senior citizen then give some discount. PLZ give this answer...... Thanks advance.....

 3 SQL PLSQL 5133

create or replace procedure show_tab_rec ( P_tab VARCHAR2 ) IS cmd varchar2(50); begin cmd := 'select * from '|| P_tab; for int in ( execute immediate cmd ) loop dbms_output.put_line ( int.ename||' '||int.deptno); end loop; end; when i m compling this procedure i m getting this error PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in is mod remainder not range rem => .. <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ PLZ solve this error give this question answer asap Thanks advance.......

HCL,

1 Oracle General 4196

When i am connect database through toad,one error occured. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. plz help me thanks advance.............

 Oracle Errors 2325

what is the pl/sql block size in oracle 10g and 11g?

 Programming Languages AllOther 1707

Can any one explain me when i execute below query.. select months_between('07-JUL-12','10-FEB-12') from dual; Out put:- 4.90322581 How oracle calculate?

 Oracle General 930

material view and view disadvantages?

 Oracle General 633



Answers / { ajit kumar nayak }

Question { 6040 }

What is the system function to get the current user's user
id?


Answer

select USERNAME,user_id from user_USERS;

Is This Answer Correct ?    0 Yes 0 No

Question { 6998 }

how to find the second highest salary in a given table????


Answer

SELECT MAX( Sal ), LEVEL
FROM Emp
WHERE LEVEl = &Givelevelno
CONNECT BY PRIOR Sal > Sal
GROUP BY LEVEL;

Is This Answer Correct ?    0 Yes 0 No


Question { IBM, 15137 }

i have a table
eno dno sal
1 10 200
2 10 150
3 10 100
4 20 75
5 20 100

i want to get sal which is less than the avg sal of thri dept.

eno dno sal
2 10 150
3 10 100
4 20 75


Answer

select eno, dno, sal
from test
group by eno, dno, sal
having sal
Is This Answer Correct ?    0 Yes 0 No

Question { Keane India Ltd, 8393 }

suppose we have values like 1 5 7 in a colum.Now we want
numbers like(2 3 4 6) that exists between 1 5 7.How can we
do this using sql query??


Answer

select rownum
from dual
connect by level <= (select max(a) from mising_values)
minus
select * from mising_values;

Is This Answer Correct ?    0 Yes 0 No

Question { 7492 }

how to Update table Sales_summary with max(sales) data from
table sales_dataTable 1. sales_data table Table 2.
Sales_summary

Region sales Region sales
N 500 N 0
N 800 W 0
N 600
W 899
W 458
W 900

I want the Sales_summary After Update like this
Region Sales
N 800
W 900



Answer

update sales_sum s set sales =
(select max(sales) from sales_sum s2 where s.REGIN = s2.REGIN group by REGIN );

Is This Answer Correct ?    2 Yes 0 No

Question { 15820 }

How do you retrieve the last N records from a table?


Answer

SELECT RN, Ename
FROM ( SELECT Rownum RN, Ename
FROM Emp )
WHERE RN = &Grecno;

Is This Answer Correct ?    0 Yes 0 No

Question { TCS, 11663 }

wirte a query to remove null? following table are

col1 col2 col3
dinesh null null
null suresh null
null null prakesh
i want the output like

col1 col2 col3
dinesh suresh prkaesh


Answer

select distinct col1 from samp
where col1 is not null
union all
select distinct col2 from samp
where col2 is not null

Is This Answer Correct ?    0 Yes 0 No

Question { 4578 }

how to get count of tables in particular database in Oracle?


Answer

SELECT COUNT(DISTINCT TABLE_NAME)
FROM COLS

Is This Answer Correct ?    0 Yes 0 No

Question { 3499 }

the user should know to which database he is connected
currently in oracle


Answer

SELECT BANNER FROM V$VERSION;

Is This Answer Correct ?    0 Yes 0 No

Question { 4561 }

how to find the first two highest salaries in deptno in emp
table


Answer

select e.*
from emp e
where rownum < 3
order by e.sal desc ;

Is This Answer Correct ?    0 Yes 0 No

Question { IBM, 21908 }

A table has 150 records. How do you retrieve 100th row to
120th row from that table ?


Answer

select Ename, empno, rn
from (select rownum rn, ename, empno
from emp)
where rn between &gno and &gno

Is This Answer Correct ?    0 Yes 0 No

Question { Zensar, 6756 }

write a pl/sql function if enter a value=0 then output
value=1 and vise verse with out using if and case statements.


Answer

create or replace function vice_versa2 (inp_no number) return number
is
a number := 0;
begin

select decode(inp_no, 1, 0,1) into a from dual;

return a;

end;

Is This Answer Correct ?    1 Yes 0 No

Question { 6961 }

how to retrieve 1st and last row of table without using
group functions??


Answer

SELECT *
FROM EMP
WHERE ROWID = (SELECT MIN(ROWID) FROM EMP)
UNION
SELECT *
FROM EMP
WHERE ROWID = (SELECT MAX(ROWID) FROM EMP)

Is This Answer Correct ?    0 Yes 0 No

Question { 13229 }

How to update salary of employees department wise?


Answer

update emp
set sal = case Deptno when 10 then 1000
when 20 then 2000
when 30 then 3000
else 0
end;

Is This Answer Correct ?    7 Yes 0 No

Question { 5052 }

I have a table emp. There is only one column in the table.
In that , there are only three rows in that column.
The value in the first row is 'A' and the value in the
second row is 'B' and the third row is 'C'. Now, my question
is ,

How will you write a select query to display the output as
B
C
A

Note: order by cannot be used coz it gives us output as CBA.
But the output should be BCA.


Answer

declare
type samp_type is table of VARCHAR2(20)
index by binary_integer;

table_type samp_type;

cursor c1 is
select Colum-Name from Table_name;

begin
open c1;

fetch c1 bulk collect into table_type;

close c1;

for i in reverse 1..4 loop
dbms_output.put_line(table_type(i));
end loop;
end;

Is This Answer Correct ?    0 Yes 0 No

 [1]   2    Next