Q:1. 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.



Q:2. 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);.

Q:3. WHICH
STATEMENT ABOUT IMPLICT CURSOR IS TRUE ?

IMPLICT CURSORT ARE DECLARED IMPLICITY FOR ALL THE DML AND SELECT STATEMENTS.

Q:4. 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.

Q:5. 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.


Q:6. 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;




Q:7. 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.

Q:8. 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?

Q:9. IN WHICH ORDER DOES THE ORACLE SERVER EVALUATE CLAUSES?

WHERE, GROUP BY, HAVING


Q:10. 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.

---