SQL Questions And Answers

121⟩ If a view on a single base table is manipulated will the changes be reflected on the base table?

Changes on the view will be reflected on the base table under certain conditions.

But if changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

207 views

122⟩ What happens when rows are found using a FETCH statement?

1. It causes the cursor to close

2. It causes the cursor to open

3. It loads the current row values into variables

4. It creates the variables to hold the current row values

Is this answer helpful? 0 Yes | 0 No

Answer This Question

195 views

123⟩ Read the following code and explain

Read the following code:

22. CREATE OR REPLACE TRIGGER update_show_gross

23. {trigger information}

24. BEGIN

25. {additional code}

26. END;

The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?

1. WHEN (new.cost_per_ticket > 3.75)

2. WHEN (:new.cost_per_ticket > 3.75

3. WHERE (new.cost_per_ticket > 3.75)

4. WHERE (:new.cost_per_ticket > 3.75)

Is this answer helpful? 0 Yes | 0 No

Answer This Question

201 views

124⟩ Examine this below sql code

71. BEGIN

72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;

73. END;

For this code to be successful, what must be true?

1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.

2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.

3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.

4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

200 views

125⟩ Read the following code 2

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?

1. VARIABLE g_yearly_budget NUMBER

EXECUTE g_yearly_budget := GET_BUDGET(11);

2. VARIABLE g_yearly_budget NUMBER

EXECUTE :g_yearly_budget := GET_BUDGET(11);

3. VARIABLE :g_yearly_budget NUMBER

EXECUTE :g_yearly_budget := GET_BUDGET(11);

4. VARIABLE g_yearly_budget NUMBER

31. CREATE OR REPLACE PROCEDURE update_theater

32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS

33. BEGIN

34. UPDATE theater

35. SET name = v_name

36. WHERE id = v_theater_id;

37. END update_theater;

Is this answer helpful? 0 Yes | 0 No

Answer This Question

195 views

126⟩ How to Select last N records from a Table?

select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)

where a > ( select (max(rownum)-10) from clm)

Here N = 10

The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.

Cursor rem_master(brepno VARCHAR2) IS

select a.* from ter.ter_master a

where NOT a.repno in (select repno from ermast) and

(brepno = 'ALL' or a.repno > brepno)

Order by a.repno

What are steps required tuning this query to improve its performance?

-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO

-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:

SELECT a.*

FROM ter.ter_master a

WHERE NOT EXISTS (SELECT b.repno FROM ermast b

WHERE a.repno=b.repno) AND

(a.brepno = 'ALL' or a.repno > a.brepno)

ORDER BY a.repno;

Is this answer helpful? 0 Yes | 0 No

Answer This Question

183 views

127⟩ Which procedure can be used to create a customized error message?

1. RAISE_ERROR

2. SQLERRM

3. RAISE_APPLICATION_ERROR

4. RAISE_SERVER_ERROR

Is this answer helpful? 0 Yes | 0 No

Answer This Question

187 views

128⟩ Examine this database trigger?

52. CREATE OR REPLACE TRIGGER prevent_gross_modification

53. {additional trigger information}

54. BEGIN

55. IF TO_CHAR(sysdate, DY) = MON

56. THEN

57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);

58. END IF;

59. END;

This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?

1. BEFORE DELETE ON gross_receipt

2. AFTER DELETE ON gross_receipt

3. BEFORE (gross_receipt DELETE)

4. FOR EACH ROW DELETED FROM gross_receipt

Is this answer helpful? 0 Yes | 0 No

Answer This Question

183 views

129⟩ What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?

1. Only one

2. All that apply

3. All referenced

4. None

Is this answer helpful? 0 Yes | 0 No

Answer This Question

216 views

130⟩ What occurs if a procedure or function terminates with failure without being handled?

1. Any DML statements issued by the construct are still pending and can be committed or rolled back.

2. Any DML statements issued by the construct are committed

3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.

4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.

Is this answer helpful? 0 Yes | 1 No

Answer This Question

271 views

131⟩ Read the following code 3?

Read the following code

40. CREATE OR REPLACE PROCEDURE calculate_budget IS

41. v_budget studio.yearly_budget%TYPE;

42. BEGIN

43. v_budget := get_budget(11);

44. IF v_budget < 30000

45. THEN

46. set_budget(11,30000000);

47. END IF;

48. END;

You are about to add an argument to CALCULATE_BUDGET. What effect will this have?

1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.

2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.

3. Only the CALCULATE_BUDGET procedure needs to be recompiled.

4. All three procedures are marked invalid and must be recompiled.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

175 views

132⟩ What is a database link?

Database link is a named path through which a remote database can be accessed.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

195 views

133⟩ Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?

1. When the transaction is committed

2. During the data manipulation statement

3. When an Oracle supplied package references the trigger

4. During a data manipulation statement and when the transaction is committed

Is this answer helpful? 1 Yes | 0 No

Answer This Question

223 views

134⟩ Which line in the following statement will produce an error?

1. cursor action_cursor is

2. select name, rate, action

3. into action_record

4. from action_table;

5. There are no errors in this statement.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

189 views

135⟩ Which three of the following are implicit cursor attributes?

1. %found

2. %too_many_rows

3. %notfound

4. %rowcount

5. %rowtype

Is this answer helpful? 0 Yes | 0 No

Answer This Question

199 views

136⟩ What does preemptive in preemptive multitasking mean?

Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

192 views

137⟩ What does one do when one is rightsizing?

With rightsizing, one would move applications to the most appropriate server platforms.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

206 views

138⟩ Why is the most of the processing done at the sever?

To reduce the network traffic and for application sharing and implementing business rules.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

196 views

139⟩ What is the most important requirement for OLTP?

OLTP requires real time response.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

178 views

140⟩ What is sql event trigger?

An event trigger, a segment of code which is associated with each event and is fired when the event occurs.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

185 views

SQL

Home Databases Programming SQL

172 SQL Questions And Answers

121⟩ If a view on a single base table is manipulated will the changes be reflected on the base table?

122⟩ What happens when rows are found using a FETCH statement?

123⟩ Read the following code and explain

124⟩ Examine this below sql code

125⟩ Read the following code 2

126⟩ How to Select last N records from a Table?

127⟩ Which procedure can be used to create a customized error message?

128⟩ Examine this database trigger?

129⟩ What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?

130⟩ What occurs if a procedure or function terminates with failure without being handled?

131⟩ Read the following code 3?

132⟩ What is a database link?

133⟩ Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?

134⟩ Which line in the following statement will produce an error?

135⟩ Which three of the following are implicit cursor attributes?

136⟩ What does preemptive in preemptive multitasking mean?

137⟩ What does one do when one is rightsizing?

138⟩ Why is the most of the processing done at the sever?

139⟩ What is the most important requirement for OLTP?

140⟩ What is sql event trigger?

Quick Links:

SQL

Home Databases Programming SQL

172 SQL Questions And Answers

121⟩ If a view on a single base table is manipulated will the changes be reflected on the base table?

122⟩ What happens when rows are found using a FETCH statement?

123⟩ Read the following code and explain

124⟩ Examine this below sql code

125⟩ Read the following code 2

126⟩ How to Select last N records from a Table?

127⟩ Which procedure can be used to create a customized error message?

128⟩ Examine this database trigger?

129⟩ What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?

130⟩ What occurs if a procedure or function terminates with failure without being handled?

131⟩ Read the following code 3?

132⟩ What is a database link?

133⟩ Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?

134⟩ Which line in the following statement will produce an error?

135⟩ Which three of the following are implicit cursor attributes?

136⟩ What does preemptive in preemptive multitasking mean?

137⟩ What does one do when one is rightsizing?

138⟩ Why is the most of the processing done at the sever?

139⟩ What is the most important requirement for OLTP?

140⟩ What is sql event trigger?

BE THE FIRST TO KNOW

Quick Links: