Interview Questions Answers in SQL PLSQL, Oracle General, Oracle Errors, Programming Languages AllOther, DB Development, Oracle AllOther

Answers / { ajit kumar nayak }

Question { 7872 }

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 { 8557 }

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, 19692 }

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, 9734 }

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 { 9321 }

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 { 18222 }

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, 13413 }

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 { 5657 }

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 { 4507 }

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 { 5530 }

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, 27157 }

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, 8011 }

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 { 8587 }

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 { 15026 }

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 { 6432 }

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