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

Ans:EXECUTABLE

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

3. WHICH STATEMENT IS TRUE ABOUT NESTING BLOCKS ?

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

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

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

Ans: SPOOL MYOUTPUT.LST

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

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

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

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

10. WHICH STATEMENT ABOUT MULTIPLE
COLUMN SUBQUERIES IS TRUE?

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


11. THE EMPLOYEE TABLE CONTAINS

THESE COLUMNS


FIRST_NAME VARCHAR2(25)

LAST_NAME VARCHAR2(25)

EVALUATE THESE TWO SQL STATEMENT

SELECT CONTACT(FIRST_NAME,

LAST_NAME),


LENGTH(CONTACT (FIRST_NAME,

LAST_NAME))

FROM EMPLOYEE

WHERE UPPER(LAST_NAME) LIKE В‘%J%В’

OR UPPER(LAST_NAME) LIKE В‘%K%В’


OR UPPER(LAST_NAME) LIKE В‘%L%В’


SELECT INTCAP(FIRST)NAME),
INITCAP(LAST_NAME),
LENGTH(LAST_NAME) + LENGTH(FIRST_NAME) FROM EMPLOYEE

WHERE INITCAP(SUBSTR(LAST_NAME,1,1)) IN (???;

HOW WILL THE RESULTS DIFFER:

Ans: THE STATEMENT WILL RETRIVE SAME
DATA FROM THE DATABASE BUT WILL DISPLAY IT DIFFERENTLY

12. YOU RECEIVED AN INTEGRATED CONSTRAINT ERROR BECAUSE A CHILD RECORD WAS FOUND WHAT
COULD YOU DO TO MAKE THE SATEMENT EXECUTE ?

Ans:DELETE THE CHILD RECORD FIRST

13. WHICH STATEMENT DESCRIBES THE USE OF A GROUP FUNCTION


Ans:A GROUP FUNCTION PRODUCES ON RESULT FROM MANY ROWS PER GROUP

14. THE EMPLOYEE TABLE CONTAINS THESE COLUMNS
ID NUMBER(9) PK
LAST
_NAME VARCHAR2(25) NN
DEPT_ID NUMBER(9)
EVALUATE THIS SQL SCRIPT
DEFINE ID_2 = 93004
SELECT * FROM EMPLOYE WHERE ID = (%ID_2)
WHICH CNAGES
SHOULD YOU MAKE TO SCRIPT SO THAT IT WILL EXECUTE

Ans:NO CHANGE IS NEEDED

15. EVALULATE THIS SQL STATEMENT
SELECT E.ID(.15*E.SALARY) +
(.25 * E.BONUS)) (S.SALE_AMOUNT * (.15* E.COMMISSION_PCT))
FROM EMPLOYEE, SALES
WHERE E.ID = S.EMP_ID;
WHAT WOULD HAPPEN IF YOU REMOVED ALL
PARENTHESES FROM CALCULATION?

Ans:RESULT WILL BE SAME

16. WHICH IS NOT AN SQL * PLUS COMMAND

Ans:UPDATE

17. WHEN SELECTING DATA
WHICH STATEMENT IS VALID ABOUT PROJECTION

Ans:PROJECT ALLOWS YOU TO CHOOSE COLUMNS

18. GIVE THIS EXECUTABLE SECTION OF THE PL/SQL BLOCK

BEGIN
FOR EMPLOYEE_RECORD IN SALARY_CURSOR
LOOP
EMPLOYEE_ID_TABLE(EMPLOYEE) := EMPLOYEE_RECORD.LAST_NAME;
ENDLOOP
CLOSE SALARY_CURSOR;

END;
WHY DOES THIS SECTION CAUSE AN ERROR

Ans: THE CURSOR DOES NOT NEED TO BE EXPLICITY CLOSED.

19. THE EMPLOYEE TABLE CONTAINS THESE
COLUMNS
ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
COMMISSION NUMBER(7,12)
YOU NEED TO DISPLAY THE CURRENT COMMISSION FOR
ALL EMPLOYEES DESIRED RESULTS ARE
1. DISPLAY THE COMMISSION MULTIPIED BY 1.5
2. EXCLUDE EMPLOYEE WITH ZERO COMMISSION
3. DISPLAY A ZERO FOR
EMPLOYEE WITH NULL COMMISSION VALUE

EVALUATE THIS SQL STATEMENT
SELECT ID, LAST_NAME, FIRST_NAME, COMMISSION * 1.5 FROM EMPLOYEE
WHERE
COMMISSION 0;
WHICH OF THE DESIRED RESULTS DOES THE STATEMENT PROVIDE

Ans:TWO OF THE DESIRED RESULTS.

20. EXAMINE THIS BLOCK OF CODE

SET SERVER OUTPUT ON
X NUMBER;
V_SAL NUMBER;
V_FOUND VARCHAR2(10) :=
BEGIN
X:=1;
V_SAL:= 1000;
DECLARE
V_FOUND VARCHAR2(10);
Y
NUMBER
BEGIN
IF (V_SAL>500)
THEN V_FOUND :=
END IF
DBMS_OUTPUT.PUT_LINE(VALUE OF V_FOUND IS| IV_FOUND);

DBMS_OUTPUT.PUT_LINE(VALUE OF V_SAK IS| V_SAL);
Y:=20;
END;
DBMS_OUTPUT.PUT_LINE(ALUE OF V_FOUND IS| IV_FOUND);

DBMS_OUTPUT.PUT_LINE(ALUE OF Y| TO_CHAR(Y));
END;
SET SERVER OUTPUT OFF
WHAT IS THE RESULT OF EXECUTING THIS BLOCK OF CODE?


Ans:PLS_00201 IDENTIFIER MUST BE DECLARED

---