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

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

ANS:EXECUTABLE.


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



Q:4.
WHICH STATEMENT IS TRUE ABOUT NESTING BLOCKS ?

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

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

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

ANS:SPOOL MYOUTPUT.LST.

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

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


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

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

---