1Z0-001考题回忆12.17

　　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_sal>500 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_sal>500 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 aclearcase/" target="_blank" >ccess 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_val>100 THEN
　　v_new_val:=2*v_val;
　　ELSIF v_val > 200 THEN
　　v_new_val :=4* v_val;
　　ELSIF v_val <300 THEN
　　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 clause
　　d.datatype mismatch in the select list and INTO list
　　e.
　　
　　25.know what 'WHEN OTHERS ..' exception handler will do
　　26.u want to select sal,ename,empno from emp table
　　u want to display to be sorted in decreasing salaries,ename further if two salaries matches u want ename's to
　　
　　be in ascending order.which statement will u use?
　　a. select empno,ename,sal
　　from emp
　　order by sal DESC, ename;
　　--some 3 other wrong options
　　
　　27.u as a DBA created a user and the user needs to connect to the database and create tables and packages
　　which statement will u use?
　　a.GRANT CREATE table, CREATE package to user;
　　b.GRANT create connect,create table, create procedure to user
　　c.grant create session,create table, create procedure to user **
　　d. ---
　　
　　28. how will u disable a primary key constraint?
　　a.alter table table disable primary key(id);
　　b.alter table table disable constraint cons_name_pk;
　　c.alter table add constraint primary key null;
　　d. ---
　　
　　29.a ques about truncate table command
　　(which command is used to delete all rows from table by keeping its structure)
　　
　　30.u want to create a script to delete a specified row from a table the input will be taken from the user
　　which command will accomplish the above requirement?
　　
　　a.delete from emp
　　where empid = &empid;
　　
　　b.define id
　　delete from emp
　　where empid=@id;
　　c.delete
　　define id
　　from emp
　　where id = :id
　　
　　31. u want to extract all rows and columns from emp table fro processing which feature will u use?
　　a.PLSQL Record
　　b.%ROWTYPE
　　c.PLSQL tables
　　d.PLSQL Table of Records **
　　
　　32.Consider the statements
　　
　　1.select * from user_objects;
　　2.select * from all_objects
　　
　　which is true about this statements
　　ans :stmt1 will display objects owned by the user and stmt 2 will display all objects for which the user has
　　
　　access to.
　　
　　33. emp table contain the following columns
　　LASTNAME varchar2(25);
　　FIRSTNAME varchar2(25);
　　
　　u issue the following statements
　　1.select concat(lastname||' '||firstname),length(concat(lastname||firstname)
　　from emp
　　where UPPER(lastname) LIKE '%J' OR '%K' OR '%H';
　　
　　2.select lastname||firstname,length(firstname)+length(lastname) from emp
　　where SUBSTR(lastname,1,1) IN ('J','K','H');
　　what is true about the statements?
　　
　　a.statement 1 will execute and statement2 will not
　　b.statement2 will execute and statement 1 will not
　　c.both will select different data
　　d.both statements will select the same data but display it differently
　　
　　34. u created a view as
　　CREATE VIEW emp_v AS
　　SELECT ename,AVG(sal) FROM emp GROUP BY ename;
　　u attempt to issue a update statement what will happen
　　a.u can not update a view that has group function
　　b.update some values
　　c.update is possible ... some values
　　d. ...
　　
　　35.know that u cannot rename a column in a table
　　
　　Thats all folks!
　　
　　Happy certification,
　　Ramakrishna Deekshit Archakam
　　

原文转自：http://www.ltesting.net