Io 1s1i49

1

Enter the missing keyword to make a new stored procedure. ____________ PROCEDURE foo ( p_foo_text IN VARCHAR2 ) AS BEGIN NULL; END; Correct answers:  CREATE  CREATE OR REPLACE 2

List the correct sequence of commands to process a set of records when using explicit cursors A.GET, SEEK, HIDE B.INITIALIZE, GET, CLOSE C.CURSOR, GET, FETCH, CLOSE D.OPEN, FETCH, CLOSE(Missed) E.CURSOR, FETCH, CLOSE 3

You have decided to permanently remove all the data from the STUDENT table, and you need the table structure in the future. Which single command performs this? A.DROP TABLE student; B.DELETE * FROM student; C.DROP TABLE student; D.TRUNCATE TABLE student;(Missed) 4

PROCEDURE foo ( p_foo_text IN VARCHAR2, p_foo_number IN OUT NUMBER ) IS

p_foo_text and p_foo_number are referred to as this procedure's _________ A.IN OUT Parameters B.Name C.Variables D.Signature E.None of the above 5

Select invalid variable types A.VARCHAR2 B.NUMBER C.VARCHAR1 D.CHAR E.INTEGER



VARCHAR1 is not an acceptable variable type. Only VARCHAR and VARCHAR2 exist.

6

Write a function named date_as_mmyyyy which will accept a date as a parameter. This date is then converted to characters and formatted as MMYYYY and then returned. If this date is null then return sysdate in this format. Your Answer

s



Sample solution 1 CREATE OR REPLACE FUNCTION date_as_mmyyyy ( p_date IN date ) RETURN varchar2 AS l_date VARCHAR2(6); BEGIN SELECT TO_CHAR(NVL(p_date,SYSDATE),'MMYYYY) INTO l_date FROM dual; RETURN l_date; END date_as_mmyyyy; Sample solution 2 CREATE OR REPLACE FUNCTION date_as_mmyyyy ( p_date IN date ) RETURN varchar2 AS

BEGIN RETURN TO_CHAR(NVL(p_date,SYSDATE),'MMYYYY); END date_as_mmyyyy;

7

Using SQL*Plus, you created a with this command: CREATE jennifer IDENTIFIED BY jbw122; What should you do to allow the database access?

A.Use the ALTER to assign the a default profile. B.Grant the the CREATE SESSION privilege. C.Use the ALTER command to assign the a default tablespace. D.No action is required to give the database access. 8

Select the best answer. Which listed attribute is an invalid attribute of an Explicit cursor. A.%FOUND B.%ROWCOUNT C.None of the above. All of these are valid.(Missed) D.%ISOPEN E.%NOTFOUND 9

Which of the following is not an Oracle DML function? A.TRUNCATE(Missed) B.NVL C.TO_CHAR D.DECODE E.Trick question, all of these are Oracle DML functions.



Do not confuse TRUNCATE with TRUNC. Truncate is used to remove all rows from an Oracle table.

10

Which two conditions in a PL/SQL block cause an exception to occur?(Choose two.)

A.The SELECT statement does not have a WHERE clause.(Missed) B.The SELECT statement returns more than one row. C.The SELECT statement contains a GROUP BY clause.

D.The datatypes in the SELECT list are inconsistent with the datatypes in the INTO clause. 11

Fill in the blank with the name of the function to convert a date to an alphanumeric string SELECT _______(sysdate, 'MM/DD/YYYY') FROM dual; kwk Correct answer:  TO_CHAR 12

Supply the missing keyword DECLARE CURSOR c1 IS SELECT * FROM DUAL; r1 c1%ROWTYPE; BEGIN OPEN c1: ______________ c1 INTO r1; IF c1%NOTFOUND THEN NULL; END IF; CLOSE c1; END; ew Correct answer:  FETCH

13

Enter the missing keyword IF foo = 1 THEN l_text := 'A'; ______ foo = 2 THEN l_text := 'B'; ELSE l_text := 'C'; END IF; A.ELSE IF B.ELSIF(Missed) C.ELIF D.ELSE E.None of the above 14

The EMPLOYEE table contains these columns: FIRST_NAMEVARCHAR2(25) COMMISSION NUMBER(3,2) Evaluate this SQL statement: SELECT first_name, commission " FROM employee WHERE commission = (SELECTcommission FROMemployee WHEREUPPER(first_name) = 'SCOTT') What would cause this statement to fail?

A.The FIRST_NAME values in the database are in lowercase B.Scott has a zero commission value. C.A. There is more than one employee with the first name Scott. (Missed)

D.Scott has a NULL commission value. 15

Select the best answer to complete this variable declaration for a column value. DECLARE l_foo_column_id SOME_TABLE.SOME_COLUMN_________; BEGIN ...

A.%ID B.%TYPE C.None of the above. D.%COLUMNTYPE E.%ROWTYPE 16

What command can you use to see the errors from a recently created view or stored procedure? A.DISPLAY MISTAKES; B.None of the above. C.SHOW MISTAKES; D.DISPLAY ERRORS; E.SHOW ERRORS;(Missed) 17

Which of the following is not a grouping function. A.COUNT B.SUM C.DISTINCT D.All of the above. E.MIN 18

Select the best answer to complete this variable declaration for a record. DECLARE l_foo_table BEGIN ...

A.%TABLE B.%COLUMNTYPE C.%ROWTYPE(Missed) D.%TYPE E.None of the above 19

SOME_TABLE_________;

Which statement shows the view definition of the view EMP_VIEW that is created based on the table EMP? A.SELECT view_text FROM my_views WHERE view_name = 'EMP_VIEW'; B.SELECT text FROM _views WHERE view_name = 'EMP_VIEW'; C.DESCRIBE VIEW emp_view D.SELECT view_text FROM TABLE emp WHERE view_name = 'EMP_VIEW'; 20

Select incorrect variable declarations A.Foo_char char(1) := 'Y'; B.Foo_number varchar2(10); C.Foo_text varchar2(10) := 'hello world'; D.Foo_text number(10); E.Foo_time date; 21 The PLAYER table contains these columns: ID NUMBER(9) NAME VARCHAR(2) MANAGER_ID NUMBER(9) In this instance, managers are players and you need to display a list of players. Evaluate these two SQL statements: SELECT p.name, m.name FROMplayer p, player m WHEREm.id = p.manager_id; SELECT p.name, m.name FROMplayer p, player m WHEREm.manager_id = p.id; How will the results differ?

A.Statement 1 will not execute; statement 2 will. B.Statement 1 is self-; statement 2 is not. C.Statement 1 will execute; statement 2 will not D.The results will be the same, but the display will be different. (Missed)

22

Where do you declare an explicit cursor in the PL/SQL language? A.In the PL/SQL body section B.In the PL/SQL working storage section C.In the PL/SQL exception section D.In the PL/SQL declaration section(Missed) E.None of the above

23

Select the best answer. This is an example of what _____ type of cursor? DECLARE l_date DATE; CURSOR c1 IS SELECT TRUNC(SYSDATE) FROM DUAL; BEGIN OPEN c1; FETCH c1 INTO l_date; CLOSE c1; END; A.Select B.Explicit C.PL/SQL D.Implicit E.None of the above 24

Supply the missing key word IF foo IS NULL _________ NULL; END IF;

h Correct answer:  THEN 25

Select the invalid PL/SQL looping construct. A.LOOP ... EXIT WHEN ; END LOOP; B.None of the above. All are valid. C.FOR rec IN some_cursor LOOP ... END LOOP;

D.LOOP ... UNTIL ; END LOOP; E.WHILE LOOP ... END LOOP;



Oracle doesn't have a Perform Until or Repeat Until loop, but you can emulate one. The syntax for emulating a REPEAT UNTIL Loop is: LOOP {.statements.} EXIT WHEN boolean_condition; END LOOP; You would use an emulated REPEAT UNTIL Loop when you do not know how many times you want the loop body to execute. The REPEAT UNTIL Loop would terminate when a certain condition was met.

26

Which statement is true when a DROP TABLE command is executed on a table? A.The table structure and its deleted data cannot be rolled back and restored once the DROP TABLE command is executed. B.The table structure and its deleted data cannot be rolled back and restored once the DROP TABLE command is executed. C.Any pending transactions on the table are rolled back. D.Only a DBA can execute the DROP TABLE command. 27

Supply the missing keyword DECLARE CURSOR c1 IS SELECT * FROM DUAL; r1 c1%ROWTYPE; BEGIN OPEN c1: FETCH c1 INTO r1; IF c1%NOTFOUND THEN RAISE; END IF; ________________ c1; END;

dl Correct answer:  CLOSE

An explicit cursor must always be closed.

Discuss this Question 28

Select the best answer. This is an example of what _____ type of cursor? DECLARE l_date DATE; BEGIN SELECT TRUNC(SYSDATE) INTO l_date FROM DUAL; END; A.Explicit B.PL/SQL C.Implicit(Missed) D.Select E.None of the above 29

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 is 250? A.500(Missed) B.750 C.250 D.1250 30

Describe the result set that will be obtained from this SELECT d.department_name, s.first_name, s.last_name, s.title, s.salary FROM employee s, department d WHERE s.salary > 20000 AND s.title = 'ANALYST' AND ( d.department = 'FINANCE' OR d.department = 'SALES' ) A.This SQL will list all employees in the Finance department earning more than 20000 B.This SQL will list all employees who are in the Finance or Sales department earning more than 20000 C.This SQL will return a cartesian product D.This SQL will list all employees who are Analysts in the Finance or Sales department earning more than 20000 E.None of the above