some questions about sql

question 40

1. Evaluate the SELECT statements in the following SQL compound queries:

SELECT emp_id

FROM emp

INTERSECT

SELECT emp_id FROM emp_hist;

SELECT emp_id FROM emp_hist INTERSECT SELECT emp_id FROM emp;

Which statement is TRUE regarding these SQL compound queries?

The results of the compound queries will be identical.

The first compound query will return more results than the second.

The second compound query will return more results than the first.

The second compound query will return a syntax error.

0.5 points

Question 45

1.

You have created the following function:

CREATE OR REPLACE FUNCTION get_salary

(v_employee_id NUMBER)

RETURN NUMBER

IS

v_salary NUMBER;

BEGIN

SELECT salary

INTO v_salary

FROM employees

WHERE employee_id = v_emp_id;

RETURN v_salary;

END get_salary;

Which two statements will successfully invoke the get_salary function from the SQL*Plus environment?

(Choose two.)

	a.	get_salary(205);
	b.	EXECUTE get_salary(200);
	c.	SELECT get_salary(150) from emp;
	d.	SELECT get_salary(205) from dual;
	e.	UPDATE emp SET SALARY = get_salary(200) WHERE employee_id = 250;

Question 48

1.

Examine this procedure:

CREATE OR REPLACE PROCEDURE find_cpt

(v_movie_id {argument mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)

IS

BEGIN

IF v_cost_per_ticket > 9.50 THEN

SELECT cost_per_ticket

INTO v_cost_per_ticket

FROM gross_receipt

WHERE movie_id = v_movie_id;

END IF;

END;

Which argument mode should be used for v_movie_id ?

	a.	IN
	b.	OUT
	c.	IN OUT
	d.	IN RETURN

Question 51

1.

Examine this function:

CREATE OR REPLACE FUNCTION set_budget

(v_studio_id IN NUMBER, v_new_budget IN NUMBER)

RETURN NUMBER

IS

BEGIN

UPDATE studio

SET yearly_budget = v_new_budget

WHERE id = v_studio_id;

COMMIT;

RETURN SQL%ROWCOUNT;

END;

While executing this in SQL*Plus, you want to see the value of SQL%ROWCOUNT displayed on the screen.

Which line of code will accomplish this?

	a.	OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
	b.	DBMS_DEBUG.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
	c.	DBMS_OUTPUT.DISPLAY(TO_CHAR(SQL%ROWCOUNT));
	d.	DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));

Question 58

1. The ACCOUNT table contains these columns:

ACCOUNT_ID NUMBER(12) NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2)

You must create statements to be mailed to all account holders. Each customer's statement must include the account holder's previous balance and finance charge in this format:

Previous Balance: 5000 Finance Charge: 45

Which SELECT statement will produce these results?

		SELECT Previous Balance: ||prev_balance|| Finance Charge: ||prev_balance * .009 FROM account;
		SELECT 'Previous Balance:' ||prev_balance|| 'Finance Charge:' ||prev_balance * .009 FROM account;
		SELECT 'Previous Balance: '||prev_balance||' Finance Charge: '||prev_balance * .009 FROM account;
		SELECT "Previous Balance: "||prev_balance||" Finance Charge: "||prev_balance * .009 FROM account;

Question 60

1.

Examine the following code:

CREATE OR REPLACE PACKAGE BODY update_emp

AS

FUNCTION show_sal(emp_id)

RETURN NUMBER

IS

v_salary NUMBER;

inc_sal NUMBER;

BEGIN

SELECT salary INTO v_salary FROM emp

WHERE employee_id = emp_id;

RETURN (v_salary);

END show_sal;

BEGIN

inc_sal := 5000;

END update_emp;

In the given code, when will the inc_sal variable be initialized?

	a.	every time the update_emp package is invoked in a session
	b.	when the package body of the update_emp package is compiled
	c.	when the update_emp package is invoked for the first time in a session
	d.	every time you invoke the show_sal function contained in the update_emp package