Diberdayakan oleh Blogger.
RSS

Practice 2

11:10 |

Mata Kuliah  : Pemrograman Basis Data II
Dosen            : Titik Lusiani, M.Kom, OCA

Practice 2
  1. The UPDATE_EMPLOYEE procedure contains an algorithm that calculates an employee's commission multiple times throughout the program. If a change is made to the algorithm, the change must be made multiple times. How can this procedure be modified to simplify the code and reduce duplicated code?
    1. Add an algorithm exception handler.
    2. Create a library containing the algorithm.
    3. Add a local subprogram containing the algorithm.
    4. Create multiple anonymous blocks containing the algorithm.
  2. For which reason might you create a subprogram within a procedure?
    1. to bypass a pragma restriction
    2. to bypass the CURRENT AUTHID restriction
    3. to allow execution within a SQL statement
    4. to store a repeating block of code once without creating a separate construct
  3. When invoking a procedure, you can specify the arguments using the positional method by listing the values in the order of the argument list. Which method would you use to list values in an arbitrary order?
    1. FIFO
    2. list
    3. type
    4. named

  1. Which type of construct should you create to solely perform an action without returning a value?
    1. view
    2. function
    3. procedure
    4. packaged function
  1. Examine this procedure:
               CREATE OR REPLACE PROCEDURE find_seats_sold
               (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS
               v_seats_sold gross_receipt.seats_sold%TYPE;
               BEGIN
               SELECT seats_sold INTO v_seats_sold FROM gross_receipt
               WHERE movie_id = v_movie_id AND theater_id = v_theater_id;
               END;
               Which command will successfully invoke this procedure in SQL*Plus?
    1. EXECUTE find_seats_sold;
    2. RUN find_seats_sold (v_theater_id => 500, v_movie_id => 34);
    3. EXECUTE find_seats_sold (v_theater_id => 500, v_movie_id => 34);
    4. EXECUTE find_seats_sold (v_theater_id := 500, v_movie_id := 34);
  1. Examine this procedure:
                           CREATE OR REPLACE PROCEDURE find_seats_sold
                           (v_movie_id IN NUMBER) IS
                           v_seats_sold gross_receipt.seats_sold%TYPE;
                           BEGIN
                           SELECT seats_sold INTO v_seats_sold
                           FROM gross_receipt
                           WHERE movie_id = v_movie_id;
                           END;
                           Which command will successfully invoke this procedure in SQL*Plus?
    1. RUN find_seats_sold(34);
    2. EXECUTE find_seats_sold;
    3. EXECUTE find_seats_sold (34);
    4. find_seats_sold ('Riverplace Theater');
  1. A stored function can be invoked in many different ways. Which invocation example is NOT valid?
    1. executing the stored function within an INSERT statement
    2. executing the stored function within a server-side function
    3. executing the stored function within a client-side function
    4. executing the stored function within a CHECK constraint of a table

  1. Examine this function:
                                 CREATE OR REPLACE FUNCTION get_budget
                                 (v_studio_id IN NUMBER) RETURN number IS
                                 v_yearly_budget NUMBER;
                                 BEGIN
                                 SELECT yearly_budget INTO v_yearly_budget
                                 FROM studio WHERE id = v_studio_id;
                                 RETURN v_yearly_budget;
                                 END;
                                 Which set of statements will successfully invoke this function within SQL*Plus?
    1. VARIABLE g_yearly_budget NUMBER
                   :g_yearly_budget := GET_BUDGET(11);
B.   VARIABLE g_yearly_budget NUMBER
                   EXECUTE g_yearly_budget := GET_BUDGET(11);
C.   VARIABLE g_yearly_budget NUMBER
                   EXECUTE :g_yearly_budget := GET_BUDGET(11);
D.  VARIABLE :g_yearly_budget NUMBER
                   EXECUTE :g_yearly_budget := GET_BUDGET(11
  1. Examine this function:
                   CREATE OR REPLACE FUNCTION get_budget
                   (v_studio_id IN NUMBER, v_max_budget IN NUMBER) RETURN number IS
                   v_yearly_budget NUMBER;
                   BEGIN
                   SELECT yearly_budget INTO v_yearly_budget FROM studio
                   WHERE id = v_studio_id;
                   IF v_yearly_budget > v_max_budget THEN
                   RETURN v_max_budget;
                   ELSE
                   RETURN v_yearly_budget;
                   END IF;
                   END;
                   Which set of statements will successfully invoke this function within SQL*Plus?
A.    SELECT id, name, get_budget(id,200) FROM studio;
B.     VARIABLE g_yearly_budget NUMBER
                   EXECUTE g_yearly_budget := get_budget(11, 4000000000);
C.     VARIABLE g_yearly_budget NUMBER
                   RUN :g_yearly_budget := get_budget(v_studio_id => 11,v_max_budget => 4000000000);
D.    VARIABLE g_yearly_budget NUMBER
                   EXECUTE g_yearly_budget := get_budget(v_max_budget => 4000000000, v_studio_id => 11);

  1. For which purpose are formal parameters used when creating functions?
A.    restricting pragma references
B.     passing values to the function
C.    bypassing directives to the compiler
D.    prompting the end user for information.
  1. When creating a function in SQL*Plus, you receive an error message stating that the function created with compilation errors. What must you do to see the compilation errors?
A.     Issue the SHOW ERRORS command.
B.     Issue the DISPLAY ERRORS command.
C.     Query the FUNCTION_ERRORS data dictionary view.
D.     Do nothing. The errors will display after a five-second delay.
  1. Examine this function:
                   CREATE OR REPLACE FUNCTION set_budget
                   (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
                   BEGIN
                   UPDATE studio SET yearly_budget = v_new_budget
                   WHERE id = v_studio_id;
                   IF SQL%FOUND THEN RETURN TRUE;
                   ELSE RETURN FALSE;
                   END IF;
                   COMMIT;
                   END;
                   Which code must be added to successfully compile this function?
A.     Add "RETURN;" immediately before the IS keyword.
B.     Add "RETURN NUMBER" immediately before the IS keyword.
C.     Add "RETURN BOOLEAN" immediately after the IS keyword.
D.     Add "RETURN BOOLEAN" immediately before the IS keyword.
13.  Procedures and functions can be created and stored in the database or in an Oracle Developer application. How is performance improved when storing procedures and functions in the database?
A.     Network roundtrips are reduced.
B.      The object code is created during execution.
C.      Network traffic is decreased by bundling commands.
D.      The source code is stored externally, and the object code is stored internally.
  1. Examine this function:
                   CREATE OR REPLACE FUNCTION set_budget
                   (v_studio_id IN NUMBER, v_new_budget IN NUMBER)
                   RETURN BOOLEAN IS
                   BEGIN
                   UPDATE studio
                   SET yearly_budget = v_new_budget
                   WHERE id = v_studio_id;
                   IF SQL%FOUND THEN
                   RETURN TRUE;
                   ELSE RETURN FALSE;
                   END IF;
                   COMMIT;
                   END;
                   Which code will successfully invoke this function?
A.    SELECT id,               set_budget(11,500000000)
                   FROM studio;
B.     DECLARE v_updated_flag BOOLEAN;
                   BEGIN
                   set_budget(11,500000000);
                   END;
C.    VARIABLE g_updated_flag BOOLEAN
                   BEGIN
                   g_updated_flag :=           set_budget(11,500000000);
                   END;
D.     DECLARE v_updated_flag BOOLEAN;
                   BEGIN
                   v_updated_flag :=           set_budget(11,500000000);
                   END;
  1. Which two subprogram headers are correct? (Choose two.)
A.     CREATE OR REPLACE PROCEDURE get_sal IS (v_sal IN number)
B.     CREATE OR REPLACE PROCEDURE get_sal (v_sal IN number) IS
C.     CREATE OR REPLACE FUNCTION calc_comm RETURN number (p_amnt IN number)
D.     CREATE OR REPLACE FUNCTION calc_comm (p_amnt IN number) RETURN number
E.     CREATE OR REPLACE FUNCTION calc_comm (p_amnt IN number(3,2)) RETURN number
  1. Procedures and functions are very similar. For which reason would you choose a function over a procedure?
A.     A function allows Oracle group functions.
B.     A function can be used in a SQL statement.
C.     A function guarantees read consistency, while a procedure cannot.
D.     A function can only be executed from a previously created procedure
  1. The GET_BUDGET function is no longer needed and should be removed. Which command will successfully remove this function from the database?
A.     DROP get_budget;
B.     REMOVE get_budget;
C.     DROP FUNCTION get_budget;
D.     ALTER get_budget DROP FUNCTION;
  1. Which code successfully calculates commission returning it to the calling environment?
A.     CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) RETURN number IS
                   v_total NUMBER;
                   BEGIN
                   SELECT SUM(ord.total) INTO v_total
                   FROM ord,customer
                   WHERE ord.custid = customer.custid
                   AND customer.repid = v_emp_id;
                   END;
B.     CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) IS
                   v_total NUMBER;
                   BEGIN
                   SELECT SUM(ord.total)            INTO v_total
                   FROM ord,customer
                   WHERE ord.custid = customer.custid
                   AND customer.repid = v_emp_id;
                   RETURN (v_total * .20);
                   END;
C.   CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) IS RETURN number
                   v_total NUMBER;
                   BEGIN
                   SELECT SUM(ord.total) INTO v_total
                   FROM ord,customer
                   WHERE ord.custid = customer.custid
                   AND customer.repid = v_emp_id;
                   RETURN (v_total * .20);
                   END;
D.   CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) RETURN number IS
                   v_total NUMBER;
                   BEGIN
                   SELECT SUM(ord.total) INTO v_total
                   FROM ord,customer
                   WHERE ord.custid = customer.custid
                   AND customer.repid = v_emp_id;
                   RETURN (v_total * .20);
                   END;
  1. Examine this function:
                   CREATE OR REPLACE FUNCTION get_budget
                   (v_studio_id IN NUMBER, v_max_budget IN NUMBER) RETURN number IS
                   v_yearly_budget NUMBER;
                   BEGIN
                   SELECT yearly_budget INTO v_yearly_budget FROM studio
                   WHERE id = v_studio_id;
                   IF v_yearly_budget > v_max_budget THEN RETURN v_max_budget;
                   ELSE RETURN v_yearly_budget;
                   END IF;
                   END;
                   Which set of statements will successfully invoke this function within SQL*Plus?
A.     SELECT id, name, get_budget(id,200) FROM studio;
B.     VARIABLE g_yearly_budget NUMBER
                   EXECUTE g_yearly_budget := get_budget(11, 4000000000);
C.    VARIABLE g_yearly_budget NUMBER
                   RUN :g_yearly_budget := get_budget(v_studio_id => 11,v_max_budget => 4000000000);
D.    VARIABLE g_yearly_budget NUMBER
     EXECUTE g_yearly_budget := get_budget(v_max_budget => 4000000000, v_studio_id => 11);
  1. A stored function can be invoked in many different ways. Which invocation example is NOT valid?
    1. executing the stored function within an INSERT statement
    2. executing the stored function within a server-side function
    3. executing the stored function within a client-side function
    4. executing the stored function within the DEFAULT clause of the CREATE TABLE
  2. You have just created a PL/SQL user-defined function called CALC_COMM. Which statement will successfully test it?
    1. EXECUTE calc_comm;
    2. SELECT * FROM calc_comm(emp);
    3. EXECUTE calc_comm(SELECT total FROM ord);
    4. SELECT * FROM ord GROUP BY ordid HAVING calc_comm(total) > 5000;
  3. How do functions simplify maintainability?
    1. by limiting changes to logic to one location
    2. by eliminating the need for an executable section
    3. by eliminating the need for the storage of object code
    4. by ignoring side effects when invoked from within a SQL statement
  4. Which two statements are true? (Choose two.)
    1. A function must return a value.
    2. Functions and procedures must return a value.
    3. Functions and procedures must contain IN arguments.
    4. A function can be invoked from within a PL/SQL expression.
    5. A procedure must be invoked from within a PL/SQL expression.
  1. Examine this statement:
            SELECT id, theater_pck.get_budget(id)
            FROM studio;
            What must be true about the GET_BUDGET function for this statement to be successful?
    1. It must be remote.
    2. It must not modify the database.
    3. It must also exist as a stand-alone function.
    4. It must only exist in the body of THEATER_PCK.
  1. Examine this function:
            CREATE OR REPLACE FUNCTION get_budget
            RETURN number IS
            v_yearly_budget NUMBER;
            BEGIN
            SELECT yearly_budget INTO v_yearly_budget
            FROM studio WHERE id = v_studio_id;
            RETURN v_yearly_budget;
            END;
            What additional code is needed to compile this function successfully?
    1. Remove the first RETURN statement.
    2. Change "RETURN number" to "OUTPUT number".
    3. Add "(v_studio_id IN NUMBER)" right after the IS keyword.
    4. Add "(v_studio_id IN NUMBER)" right before the RETURN statement of the header.
26.  Which subprogram type can be invoked from within a SQL statement?
A.    function
    1. procedure
    2. public packaged procedure
    3. private packaged procedure
    4. private packaged function
  1. Examine this function:
            CREATE OR REPLACE FUNCTION get_budget
            (v_studio_id IN NUMBER) RETURN number IS
            v_yearly_budget NUMBER;
            BEGIN
            SELECT yearly_budget INTO v_yearly_budget
            FROM studio WHERE id = v_studio_id;
            END;
            To execute this function successfully, what additional code must be added to the executable section?
    1. RETURN;
    2. RETURN get_budget;
    3. OUT v_yearly_budget;
    4. RETURN v_yearly_budget;
28.  While creating a package, you placed the function name in the specification and the body. Which type of construct have you created?
A.    public
    1. illegal
    2. private
    3. one-time only
  1. Examine this code:
            CREATE OR REPLACE PACKAGE prod_pack IS
            g_tax_rate NUMBER := .08;
            END prod_pack;
            Which statement about this code is true?
    1. This package specification can exist without a body.
    2. This package body can exist without a specification.
    3. This package body cannot exist without a specification.
    4. This package specification cannot exist without a body.
  1. Examine this package specification:
            CREATE OR REPLACE PACKAGE theater_package IS
            PROCEDURE find_cpt
            (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);
            PROCEDURE update_theater (v_name IN VARCHAR2);
            PROCEDURE find_seats_sold
            (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER);
            PROCEDURE add_theater;
            END theater_package;
            Which statement about the procedures in this specification is true?
    1. They are public procedures.
    2. They are private procedures.
    3. Each procedure is missing code and, therefore, is illegal.
    4. Each procedure contains an argument list and, therefore, is illegal.

  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • RSS
Read User's Comments(0)
Diposting oleh Fury