1z0-001 Braindump 2002.02.16

　　1. SCOTT FORGOT HIS PASSWORD WHILE ON VACATION WHICH COMMAND MUST BE EXECUTED TO SET A PASSWORD FOR SCOTT
　　
　　A. DBA MUST EXECUTE THE COMMAND ALTER USER SCOTT IDENTIFIED BY LION
　　
　　2. YOU ARE UPDATING THE EMPLOYEE TABLE JANE HAS BEEN GRANTED THE SAME PRIVILAGES AS YOU ON THE EMPLOYEE TABLE YOU ASK JANE TO LOG ON TO THE DATABASE TO CHECK YOUR WORD BEFORE YOU ISSUE THE COMMIT COMMAND WHAT CAN SHE DO TO THE EMPLOYEE TABLE
　　
　　A. JANE CAN ACCESS THE TABLE BUT SHE CAN NOT SEE YOU CHANGES AND CAN NOT MAKE THE CHANGES TO THE ROLES THAT YOU ARE CHANGING
　　
　　3. EXAMINE THE STRUCTURE OF THE STUDENT TABLE
　　
　　NAME NULL TYPE
　　
　　STUD_ID NOT NULL NUMBER(3)
　　NAME NOT NULL VARCHAR2(25)
　　ADDRESS VARCHAR2(50)
　　GRADUATION DATE
　　WHICH STATEMENT INSERT A NEW ROW INTO THE STUDENT TABLE ?
　　
　　A. INSERT INTO STUDENT (STUD_ID, ADDRESS, NAME, GRADUATION) VALUES (101,?00 MAIN STREET? 慡MITH? ?7-JAN-00?;
　　
　　4. EXAMINE THE STRURE OF THE STUDENT TABLE

网管联盟bitsCN_com

　　GRADUATION COLUMN IS A FORIGN KEY COLUMN TO THE GRADUATE TABLE EXAMINE THE DATA IN THE GRAD_DATE_TABLE
　　
　　GRADUATION 20-JAN-1999
　　12-MAY-1991
　　19-JAN-2000
　　25-JAN-2000
　　13-JAN-2001
　　29-JAN-2001
　　
　　WHICH UPDATE STATEMENT PRODUCES THE FOLLOWING ERROR
　　
　　ORA-02291 INTEGRITY CONSTRAINT (SYS-C-23) VIOLDATED PARENT
　　KEY NOT FOUND
　　
　　A. UPDATE STUDENT SET NAME = 慡MITH? GRADUATION = ?5-AUG-2000?
　　WHERE STUD-ID = 101
　　
　　
　　5. THE VIEW EMP-VIEW IS CREATED BASED ON THE EMP TABLE AS FOLLOWS
　　
　　CREATE OF REPLACE VIEW EMP_VIEW AS
　　SELECT DEPTNO, SUM(SAL) TOT_SAL, COUNT(*) NOT_EMP FROM EMP
　　GROUP BY DEPTNO;
　　
　　WHAT HAPPENS WHEN THIS COMMAND IS USED ?
　　
　　UPDATE EMP_VIEW SET TOT_SAL = 20000 WHERE DEPTNO = 10;
　　
　　ANS THE BASE TABLE CANNOT BE UPDATED THROUGH THIS VIEW
　　
　　6. YOU HAVE A VIEW CALLED ANN_SAL THAT IS BASED ON THE EMPLOYEE TABLE STRUCTURE ON THE ANN-SAL VIEW IS
　　
　　NAME NULL TYPE
　　
　　EMPNO NOTNULL NUMBER(4)
　　YEARLY_SAL NUMBER(9,2)
　　MONTHL_SAL NUMBER(9,2)
　　
　　WHICH STATEMENT RETRIVES ALL DATA FROM ANN_SAL VIEW ?
　　
　　ANS. SELECT * FROM ANN_SAL;
　　
　　7. EVALUATE THIS IF STATEMENT
　　
　　IF V_VALUE > 100 THEN
　　V_NEW_VALUE := 2 * V_VALUE
　　ELSEIF V_VALUE >200 THEN
　　V_NEW_VALUE := 3 * V_VALUE
　　ELSEIF V_VALUE >300 THEN
　　V_NEW_VALUE := 4 * V_VALUE
　　ELSE
　　V_NEW_VALUE := 4 * V_VALUE
　　ENDIF;
　　
　　WHAT VALUE BE ASSIGNED TO V_NEW_VALUE IF V_VALUE = 250:
　　
　　ANS: 500
　　
　　
　　
　　8. THE PLAYER TABLE CONTAINTS THESE COLUMNS
　　
　　ID NUMBER(9)
　　NAME VARCHAR(2)
　　MANAGERID NUMBER(9)
　　
　　IN THIS INSTANCE MANAGER ARE PLAYER AND YOU NEED TO DISPLAY A LIST OF PLAYERS EVALUATE THESE SQL STATEMENT
　　
　　SELECT P.NAME,M.NAME FROM PLAYER P, PLAYER M
　　WHERE M.ID = P.MANAGERID;
　　
　　SELECT P.NAME,M.NAME FROM PLAYER P, PLAYER M
　　WHERE M.MANAGERID = P.ID
　　
　　HOW WOULD THE RESULT DIFFER
　　
　　ANS: THE RESULT WILL BE SAME BUT DISPLAY WILL BE DIFFERENT.
　　
　　9. HOW WOULD YOU DECLARE A PLSQL TABLE OF RECORDS TO HOLD THE ROWS SELECTED FROM THE EMP TABLE?
　　
　　DECLARE
　　TYPE EMP_TABLE IS TABLE OF EMP%ROWTYPE
　　INDEX BY BINARY INTEGER E
　　EMP_TABLE EMP_TABLE_TYPE;
　　
　　YOU WANT TO CREATE A CURSOR THAT CAN BE USED SEVERAL TIMES IN A BLOCK. SELECTING A DIFFERENT ACTIVE SET EACH TIME THAT IT IS OPENED WHICH TYPE OF CURSOR DO YOU CREATE
　　
　　ANS: A CURSOR THAT USE PARAMETERS
　　
　　10. WHICH STATEMENT IS TRUE WHEN WRITING A CURSOR FOR LOOP ?
　　
　　ANS: YOU DO NOT EXPLICITY OPEN, FETCH OR CLOSE A CURSOR WITHIN A CURSOR FOR LOOP.
　　
　　
　　
　　11. THE STRUCTURE OF THE DEPT TABLE
　　
　　NAME NULL TYPE
　　
　　DEPTNO NOTNULL NUMBER(25)
　　DNAME VARCHAR2(14) 网管u家u.bitscn@com
　　LOC VARCHAR2(5)
　　
　　EXAMINE THE CODE
　　
　　DECLARE
　　DEPT_REC DEPT%ROWTYPE
　　
　　BEGIN
　　SELECT * INTO DEPT_REC FROM DEPT WHERE DEPTNO = 10;
　　END
　　
　　WHICH PL/SQL STATEMENT DISPLAYS THE LOCATION OF SELECTED DEPARTMENT.
　　
　　ANS: DBMS_OUTPUT.PUT_LINE(DEPT_REC_LOC);
　　
　　12. WHICH STATEMENT ABOUT IMPLICT CURSOR IS TRUE ?
　　
　　IMPLICT CURSORT ARE DECLARED IMPLICITY FOR ALL THE DML AND SELECT STATEMENTS
　　
　　13. EVALUATE THIS PL/SQL BLOCK
　　
　　DECLARE
　　V_RESULT NUMBER(2)
　　BEGIN
　　DELETE FROM EMPLOYEE WHERE DEP_ID IN(10,20,30);
　　V_RESULT:= SQL%ROWCOUNT;
　　COMMIT
　　END;
　　
　　WHAT WILL BE THE VALUE OF V_RESULT IF NO ROWS ARE DELETED;
　　
　　ANS: ZERO
　　
　　14. WHICH TWO CONDITIONS IN A PL/SQL BLOCK CAUSE AN EXCEPTION ERROR TO OCCUR ? CHOOSE TWO
　　
　　ANS: SELECT STATEMENT DOES NOT RETURN A ROW
　　SELECT STATEMENT RETURNS MORE THAN ONE ROW
　　
　　
　　15. YOU NEED TO CREATE A PL/SQL PROGRAME TO INSERT RECORD INTO EMPLOYEE TABLE WHICH BLCOK OF CODE SUCCESSFULLY USES THE INSERT COMMAND
　　
　　ANS:
　　
　　DECLARE
　　V_HIREDAT DATE:= SYSDATE;
　　BEGIN
　　INSERT INTO EMP (EMPNO,ENAME,HIREDATE)
　　VALUES (EMPNO_SEQUENCE.NEXTVAL,?NAME?V_HIREDATE,&DEPTNO);
　　END;
　　
　　EVALUATE THIS PL/SQL BLOCK
　　
　　16. EVALUATE THIS PL/SQL BLOCK
　　
　　BEGIN
　　FOR I IN 1..10 LOOP
　　IF I=4 OR I=6 THEN NULL;
　　ELSE
　　INSERT INTO TEST(RESULT) VALUES(1);
　　END;
　　COMMIT;
　　END LOOP;
　　ROLL BACK;
　　END;
　　
　　HOW MANY VALUES WILL BE INSERTED INTO THE TEST TABLE ?
　　
　　ANS: 8
　　
　　17. YOU ISSUE COMMAND
　　
　　CREATE PUBLIC SYNONYM EMP FOR ED_EMPLOYEE
　　
　　WHICH TASK HAS BEEN ACCOMPLISHED?
　　
　　ANS: THE NEED TO QUALIFY THE OBJECT VALUE WITH ITS SCHEME IS ELIMINATED FOR ALL USERS 网管联盟bitsCN@com
　　
　　18. IN WHICH ORDER DOES THE ORACLE SERVER EVALUATE CLAUSES?
　　
　　WHERE, GROUP BY, HAVING
　　
　　
　　19. YOU ATTEMPT TO QUERY TO THE DATABASE WITH COMMAND
　　
　　SELELECT DEPT_NO, AVG(MONTHS_BETWEEN(SYSDATE,HIRE_DATE))
　　FROM EMPLOYEE
　　WHERE AVG(MONTHS_BETWEENS(SYSDATE,HIRE_DATE))>60
　　GROUP BY DEPT_NO
　　ORDER BY AVG(MONTHS_BETWEEN(SYSDATE,HIRE)DATE)
　　
　　WHY DOES THIS STATEMENT CAUSE AN ERROR ?
　　
　　ANS: A WHERE CLAUSE CAN NOT BE USED TO RESTRICT GRUPS.
　　
　　
　　20. THE PATH TABLE CONTAINS THESE COLUMNS
　　
　　ID NUMBER(7) PK
　　COST NUMBER(7,2)
　　PRODUCT_ID NUMBER(7)
　　
　　EVALUATE THESE SQL STATEMENT
　　
　　SELECT ROUND(MAX(COST),2),
　　ROUND(MIN(COST),2),
　　ROUND(SUM(COST),2),
　　ROUND(AVG(COST),2) FROM PART;
　　
　　SELECT PRODUCT_ID,ROUND(MAX(COST),2),
　　ROUND(MIN(COST),2),
　　ROUND(SUM(COST),2),
　　ROUND(AVG(COST),2) FROM PART
　　GROUP BY PRODUCT_ID;
　　
　　HOW WILL THE RESULT DIFFER
　　
　　ANS: STATEMENT 1 WILL ONLY DISPLAY ONE ROW OF RESULTS
　　STATEMENT CAN DISPLAY MORE THAN ONE.
　　
　　21. IN WHICH SELECTION OF A PL/SQL BLOCK IS A USER DEFINED EXCEPTION RAISED?
　　
　　EXECUTABLE
　　
　　
　　22. EXAMINE THE CODE
　　
　　SET SERVER OUTPUT ON
　　DECLARE
　　V_CHAR_VAL VARCHAR2(100);
　　BEGIN
　　V_CHAR_VAL := 慔ELLO WORLD?
　　DBMS_OUTPUT.PUT_LINE (V_CHAR_VAL);
　　END
　　SET SERVER OUTPUT OFF
　　
　　THIS CODE IS STORED IN A SCRIPT TITLE NAME 慚YPROC.SQL?WHICH STATEMENT