Tuesday, April 29, 2008

Oracle job sample paper 2

Oracle job sample paper 2

Which of the following statements is correct?

(A) Usage of labels is invalid in PL/SQL.
(B) GOTO cannot branch into an IF statement
(C) GOTO is not a valid syntax.
(D) (A) and (C) are correct statements.

23. Consider the following PL/SQL block.

DECLARE
l_var1 NUMBER := &a;
l_var2 NUMBER := &b;
Data_problem EXCEPTION;
BEGIN
BEGIN
IF (l_var1 > l_var2) THEN
RAISE NO_DATA_FOUND;
ELSE
RAISE Data_Problem;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('No Data');
WHEN Data_Problem THEN
RAISE Data_problem;
END;
EXCEPTION
WHEN Data_problem THEN
dbms_output.put_line ('Data Problem');
END;

Identify which of the following statements is/are correct.

(A) Output printed is 'No Data' when a > b .
(B) Exception cannot be used to move from inner to outer block.
(C) Output printed is 'Data Problem' when b > a .
(D) Statements A and C are correct.

24. Employee table has records of 10 employees.
Execution of the following PL/SQL block given below will result in :

DECLARE
CURSOR C1 IS
SELECT name, basic+hra salary
FROM employee ;
Incentive NUMBER;
l_name VARCHAR2(30);
l_salary NUMBER;
BEGIN
LOOP
Fetch c1 INTO l_name, l_salary;
EXIT WHEN c1%NOTFOUND OR
c1%ROWCOUNT = 9 ;
Incentive := l_salary * 0.1 ;
END LOOP;
close c1;
dbms_output.put_line ('Name - ' || l_name);
dbms_output.put_line ('Incentive =' || Incentive);
END;

(A) Error as alias cannot be used in cursor.
(B) Name & Incentive of nine employees will be printed.
(C) Error as %NOTFOUND & %ROWCOUNT cannot be used with explicit cursors.
(D) Error - Invalid cursor

25. Consider the procedure given below:

PROCEDURE calc_bonus (salary IN INTEGER, gross_salary IN OUT NUMBER,
bonus OUT NUMBER) IS
BEGIN
IF (salary > 1000) THEN
bonus := Salary * .1 ; — statement 1
ELSE
salary := Salary * 2; — statement 2
bonus := salary * 0.1 ; — statement 3
END IF;
gross_salary := Salary * 2 ; — statement 4
END;

(A) No errors are there in the PL/SQL block
(B) Statements 1 and 3 will result in error.
(C) Statement 2 will result in error.
(D) Statements 1, 2 and 3 will result in error.

26. Consider the following data

TABLE A TABLE B
COL1 COL1
—— ——-
10 15
20 20
30 25
40 30
50 35
60 40
70 45
80 50
10 35
100 60
65
30
80
90
100

select count(*)
from (select A1.col1 col1
from A A1, A A2
where A1.col1 = A2.col1
UNION ALL
select A.col1 col1
from A, B
where A.col1(+) = B.col1 );

What would be output of the above SQL query?

(A) 25
(B) 27
(C) 29
(D) error

27. Consider the following DDL

create table emp(
empno number,
name char(30),
sal number,
deptno number,
manager number)

create table dept(
deptno number,
name char(30),
location char(30))

i) create view view1 as
select location, count(empno) emp_count
from emp, dept
where emp.deptno = dept.deptno
group by location;

ii) create view view2 as
select empno, name, location
from emp, dept
where emp.deptno = dept.deptno;

iii) create view view3 as
select *
from emp, dept;

Which is the correct view definition ?

(A) i
(B) i and ii
(C) ii and iii
(D) All

28. What is the output of the SQL statement

select floor((ceil(-0.42) - abs(round(-0.64)))/2) from dual;

(A) -2
(B) -1
(C) 0
(D) 1

29. Consider the following data

Table STUDENT

Name subject status
———– ————— ——–
Student1 Sub1 P
Student1 Sub2 F
Student2 Sub1 P
Student3 Sub2 F
Student4 Sub1 F
Student4 Sub2 P
Student5 Sub1 P
Student5 Sub2 P
Student6 Sub1 F
Student6 Sub2 F

i) select * from student
where status = 'P' OR status = 'F' AND subject = 'Sub1′;

ii) select * from student
where (status = 'P' OR status = 'F') AND subject = 'Sub1′;

iii) select * from student
where subject = 'Sub1′ AND status = 'P' OR status = 'F';

iv) select * from student
where status = 'P' OR (status = 'F' AND subject = 'Sub1′);

Which statements would produce same output

(A) i & ii
(B) ii & iii
(C) iii & iv
(D) i & iv

30. Consider the following DML operation along with table data from above question

i) update student s1
set s1.status = 'P'
where s1.subject =
( select distinct(s2.subject)
from student s2
where s1.name = s2.name );

ii) delete from student s1
where status > 'F'
and s1.name not in
( select s1.name
from student s2
where s2.subject = 'Sub2′);

iii) update student s1
set s2.status = 'P'
where s1.subject in
( select s2.subject
from student s2
where s2.status = 'F');

Which statement(s) are incorrect/errors out:

(A) i & ii
(B) ii & iii
(C) i & iii
(D) i, ii & iii

Answers:a,d,d,c,d,c,d,c,a,a,c,b,b,d,a,b,d,c,c,b,c,b,d,d,c,b, a,b,d,c
———————————————————— ——————————–
1. What is the output of the following program
class ExceptionClass1 extends Error {
public String toString() {
return "ExceptionClass1″;
}
}

class ExceptionClass2 extends Exception {
public String toString() {
return "ExceptionClass2″;
}
}

public class ExceptionClassesTest {
private static final int CLASS1 = 10;
private static final int CLASS2 = 20;

public static void main( String[] args ) {
int param = Integer.parseInt(args[0]);
try {
exceptionClassTest( param );
}
catch(Throwable t) {
System.out.println("" + t );
}
}
public static void exceptionClassTest(int param) throws ExceptionClass2 {
try {
if( param == CLASS1 ) throw new ExceptionClass1();
if( param == CLASS2 ) throw new ExceptionClass2();
}
catch( Exception ex ) {
System.out.println("" + ex );
throw (ExceptionClass2)ex;
}
}
}

main()
{
int i = 5;
printf("%d\n", i++ * i–);
}

If you compile the above program and do the following, what is the output ?

0 comments: