Hi, here is my ocp 1z0-001 pl/sql dump(some answers may be wrong).
I passed the exam with 55 score. The questions are not difficult, but you should answer the question carefully.
----------------------------------------------------------
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,MAIN STREET? □MITH? ?7-JAN-00?;
4. EXAMINE THE STRURE OF THE STUDENT TABLE
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)
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
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 := ‘HELLO WORLD’;
DBMS_OUTPUT.PUT_LINE (V_CHAR_VAL);
END
SET SERVER OUTPUT OFF
THIS CODE IS STORED IN A SCRIPT TITLE NAME MYPROC.SQL.
WHICH STATEMENT EXECUTES THE CODE IN THE SCRIPT TILE?
ANS: START MYPROC.SQL
23. WHICH STATEMENT IS TRUE ABOUT NESTING BLOCKS ?
ANS. A VARIABLE DEFINED IN THE OUTER BLOCK IS VISIBLE IN THE INNER BLOCK.
24. WHICH STATEMENT IS VALID WITHIN THE EXECUTABLE SECTION OF PL/SQL BLOCK
ANS: SELECT ENAME, SAL INTO V_ENAME,V_SAL
FROM EMP
WHERE EMPNO = 101;
25. HOW DO YOU SEND THE OUTPUT OF YOUR SQL*PLUS SESSION TO A TEXT OPERATING SYSTEM FILE CALLED MYOUTPUT.LST?
ANS. SPOOL MYOUTPUT.LST
26. THE PRDOUCT TABLE CONTAINS THESE COLUMNS
ID NUMBER(9) PK
COST NUMBER(7,2)
SALE_PRICE NUMBER(7,2)
MANAGEMENT HAS ASKED YOU TO CALCULATE THE NET REVENUE PER LIMIT FOR EACH PRODUCT IF THE COST OF EACH PRODUCT IS INCREASED BY 10% AND THE SALE PRICE OF EACH PRODUCT IS INCREASED BY 25% YOU ISSUE THE SQL STATEMENT
SELECT ID, SAL_PRICE *1.25 ?COST *1.10 FROM PRODUCT;
WHICH CONCLUSION CAN YOU DRAW FROM THE RESULT ?
ANS.THE RESULTS PROVIDE MORE INFORMATION THAN MANAGEMENT REQUESTED
27. YOU WANT TO CREATE TO SHOW DIFFERENT JOBS IN EACH DEPARTMENT YOU DO NOT WANT TO DISPLAY ANY DUPLICATE ROLES IN THE REPORT WHICH SELECT SATEMENT DO YOU USE TO CREATE THE REPORT?
ANS SELECT DISTINCT DEPTNO, JOB FROM EMP;
28. WHICH SELECT STATEMENT DISPLAYS EMPLOYEE NAMES, SALARY, DEPARTMENT NUMBERS AND AVERAGE SALARY IN THEIR DEPARTMENT?
ANS. SELECT ENAME,SAL, DEPTNO, AVG(SAL)
FROM EMP
GROUP BY ENAM
