1. SCOTT FORGOT HIS PASSWORD WHILE ON VACATION WHICH COMMAND MUST BE EXECUTED TO SET A PASSWORD FOR SCOTT


ANS: 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

ANS: 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 ?

ANS: 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

ANS: 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 ?

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

---