Samplevikas

1. The contents of a collection can be copied to another collection as long as they are built from the exact same collection type. State true or false ?

True

False

2. An index, once defined, is automatically maintained and used by oracle. State True or False.?

True

False

3. Which of the following features of Oracle 10g allows user to see a consistent view of the database at a point in the past without requiring any structural changes to the database?

Query flashback

Rollback segments

Cursor management

Indexing

4. In Oracle database, Number data type allows both number and decimal digits, state true or false?

True

False

5. . Data dictionary views are static views. State True or False?

True

False

6. Consider the following employee table emp(empno,empname) Which of the following sql statement will retrieve the list of employee names of 4 characters length and ending with 'ill'(eg. Bill)?

Select empname from emp where empname like '_%ill'

Select empname from emp where empname like 'ill_'

Select empname from emp where empname like '%ill%'

Select empname from emp where empname like '_ill'

7. State the following is True/False-- The SQL query that builds the view is stored in the data dictionary and needs not be reassembled every time the view is used.

True

False

8. Consider the following code: declare n1 number:=20; n2 number:=10 n3 number; begin n1:=30; n2:=n1; n3:=n1*n2; dbms_output.put_line(n1); dbms_output.put_line(n2); dbms_output.put_line(n3); end; What will be the output?

20,10,200

30,10,300

30,20,600

30,30,900

9. Examine the code below: if v_val>100 then v_new_val:=2*v_val; elsif v_val>200 then v_new_val:-3*v_val; elsif v_val>300 then v_val:=4*v_val; else v_new_val:=5*v_val; end if; What would be the value assigned to v_new_val if v_val=250 ?

500

250

1000

750

10. In the following code, does the IF condition evaluates to True or False? IF Dinu=dinu THEN END IF;

True

False

11. Which of the following will allow you to pass a value from a procedure to the calling environment?

Varchar2

Out

Return

Number

In

12. Consider the following Sql statement: Select trunc(round(546.56)) from dual; What will be the output of the above query?

547.5

547

546

546.5

13. The correct syntax for defining a user-defined exception, called myexp is_____?

CREATE EXCEPTION myexp;

MyEXP new Exception;

Exception myExp;

MyExp Exception;

TYPE Exception myExp;

14. In Pl/Sql, a function can return a record, State True or False.

True

False

15. Consider the following table: employees(emp_id,lname,fname,sal,dept_id,job_id) An sql query is formed as: select emp_id,lname,fname,sal from employees where salary in (select max(sal) from employees where dept_id=60) and job_id not like 'IT_%'; Which is the correct description for the above query?

The sql query list the employee details of all the employees working with department = IT.

Query list the employee details of all the employees working with the department 60.

None of the above.

16. Consider the following: select emp_id,lname,job_id,salary from employees where salary>=10000 and job_id like %MAN%. Which of the following is true?

The Sql query returns the list of employees who earn 10000 or more and whoose job id contains MAN.

Sql query returns the list of employees who earn 10000 or more and whoose job id begins with MAN.

17. In the condition evaluates to NULL in a while loop, the loop body is executed, State True or False

True

False

18. Consider the following statements: Statement 1: Declare and exception sections are mandatory. Statement 2: Begin and End section are optional

Only statement 2 is false

Only statement 1 is true

Statement 1 and statement 2 are true

Statement 1 and statement 2 are false

19. The maximum no of characters allowed when defining a table name is________________.?

20

Unlimited Characters

30

10

20. In sql, ypu issue the following command: Delete from patient_details where patient_id=811 You recieved an integrity constraint(restrict on delete) error because child records were found in case_history table for the patient. Which of the following gives the solution to make the above delete go through , assuming you are allowed to change the constraints?

Use truncate option instead of delete

Use on delete cascade option

You have to remove the constraint before a row can be deleted

You have to delete the child record first.

21. Consider the following table that contains details of CLUB members: MemberDetails table Member_id number(5) Member_name varchar(30) date_of_birth date city varchar(30) You are required to display the list of city names where the club members reside.Those members whose residing city is not known, it should be displayed as 'Not Known'.Which of the following statement will achieve the above task?

Select city,translate(city,not known) from Memberdetails

Select city,coalesce(city,not known) from Memberdetails

Select city,nullif(city,not known) from Memberdetails

Select city,decode(city,not known) from Memberdetails

22. Consider the following code: declare j number; begin for index in 1..6 loop j:=index; goto outer; end loop; <<outer>> j:=j+1; end; What is the value of the j after the above code is executed?

1

6

2

7

Null

23. Which of the following tablespace can be used when a user runs a SQL statement that requires the creation of temporary segments?

Default Tablespace

Segment Tablespace

Temporary Tablespace

User Tablespace

24. What does the method Extend(n,i) do?

Adds i elements at nth position

Adds a single null element at ith position

Appends n elements and sets each to the same value as the ith element.

Adds n null elements at the ith position.

Appends n elements and sets each to a null value

25. Examine the code below: declare v_sal number(9,2):=0; v_emp_id number:=1; begin execute immediate 'update emp set sal:=salary where empno:=employee_id' --line 6 end; Which of the following code if provided at line 6 successfully executes the program?

Into v_sal,v_emp_id

Into v_sal

Using v_emp_id

Using v_sal

Using v_sal,v_emp_id

26. Consider the following Sql statement: select * from emp where emp_id=100'; Oracle opens an explicit cursor for the above statement.

True

False

27. Examine the following code: declare j number:=100; result number:=0; begin ---other stmnts here Begin result:=j/0; Exception When Others Then dbms_output.put_line('When other handlers'); End; exception When Zero_divide then dbms_output.put_line('Zero divide handler'); end; Which of the following gives the output of above code?

'When other handler' is displayed

Program issues error

No message is displayed

'Zero divide handler' is displayed

'When other handler' is displayed after which 'Zero divide handler' is displayed.

28. Allied Corporation is a Software services company.The management has asked Ram, the database engineer, to list the names and year of joining of all their employees.They have also specified that the year should be in 'YYYY' format. Consider employees table as emp(empid,ename,hiredate) Which of the following statements will accomplish him the above task?

Select ename,To_char(hiredate,YYYY) from emp;

Select ename,Decode(substr(hiredate,8),Year) from emp;

Select ename,Decode(substr(hiredate,8)YYYY) from emp;

Select ename,To_date(hiredate,YYYY) from emp;

Select ename,To_char(substr(hiredate,8,2)YYYY) from emp;