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


A. DBA MUST EXECUTE THE COMMAND
ALTER USER SCOTT IDENTIFIED BY LION.

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

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

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


A. INSERT INTO STUDENT (STUD_ID,
ADDRESS, NAME, GRADUATION)
VALUES (101,В’100 MAIN STREETВ’, В‘SMITHВ’, В’17-JAN-00В’);.

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

A. UPDATE STUDENT SET NAME = В‘SMITHВ’, GRADUATION = В’15-AUG-2000В’
WHERE STUD-ID = 101.



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


A.THE BASE TABLE CANNOT BE UPDATED THROUGH THIS VIEW

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

A. SELECT * FROM ANN_SAL;.

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

A. 500.

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

A.THE RESULT WILL BE SAME BUT DISPLAY WILL BE DIFFERENT.

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

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

A: A CURSOR THAT USE PARAMETERS.

---