Samplevikas
- 1.
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?
- A.
the sql query list the employee details of all the employees working for the departments other than IT related, and earning salary equal to the maximum salary earned by employees in department 60.
- B.
The sql query list the employee details of all the employees working with department = IT.
- C.
Query list the employee details of all the employees working with the department 60.
- D.
None of the above.
Correct Answer
A. the sql query list the employee details of all the employees working for the departments other than IT related, and earning salary equal to the maximum salary earned by employees in department 60. -
- 2.
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?
- A.
Sql query returns the list of employees who earn 10000 or more and whoose job id has exactly 5 characters with middle three being "MAN".
- B.
The Sql query returns the list of employees who earn 10000 or more and whoose job id contains MAN.
- C.
Sql query returns the list of employees who earn 10000 or more and whoose job id begins with MAN.
- D.
Sql query returns the list of employees who earn 10000 or more and whoose job id contains one of the letters M,A,N.
Correct Answer
B. The Sql query returns the list of employees who earn 10000 or more and whoose job id contains MAN. -
- 3.
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?
- A.
20,10,200
- B.
30,10,300
- C.
30,20,600
- D.
30,30,900
Correct Answer
D. 30,30,900 -
- 4.
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?
- A.
Query flashback
- B.
Rollback segments
- C.
Cursor management
- D.
Indexing
Correct Answer
A. Query flashback -
- 5.
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?
- A.
Into v_sal,v_emp_id
- B.
Into v_sal
- C.
Using v_emp_id
- D.
Using v_sal
- E.
Using v_sal,v_emp_id
Correct Answer
E. Using v_sal,v_emp_id -
- 6.
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 ?
- A.
500
- B.
250
- C.
1000
- D.
750
Correct Answer
A. 500 -
- 7.
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?
- A.
Use truncate option instead of delete
- B.
Use on delete cascade option
- C.
You have to remove the constraint before a row can be deleted
- D.
You have to delete the child record first.
Correct Answer
B. Use on delete cascade option -
- 8.
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)?
- A.
Select empname from emp where empname like '_%ill'
- B.
Select empname from emp where empname like 'ill_'
- C.
Select empname from emp where empname like '%ill%'
- D.
Select empname from emp where empname like '_ill'
Correct Answer
D. Select empname from emp where empname like '_ill' -
- 9.
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?
- A.
1
- B.
6
- C.
2
- D.
7
- E.
Null
Correct Answer
C. 2 -
- 10.
Consider the following Sql statement: select * from emp where emp_id=100'; Oracle opens an explicit cursor for the above statement.
- A.
True
- B.
False
Correct Answer
B. False -
- 11.
Consider the following Sql statement: Select trunc(round(546.56)) from dual; What will be the output of the above query?
- A.
547.5
- B.
547
- C.
546
- D.
546.5
Correct Answer
B. 547 -
- 12.
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?
- A.
'When other handler' is displayed
- B.
Program issues error
- C.
No message is displayed
- D.
'Zero divide handler' is displayed
- E.
'When other handler' is displayed after which 'Zero divide handler' is displayed.
Correct Answer
A. 'When other handler' is displayed -
- 13.
Consider the following statements: Statement 1: Declare and exception sections are mandatory. Statement 2: Begin and End section are optional
- A.
Only statement 2 is false
- B.
Only statement 1 is true
- C.
Statement 1 and statement 2 are true
- D.
Statement 1 and statement 2 are false
Correct Answer
D. Statement 1 and statement 2 are false -
- 14.
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?
- A.
Select city,translate(city,not known) from Memberdetails
- B.
select city,coalesce(city,not known) from Memberdetails
- C.
Select city,nullif(city,not known) from Memberdetails
- D.
Select city,decode(city,not known) from Memberdetails
Correct Answer
B. select city,coalesce(city,not known) from Memberdetails -
- 15.
What does the method Extend(n,i) do?
- A.
Adds i elements at nth position
- B.
Adds a single null element at ith position
- C.
Appends n elements and sets each to the same value as the ith element.
- D.
Adds n null elements at the ith position.
- E.
Appends n elements and sets each to a null value
Correct Answer
C. Appends n elements and sets each to the same value as the ith element. -
- 16.
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?
- A.
Select ename,To_char(hiredate,YYYY) from emp;
- B.
Select ename,Decode(substr(hiredate,8),Year) from emp;
- C.
Select ename,Decode(substr(hiredate,8)YYYY) from emp;
- D.
Select ename,To_date(hiredate,YYYY) from emp;
- E.
Select ename,To_char(substr(hiredate,8,2)YYYY) from emp;
Correct Answer
A. Select ename,To_char(hiredate,YYYY) from emp; -
- 17.
Which of the following statements are true about the package(choose 3)?
- A.
Specification is required but body is optional.
- B.
To reference a package procedure, you have to use the . notation.
- C.
the specification and body are stored separately in the database.
- D.
Both specification and body are required components of a package.
- E.
The specification and body are stored together in the database.
Correct Answer(s)
A. Specification is required but body is optional.
B. To reference a package procedure, you have to use the . notation.
C. the specification and body are stored separately in the database. -
- 18.
Which of the following are true about Dynamic SQL (choose 3)?
- A.
To execute dynamic sql,the syntax is dynamic sql ,where sql_string is the string containing the sql code.
- B.
It contains sql statements that are constructed and executed at run time.
- C.
It can contain DDL statements.
- D.
It can be used to build generic and reusable code.
- E.
It can contain only DML statements.
Correct Answer(s)
B. It contains sql statements that are constructed and executed at run time.
D. It can be used to build generic and reusable code.
E. It can contain only DML statements. -
- 19.
In Oracle database, Number data type allows both number and decimal digits, state true or false?
- A.
True
- B.
False
Correct Answer
A. True -
- 20.
Which of the following will allow you to pass a value from a procedure to the calling environment?
- A.
Varchar2
- B.
Out
- C.
Return
- D.
Number
- E.
In
Correct Answer
B. Out -
- 21.
In the condition evaluates to NULL in a while loop, the loop body is executed, State True or False
- A.
True
- B.
False
Correct Answer
B. False -
- 22.
In Pl/Sql, a function can return a record, State True or False.
- A.
True
- B.
False
Correct Answer
A. True -
- 23.
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.
- A.
True
- B.
False
Correct Answer
A. True -
- 24.
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 ?
- A.
True
- B.
False
Correct Answer
A. True -
- 25.
The maximum no of characters allowed when defining a table name is________________.?
- A.
20
- B.
Unlimited Characters
- C.
30
- D.
10
Correct Answer
C. 30 -
- 26.
The correct syntax for defining a user-defined exception, called myexp is_____?
- A.
CREATE EXCEPTION myexp;
- B.
MyEXP new Exception;
- C.
Exception myExp;
- D.
MyExp Exception;
- E.
TYPE Exception myExp;
Correct Answer
D. MyExp Exception; -
- 27.
. Data dictionary views are static views. State True or False?
- A.
True
- B.
False
Correct Answer
A. True -
- 28.
In the following code, does the IF condition evaluates to True or False? IF Dinu=dinu THEN END IF;
- A.
True
- B.
False
Correct Answer
B. False -
- 29.
An index, once defined, is automatically maintained and used by oracle. State True or False.?
- A.
True
- B.
False
Correct Answer
A. True -
- 30.
Which of the following tablespace can be used when a user runs a SQL statement that requires the creation of temporary segments?
- A.
Default Tablespace
- B.
Segment Tablespace
- C.
Temporary Tablespace
- D.
User Tablespace
Correct Answer
C. Temporary Tablespace -
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Feb 18, 2013Quiz Edited by
ProProfs Editorial Team -
May 19, 2012Quiz Created by
Vikasverma
Back to top