Resume PBD II

Mata Kuliah : Pemrograman Basis Data II
Dosen : Titik Lusiani


PRACTICE I

1. (A) USER_SOURCE

2. (D) PROCEDURE, FUNCTION, dan PACKAGES

3. (D) USER_DEPENDENCIES

4. (C) USER_DEPENDENCIES

5. (C) USER_OBJECTS

6. (A) SELECT text FROM user_source WHERE name = 'THEATER_PCK';

7. (C) USER_OBJECTS

8. (C) USER_OBJECTS 

9. (C) REVOKE UPDATE ON employee FROM hr_emp;

    (E) GRANT EXECUTE ON update_employee TO hr_emp;

10. (C) A user needs only the privilege to execute the procedure and does not need privileges on the underlying tables.

11. (A) GRANT EXECUTE ON update_theater TO jsmith;

12. (D) Only the database administrator can recover this procedure using backups.

13. (D) Declare a new exception and associate it with error code -2292. Create an exception section, and add code to handle this non-predefined exception that you just declared.

14. (D) Use (v_total IN sales_order.total%TYPE DEFAULT 0) as the parameter definition.

15. (C) Add (v_name IN VARCHAR2) immediately before the IS keyword.

16. (D) CREATE OR REPLACE PROCEDURE

17. (A) IN

18. (D) Formal arguments allow you to transfer values to and from the calling environment.

19. (A) The statement compiles, and the procedure is created.

20. (D) When an exception is raised in a called procedure, control goes to the exception section of that block.

21. (D) IN OUT parameters cannot be initialized with a default value.

22. (E) The declaration of V_COST_PER_TICKET cannot have a DEFAULT value.

23. (B) An IN OUT formal parameter does not require a value before returning to the calling environment.

24. (C) EXECUTE find_seats_sold (v_theater_id => 500);

25. (C) source code and compilation errors

26. (A) Declare V_SEATS_SOLD as an OUT argument.

27. (A) Only data type is required.

28. (D) IN OUT

29. (C) DROP PROCEDURE calc_comm;

30. (A) The ADD_THEATER procedure is written in SQL.

      (B) The ADD_THEATER procedure can be shared by multiple programs.

      (C) The ADD_THEATER procedure will be stored in the database as a schema object.

  


PRACTICE II

1. (C) Add a local subprogram containing the algorithm.

2. (D) To store a repeating block of code once without creating a separate construct

3. (D) Named
4. (C) Procedure
5. (C) EXECUTE find_seats_sold (v_theater_id => 500, v_movie_id => 34);
6. (C) EXECUTE find_seats_sold (34);
7. (D) executing the stored function within a CHECK constraint of a table
8. (C) VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
9. (A) SELECT id, name, get_budget(id,200) FROM studio;
10. (B) passing values to the function
11. (A) Issue the SHOW ERRORS command
12. (D) Add "RETURN BOOLEAN" immediately before the IS keyword
13. (C) Network traffic is decreased by bundling commands
14. (D) DECLARE v_updated_flag BOOLEAN;
15. (B) CREATE OR REPLACE PROCEDURE get_sal (v_sal IN number) IS
      (D) CREATE OR REPLACE FUNCTION calc_comm (p_amnt IN number) RETURN number
16. (B) A function can be used in a SQL statement
17. (C) DROP FUNCTION get_budget;
18. (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;

19. (A) SELECT id, name, get_budget(id,200) FROM studio;
20. (D) Executing the stored function within the DEFAULT clause of the CREATE

TABLE

21. (D) SELECT * FROM ord GROUP BY ordid HAVING calc_comm(total) >

5000

22. (A) By limiting changes to logic to one location
23. (A) A function must return a value

      (D) A function can be invoked from within a PL/SQL expression.

24. (B) It must not modify the database
25. (D) Add "(v_studio_id IN NUMBER)" right before the RETURN statement of

the header

26. (A) Function
27. (D) RETURN v_yearly_budget;
28. (A) Public
29. (A) This package specification can exist without a body
30. (A) They are public procedures