• Author / Uploaded
• Ovie Widiyastuti

Citation preview

Section 9 Quiz (Answer all questions in this section) 1.Which dictionary view will list all the PL/SQL subprograms in your schema?

Mark for Review (1) Points

user_procedures user_objects (*) user_subprograms user_source user_dependencies Correct 2.Examine the following code: CREATE PROCEDURE parent IS BEGIN child1; child2; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END parent;

Mark for Review (1) Points

Neither CHILD1 nor CHILD2 has an exception handler. When PARENT is invoked, CHILD1 raises a NO_DATA_FOUND exception. What happens next? CHILD1 ends abruptly, PARENT handles the exception, and then CHILD2 executes. CHILD1 ends abruptly; PARENT handles the exception and then ends; CHILD2 does not execute. (*) PARENT does not compile because you cannot use NULL; in an exception handler. CHILD1 ends abruptly; PARENT also ends abruptly and returns an unhandled exception. PARENT handles the exception, and then CHILD1 continues to execute. Correct 3.Which of the following is a difference between a procedure and a function?

Mark for Review (1) Points

A function must have at least one IN parameter, while parameters are optional for a procedure. A procedure can include an EXCEPTION section, while a function cannot. A procedure can return a BOOLEAN datatype, while a function cannot. A procedure can include DML statements, but a function cannot. A function can be used inside a SQL statement, while a procedure cannot. (*) Correct 4.You have created a function called GET_COUNTRY_NAME which accepts a country_id as an IN parameter and returns the name of the country. Which one of the following calls to

Mark for Review

the function will NOT work?

(1) Points

SELECT get_country_name(100) FROM dual; DBMS_OUTPUT.PUT_LINE(get_country_name(100)); BEGIN

get_country_name(100, v_name); END; (*)

v_name := get_country_name(100); Correct 5.You try to create a function named MYFUNC. The function does not compile correctly because there are errors in your code. Which Dictionary view can you query to see the errors?

Mark for Review (1) Points

USER_COMPILES USER_SOURCE USER_DEPENDENCIES USER_OBJECTS USER_ERRORS (*) Correct 6.When must AUTHID CURRENT_USER be included in an autonomous transaction subprogram?

Mark for Review (1) Points

When declaring Invoker's rights (*) When declaring Definer's rights When using GRANT on the subprogram When using COMMIT or ROLLBACK Correct 7. User REYHAN creates the following procedure: CREATE PROCEDURE proc1 AUTHID CURRENT_USER IS v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM tom.employees; END; User BILL wants to execute this procedure. What privileges will BILL need? BILL needs no privileges EXECUTE on REYHAN.PROC1 and SELECT on TOM.EMPLOYEES (*) EXECUTE on REYHAN.PROC1

Mark for Review (1) Points

None of these. The procedure will fail to compile because REYHAN does not have SELECT privilege on TOM.EMPLOYEES. SELECT on TOM.EMPLOYEES Correct 8. Which object privilege can be granted on a single column of a table?

Mark for Review (1) Points

UPDATE (*) SELECT CREATE DROP DELETE Correct 9. User COLLEEN owns an EMPLOYEES table and wants to allow user AYSE to create indexes on the table. Which object privilege must Colleen grant to Ayse?

Mark for Review (1) Points

SELECT on EMPLOYEES CREATE on EMPLOYEES None of these ALTER on EMPLOYEES INDEX on EMPLOYEES (*) Correct 10. You want to display the names of all tables in your schema, but you have forgotten which Dictionary view to query. Which of the following will remind you of the name of the correct Dictionary view? HELP DICTIONARY SELECT * FROM DICTIONARY WHERE table_name LIKE 'DBA%TABLE%'; SELECT * FROM DICTIONARY WHERE table_name LIKE 'USER%TAB%'; (*) SELECT * FROM USER_OBJECTS WHERE table_name LIKE '%TABLE%'; SELECT * FROM DICTIONARY WHERE table_name LIKE 'user%table%'; Correct Section 9 Quiz (Answer all questions in this section)

Mark for Review (1) Points

11.Which of the following will tell you how many functions you own?

Mark for Review (1) Points

SELECT COUNT(*) FROM USER_OBJECTS; SELECT COUNT(*) FROM USER_PROCEDURES; SELECT COUNT(*) FROM USER_FUNCTIONS; SELECT FUNCTIONS FROM USER_OBJECTS; SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION'; (*) Incorrect. Refer to Section 9 Lesson 3. 12.User JOHN wants to see the names of all the tables in his schema. He does NOT want to see the names of any tables in other users' schemas. Which Dictionary view should he query?

Mark for Review (1) Points

DBA_TABLES USER_TABLES (*) DICTIONARY JOHN_TABLES ALL_TABLES Incorrect. Refer to Section 9 Lesson 3. 13.Which of the following is a benefit of user-defined functions? (Choose 3)

Mark for Review (1) Points

(Choose all correct answers) They can do the same job as built-in system functions such as UPPER and ROUND. They can often be used inside SQL statements. (*) They can add business rules to the database and can be reused many times. (*) They can be used in a WHERE clause to filter data and thereby increase efficiency. (*) Correct 14.Function DOUBLE_SAL has been created as follows: CREATE OR REPLACE FUNCTION double_sal (p_salary IN employees.salary%TYPE) RETURN NUMBER IS BEGIN RETURN(p_salary * 2); END; Which of the following calls to DOUBLE_SAL will NOT work? None, they will all work (*) SELECT * FROM employees ORDER BY double_sal(salary) DESC; SELECT last_name, double_sal(salary) FROM employees; UPDATE employees SET salary = double_sal(salary); SELECT * FROM employees WHERE double_sal(salary) > 20000; Correct

Mark for Review (1) Points

15.The function avg_ann_sal returns the average annual salary for a particular department. The example below is a valid use of this function. True or False? SELECT first_name, last_name FROM employees WHERE avg_ann_sal(20) > 15000; True (*) False Correct

Mark for Review (1) Points