kuis 13,oracleacademy 2019

1. There are five employees in department 50. The following trigger is created:
CREATE TRIGGER upd_emp
AFTER UPDATE ON employees
BEGIN
    INSERT INTO audit_table VALUES (USER, SYSDATE);
END;

A user now executes:

UPDATE employees
SET salary = salary * 1.1
    WHERE department_id = 50;

How many rows will be inserted into audit_table?

 Mark for Review
(1) Points


One (*)


Two


Five


Six


None of these.



Correct Correct


2. What is wrong with the following code?
CREATE OR REPLACE TRIGGER loc_trigg
BEFORE DELETE ON locations
BEGIN
    RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
    ROLLBACK;
END;
 Mark for Review
(1) Points


Nothing is wrong, this trigger will compile and execute successfully.


You cannot use RAISE_APPLICATION_ERROR inside a trigger.


The second line should be:
BEFORE DELETE OF locations


The last line should be:
END loc_trigg;


You cannot use ROLLBACK inside a trigger.
(*)




Correct Correct


3. The following code will successfully create emp_trigg: True or False?
CREATE OR REPLACE TRIGGER emp_trigg
BEFORE DELETE OF salary ON employees
BEGIN
    RAISE_APPLICATION_ERROR(-20202,'Deleting salary is not allowed');
END;
 Mark for Review
(1) Points


True


False (*)



Correct Correct


4. You have created several DML triggers which reference your DEPARTMENTS table. Now you want to disable all of them using a single SQL statement. Which command should you use? Mark for Review
(1) Points


ALTER TABLE departments DISABLE TRIGGERS;


DROP ALL TRIGGERS ON departments;


ALTER TABLE departments DISABLE ALL TRIGGERS; (*)


ALTER TRIGGER DISABLE ALL ON departments;



Correct Correct


5. By default, any user can create a DML trigger on a table in his/her schema. True or False? Mark for Review
(1) Points


True


False (*)

6. User AYSEGUL successfully creates the following trigger:
CREATE TRIGGER loc_trigg
BEFORE UPDATE ON aysegul.locations
BEGIN ....

AYSEGUL now tries to drop the LOCATIONS table. What happens?

 Mark for Review
(1) Points


Both the table and the trigger are dropped. (*)


The trigger is dropped but the table is not dropped.


An error message is displayed because you cannot drop a table that is associated with a trigger.


The table is dropped and the trigger is disabled.


None of these.



Correct Correct


7. Which of the following best describes a database trigger? Mark for Review
(1) Points


A PL/SQL subprogram that executes automatically whenever an associated database event occurs (*)


A subprogram that is invoked explicitly by the calling application


A PL/SQL subprogram that inserts rows into a logging table


A PL/SQL subprogram that always returns exactly one value


A subprogram that checks whether a user has typed the correct password to log on to the database



Correct Correct


8. What type of database object would you create to write an auditing record automatically every time a user connects to the database? Mark for Review
(1) Points


A trigger (*)


A package


A procedure


A function


A complex view



Correct Correct


9. Which of the following are NOT allowed within a database trigger? (Choose two) Mark for Review
(1) Points

(Choose all correct answers)


COMMIT (*)


INSERT


A Boolean variable


SAVEPOINT (*)


A call to a packaged procedure



Correct Correct


10. What is the purpose of using the CALL statement in a trigger? Mark for Review
(1) Points


It allows the trigger body code to be placed in a separate procedure. (*)


It allows the trigger body code to be placed in a separate trigger.


It allows both DML events and DDL events to be handled using a single trigger.


It allows an INSTEAD OF trigger to be a statement trigger.


It prevents cascading triggers.



Correct Correct

11. You can create a trigger which prevents DDL statements on an individual table, while still allowing DDL on other tables in the same schema. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct


12. The database administrator creates a trigger that automatically disconnects user HACKER whenever HACKER connects to the database. What type of trigger is this? Mark for Review
(1) Points


A Database Event trigger (*)


A DDL trigger


A DML trigger


An INSTEAD OF trigger


A statement trigger



Correct Correct


13. Which of the following best describes conditional predicates in a trigger? Mark for Review
(1) Points


They allow the trigger code to see what data values are being inserted into a row.


They are automatically declared boolean variables which allow the trigger body to detect which DML operation is being executed. (*)


They are special cursor attributes, like %ROWCOUNT and %NOTFOUND


They are special variables which must be DECLAREd within the trigger.



Correct Correct


14. What is the event that will cause the trigger on the emp_details view below to fire?
CREATE OR REPLACE TRIGGER new_emp_dept
INSTEAD OF INSERT ON emp_details
BEGIN
  INSERT INTO new_emps
    VALUES (:NEW.employee_id, :NEW.last_name,
      UPDATE new_depts         :NEW.salary, :NEW.department_id);
      new_depts
      SET dept_sal = dept_sal + :NEW.salary
      WHERE department_id = :NEW.department_id;
END;
 Mark for Review
(1) Points


An attempt to update salary column on the new_depts table


An attempt to add a row in the emp_details view (*)


A new employee is added to the emp_details table


A procedure calls the new_emp_dept trigger.


An attempt to add a row in the new_depts table.



Correct Correct


15. What are the components of a compound trigger? Mark for Review
(1) Points


Declaration section and at least two timing sections.


Declaration section and all four timing sections.


Declaration section and at least one timing section. (*)


Declaration section, timing sections, and exception section.


Declaration section, referencing section, and timing sections.



Correct Correct





Correct Correct

Semester 1 Final Exam( Database Programming with PL/SQL 2019 Learner - English)

1. What is missing from the following cursor declaration?

  CURSOR emp_curs

IS

SELECT * FROM departments

  WHERE location_id = p_loc_id;

 Mark for Review 

(1) Points

A parameter is missing. The parameter should be coded as: (p_loc_id NUMBER) (*)

Nothing is wrong; the cursor declaration is correct.

The declaration is invalid. You cannot reference a cursor parameter in a WHERE clause.

A parameter is missing. The parameter should be coded as: (p_loc_id IN NUMBER)

Correct Correct

2. You want to use explicit cursors to fetch and display all the countries in a specific region. There are 19 rows in the WF_WORLD_REGIONS table. You want to use a different region each time the cursor is opened. How many cursors should you declare? Mark for Review 

(1) Points

20 cursors, in case an extra row is inserted into WF_WORLD_REGIONS later.

19 cursors in 19 PL/SQL blocks (one in each block).

19 cursors, all in the same PL/SQL block.

None of the these.

One cursor with a parameter in the WHERE clause. (*)

Correct Correct

3. You have declared the following cursor: CURSOR country_curs IS

   SELECT country_id, country_name

   FROM wf_countries

   FOR UPDATE WAIT 10;  Another user updates a row in WF_COUNTRIES but does not COMMIT the update. What will happen when you OPEN country_curs; ? Mark for Review 

(1) Points

A LOCKED_ROWS exception is raised immediately.

Your block fails because you should have coded: FOR UPDATE WAIT (10);

Your session waits for 10 seconds, and then returns control to your block so that it can continue to execute. (*)

Your session waits indefinitely until the other user COMMITs.

The other user's transaction is automatically rolled back.

Correct Correct

4. You declare a cursor as a join of two tables: CURSOR emp_dept_curs IS

   SELECT last_name, salary, department_name

   FROM employees e, departments d

   WHERE e.department_id = d.department_id

   -- Point A -- ; You want to lock fetched rows from EMPLOYEES, but NOT lock fetched rows from DEPARTMENTS. Which of the following is correct at Point A? Mark for Review 

(1) Points

FOR UPDATE

FOR UPDATE of salary (*)

FOR UPDATE (last_name)

FOR UPDATE OF employees

Correct Correct

5. Examine the following code:

DECLARE

  CURSOR region_cur IS

   SELECT * FROM wf_world_regions;

  v_region_rec region_cur%ROWTYPE;

  CURSOR country_cur (p_region_id NUMBER) IS

   SELECT * FROM wf_countries

   WHERE region_id = p_region_id;

   v_country_rec country_cur%ROWTYPE;

BEGIN

  OPEN region_cur;

  LOOP

   FETCH region_cur INTO v_region_rec;

   EXIT WHEN region_cur%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE

    (v_region_rec.region_name);

   -- Line A --

   LOOP

    FETCH country_cur INTO v_country_rec;

    EXIT WHEN country_cur%NOTFOUND;

     ......

What would you code at Line A?

 Mark for Review 

(1) Points

OPEN country_cur (v_region_rec.region_id); (*)

OPEN country_cur (p_region_id);

OPEN country_cur (wf_world_regions.region_id);

OPEN country_cur;

OPEN country_cur (region_cur.region_id);

Correct Correct

6. Assume that table BIGDEPTS contains 100 rows, and table BIGEMPS contains 1000 rows, with 10 employees in each department. Consider the following code:

DECLARE

  CURSOR bigdept_cur IS

   SELECT * FROM bigdepts;

  CURSOR bigemp_cur IS

   SELECT * FROM bigemps;

BEGIN

  FOR dept_rec IN bigdept_cur LOOP

   DBMS_OUTPUT.PUT_LINE (dept_rec.department_name);

   FOR emp_rec IN bigemp_cur LOOP

    IF emp_rec.department_id=dept_rec.department_id

     THEN DBMS_OUTPUT.PUT_LINE (emp_rec.last_name);

    END IF;

   END LOOP;

  END LOOP;

END;

Why is this code inefficient?

 Mark for Review 

(1) Points

It reads 1000 employee rows every time BIGEMP_CUR is OPENed, and then ignores 990 of them. (*)

It is doing a Cartesian Product, joining every employee with every department and displaying 1100 lines of output.

It is using cursor FOR loops, which are less efficient than OPENing and CLOSEing the cursors explicitly.

It is using two cursors when one cursor is enough.

It locks both tables unnecessarily.

Correct Correct

7. How must you reference one field which is part of a PL/SQL record? Mark for Review 

(1) Points

It cannot be done.

field_name.record_name

field_name OF record_name

record_name(field_name)

record_name.field_name (*)

Correct Correct

8. Which of the following statements about the %ISOPEN cursor attribute is true? Mark for Review 

(1) Points

You can issue the %ISOPEN cursor attribute only when more than one record is returned.

You can issue the %ISOPEN cursor attribute only when a cursor is open.

If a cursor is open, then the value of %ISOPEN is false.

You can issue the %ISOPEN cursor attribute when a cursor is open or closed. (*)

Correct Correct

9. Which of the following explicit cursor attributes evaluates to TRUE if the most recent FETCH returns a row? Mark for Review 

(1) Points

%FOUND (*)

%ROWCOUNT

%NOTFOUND

%ROWTYPE

Correct Correct

10. A cursor FOR loop using a subquery can shorten code length when compared to an explicit cursor declaration. True or False? Mark for Review 

(1) Points

True (*)

False

Correct Correct

11. There are no employees in department_id 75.

Which of the following is NOT a valid cursor FOR loop with a subquery? Mark for Review 

(1) Points

FOR emp_rec IN

    (SELECT last_name, salary FROM employees ORDER BY last_name) LOOP ...

FOR emp_rec IN

    (SELECT * FROM employees WHERE department_id = 75) LOOP ...

FOR emp_rec IN

   (SELECT last_name, salary FROM employees) LOOP ...

None of these. They are all valid. (*)

FOR emp_rec IN

    (SELECT * FROM employees) LOOP ...

Correct Correct

12. When using a cursor FOR loop, OPEN, CLOSE, and FETCH statements should not be explicitly coded. True or False? Mark for Review 

(1) Points

True (*)

False

Correct Correct

13. Which of these constructs can be used to fetch multiple rows from a cursor's active set? Mark for Review 

(1) Points

An IF .... ELSE statement

A CASE statement

A basic loop which includes FETCH and EXIT WHEN statements (*)

A basic loop which includes OPEN, FETCH, and CLOSE statements

Correct Correct

14. Which of the following best describes the difference between implicit and explicit cursors? Mark for Review 

(1) Points

Implicit cursor are named by the PL/SQL programmer, while explicit cursors are always named SQL.

Implicit cursors store rows on disk, while explicit cursors store rows in memory.

Implicit cursors are used for SELECT statements, while explicit cursors are used for DML statements.

Implicit cursors are defined automatically by Oracle, while explicit cursors must be declared by the PL/SQL programmer. (*)

Correct Correct

15. There are 8 countries in REGION_ID 13 (Central America). What will happen when the following code is executed?

DECLARE

  CURSOR country_curs IS SELECT country_name FROM wf_countries

   WHERE region_id = 13;

  v_country_name wf_countries.country_name%TYPE;

BEGIN

  OPEN country_curs;

  WHILE country_curs%FOUND

  LOOP

   FETCH country_curs INTO v_country_name;

   DBMS_OUTPUT.PUT_LINE(v_country_name);

  END LOOP;

  CLOSE country_curs;

END;

 Mark for Review 

(1) Points

The block will execute, but no rows will be displayed. (*)

None of the these.

The block will fail because you can not use a WHILE loop with an explicit cursor.

Eight rows will be fetched and displayed successfully.

The last seven rows will be fetched and displayed.

Correct Correct

16. Which one of the following explicit cursor declarations is NOT valid? Mark for Review 

(1) Points

CURSOR country_curs IS SELECT country_name INTO v_country_name FROM wf_countries; (*)

CURSOR country_curs IS SELECT country_name FROM wf_countries ORDER BY population DESC;

CURSOR country_curs IS SELECT country_name, region_name FROM wf_countries c, wf_world_regions r WHERE c.region_id = r.region_id;

CURSOR country_curs IS SELECT country_name FROM wf_countries WHERE region_id IN (SELECT region_id FROM wf_world_regions WHERE LOWER(region_name) LIKE '%asia%');

Correct Correct

Section 6

(Answer all questions in this section)

17. Which of the following methods can be used to reference elements of an INDEX BY table? (Choose three.) Mark for Review 

(1) Points

(Choose all correct answers)

EXISTS (*)

PREVIOUS

DROP

COUNT (*)

FIRST (*)

Correct Correct

18. You can store a whole record in a single variable using %ROWTYPE or by creating your own record structure as a type and then declaring a variable of that type. Mark for Review 

(1) Points

True (*)

False

Correct Correct

Section 7

(Answer all questions in this section)

19. How can you retrieve the error code and error message of any Oracle Server exception? Mark for Review 

(1) Points

By using the functions SQLCODE and SQLERRM (*)

By defining an EXCEPTION variable and using PRAGMA EXCEPTION_INIT

By using RAISE_APPLICATION_ERROR

By using the functions SQLCODE and SQLERR

Correct Correct

20. Which one of the following events would implicitly raise an exception? Mark for Review 

(1) Points

A SELECT statement returns exactly one row.

An UPDATE statement modifies no rows.

A database constraint is violated. (*)

The PL/SQL programmer mis-spells the word BEGIN as BEGAN.

Correct Correct

21. Which of the following best describes a user-defined exception? Mark for Review 

(1) Points

An error which is not automatically raised by the Oracle server (*)

A predefined Oracle Server error such as NO_DATA_FOUND

Any error which has an Oracle error number of the form ORA-nnnnn

A non-predefined Oracle Server error such as ORA-01400

Correct Correct

22. Non-predefined Oracle Server errors (associated with Oracle error numbers by PRAGMA EXCEPTION_INIT) can be declared and raised in inner blocks and handled in outer blocks. True or False? Mark for Review 

(1) Points

True

False (*)

Correct Correct

23. What will happen when the following code is executed?

DECLARE

    e_excep1 EXCEPTION;

    e_excep2 EXCEPTION;

BEGIN

    RAISE e_excep1;

EXCEPTION

    WHEN e_excep1 THEN

         BEGIN

             RAISE e_excep2;

         END;

END;

 Mark for Review 

(1) Points

It will fail to compile because you cannot declare more than one exception in the same block.

It will fail to compile because you cannot have a subblock inside an exception section.

It will fail to compile because e_excep1 is out of scope in the subblock.

It will compile successfully and return an unhandled e_excep2 to the calling environment. (*)

Correct Correct

24. Which of the following EXCEPTION sections is constructed correctly? (Choose three.) Mark for Review 

(1) Points

(Choose all correct answers)

EXCEPTION

    WHEN NO_DATA_FOUND THEN statement_1;

    WHEN OTHERS THEN statement_2;

END;

(*)

EXCEPTION

    WHEN OTHERS THEN statement_1;

    WHEN NO_DATA_FOUND THEN statement_2;

END;

EXCEPTION

    WHEN NO_DATA_FOUND THEN statement_1;

    WHEN NO_DATA_FOUND THEN statement_2;

    WHEN OTHERS THEN statement_3;

END;

EXCEPTION

    WHEN TOO_MANY_ROWS THEN statement_1;

END;

(*)

EXCEPTION

    WHEN OTHERS THEN statement_1;

END;

(*)

Incorrect Incorrect. Refer to Section 7 Lesson 1.

25. Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block? Mark for Review 

(1) Points

A SELECT statement returns more than one row.

None of these.

Any other kind of exception that can occur within the block

A SELECT statement returns no rows.

All of these. (*)

Correct Correct

While a PL/SQL block is executing, more than one exception can occur at the same time. True or False? Mark for Review (1) Points TRUE FALSE (*) Correct Correct 27. No employees are in department_id 99. What output will be displayed when the following code is executed? DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = 99; IF v_count = 0 THEN RAISE NO_DATA_FOUND; DBMS_OUTPUT.PUT_LINE('No employees found'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Department 99 is empty'); END; Mark for Review (1) Points No employees found No employees found Department 99 is empty The block will fail because you cannot explicitly RAISE a predefined Oracle Server error such as NO_DATA_FOUND Department 99 is empty (*) Correct Correct 28. The following three steps must be performed to use a user-defined exception: - Raise the exception - Handle the exception - Declare the exception In what sequence must these steps be performed? Mark for Review (1) Points Raise, Handle, Declare Handle, Raise, Declare Declare, Raise, Handle (*) The steps can be performed in any order. Correct Correct 29. Department-id 99 does not exist. What will be displayed when the following code is executed? DECLARE v_deptname departments.department_name%TYPE; BEGIN SELECT department_name INTO v_deptname FROM departments WHERE department_id = 99; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20201,'Department does not exist'); END; Mark for Review (1) Points ORA-01403: No Data Found ORA-20201: Department does not exist ORA-20201: Department does not exist (*) None of these. ORA-01403: No Data Found Correct Correct Section 8 (Answer all questions in this section) 30. Which of the following keywords MUST be included in every PL/SQL procedure definition? (Choose two.) Mark for Review (1) Points (Choose all correct answers) END (*) REPLACE BEGIN (*) EXCEPTION DECLARE Correct Correct

31. A programmer wants to create a PL/SQL procedure named MY_PROC. What will happen when the following code is executed?

CREATE OR REPLACE PROCEDURE my_proc IS

    v_empid employees.empid%TYPE;

BEGIN

    SELECT employee_id INTO v_empid FROM employees

       WHERE region_id = 999;

    DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary);

 Mark for Review 

(1) Points

The statement will raise a NO_DATA_FOUND exception because region_id 999 does not exist.

The statement will fail because the last line of code should be END my_proc; (*)

The statement will fail because you cannot declare variables such as v_empid inside a procedure.

Correct Correct

32. The following are the steps involved in creating, and later modifying and re-creating, a PL/SQL procedure in Application Express. In what sequence should these steps be performed?

Retrieve the saved code from "Saved SQL" in SQL Commands

Execute the code to create the procedure

Execute the code to re-create the procedure

Click on the "Save" button and save the procedure code

Modify the code in the SQL Commands window

Type the procedure code in the SQL Commands window

 Mark for Review 

(1) Points

F,B,D,A,E,C (*)

E,D,F,C,A,B

F,B,C,D,E,A

F,B,D,E,A,C

F,C,A,B,E,D

Correct Correct

33. You have created procedure MYPROC with a single parameter PARM1 NUMBER. Now you want to add a second parameter to the procedure. Which of the following will change the procedure successfully? Mark for Review 

(1) Points

REPLACE PROCEDURE myproc

(parm1 NUMBER, parm2 NUMBER)

IS

BEGIN ...

CREATE OR REPLACE PROCEDURE myproc

(parm1 NUMBER, parm2 NUMBER)

IS

BEGIN ... (*)

The procedure cannot be modified. Once a procedure has been created, the number of parameters cannot be changed.

ALTER PROCEDURE myproc ADD (parm2 NUMBER);

CREATE OR REPLACE PROCEDURE myproc

(parm1 NUMBER, parm2 NUMBER);

(You do not need to repeat the detailed code of the procedure, only the header)

Correct Correct

34. A procedure will execute faster if it has at least one parameter. Mark for Review 

(1) Points

True

False (*)

Correct Correct

35. Suppose you set up a parameter with an explicit IN mode. What is true about that parameter? Mark for Review 

(1) Points

It must be the same type as the matching OUT parameter.

It must have a DEFAULT value.

It inherits its type from the matching OUT parameter.

It cannot have a DEFAULT value.

It acts like a constant (its value cannot be changed inside the subprogram). (*)

Correct Correct

36. What are the types of parameter modes? Mark for Review 

(1) Points

LOCAL, GLOBAL, BOTH

IN, OUT, IN OUT (*)

CHARACTER, NUMBER, DATE, BOOLEAN

CONSTANT, VARIABLE, DEFAULT

Correct Correct

Section 9

(Answer all questions in this section)

37. The following code shows the dependencies between three procedures:

CREATE PROCEDURE parent

IS BEGIN

    child1;

    child2;

END parent;

You now try to execute:

DROP PROCEDURE child2;

What happens?

 Mark for Review 

(1) Points

You cannot drop CHILD2 because PARENT is dependent on it.

CHILD2 is dropped successfully. PARENT is marked INVALID, but CHILD1 is still valid. (*)

The database automatically drops CHILD1 as well.

The database automatically drops PARENT as well.

CHILD2 is dropped successfully. PARENT and CHILD1 are both marked INVALID.

Correct Correct

38. Which Data Dictionary view can be used to display the detailed code of a procedure in your schema? Mark for Review 

(1) Points

USER_SUBPROGRAMS

USER_OBJECTS

None of these

USER_SOURCE (*)

USER_PROCEDURES

Correct Correct

39. You want to create a function which can be used in a SQL statement. Which one of the following can be coded within your function? Mark for Review 

(1) Points

An OUT parameter

COMMIT;

One or more IN parameters (*)

RETURN BOOLEAN

Correct Correct

40. The following function has been created:

CREATE OR REPLACE FUNCTION upd_dept

    (p_dept_id IN departments.department_id%TYPE)

    RETURN NUMBER IS

BEGIN

    UPDATE departments

    SET department_name = 'Accounting'

       WHERE department_id = p_dept_id;

    RETURN p_dept_id;

END;

Which of the following will execute successfully?

 Mark for Review 

(1) Points

DELETE FROM departments

WHERE department_id = upd_dept(department_id);

SELECT upd_dept(80)

FROM dual;

DELETE FROM employees

WHERE department_id = upd_dept(80);

(*)

SELECT upd_dept(department_id)

FROM employees;

41. Which of the following is a legal location for a function call in a SQL statement? (Choose 3) Mark for Review 

(1) Points

(Choose all correct answers)

VALUES clause of an INSERT statement (*)

WHERE clause in a DELETE statement (*)

CREATE TABLE statement

The ORDER BY and GROUP BY clauses of a query (*)

Correct Correct

42. Why will this function not compile correctly?

CREATE FUNCTION bad_one

IS BEGIN

    RETURN NULL;

END bad_one;

 Mark for Review 

(1) Points

The body of the function must contain at least one executable statement (as well as RETURN).

You must declare the type of the RETURN before the IS. (*)

You must code CREATE OR REPLACE, not CREATE.

You cannot RETURN a NULL.

You must have at least one IN parameter.

Correct Correct

43. Based on the following function definition:

Create function annual_comp

     (sal employees.salary%type,

     comm_pct IN employees.commission%type)

     ...

Which one of the following is an incorrect call for annual_comp?

 Mark for Review 

(1) Points

Execute dbms_output.put_line(annual_comp (1000,.2));

Select employee_id, annual_comp(salary)

from employees; (*)

Declare

     Ann_comp number (6,2);

Begin

     ...

     Ann_comp := annual_comp(1000,.2);

     ...

End;

Select employee_id, annual_comp(salary, commission_pct)

from employees;

Correct Correct

44. Which of the following is found in a function and not a procedure? Mark for Review 

(1) Points

IN parameters

An exception section

Return statement in the header (*)

Local variables in the IS/AS section

Correct Correct

45. You want to see the names of all the columns in a table in your schema. You want to query the Dictionary instead of using the DESCRIBE command. Which Dictionary view should you query? Mark for Review 

(1) Points

USER_TAB_COLS (*)

USER_COLUMNS

USER_TABLES

USER_OBJECTS

Incorrect Incorrect. Refer to Section 9 Lesson 3.

46. A user executes the following statement:

CREATE INDEX fn_index ON employees(first_name);

What output will the following statement now display:

SELECT index_name

FROM user_indexes

WHERE index_name LIKE 'fn%';

 Mark for Review 

(1) Points

No output will be displayed (*)

FN_INDEX

fn_index FN_INDEX

fn_index

Correct Correct

47. How do you specify that you want a procedure MYPROCA to use "Definer's Rights"? Mark for Review 

(1) Points

ALTER PROCEDURE myproca TO DEFINER;

CREATE OR REPLACE PROCEDURE myproca

AUTHID OWNER IS...

GRANT DEFINER TO myprocA;

CREATE OR REPLACE PROCEDURE myproca

AUTHID CURRENT_USER IS...

Definer's Rights are the default, therefore no extra code or commands are needed. (*)

Correct Correct

48. Which statement is true regarding the following subprogram?

PROCEDURE at_proc IS

 PRAGMA AUTONOMOUS_TRANSACTION;

 dept_id NUMBER := 90;

 BEGIN

   UPDATE ...

   INSERT ...

 END at_proc;

 Mark for Review 

(1) Points

The subprogram will fail because the RETURN is not specified.

The subprogram's success depends on the calling program.

The subprogram's success is independent of the calling program. (*)

The subprogram cannot do a COMMIT.

Correct Correct

49. 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

ALTER on EMPLOYEES

SELECT on EMPLOYEES

None of these

INDEX on EMPLOYEES (*)

CREATE on EMPLOYEES

Correct Correct

50. JOE's schema contains a COUNTRIES table. The following commands are executed by JOE and TOM:

       (JOE): GRANT SELECT ON countries TO tom WITH GRANT OPTION;

       (TOM): GRANT SELECT on joe.countries TO dick WITH GRANT OPTION;

Now, JOE executes:

       REVOKE SELECT ON countries FROM tom;

What happens to the grant to DICK?

 Mark for Review 

(1) Points

Nothing. DICK's privilege is preserved even though TOM lost his privilege.

DICK also loses his SELECT privilege. (*)

The REVOKE statement fails because JOE must remove the SELECT privilege from both users at the same time.

The REVOKE statement fails because only the Database Administrator (not JOE) can revoke privileges.

Correct Correct