Some questions about database

Question 48

1.

When a local procedure that references a remote procedure is compiled, what is recorded in the p-code or object code?

	a.	both timestamps of the local and remote procedures
	b.	the timestamp of the local procedure compilation only
	c.	the timestamp of the remote procedure compilation only
	d.	no timestamps, unless a reference is made to PRAGMA RESTRICT_REFERENCES

0.5 points

Question 49

1. Code example 6-2 WITH invoice_averages AS (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100 ORDER BY average_invoice DESC) SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN invoice_averages ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC

(Please refer to code example 6-2.) When this query is executed, the result table will contain one row for

	a.	each invoice in the invoice_averages table
	b.	each vendor
	c.	each invoice
	d.	each vendor in the invoice_averages table

Question 50

1. Making adjustments to physical database structures in order to improve performance is called system

	A.	prototyping
	B.	evolution
	C.	analysis
	D.	tuning

0.5 points

Question 51

1.

Examine this package:

CREATE OR REPLACE PACKAGE theater_pck

IS

current_avg_cost_per_ticket NUMBER;

PROCEDURE find_seats_sold

(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER);

FUNCTION get_budget

(v_studio_id IN NUMBER)

RETURN NUMBER;

END theater_pck;

You issue this statement in SQL*Plus:

EXECUTE theater_pck.current_avg_cost_per_ticket := 1

What is true about the state of current_avg_cost_per_ticket ?

	a.	It is set to 10 for all user sessions.
	b.	It is set to 0 due to an illegal reference.
	c.	It is set to 10 for the duration of the statement.
	d.	It is set to 10 in the current session, but potentially can be modified again by that same session

Question 55

1. Examine the structures of the PLAYER and TEAM tables:

PLAYER ------------- PLAYER_ID NUMBER PK LAST_NAME VARCHAR2(30) FIRST_NAME VARCHAR2(25) TEAM_ID NUMBER MGR_ID NUMBER SIGNING_BONUS NUMBER(9,2)

TEAM --------- TEAM_ID NUMBER TEAM_NAME VARCHAR2(30)

Which situation would require a subquery to return the desired result?

		a list of all players who are also managers
		a list of all teams that have more than 11 players
		a list of all players, including their signing bonus amounts and their manager names
		a list of all players who have a larger signing bonus than their manager
		a list of all players who received a signing bonus that was lower than the average bonus