|
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 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 EXAMINE THE STRUCTURE OF THE STUDENT TABLE NAME NULL TYPE STUD_ID NOT NULL NUMBER() NAME NOT NULL VARCHAR() ADDRESS VARCHAR() GRADUATION DATE WHICH STATEMENT INSERT A NEW ROW INTO THE STUDENT TABLE ? A INSERT INTO STUDENT (STUD_ID ADDRESS NAME GRADUATION) VALUES (? MAIN STREET? 慡MITH? ?JAN?; 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 JAN MAY JAN JAN JAN JAN WHICH UPDATE STATEMENT PRODUCES THE FOLLOWING ERROR ORA INTEGRITY CONSTRAINT (SYSC) VIOLDATED PARENT KEY NOT FOUND A UPDATE STUDENT SET NAME = 慡MITH? GRADUATION = ?AUG? WHERE STUDID = THE VIEW EMPVIEW 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 = WHERE DEPTNO = ; ANS THE BASE TABLE CANNOT BE UPDATED THROUGH THIS VIEW YOU HAVE A VIEW CALLED ANN_SAL THAT IS BASED ON THE EMPLOYEE TABLE STRUCTURE ON THE ANNSAL VIEW IS NAME NULL TYPE EMPNO NOTNULL NUMBER() YEARLY_SAL NUMBER() MONTHL_SAL NUMBER() WHICH STATEMENT RETRIVES ALL DATA FROM ANN_SAL VIEW ? ANS SELECT * FROM ANN_SAL; EVALUATE THIS IF STATEMENT IF V_VALUE > THEN V_NEW_VALUE := * V_VALUE ELSEIF V_VALUE > THEN V_NEW_VALUE := * V_VALUE ELSEIF V_VALUE > THEN V_NEW_VALUE := * V_VALUE ELSE V_NEW_VALUE := * V_VALUE ENDIF; WHAT VALUE BE ASSIGNED TO V_NEW_VALUE IF V_VALUE = : ANS: THE PLAYER TABLE CONTAINTS THESE COLUMNS ID NUMBER() NAME VARCHAR() MANAGERID NUMBER() IN THIS INSTANCE MANAGER ARE PLAYER AND YOU NEED TO DISPLAY A LIST OF PLAYERS EVALUATE THESE SQL STATEMENT SELECT PNAMEMNAME FROM PLAYER P PLAYER M WHERE MID = PMANAGERID; SELECT PNAMEMNAME FROM PLAYER P PLAYER M WHERE MMANAGERID = PID HOW WOULD THE RESULT DIFFER ANS: THE RESULT WILL BE SAME BUT DISPLAY WILL BE DIFFERENT 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 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 THE STRUCTURE OF THE DEPT TABLE NAME NULL TYPE DEPTNO NOTNULL NUMBER() DNAME VARCHAR() LOC VARCHAR() EXAMINE THE CODE DECLARE DEPT_REC DEPT%ROWTYPE BEGIN SELECT * INTO DEPT_REC FROM DEPT WHERE DEPTNO = ; END WHICH PL/SQL STATEMENT DISPLAYS THE LOCATION OF SELECTED DEPARTMENT ANS: DBMS_OUTPUTPUT_LINE(DEPT_REC_LOC); WHICH STATEMENT ABOUT IMPLICT CURSOR IS TRUE ? IMPLICT CURSORT ARE DECLARED IMPLICITY FOR ALL THE DML AND SELECT STATEMENTS EVALUATE THIS PL/SQL BLOCK DECLARE V_RESULT NUMBER() BEGIN DELETE FROM EMPLOYEE WHERE DEP_ID IN(); V_RESULT:= SQL%ROWCOUNT; COMMIT END; WHAT WILL BE THE VALUE OF V_RESULT IF NO ROWS ARE DELETED; ANS: ZERO WHICH TWO CONDITIONS IN A PL/SQL BLOCK CAUSE AN EXCEPTION ERROR TO OCCUR ? CHOOSE TWO ANS: SELECT STATEMENT DOES NOT RETURN A ROW SELECT STATEMENT RETURNS MORE THAN ONE ROW 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 (EMPNOENAMEHIREDATE) VALUES (EMPNO_SEQUENCENEXTVAL?NAME?V_HIREDATE&DEPTNO); END; EVALUATE THIS PL/SQL BLOCK EVALUATE THIS PL/SQL BLOCK BEGIN FOR I IN LOOP IF I= OR I= THEN NULL; ELSE INSERT INTO TEST(RESULT) VALUES(); END; COMMIT; END LOOP; ROLL BACK; END; HOW MANY VALUES WILL BE INSERTED INTO THE TEST TABLE ? ANS: 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 IN WHICH ORDER DOES THE ORACLE SERVER EVALUATE CLAUSES? WHERE GROUP BY HAVING YOU ATTEMPT TO QUERY TO THE DATABASE WITH COMMAND SELELECT DEPT_NO AVG(MONTHS_BETWEEN(SYSDATEHIRE_DATE)) FROM EMPLOYEE WHERE AVG(MONTHS_BETWEENS(SYSDATEHIRE_DATE))> GROUP BY DEPT_NO ORDER BY AVG(MONTHS_BETWEEN(SYSDATEHIRE)DATE) WHY DOES THIS STATEMENT CAUSE AN ERROR ? ANS: A WHERE CLAUSE CAN NOT BE USED TO RESTRICT GRUPS THE PATH TABLE CONTAINS THESE COLUMNS ID NUMBER() PK COST NUMBER() PRODUCT_ID NUMBER() EVALUATE THESE SQL STATEMENT SELECT ROUND(MAX(COST)) ROUND(MIN(COST)) ROUND(SUM(COST)) ROUND(AVG(COST)) FROM PART; SELECT PRODUCT_IDROUND(MAX(COST)) ROUND(MIN(COST)) ROUND(SUM(COST)) ROUND(AVG(COST)) FROM PART GROUP BY PRODUCT_ID; HOW WILL THE RESULT DIFFER ANS: STATEMENT WILL ONLY DISPLAY ONE ROW OF RESULTS STATEMENT CAN DISPLAY MORE THAN ONE IN WHICH SELECTION OF A PL/SQL BLOCK IS A USER DEFINED EXCEPTION RAISED? EXECUTABLE EXAMINE THE CODE SET SERVER OUTPUT ON DECLARE V_CHAR_VAL VARCHAR(); BEGIN V_CHAR_VAL := 慔ELLO WORLD? DBMS_OUTPUTPUT_LINE (V_CHAR_VAL); END SET SERVER OUTPUT OFF THIS CODE IS STORED IN A SCRIPT TITLE NAME 惭YPROCSQL?WHICH STATEMENT |
