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


2. 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

3. IN WHICH ORDER DOES THE ORACLE SERVER EVALUATE CLAUSES?

ANS:WHERE, GROUP BY, HAVING

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

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


6.
IN WHICH SELECTION OF A PL/SQL BLOCK IS A USER DEFINED EXCEPTION RAISED?

ANS:EXECUTABLE

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

8. WHICH
STATEMENT IS TRUE ABOUT NESTING BLOCKS ?

ANS: A VARIABLE DEFINED IN THE OUTER BLOCK IS VISIBLE IN THE INNER BLOCK.

9. 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;

10. HOW DO YOU
SEND THE OUTPUT OF YOUR SQL*PLUS SESSION TO A TEXT OPERATING SYSTEM FILE CALLED MYOUTPUT.LST?

ANS: SPOOL MYOUTPUT.LST

11. 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

12. 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;

13. 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 ENAME, SAL, DEPTNO;

14. MR. KING IS THE PRESIDENT OF A COMPANY. FIVE MANAGER REPORT TO HIM. ALL OTHER EMPLOYEES REPORT TO
THESE MANAGERS.
EXAMINE THIS CODE:
SELECT EMPLOYEE.ENAME
FROM EMP.EMPLOYEE
WHERE EMPLOYEE, EMPNO NOT IN (SELECT MANAGER.MGR
FROM EMP
MANAGER);
THE ABOVE STATEMENT RETURNS NO ROWS SELECTED AS THE RESULT WHY?

ANS: A NULL VALUE IS RETURNED FROM THE SUB QUERY

15. WHICH
STATEMENT ABOUT MULTIPLE COLUMN SUBQUERIES IS TRUE?

ANS: A NON-PAIR WISE COMPARISON PRODUCES A CROSS PRODUCT

---