Hi there!
I've passed 1z0-001 today with a score of 50/57.I've used the SYBEX SQL-PLSQL Study guide,Exam cram
SQL/PLSQL and a host of other resources including braindumps from a very good site.But i've not taken the full
advantage of Dumps as i 've very less time when i got them.I,ve also used SelfTest Practice Software which is
pretty good.
Don't miss to visit 'http:\\nav.to\ocpbraindump\' site if u're planning to take 1z0-001 exam.
Here are my contributions,
1.There was a table structure .. for PLAYER table
ID NUMBER;
FIRSTNAME VARCHAR@;
LASTNAME VARCHAR2;
MANAGER NUMBER;
u've to selfjoin the table to obtain players and their managers the answer query is..
select p.id,p.firstname||p.lastname,m.firstname||m.lastname
from players p,players m
where p.id=m.manager; something like this cant remeber full options..
2.which operator is not used in a non equijoin
a.in b.= c.between d.
3.In which section of the PL/SQL block the user defined exception is raised.
a.heading b.declarative c.executive d.exception
4.u need to store data of numbers u know that the scale value will always be 2 decimals,but not sure about how
many digits in integer part.which datatype is most appropriate for this scenario.
a.number b.number(P) c.long d.raw
5.Evaluate the following PL/SQL block
BEGIN
FOR i in 1..10 LOOP
IF i=4 OR i=6 THEN
null;
ELSE
insert into TEST(no) Values(i);
END IF;
COMMIT;
END LOOP;
ROLLBACK;
END;
How may values do the test table will have?
a.0 b.8 c.10 d.4
6.Evaluate the following PL/SQL block
DECLARE
x NUMBER;
v_sal NUMBER := 1000;
V_found VARCHAR2(10):= 'YES'
BEGIN
x:=500;
v_sal:=1000;
DECLARE
v_found VARCHAR2(10) := 'TRUE';
y NUMBER;
BEGIN
IF v_sal500 THEN
v_found:='YES';
ELSE
null;
END IF;
DBMS_OUTPUT.PUT_LINE(v_sal||' '||v_found);
y:=20
END;
DBMS_OUTPUT.PUT_LINE(v_sal||' '||v_found || y);
END;
What will be the output of this Block..
a.PLS:201 identifier 'Y' must be declared
b.value of v_sal,v_found will be displayed
c.value of v_sal,v_found,y will be displayed
d.PLS:201 identifier v_sal must be declared
7.6.Evaluate the following PL/SQL block
DECLARE
x NUMBER;
v_sal NUMBER := 1000;
V_found VARCHAR2(10):= 'YES'
BEGIN
x:=500;
v_sal:=1000;
DECLARE
v_found VARCHAR2(10) := 'TRUE';
y NUMBER;
BEGIN
IF v_sal500 THEN
v_found:='YES';
ELSE
null;
END IF;
DBMS_OUTPUT.PUT_LINE(v_sal||' '||v_found);
y:=20
END;
DBMS_OUTPUT.PUT_LINE(v_sal||' '||v_found || y);
END;
Why this block will show an error?
a.the scope of variable 'y' is limited to inner block
b.the scope of variable v_sal is limited to outer block
c. --- d. ----
8.Jane has been granted the same privileges as u got on u'r emp table.u're updating the emp table and u
asked Jane to log to the database and check the emp table;
Which tasks can the Jane be allowed to do?
a.Jane can access the emp table but cannot see the changes and not be able to make the same changes.
b.Jane will not be allowed to access the emp table;
c.Jane will be allowed to access the emp table and can see teh changes made by u.
9.Which of these is a system level security?
a.DROP ANY TABLE
b.ALTER
c.INDEX
d.EXECUTE
10.Evaluate the following PLSQL block
DECLARE
CURSOR cust_cursor (p_empno NUMBER, p_ename VARCHAR2) IS
SELECT * FROM emp
WHERE empno=p_empno
AND ename = p_ename;
Which statement will correctly open this cursor?
a.OPEN cust_cursor;
b.OPEN cust_cursor('ALLEN',20);
c.OPEN cust_cursor(10,'SCOTT');
d. ...
11. Consider the following statements ..
CREATE ROLE clerk;
CREATE ROLE hrmanager;
CREATE ROLE inventory;
CREATE USER scott IDENTIFIED BY tiger;
GRANT inventory to clerk;
GRANT hrmanager TO clerk;
GRANT inventory TO SCOTT;
How many roles has been granted to user SCOTT?
a. 0
b.1
c.2
d.None
12. u've created one view on emp as emp_hist_v and u want to see what are all the columns that are retrieved
by the view(view definition) what will u do?
a. describe emp
b.describe emp_hist_v
c.select text from user_views
where view_name='EMP_HIST_V';
d. some wrong select statement
13.U've created an index like
create index emp_deptid_fk_idx on emp(deptid);
there are two tables emp and dept and emp table contain a FK deptid which reference the dept tables deptid.
Know u've created the index what is achieved ?
a.index is stored in emp table
b.it reduces I/O using SELECT statement
c.performs full table scan
d.increases performance of u'r insert statements
14.a question about TO_CHAR function using 'RR' format
15.know what ALL_COL_PRIVS_RECD datadictionary view
16.Evaluate this block
IF v_val100 THEN
v_new_val:=2*v_val;
ELSIF v_val 200 THEN
v_new_val :=4* v_val;
ELSIF v_val
v_new_val:=6*v_val;
END IF;
What is the value of v_new_val if v_val =250?
a. 1000
b.500
c.1500
d.error
17.know how to run a script file in SQL*PLUS environment(START )
18. Evaluate this block
DECLARE
CURSOR some cursor definition ....
BEGIN
FOR cur_rec in curs LOOP
--some processing given here ....
END LOOP;
CLOSE curs;
END;
Why this statement will fail?
A.no explicit closing of the cursor
19. u've given the following update statement ...
-- some update statement given ...
u want to know whether next fetch returned rows are not what will u do?
Ans: use SQL%FOUND attribute (this's the only valid thing given)
20.u're proceesing a cursor and in the middle fetched some rows..
what will be the next step in the processing of a cursor?
a.OPEN
b.Test whether fetch return rows
c.CLOSE
d. ....
21.u've created this
CREATE SYNONYM emp FOR Ed.Employee;
what will be achieved?
a.Need to qualify the table with schema name is removed for the user Ed
b..Need to qualify the table with schema name is removed for u only
c..Need to qualify the table with schema name is removed for all users of the database
d..Need to qualify the table with schema name is removed for the users who can access emp table
22.a ques about sequence i only remember answer
CREATE SEQUENCE patient_id_seq
START WITH 10000
MAXVALUE 999999999
NOCYCLE;
23.a ques about the uses of WHERE clause(u've to pick 3)
24. In a PLSQL block which of these will generate an exception? (Pick 2)
a.select will return a single row (when used with INTO)
b.select will return multiple rows (when used with INTO)
c.a select statement with group by c
