• Author / Uploaded
• lemi teknik

Citation preview

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

1. A function-based index may be made using your own functions, but only if the function is created using the DETERMINISTIC clause. True or False? Mark for Review (1) Points

True (*)

False

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

2.

What is the correct syntax to use the RETURNING phrase at Position A?

DECLARE TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE); emp_info EmpRec; emp_id NUMBER := 100;

BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id -- Position A dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary); END; Mark for Review (1) Points

RETURNING last_name, salary INTO emp_info; (*)

RETURNING last_name, salary TO emp_info;

last_name, salary RETURNING INTO emp_info;

RETURNING FROM emp_info;

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

3. ... BEGIN

In the following example, where do you place the phrase BULK COLLECT?

SELECT -- Position A salary -- Position B INTO v_saltab -- Position C FROM employees WHERE department_id = 20 ORDER BY salary -- Position D ; ... Mark for Review (1) Points

Position A

Position B (*)

Position C

Position D

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

4.

The following statement is a valid example of using the RETURNING clause.

True or False? DECLARE TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE); emp_info EmpRec; emp_id NUMBER := 100; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id RETURNING last_name, salary INTO emp_info; dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary); END; Mark for Review (1) Points

True (*)

False

Correct

Correct

5.

In the following example, where do you place the phrase DETERMINISTIC?

CREATE OR REPLACE FUNCTION total_sal

(p_dept_id IN -- Position A employees.department_id%TYPE) RETURN NUMBER -- Position B IS v_total_sal NUMBER; BEGIN SELECT SUM(salary) INTO v_total_sal FROM employees WHERE department_id = p_dept_in; RETURN v_total_sal -- Position C; END total_sal;

Mark for Review (1) Points

Position A

Position B (*)

Position C

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

Page 1 of 3

Next

Summary

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

6. Deterministic means the function will always return the same output return value for any given set of input argument values. True or False? Mark for Review (1) Points

True (*)

False

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

7. You want to take make a copy of all the cities in the world listed in the cities table, which contains millions of rows. The following procedure accomplishes this efficiently. True or False?

CREATE OR REPLACE PROCEDURE copy_cities IS TYPE t_cities IS TABLE OF cities%ROWTYPE INDEX BY BINARY_INTEGER; v_citiestab t_emp; BEGIN SELECT * BULK COLLECT INTO v_citiestab FROM cities; FORALL i IN v_citiestab.FIRST..v_citiestab.LAST INSERT INTO new_cities VALUES v_citiestab(i); END copy_cities; Mark for Review (1) Points

True (*)

False

Incorrect

two)

Incorrect. Refer to Section 12 Lesson 2.

8. Which of the following are NOT benefits of using the NOCOPY hint? (Choose Mark for Review

(1) Points

(Choose all correct answers)

Eliminates extra processing

Uses a larger block of server memory for faster access (*)

Faster because a single copy of the data is used

Efficient since it uses less memory

Safer because it uses passing by value (*)

Incorrect

False?

Incorrect. Refer to Section 12 Lesson 2.

9. The DBMS_SQL package is easier to use than EXECUTE IMMEDIATE. True or Mark for Review

(1) Points

True

False (*)

Incorrect

Incorrect. Refer to Section 12 Lesson 1.

10.

You want to create a function which drops a table. You write the following

code: CREATE OR REPLACE FUNCTION droptab (p_tab_name IN VARCHAR2) RETURN BOOLEAN IS BEGIN DROP TABLE p_tab_name; RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END;

Why will this procedure not compile successfully?

Mark for Review (1) Points

Because you cannot use RETURN in the exception section

Because you can never drop a table from inside a function

Because the PL/SQL compiler cannot check if the argument of p_tab_name is a valid tablename (*)

Because you do not have the privilege needed to drop a table

Incorrect

Previous

Incorrect. Refer to Section 12 Lesson 1.

Page 2 of 3

Next

Summary

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

11. as an IN parameter:

Examine the following procedure, which drops a table whose name is passed

CREATE OR REPLACE PROCEDURE drop_tab (p_table_name IN VARCHAR2) IS

v_sql_statement VARCHAR2(100); BEGIN ... END;

Which of the following will work correctly when coded in the procedure's executable section? (Choose two.)

Mark for Review (1) Points

(Choose all correct answers)

v_sql_statement := 'DROP TABLE ' || p_table_name; EXECUTE IMMEDIATE v_sql_statement; (*)

v_sql_statement := 'DROP TABLE '; EXECUTE IMMEDIATE v_sql_statement p_table_name;

EXECUTE IMMEDIATE 'DROP TABLE p_table_name';

EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;

(*)

v_sql_statement := 'DROP TABLE ' || p_table_name; EXECUTE IMMEDIATE 'v_sql_statement';

Incorrect

Incorrect. Refer to Section 12 Lesson 1.

12.

A public packaged procedure contains the following SQL statement:

UPDATE employees SET salary = salary * 1.1; When is this SQL statement parsed?

Mark for Review

(1) Points

Only the first time the procedure is executed

When the package body is created (*)

When the package header is loaded into memory

When the package is loaded into memory

When the package specification is created

Incorrect

Incorrect. Refer to Section 12 Lesson 1.

13. For which of the following is it necessary to use Dynamic SQL? (Choose three.) Mark for Review (1) Points

(Choose all correct answers)

GRANT (*)

ALTER (*)

SAVEPOINT

UPDATE

DROP (*)

Incorrect

Incorrect. Refer to Section 12 Lesson 1.

14.

Examine the following code:

CREATE OR REPLACE PROCEDURE myproc IS CURSOR c_curs IS SELECT view_name FROM user_views; BEGIN FOR v_curs_rec IN c_curs LOOP EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name; END LOOP; END;

What will happen when this procedure is invoked?

Mark for Review (1) Points

All views in the user's schema will be dropped. (*)

The procedure will raise an exception because one of the views is a complex view.

The procedure will raise an exception because Dynamic SQL can drop tables but cannot drop views.

The procedure will not compile successfully because the syntax of EXECUTE IMMEDIATE is incorrect.

Correct

Correct

15. The easiest way to include DDL statements in a PL/SQL block is to use the DBMS_SQL package. True or False? Mark for Review (1) Points

True

False (*)

Incorrect

Incorrect. Refer to Section 12 Lesson 1.

Previous

Page 3 of 3

Summary

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

1. To create a list movies from a catalog of millions of titles, you could use the following code statement to create a collection. True or False? ... TYPE nametab IS TABLE OF movies.title%TYPE; Title_tab nametab; ... SELECT title BULK COLLECT INTO title_tab FROM movies ORDER BY rental_count DESC; ... Mark for Review (1) Points

True (*)

False

Correct

Correct

2.

What is the main purpose for using the RETURNING clause?

Mark for

Review (1) Points

Improve performance by returning a single value

Return more readily any exceptions that are raised by the statement

Improve performance by minimizing the number of statements

Improve performance by making one call to the SQL engine (*)

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

3. DECLARE

What is the correct syntax to use the RETURNING phrase at Position A?

TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE); emp_info EmpRec; emp_id NUMBER := 100; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id -- Position A dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary); END; Mark for Review (1) Points

last_name, salary RETURNING INTO emp_info;

RETURNING last_name, salary INTO emp_info; (*)

RETURNING FROM emp_info;

RETURNING last_name, salary TO emp_info;

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

4.

The following statement is a valid example of using the RETURNING clause.

True or False? DECLARE TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE); emp_info EmpRec; emp_id NUMBER := 100; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id RETURNING last_name, salary INTO emp_info; dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary); END; Mark for Review (1) Points

True (*)

False

Correct

Correct

5. ...

In the following example, where do you place the phrase BULK COLLECT?

BEGIN SELECT -- Position A salary -- Position B INTO v_saltab -- Position C FROM employees WHERE department_id = 20 ORDER BY salary -- Position D ; ... Mark for Review (1) Points

Position A

Position B (*)

Position C

Position D

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

Page 1 of 3

Next

Summary

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

two)

6. Which of the following are NOT benefits of using the NOCOPY hint? (Choose Mark for Review

(1) Points

(Choose all correct answers)

Uses a larger block of server memory for faster access (*)

Eliminates extra processing

Faster because a single copy of the data is used

Safer because it uses passing by value (*)

Efficient since it uses less memory

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

7. You want to take make a copy of all the cities in the world listed in the cities table, which contains millions of rows. The following procedure accomplishes this efficiently. True or False? CREATE OR REPLACE PROCEDURE copy_cities IS TYPE t_cities IS TABLE OF cities%ROWTYPE INDEX BY BINARY_INTEGER; v_citiestab t_emp; BEGIN SELECT * BULK COLLECT INTO v_citiestab FROM cities; FORALL i IN v_citiestab.FIRST..v_citiestab.LAST INSERT INTO new_cities VALUES v_citiestab(i); END copy_cities; Mark for Review (1) Points

True (*)

False

Correct

Correct

8. A function-based index may be made using your own functions, but only if the function is created using the DETERMINISTIC clause. True or False? Mark for Review (1) Points

True (*)

False

Correct

Correct

9. Only one call to DBMS_SQL is needed in order to drop a table. True or False? Mark for Review (1) Points

True

False (*)

Correct

Correct

10.

Examine the following code:

CREATE OR REPLACE PROCEDURE myproc IS CURSOR c_curs IS SELECT view_name FROM user_views; BEGIN FOR v_curs_rec IN c_curs LOOP EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name; END LOOP; END;

What will happen when this procedure is invoked?

Mark for Review (1) Points

The procedure will raise an exception because Dynamic SQL can drop tables but cannot drop views.

The procedure will raise an exception because one of the views is a complex view.

All views in the user's schema will be dropped. (*)

The procedure will not compile successfully because the syntax of EXECUTE IMMEDIATE is incorrect.

Incorrect

Previous

Incorrect. Refer to Section 12 Lesson 1.

Page 2 of 3

Next

Summary

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

11. When SQL statements are included within a procedure, the statements are parsed when the procedure is compiled. True or False? Mark for Review (1) Points

True (*)

False

Correct

Correct

12. Review (1) Points

Execute Parse Fetch Bind

Bind Parse Execute Fetch

Which is the correct order for the execution flow of SQL?

Mark for

Parse Fetch Bind Execute

Parse Bind Execute Fetch (*)

Incorrect

Incorrect. Refer to Section 12 Lesson 1.

13.

What will happen when the following procedure is invoked?

CREATE OR REPLACE PROCEDURE do_some_work IS CURSOR c_curs IS SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION'; BEGIN FOR v_curs_rec IN c_curs LOOP EXECUTE IMMEDIATE 'ALTER FUNCTION ' || v_curs_rec.object_name || ' COMPILE'; EXIT WHEN c_curs%ROWCOUNT > 2; END LOOP; END;

Mark for Review (1) Points

The first two functions in the user's schema will be recompiled.

The first three functions in the user's schema will be recompiled. (*)

The procedure will not compile successfully because the syntax of the ALTER FUNCTION statement is incorrect.

All functions in the user's schema will be recompiled.

The procedure will not compile successfully because you cannot ALTER functions using Dynamic SQL.

Correct

Correct

14. (1) Points

Name two reasons for using Dynamic SQL.

Mark for Review

(Choose all correct answers)

Provides the ability to handle mutating rows when executing a statement involving the same table.

Provides the ability to execute SQL statements whose structure is unknown until execution time. (*)

Enables session-control statements to be written and executed from PL/SQL. (*)

Allows fetch of data for DML statements.

Incorrect

Incorrect. Refer to Section 12 Lesson 1.

15.

Name two reasons for using Dynamic SQL.

Mark for Review

(1) Points

(Choose all correct answers)

Enables system control statements to be written and executed from PL/SQL

Avoids errrors at compile time of DML statements

Enables data-definition statements to be written and executed from PL/SQL (*)

Creates a SQL statement with varying column data, or different conditions (*)

Incorrect

Incorrect. Refer to Section 12 Lesson 1.

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

1. Review (1) Points

Parse

Which is the correct order for the execution flow of SQL?

Mark for

Fetch Bind Execute

Execute Parse Fetch Bind

Bind Parse Execute Fetch

Parse Bind Execute Fetch (*)

Correct

Correct

2.

Name two reasons for using Dynamic SQL.

Mark for Review

(1) Points

(Choose all correct answers)

Allows fetch of data for DML statements.

Provides the ability to execute SQL statements whose structure is unknown until execution time. (*)

Enables session-control statements to be written and executed from PL/SQL. (*)

Provides the ability to handle mutating rows when executing a statement involving the same table.

Correct

Correct

3. For which of the following is it necessary to use Dynamic SQL? (Choose three.) Mark for Review (1) Points

(Choose all correct answers)

SAVEPOINT

UPDATE

DROP (*)

ALTER (*)

GRANT (*)

Correct

Correct

4.

Examine the following code:

CREATE OR REPLACE PROCEDURE myproc IS CURSOR c_curs IS SELECT view_name FROM user_views; BEGIN FOR v_curs_rec IN c_curs LOOP

EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name; END LOOP; END;

What will happen when this procedure is invoked?

Mark for Review (1) Points

All views in the user's schema will be dropped. (*)

The procedure will raise an exception because one of the views is a complex view.

The procedure will raise an exception because Dynamic SQL can drop tables but cannot drop views.

The procedure will not compile successfully because the syntax of EXECUTE IMMEDIATE is incorrect.

Correct

Correct

5. A programmer wants to code a procedure which will create a table with a single column. The datatype of the column will be chosen by the user who invokes the procedure. The programmer writes the following code: CREATE OR REPLACE PROCEDURE create_tab (p_col_datatype IN VARCHAR2) IS BEGIN CREATE TABLE newtab (only_col p_col_datatype); END;

Why will this procedure not compile successfully?

Mark for Review (1) Points

Because you cannot create a table inside a procedure

Because the invoking user may not have CREATE TABLE privilege

Because when the procedure is compiled, Oracle cannot check if the parameter value passed into the procedure is a valid column datatype (*)

Because table NEWTAB may already exist

None of the above; the procedure will compile successfully.

Incorrect

Page 1 of 3

Incorrect. Refer to Section 12 Lesson 1.

Next

Summary

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

6. Which of the following SQL statements can be included in a PL/SQL block only by using Dynamic SQL? (Choose two.) Mark for Review (1) Points

(Choose all correct answers)

SAVEPOINT

DELETE

ALTER (*)

GRANT (*)

SELECT ..... FOR UPDATE NOWAIT

Correct

Correct

7. A SQL statement can pass through several stages. Which of the following is NOT one of these stages? Mark for Review (1) Points

PARSE

FETCH

BIND

RETURN (*)

EXECUTE

Correct

Correct

8.

Where would you place the BULK COLLECT statement in the following

example? DECLARE TYPE DeptRecTab IS TABLE OF departments%ROWTYPE; dept_recs DeptRecTab; CURSOR c1 IS SELECT department_id, department_name, manager_id, location_id -- Position A FROM departments WHERE department_id > 70; BEGIN OPEN c1 -- Position B; FETCH c1 -- Position C INTO dept_recs; END;

Mark for Review (1) Points

Position A

Position B

Position C (*)

Correct

Correct

9. Review (1) Points

True (*)

False

FORALL can be used with any DML statement. True or False?

Mark for

Correct

Correct

10.

What is the correct syntax to use the RETURNING phrase at Position A?

DECLARE TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE); emp_info EmpRec; emp_id NUMBER := 100; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id -- Position A dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary); END; Mark for Review (1) Points

last_name, salary RETURNING INTO emp_info;

RETURNING last_name, salary INTO emp_info; (*)

RETURNING last_name, salary TO emp_info;

RETURNING FROM emp_info;

Correct

Correct

Previous

Page 2 of 3

Next

Summary

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section)

11.

What are benefits of using the NOCOPY hint? (Choose two)

Review (1) Points

(Choose all correct answers)

Uses a larger block of server memory for faster access

Safer because it uses passing by value

Mark for

Efficient since it uses less memory (*)

Faster because a single copy of the data is used (*)

Correct

Correct

12.

The following example code will compile successfully. True or False?

CREATE OR REPLACE PROCEDURE dept_proc IS TYPE t_dept IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER; BEGIN (p_small_arg IN NUMBER, p_big_arg OUT NOCOPY t_dept); -- remaining code END dept_proc; Mark for Review (1) Points

True (*)

False

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

13. Mark for Review

FORALL can only be used with the INSERT statement. True or False?

(1) Points

True

False (*)

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

14.

What is wrong with this code example?

CREATE OR REPLACE PROCEDURE insert_emps IS TYPE t_emp IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; v_emptab t_emp; BEGIN FORALL i IN v_emptab.FIRST..v_emptab.LAST INSERT INTO employees VALUES v_emptab(i);

END LOOP; END insert_emps; Mark for Review (1) Points

v_emptab is incorrectly typed.

Nothing is wrong; it will compile successfully.

The phrase should be FOR ALL.

FORALL does not require END LOOP. (*)

Incorrect

Incorrect. Refer to Section 12 Lesson 2.

15.

The following statement is a valid example of using the RETURNING clause.

True or False? DECLARE TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE); emp_info EmpRec; emp_id NUMBER := 100;

BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id RETURNING last_name, salary INTO emp_info; dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary); END; Mark for Review (1) Points

True (*)

False

Correct

Correct

Previous

Page 3 of 3

Summary