MATAKULIAH : PBD 2
NAMA DOSEN :Titik Lusiani, M.Kom, OCA
BLOG :DOSEN :http://blog.stikom.edu/lusiani
1. 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 mengefisienkan 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 programnya
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 adalah jawaban C
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
Tidak ada komentar:
Posting Komentar