This blog post on Oracle 11g PL/SQL Basic Block Structures Practice Multiple Choice Questions for Interview will help entry level Database programmers to answer most common Oracle 11g PL/SQL Questions

Oracle 11g PL/SQL Basic Block Structures MULTIPLE CHOICE

1. The only required sections of a PL/SQL block are the ____ sections.

a. BEGIN & DECLARE

b. DECLARE & EXCEPTION

c. BEGIN & END

d. EXCEPTION & END

ANS: C 
2. The ____ section of a PL/SQL block contains code that creates variables, cursors, and types.

a. DECLARE

b. BEGIN

c. EXCEPTION

d. END

ANS: A 
3. ____ are used to change the values of variables.

a. Loops

b. Assignment statements

c. Exceptions

d. Blocks

ANS: B
4. The ____ section of a PL/SQL block contains handlers that allow you to control what the application will do if an error occurs when the executable statements are processed.

a. EXCEPTION

b. BEGIN

c. DECLARE

d. END

ANS: A 
5. ____ are named memory areas that hold values to allow the retrieval and manipulation of values in a program.

a. Loops

b. Assignment statements

c. Blocks

d. Variables

ANS: D 


6. Which of the following lines of code is syntactically correct?

a. DECLARE
order NUMBER;
departure DATE;
BEGIN
—- executable statements —
END

b.  DECLARE
order NUMBER;
departure DATE
BEGIN
—- executable statements —
END

 

c. DECLARE
order NUMBER(2);
departure DATE;
BEGIN;
—- executable statements —
END

d. DECLARE
order NUMBER(3);
departure DATE;
BEGIN
—- executable statements —
END

ANS: D 


7. Which of the following initializes the variable order?

a. DECLARE
order NUMBER(2);
departure DATE;
BEGIN
—- executable statements —
END;

b. DECLARE
order NUMBER(2) = 0;
departure DATE;
BEGIN
—- executable statements —
END;

c. DECLARE
order NUMBER(2) =: 0;
departure DATE;
BEGIN
—- executable statements —
END;

d. DECLARE
order NUMBER(2) := 0;
departure DATE;
BEGIN
—- executable statements —
END;

ANS: D


8. Which of the following PL/SQL blocks requires the variable to always contain a particular value within the block?

a.  DECLARE
order NUMBER(2) := 0;
departure DATE;
BEGIN
—- executable statements —
END;

b. DECLARE
order NUMBER(2,2) := .06;
departure DATE;
BEGIN
—- executable statements —
END;

c. DECLARE
order CONSTANT NUMBER(2,2) := .02;
departure DATE;
BEGIN
—- executable statements —
END;

d. DECLARE
order NUMBER(2) CONSTANT := .03;
departure DATE;
BEGIN
—- executable statements —
END;

ANS: C


9. DECLARE
order NUMBER(2) := 4;
total_amt NUMBER(2);
BEGIN
total_amt := order * 8;
END;

According to the statement block above, what value is stored in the variable total_amt?

a. 4

b. 8

c. 12

d. 32

ANS: D


10. Which of the following statement blocks correctly uses a scalar variable in an assignment statement?

a. DECLARE
order NUMBER(2) := 4;
total_amt NUMBER(2);
BEGIN
total_amt = 12;
END;

b. DECLARE
order NUMBER(2) := 4;
total_amt NUMBER(2);
BEGIN
total_amt := 12 * order;
END;

c. DECLARE
order NUMBER(2) := 4;
total_amt NUMBER(2);
BEGIN
order := total_amt *12;
END;

d. DECLARE
order NUMBER(2) := 4;
total_amt NUMBER(2);
BEGIN
total_amt := total_amt *12;
END;

ANS: B


11. The statements that are used to control the flow of logic processing in your programs are commonly referred to as ____.

a. exceptions

b. control structures

c. pragma statements

d. index-by tables

ANS: B


12. Which of the following statements is correct?

a. IF order > 5
prize = ‘yes’;
END IF;

b. IF order > 5 THEN
prize = ‘yes’;
ENDIF

c. IF order > 5 THEN;
prize = ‘yes’;
END IF;

d. IF order > 5 THEN
prize := ‘yes’;
END IF;

ANS: D


13. Which of the following code fragments would not raise an error?

a. IF rec_order.state = ’VA’ THEN
lv_tax_num := rec_order.sub * .06;
ELSEIF rec_order.state = ’ME’ THEN
lv_tax_num := rec_order.sub * .05;
ELSE
lv_tax_num := rec_order.sub * .04;
END IF;

b. IF rec_order.state = ’VA’ THEN
lv_tax_num := rec_order.sub * .06;
ELSE IF rec_order.state = ’ME’ THEN
lv_tax_num := rec_order.sub * .05;
ELSE
lv_tax_num := rec_order.sub * .04;
END IF;

c. IF rec_order.state = ’VA’ THEN
lv_tax_num := rec_order.sub * .06;
ELSIF rec_order.state = ’ME’ THEN
lv_tax_num := rec_order.sub * .05;
ELSE
lv_tax_num := rec_order.sub * .04;
END IF;

d. IF rec_order.state = ’VA’ THEN
lv_tax_num := rec_order.sub * .06;
ELS IF rec_order.state = ’ME’ THEN
lv_tax_num := rec_order.sub * .05;
ELSE
lv_tax_num := rec_order.sub * .04;
END IF;

ANS: C


14. Which of the following code fragments would not raise an error?

a. IF rec.state = ’VA’ OR ’PA’ THEN
a := b * .06;
ELSE
a := b * .04;
END IF;

b. IF rec.state = ’VA’ OR rec.state = ’PA’ THEN
a := b * .06;
ELSE
a := b * .04;
END IF;

c. IF rec.state = ’VA’ OR rec.state = ’PA’
a := b * .06;
ELSE
a := b * .04;
END IF;

d. IF rec.state = ’VA’ OR rec.state = ’PA’ THEN
a := b * .06;
ELSE
a := b * .04;
END IF

ANS: B


15. Which of the following does not use a selector, but individually evaluates conditions that are placed in WHEN clauses?

a. Control statements

b. Searched CASE

c. Loops

d. CASE expression

ANS: B


16. Which of the following evaluates conditions and returns a value in an assignment statement?

a. Searched CASE

b. Basic loop

c. CASE expression

d. Control statement

ANS: C


17. Which of the following statements is true?

a. The WHEN clause of a CASE statement ends with a semicolon.

b. The WHEN clause of a CASE statement ends with “END CASE;”.

c. The WHEN clause of a CASE expression does not end with a semicolon.

d. The WHEN clause of a CASE statement ends with “ENDCASE”.

ANS: C


18. Which of the following allow us to repeat the processing of a desired portion of code?

a. Functions

b. Looping constructs

c. IF statements

d. CASE expressions

ANS: B


19. The ____ uses the LOOP and END LOOP markers to begin and end the loop code.

a. basic loop

b. cursor

c. index-by table

d. general loop

ANS: A


20. Which of the following code fragments would not raise an error?

a. BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE( lv_cnt_num );
lv_cnt_num := lv_cnt_num + 1;
END LOOP;
END;

b. BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE( lv_cnt_num );
EXIT WHEN lv_cnt_num >= 5;
lv_cnt_num := lv_cnt_num + 1;
END;

c. BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE( lv_cnt_num );
EXIT WHEN lv_cnt_num >= 5;
lv_cnt_num := lv_cnt_num + 1
END LOOP
END

d. BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE( lv_cnt_num );
EXIT WHEN lv_cnt_num >= 5;
lv_cnt_num := lv_cnt_num + 1;
END LOOP;
END;

ANS: D


21. If the EXIT WHEN clause is not included in a basic loop, then the result is a(n) ____.

a. infinite loop

b. exception

c. RAISE_APPLICATION_ERROR

d.  SQLCODE error

ANS: A


22. Which of the following clauses ensures that a basic loop runs at least once?

a. EXIT WHEN

b. WHERE

c. CASE

d. LOOP

ANS: A


23. BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(lv_cnt_num);
lv_cnt_num := lv_cnt_num + 1;
EXIT WHEN lv_cnt_num >= 5;
END LOOP;
END;

Which of the statements in the code fragment above ensures that the loop executes at least once?

a. LOOP

b. lv_cnt_num := lv_cnt_num + 1;

c. EXIT WHEN lv_cnt_num >= 5;

d. DBMS_OUTPUT.PUT_LINE(lv_cnt_num);

ANS: C


24. Which of the following code fragments would not raise an error?

a. BEGIN
WHILE lv_cnt_num <= 5
DBMS_OUTPUT.PUT_LINE(lv_cnt_num);
lv_cnt_num := lv_cnt_num + 1;
END LOOP;
END;

b. BEGIN
WHILE lv_cnt_num <= 5
DBMS_OUTPUT.PUT_LINE(lv_cnt_num);
lv_cnt_num := lv_cnt_num + 1;
END;

c. BEGIN
WHILE lv_cnt_num <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(lv_cnt_num);
lv_cnt_num := lv_cnt_num + 1;
END LOOP;

d. BEGIN
WHILE lv_cnt_num <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(lv_cnt_num);
lv_cnt_num := lv_cnt_num + 1;
END LOOP;
END;

ANS: D


25. BEGIN
WHILE lv_cnt_num <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(lv_cnt_num);
lv_cnt_num := lv_cnt_num + 1;
END LOOP;
END;

According to the code fragment above, how many times does the loop iterate?

a. 3

b. 4

c. 5

d. 6

ANS: C


26.  FOR i IN 1..tbl_roast.COUNT LOOP
lv_tot_num := lv_tot_num + tbl_roast(i);
END LOOP;

In the above code fragment, which of the following holds the value of the current iteration number?

a. tbl_roast

b. i

c. COUNT

d. lv_tot_num

ANS: B


27. Which of the following dictates exactly how many times the loop should run in the opening LOOP clause?

a. CASE

b. WHILE loop

c. FOR loop

d. Basic loop

ANS: C


28. Which of the following code fragments would not raise an error?

a. BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

b. BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(c)
END LOOP
END;

c. BEGIN
FOR i IN 1..10
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

d. BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE
END LOOP
END;

ANS: A


29. Which of the following can be referenced in the loop but cannot be assigned a value because it is controlled by the loop?

a. The counter variable

b. The IN clause

c. The INSERT INTO clause

d. The FOR clause

ANS: A


30. Which of the following code fragments is correct?

a. FOR i IN 1..tbl.COUNT
lv_tot_num := lv_tot_num + tbl_roast(i);
END LOOP;

b. FOR i IN 1..tbl.COUNT LOOP
lv_tot_num := lv_tot_num + tbl_roast(i)
END LOOP

c. FOR i IN 1..tbl.COUNT LOOP
lv_tot_num = lv_tot_num + tbl_roast(i);
END LOOP;

d. FOR i IN 1..tbl.COUNT LOOP
lv_tot_num := lv_tot_num + tbl_roast(i);
END LOOP;

ANS: D


31. Why would the following code raise an error?

IF rec_order.state = ’VA’ THEN
lv_tax_num := rec_order.sub * .06;
ELSEIF rec_order.state = ‘ME’ THEN
lv_tax_num := rec_order.sub * .05;
ELSE
lv_tax_num := rec_order.sub * .04;
END IF;

a. Semicolon after THEN is omitted

b. No space between ELSE and IF

c. ELSEIF is not a keyword

d. “:=” should be “=”

ANS: C


32.  IF rec_order.state = ’VA’ THEN
lv_tax_num := rec_order.sub * .06;
ELSIF rec_order.state = ‘ME’ THEN
lv_tax_num := rec_order.sub * .05;
ELSE
lv_tax_num := rec_order.sub * .04;
END IF;

Which of the clauses in the code fragment above would not cause the IF statement to raise an error if it were excluded?

a. ELSE

b. IF

c. END IF

d. THEN

ANS: A


 

http://techhowdy.com/wp-content/uploads/2017/10/Oracle-11g-PLSQL-3.pnghttp://techhowdy.com/wp-content/uploads/2017/10/Oracle-11g-PLSQL-3-150x150.pngDemonDatabase ProgrammingThis blog post on Oracle 11g PL/SQL Basic Block Structures Practice Multiple Choice Questions for Interview will help entry level Database programmers to answer most common Oracle 11g PL/SQL Questions Oracle 11g PL/SQL Basic Block Structures MULTIPLE CHOICE 1. The only required sections of a PL/SQL block are the ____ sections.a. BEGIN...Latest technology news