PL/SQL Online Quiz

Following quiz provides Multiple Choice Questions (MCQs) related to PL/SQL. You will have to read all the given answers and click over the correct answer. If you are not sure about the answer then you can check the answer using Show Answer button. You can use Next Quiz button to check new set of questions in the quiz.

Q 1 - Which of the following is true about the execution section of a PL/SQL block?

A - It is enclosed between the keywords BEGIN and END.

B - It is a mandatory section.

C - It consists of the executable PL/SQL statements.

D - All of the above.

Answer : D

Q 2 - What will be the output of the following code snippet?

DECLARE
   a number (2) := 21;
   b number (2) := 10;
BEGIN
   
   IF ( a <= b ) THEN
      dbms_output.put_line(a);
   END IF;

   IF ( b >= a ) THEN
      dbms_output.put_line(a);
   END IF;
   
   IF ( a <> b ) THEN
      dbms_output.put_line(b);
   
   END IF;

END;

Answer : C

Q 3 - Which of the following is true about the following code snippet?

DECLARE
   a number(3) := 100;
BEGIN
   IF (a = 50 ) THEN
      dbms_output.put_line('Value of a is 10' );
   ELSEIF ( a = 75 ) THEN
      dbms_output.put_line('Value of a is 20' );
   ELSE
       dbms_output.put_line('None of the values is matching');
   END IF;
   dbms_output.put_line('Exact value of a is: '|| a ); 
END;

A - It has syntax error.

B - It will print 'None of the values is matching'.

C - It will print

None of the values is matching

Exact value of a is: 100

D - None of the above.

Answer : A

Explanation

the ELSIF statement is wrongly written as ELSEIF

Q 4 - Consider a variable named greetings declared as −

greetings varchar2(11) := 'Hello World';

What will be the output of the code snippet

dbms_output.put_line ( SUBSTR (greetings, 7, 5));

A - World

B - Hello

C - orld

D - None of the above.

Answer : A

Q 5 - What is wrong in the following code snippet?

CREATE OR REPLACE FUNCTION totalCustomers
total number(2) := 0;
BEGIN
   SELECT count(*) into total
   FROM customers;
   RETURN total;
END;

A - It doesn’t have the RETURN clause in function declaration.

B - The RETURN statement is wrong.

C - Function definition should not use the IS keyword

D - Nothing wrong.

Answer : A

Explanation

The correct code should be

CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
   total number(2) := 0;
BEGIN
   SELECT count(*) into total
   FROM customers;
   
   RETURN total;
END;

Q 6 - The following code tries to fetch some information from all the rows in a table named customers for use in a PL/SQL block. What is wrong in the following code?

DECLARE
   c_id customers.id%type;
   c_name customers.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   LOOP
      FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;

A - It need not use a cursor.

B - The cursor is not opened.

C - It will not print information from all the rows.

D - There is nothing wrong in the code.

Answer : B

Q 7 - Observe the syntax given below −

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

The optional [FOR EACH ROW] clause specifies

A - A table with index.

B - A table with primary key.

C - A row level trigger.

D - A table with a unique key.