Tugas PBD2 Bu Titik Lusiani

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?

Jawab  : C. Add a local subprogram containing the algorithm.
Alasan : Menambahkan subprogram agar memudahkan dan tidak membuat ulang programnya.

2. For which reason might you create a subprogram within a procedure?

Jawab : D. to store a repeating block of code once without creating a separate construct
Alasan : Untuk membuat suatu program hanya sekali tanpa harus membuat ulang

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?

Jawab : D. Named
Alasan : Suatu method yang digunakan untuk mengisi nilai yang dapat dijadikan  perintah yang diubah-ubah

4. Which type of construct should you create to solely perform an action without returning a value?

Jawab : C. procedure
Alasan : Karena procedure tidak mengembalikan nilai

5. 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?
Jawab : C. EXECUTE find_seats_sold (v_theater_id => 500, v_movie_id => 34);
Alasan : Untuk menjalankan digunakan perintah execute jawaban a tidak sesuai karena tidak ada parameter b syntax RUN bukan perintah menjalankan program dan d kurang sesuai karena berupa inputan jawaban c paling sesuai menurut syntax

6. 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?

Jawab : C. EXECUTE find_seats_sold (34);
Alasan : Syntax yang paling sesuai dengan aturan sql * plus

7. A stored function can be invoked in many different ways. Which invocation example is NOT valid?

Jawab : D. executing the stored function within a CHECK constraint of a table
Alasan : Karena constraint check hanya berupa kondisi pengecekan

8. 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?

Jawab : C. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
Alasan : Jawaban C paling sesuai dengan variabel tanpa tanda :/input parameter

9. 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?

Jawab : A. SELECT id, name, get_budget(id,200) FROM studio;
Alasan : perintah untuk menampilkan suatu function adalah dengan syntax select

10. For which purpose are formal parameters used when creating functions?

Jawab : B. passing values to the function
Alasan : Function dibuat ketika digunakan untuk mengisi suatu nilai

11. 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?

Jawab : A. Issue the SHOW ERRORS command.
Alasan : Perintah show errors digunakan untuk menampilkan error

12. 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?

Jawab : D. Add "RETURN BOOLEAN" immediately before the IS keyword.
Alasan : untuk menambahkan function adalah dengan menuliskan add “RETURN BOOLEAN” sebelum IS

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?

Jawab : C. Network traffic is decreased by bundling commands.
Alasan : untuk meningkatkan performance ketika penyimpanan prosedur dan function dalam database adalah dengan menurunkan lalulintas jaringan.

14. 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?
Jawab : D. DECLARE v_updated_flag BOOLEAN;
BEGIN
v_updated_flag := set_budget(11,500000000);
END;
Alasan : untuk menjalankan perintah function yang benar adalah dengan memberi deklarasi kemudian nama function type data true/false boolean, kemudian mengisi nama parameter dan function dengan 11,500000000.

15. Which two subprogram headers are correct? (Choose two.)

Jawab : B. CREATE OR REPLACE PROCEDURE get_sal (v_sal IN number) IS
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
Alasan : jawaban a salah karena salah penempatan is, c salah karena function akan mengembalikan type data bukan parameter, jawaban d seharusnya hanya ada parameter saja.

16. Procedures and functions are very similar. For which reason would you choose a function over a procedure?

Jawab : B. A function can be used in a SQL statement.
Alasan : karena function dapat digunakan dalam perintah SQL

17. The GET_BUDGET function is no longer needed and should be removed. Which command will successfully remove this function from the database?

Jawab : C. DROP FUNCTION get_budget;
Alasan : untuk menghapus function get_budget maka digunakan drop function

18. Which code successfully calculates commission returning it to the calling environment?

Jawab : 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;
Alasan : Jawaban A dan B salah karena tidak mengembalikan nilai sedangkan C syntax tidak lengkap

19. 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?
Jawab : A. SELECT id, name, get_budget(id,200) FROM studio;
Alasan : untuk memanggil function digunakan select kemudian nama function

20. A stored function can be invoked in many different ways. Which invocation example is NOT valid?
Jawab : D. executing the stored function within the DEFAULT clause of the CREATE TABLE
Alasan : Karena function tidak dapat membuat tabel

21. You have just created a PL/SQL user-defined function called CALC_COMM. Which statement will successfully test it?
Jawab : D. SELECT * FROM ord GROUP BY ordid HAVING calc_comm(total) > 5000;
Alasan : Memanggil function dengan kondisi having....function

22. How do functions simplify maintainability?
Jawab : A. by limiting changes to logic to one location
Alasan : memperbaiki satu lokasi function untuk pemanggilan satu function

23. Which two statements are true? (Choose two.)

Jawab : A. function must return a value.
D. A function can be invoked from within a PL/SQL expression.
Alasan : karena pernyataan yang benar adalah function pasti mengembalikan nilai dan function dapat digunakan dalam PL/SQL

24. 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?
Jawab : B. It must not modify the database.
Alasan : tidak bisa memodifikasi database karena select untuk menampilkan






25. 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?
Jawab : D. Add "(v_studio_id IN NUMBER)" right before the RETURN statement of the header.
Alasan : Karena yang dibutuhkan adalah Menambah variable sebelum return
26. Which subprogram type can be invoked from within a SQL statement?
Jawab : A. Function
Alasan : subprogram dapat digunakan dalam SQL dengan function

27. 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?
Jawab : D. RETURN v_yearly_budget;
Alasan : Untuk eksekusi function agar sukses ditambahkan 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?
Jawab : A. Public
Alasan : agar function dapat dibaca seluruh package maka digunakan public






29. 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?

Jawab : A.This package specification can exist without a body.
Alasan : karena code ini dapat berjalan tanpa body

30. 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?

Jawab : A. They are public procedures.
Alasan : Karena syntax diatas termasuk dalam public procedures