Some questions about database

Examine this package:

CREATE OR REPLACE PACKAGE prod_pack

IS

CURSOR c1 is

SELECT *

FROM product;

PROCEDURE order_product

(p1 IN NUMBER, p2 IN NUMBER);

END prod_pack;

CREATE OR REPLACE PACKAGE BODY prod_pack

IS

CURSOR c2 IS

SELECT *

FROM ord;

PROCEDURE order_product

(p1 IN NUMBER, p2 IN NUMBER)

IS

BEGIN

OPEN c2;

...

END;

END prod_pack;

You execute these commands in SQL*Plus:

EXECUTE OPEN prod_pack.c1;

EXECUTE prod_pack.order_product(100,1);

Which cursor or cursors will be opened for the duration of the session, unless it is explicitly closed?

	a.	C1 only
	b.	C2 only
	c.	both C1 and C2
	d.	neither C1 nor C2

Which three statements about restrictions on package functions used in SQL are true? (Choose three.)

	a.	A function called from a DML statement can read or modify the particular table being modified by the DML statement.
	b.	A function called from a DML statement cannot read or modify the particular table being modified by the DML statement.
	c.	A function called from a query statement or from a parallelized DML statement can execute a DML statement or modify the database.
	d.	A function called from a query statement or from a parallelized DML statement cannot execute a DML statement or modify the database.
	e.	A function called from a query or DML statement can end the current transaction, create or roll back a savepoint, or alter the system or session.
	f.	A function called from a query or DML statement cannot end the current transaction, create or roll back a savepoint, or alter the system or sessio

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?

	a.	FIFO
	b.	list
	c.	type
	d.	named

The organization for which the database is designed is referred to as

	A.	the enterprise
	B.	the entity
	C.	the universe of discourse
	D.	the metadata

Examine the structures of the PRODUCT and SUPPLIER tables:

PRODUCT ----------------------------------- PRODUCT_ID NUMBER PRODUCT_NAME VARCHAR2(25) SUPPLIER_ID NUMBER CATEGORY_ID NUMBER QTY_PER_UNIT NUMBER UNIT_PRICE NUMBER(7,2) QTY_IN_STOCK NUMBER QTY_ON_ORDER NUMBER REORDER_LEVEL NUMBER 

SUPPLIER ------------------------------------ SUPPLIER_ID NUMBER SUPPLIER_NAME VARCHAR2(25) ADDRESS VARCHAR2(30) CITY VARCHAR2(25) REGION VARCHAR2(10) POSTAL_CODE VARCHAR2(11)

You want to create a query that will return an alphabetical list of products including the name of each product's supplier. Only products in the PRODUCT table that have a supplier assigned should be included in your report.

Which two queries could you use? (Choose two. Each correct answer is a separate solution.)

		SELECT p.product_name, s.supplier_name FROM product p LEFT OUTER JOIN supplier s ON p.supplier_id = s.supplier_id ORDER BY p.product_name;
		SELECT p.product_name, s.supplier_name FROM product p JOIN supplier s ON (supplier_id) ORDER BY p.product_name;
		SELECT product_name, supplier_name FROM product NATURAL JOIN supplier ORDER BY product_name;
		SELECT p.product_name, s.supplier_name FROM product p JOIN supplier s USING (p.supplier_id) ORDER BY p.product_name;
		SELECT product_name, supplier_name FROM product JOIN supplier USING (supplier_id) ORDER BY product_name;

Examine the structures of the PLAYER and TEAM tables:

PLAYER ------------- PLAYER_ID NUMBER(9) PK LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) TEAM_ID NUMBER MANAGER_ID NUMBER(9) 

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

For this example, team managers are also players, and the MANAGER_ID column references the PLAYER_ID column. For players who are managers, MANAGER_ID is NULL.

Which SELECT statement will provide a list of all players, including the player's name, the team name, and the player's manager's name?

		SELECT p.last_name, p.first_name, p.manager_id, t.team_name FROM player p NATURAL JOIN team t;
		SELECT p.last_name, p.first_name, p.manager_id, t.team_name FROM player p JOIN team t USING (team_id);
		SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p LEFT OUTER JOIN player m ON (p.manager_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
		SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p JOIN player m ON (p.manager_id = m.player_id) RIGHT OUTER JOIN team t ON (p.team_id = t.team_id);
		SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p LEFT OUTER JOIN player m ON (p.player_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

A stored function uses the ____ statement to return a value.

	a.	PROCESS
	b.	EXIT
	c.	RETURN
	d.	END

Procedure A, which is a local procedure in your database, references procedure B. Procedure B is located on a

remote server, and the time dependency mode has been set to TIMESTAMP . After performing a dependency

check, the Oracle server detects a mismatch between the timestamps of the A and B procedures and identifies

that procedure B was recompiled after procedure A.

Which three statements are true about the two procedures? (Choose three.

	a.	Procedure A will remain valid.
		b.Procedure A will become invalid.
		c. Procedure B will become invalid.
		d.Procedure B need not be recompiled.
	e.	You must recompile procedure A.
	f.	Procedure A will be successfully recompiled when it is called the second time.