52/57
Hi there, I wish you enjoy my dumps and good luck!
1.Under which situation is it necessary to use on explicit cursor?
A. When any DML or SELECT statement is used in a PL/SQL block
B. When a delete statement in a pl/sql block deletes more than one row
C. When a select statement in pl/sql retrieves more than one row
D. When an update statement in a pl/sql block has to modify more than one row
Ans: C
2. Which statement about multiple column subquery is true?
A. A pairwiese comparison produces a cross product
B. A nonpairwise comparison produces a cross product
C. In a pairwise subquery, the value return from the subquery is compared individually to the values in the outer query
D. In a nonpairwise subquery, the value return from the subquery is compared grouply to the values in the outer que
Ans: B
3. Examine the code
set serveroutput on
declare
...
begin
...
end;
set serverouput off
This code is stored in a script file myproc.sql, which statement executes
the code in the script file?
A. Myproc.sql
B. RUN Myproc.sql
C. START Myproc.sql
D. EXECUTE Myproc.sql
E. BEGIN Myproc.sql
Ans: C
4. You issued a statement:
CREATE PUBLIC SYNONYM emp
FOR ed.employee
Which task has been accomplished?
a. The object now can be accessed by all users;
b. All users were given object privilege to the table;
c. The need to qualify the object name with its schema was elimilated only for you;
d. The need to qualify the object name with its schema was elimilated for all users.
Ans: D (the real exam a, b ,c, d, said different)
5. Which operator can not be used in nonequel join.
a. IN.
b. Like.
C. Equal operator
d. between...and..
Ans: C
6. select * from emp where id NOT IN ( select manager_id from
.....), which operator is the same as NOT IN.
a. !=
b. !=ALL *
C. NOT LIKE
d. !=ANY
Ans: B
7. Could you update following view?
create or replace view dept_view
as select dept_no, sum(salary) total_sal, count(*)
from emp
group by dept_no
a. Yes
b. No
Ans: B
8. Which select statement display employee names, salaries, department
number who earn more than the average salary in their department.
A, select ename, sal, deptno, AVG(sal)
from emp
group by ename, sal, deptno;
B, select a.ename, a.sal, a.deptno, b.salavg
from emp a, ( select deptno, AVG( sal ) salavg
from emp
group by deptno ) b
where a.deptno = b.deptno and a.sal b.salavg;
C, select outer.ename, outer.sal
outer.deptno, AVG( outer.sal )
from emp outer
group by outer.ename, outer.sal, outer.deptno
having AVG( outer.sal )
( select inner.sal
from emp inner
when inner.deptno = outer.deptno );
D, select outer ename, outer.sal,
outer.deptno, AVG( outer.sal )
from emp outer
Group by outer.ename, outer.sal, outer.deptno
having AVG( outer.sal ) IN
( select inner.sal
from emp inner
where inner.deptno = outer.deptno );
Ans: B
9. What happens if you manually create an index on a foreign key colum?
A. The unique key created on the primary key on the other table is overriden
B.Or the index will reduce the disk I/O for Select/Delete
C.----------
Ans: B
10. Can you rename a column?
A. Rename Column command
B.Alter Table command
C. Can not rename a column, unless using a temporary table
Ans: C
11. How to enable a Primary Key?
a) Alter Table xxx Enable Primary Key (id);
b) Alter Table xxx Enable Constraint xxx Primary Key;
c) Alter Table xxx Enable Primary Key Cascade;
d) Alter Table xxx Enable Primary Key;
Ans: D
12. To load a table with a huge amount of data, disable the Primary Key and
Not Null contraints, then re-enable them after the load. What happens?
a) Unique indexes automatically re-created;
b) Existing rows are validated;
c) Only subsequent insertions to the table are validated;
d) ...
Ans: B
13. In order for newly created user to get access to database, you must grant
'create session' privilege.
14. User_col_constraints only displays constraint names
User_constraints displays both constraint names and constraint type.
15. Declare cursor cursor_name(v_sal) IS
select ename, sal from
emp
where salv_sal;
What is wrong with this one?
a) must define datatype for v_sal
b) can not use where clause in cursor.
Ans: B, I am not sure
16.Try to look through the information about employee Smith, but not
sure the case stored in the database. which statement is correct
A. select lastname, firstname
from emp
where lastname='simth';
B. select lastname, firstname
from emp
where Upper(lastname)='simth';
C. select lastname, firstname
from emp
where Lower(lastname)='simth';
I choose C
17. Create role Inventory
Create role Clerk
Create role Manager
Create user Scott identified by tigger
Grant clerk to Manager
Grant Inventory to Clerk
Grant Invntory to Scott
How many role Scott can access to ?
A. ZERO
B. ONE
C. TWO
D. THREE
Ans: B
18. The structure of the department table is as follows:
Name Null Type
Deptno Not Null Number(2)
Dname Varchar2(14)
Loc Varchar2(13)
Examine the following code:
DECLARE
TYPE dept_record_type is RECORD
(dno number
name Varchar2(20));
dept_rec dept_record_type;
BEGIN
SELECT deptno, dname
INTO dept_rec
FROM dept
WHERE deptno=10;
END;
Which choice displays the name of the selected department?
A. DBMS_OUTPUT.PUT_LINE(name);
B. DBMS_OUTPUT.PUT_LINE(dname);
C. DBMS_OUTPUT.PUT_LINE(dept_rec.dname);
D. DBMS_OUTPUT.PUT_LINE(dept_rec.name);
E. DBMS_OUTPUT.PUT_LINE(dept_rec(name));
I chose D
19. You have been granted update privilege on the last_name column of the
employee table. Which data dictionary view would you query to display
the column the privilege was granted on and the schema that owns the
employee table?
A. ALL_COL_PRIVS_RECD
B. This information cannot be retrieved from a single data dictionary
view.
C. TABLE_PRIVILEGES
D. ALL_OBJECTS
Ans: A. but please check it!
20. You need to update employee salary. If the salary of an employee is less
than $1000, the salary needs to be incremented by 10%.
Use a SQL*Plus sustitution variable to accept the employee NUMBER.
Which PL/SQL block successfully updates the salary?
A. DECLARE
v_sal emp.sal%type;
BEGIN
SELECT sal
INTO V_SAL
FROM EMP
WHERE empno=&&p.empno;
IF(v_sal
