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. 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'

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

True

False

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:=2v_val; elsif v_val>200 then v_new_val:-3v_val; elsif v_val>300 then v_val:=4v_val; else v_new_val:=5v_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. 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

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

Varchar2

Out

Return

Number

In

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. 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.

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

True

False

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. 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

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

True

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. 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;

28. 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.

29. Which of the following statements are true about the package(choose 3)?

Specification is required but body is optional.

To reference a package procedure, you have to use the . notation.

The specification and body are stored separately in the database.

Both specification and body are required components of a package.

The specification and body are stored together in the database.

Submit

30. Which of the following are true about Dynamic SQL (choose 3)?

To execute dynamic sql,the syntax is dynamic sql ,where sql_string is the string containing the sql code.

It contains sql statements that are constructed and executed at run time.

It can contain DDL statements.

It can be used to build generic and reusable code.

It can contain only DML statements.

Submit

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 ?

2.

What first name or nickname would you like us to use?

2. An index, once defined, is automatically maintained and used by oracle. State True or 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?

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

5. 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)?

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

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.

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?

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 ?

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

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

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

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

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

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?

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

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

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

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?

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

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

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?

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

29. Which of the following statements are true about the package(choose 3)?

30. Which of the following are true about Dynamic SQL (choose 3)?

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