|
Question 1. Andrew forgot his password while on location. Which command must be executed to set a password for andrew? A. Andrew must execute the command. ALTER USER andrew PASSOWRD BY lion B. The DBA must execute the command. ALTER USER andrew IDENTIFIED BY lion C. Andrew must execute the command ALTER USER andrew IDENTIFIED BY lion D. The andrew must execute the command CHANGE password to lion WHERE “user=andrew”; E. The DBA must execute the command CHANGE password to lion WHERE “user=andrew”; Answer: B Explanation: Answer B is correct because of only DBA (not user himself) can change password for account if user forgot his password. Question 2. You are updating the employee table. Sharon has been granted the same privileges as you on the employee table. You ask Sharon to logon to the database to check your world before you issue the commit command. What can Sharon do to the employee table? A. Sharon can access the table and verify your changes. B. Sharon cannot access the table. C. Sharon can access the table but she cannot see your changes, she can make the changes for you. D. Sharon can access the table but she cannot see your changes and cannot make the changes to the roles that you are changing. Answer: D Explanation: Answer D is correct because before you committed changes in the employee table nobody can see changed data. Question 3 Examine the structure of STUDENT table: Name Null? Type STUD-ID NOT NULL NUMBER(3) NAME NOT NULL VARCHER2(25) ADDRESS VARCHER2(50) GRADUATION DATE Which statement inserts a new row into the STUDENT table? A. INSERT INTO student. VALUES(101, ‘Smith’); B. INSERT INTO student. VALUES(101, ‘100 Main Street’, ‘17-JUN-99’, ‘Smith’); C. INSERT INTO test. VALUES(101, ‘Smith’, ‘100 Main Street’, ‘17-JUN-99’); D. INSERT INTO student.(stud-id,address,gradulation) VALUES(101, ‘Smith’, ‘100 Main Street’, ‘17-JUN-99’); E. INSERT INTO student.(stud-id,address,name,gradulation) VALUES(101, ‘100 Main Street’,‘Smith’, ‘17-JUN-99’); Answer: E Explanation: Answer E is correct because you need to show ALL columns and in correct order for values which you try to insert into table STUDENT. Question 4. Examine the structure of the STUDENT table: NAME NULL? TYPE STUDENT_ID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(25) ADDRESS VARCHAR2(50) GRADUATION DATE Graduation column is a foreign key column to the graduate table. Examine the data in the GRADE DATE table: Graduation 20-jan-1999 12-may-1999 19-jan-2000 25-may-2000 13-jan-2001 29-may-2001 Which update statement produces the following error: ORA-02291 integrity constraint(sys_c23) violated parent key not found? A. UPDATE student SET stud-id=999, graduation= ’29-MAY-2001’ WHERE stud-id=101;’ B. UPDATE student SET name= ‘Smith’, graduation= ’29-MAY-2001’ WHERE stud-id=101; C. UPDATE student SET name= ‘Smith’, graduation= ‘15-AUG-2000’ WHERE stud-id=101 D. UPDATE student SET stud-id=NULL, address= ‘100 Main Street’ WHERE graduation= ‘20-JAN-1999’ Answer: C Explanation: Answer C is correct because there is no ’15-AUG-2000’ value in the graduate table. Foreign constraint for this statement will be violated because parent key not found. Question 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 the following command is used? UPDATE emp-view SET tot-sal=20000 WHERE deptno=10; A. The base table cannot be updated through this view. B. The TOT_SAL column in the EMP table is updated to 20,000 for department C. The TOT_SAL column in the EMP view is updated to 20,000 for department10. D. The SAL column in the EMP table is updated to 20,000 for employees in department 10. Answer: A Explanation: Answer A is correct because the user may not INSERT, DELETE, or UPDATE data on the table underlying the sample view if the SELECT statement creating the view contains GROUP BY, or a single-row operation. Question 6. You have a view card ANN_SAL that is based on the employee table. The structure of the ANN_SAL view is: NAME NULL? TYPE EMPNO NOT NULL NUMBER(4) YEARLY_SAL NUMBER(9,2) MONTHLY_SAL NUMBER(9,2) Which statement retrieves the data from the ANN_SAL view? A. SELECT * FROM ANN_SAL B. SELECT * FROM EMPLOYEE C. SELECT * FROM VIEW ANN_SAL D. SELECT * FROM VIEW ANN_SAL IS DON EMPLOYEE Answer: A Explanation: Answer A is correct because correct syntax for SELECT command for view is SELECT * FROMQuestion 7. Evaluate this IF statement: IF v_value>100 THEN v_new-value:=2*v-value; ELSIF v-value>200 THEN v-new-value:=3*v-value; ELSIF v-value>300 THEN v-new-value:=4*v-value; ELSE v-new-value:=5*v-value; END IF What would be assigned to v_new_value if v_value=250? A. 250 B. 500 C. 750 D. 1000 Answer: B Explanation: Answer B is correct because first IF condition v_value > 100 will be TRUE if v_value have been assigned with new value equal 250. Result of 2*v_value is 500. Question 8. The PLAYER table contains these columns ID NUMBER(9) NAME VARCHER(2) MANAGERID NUMBER(9) In this instance, managers are players with you need to display a list of players. Evaluate these TWO SQL statements: SELECT p.name,m.name FROM player p,player m WHERE m.id= m.manager_id; SELECT p.name,m.name FROM player p,player m WHERE m.manager_id=p.id; How would the results differ? A. Statement1 will not execute, statement2 will. B. Statement1 will execute, statement2 will not. C. Statement1 is self join, statement2 is not. D. The results will be same but the display will be different. Answer: D Explanation: Answer D is correct because the results of these queries will be same, just will look different. In first statement driving column is ID, in second – MANAGER_ID. Question 9. How would you declare a PL/SQL table of records to hold the rows selected from the EMP table? A. DECLARE emp-table is TABLE of emp%ROWTYPE. B. BEGIN TYPE emp-table is TABLE of emp%ROWTYPE emp-table emp-table-type; C. DECLARE TYPE emp-table is TABLE of emp%ROWTYPE INDEX BY WHOLE NUMBER: emp-table emp-table-type; D. DECLARE TYPE emp-table is TABLE of emp%ROWTYPE INDEX BY BINARY INTEGRATDE. emp-table emp-table-type; Answer: D Explanation: Answer D is correct because INDEX BY BINARY INTEGRATED clause need to be set for TABLE type. Question 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 for loop. B. A multiple selection cursor. C. A cursor for each active set. D. A cursor that uses parameters. Answer: D Explanation: Answer D is correct because a cursor with parameters can be used several times in a block, selecting active set each time that it was opened depending on parameters’ values.
Copyright © 2004 CertsBraindumps.com Inc. All rights reserved.