MCQ- Unit wise

RDBMS Questions:

Unit -5

**Relationship between SQL & PL/SQL**:

   - Question: What is the relationship between SQL and PL/SQL?

     - A) SQL is a subset of PL/SQL.

     - B) PL/SQL is an extension of SQL.

     - C) SQL and PL/SQL are completely independent languages.

     - D) PL/SQL cannot be used without SQL.

2. **Advantages of PL/SQL**:

   - Question: Which of the following is an advantage of using PL/SQL?

     - A) Reduced code complexity

     - B) Faster execution compared to SQL

     - C) Limited support for procedural constructs

     - D) Inability to integrate with other programming languages

3. **Arithmetic & Expressions in PL/SQL**:

   - Question: Which operator is used for exponentiation in PL/SQL?

     - A) ^

     - B) **

     - C) %

     - D) &

4. **Loops and Conditional Statements in PL/SQL**:

   - Question: Which loop statement in PL/SQL is used for iterating over a range of values?

     - A) FOR loop

     - B) WHILE loop

     - C) LOOP statement

     - D) REPEAT loop

5. **Exceptions Handling**:

   - Question: What is the purpose of the EXCEPTION block in PL/SQL?

     - A) To handle syntax errors

     - B) To catch and handle runtime errors

     - C) To define custom data types

     - D) To execute code unconditionally

6. **Cursor Management**:

   - Question: What is the primary purpose of a cursor in PL/SQL?

     - A) To define a variable

     - B) To manage connections to the database

     - C) To process result sets returned by SELECT queries

     - D) To execute DDL statements

7. **Triggers**:

   - Question: In PL/SQL, triggers are automatically executed in response to which events?

     - A) SELECT statements

     - B) UPDATE, INSERT, and DELETE operations

     - C) COMMIT and ROLLBACK statements

     - D) DDL statements

8. **Functions & Procedures**:

   - Question: What is the key difference between a function and a procedure in PL/SQL?

     - A) Functions can return multiple values, whereas procedures cannot.

     - B) Functions cannot accept parameters, whereas procedures can.

     - C) Functions can be called from SQL queries, whereas procedures cannot.

     - D) Functions return a value, whereas procedures do not necessarily return a value.

**SQL & PL/SQL Basics** 

6. Which of the following statements is true about PL/SQL variables?

   - A) They cannot hold numeric values.

   - B) They are only used for storing strings.

   - C) They are defined using the VAR keyword.

   - D) They must be declared before use.

7. What is the primary purpose of a stored procedure in PL/SQL?

   - A) To define database structures

   - B) To manage user sessions

   - C) To encapsulate a sequence of SQL statements

   - D) To execute DDL statements

8. Which keyword is used to declare a variable in PL/SQL?

   - A) VAR

   - B) DECLARE

   - C) VARIABLE

   - D) LET

9. What is the output of the following PL/SQL code snippet?

   ```

   DECLARE

       num1 INTEGER := 10;

       num2 INTEGER := 5;

       result INTEGER;

   BEGIN

       result := num1 + num2;

       DBMS_OUTPUT.PUT_LINE('Result: ' || result);

   END;

   ```

   - A) Result: 15

   - B) Result: 105

   - C) Result: 5

   - D) Error: variable not initialized

10. Which of the following is NOT a valid PL/SQL block structure?

    - A) DECLARE - BEGIN - EXCEPTION - END

    - B) DECLARE - BEGIN - END

    - C) DECLARE - EXCEPTION - BEGIN - END

    - D) BEGIN - EXCEPTION - END

**Advanced PL/SQL Concepts** (Continued)

6. What is the purpose of the COMMIT statement in PL/SQL?

   - A) To undo changes made by DML statements

   - B) To save changes made by DML statements permanently

   - C) To execute DDL statements

   - D) To roll back transactions

7. Which PL/SQL construct is used to dynamically execute SQL statements?

   - A) CURSOR

   - B) FUNCTION

   - C) EXECUTE IMMEDIATE

   - D) TRIGGER

8. What is the purpose of the RETURNING clause in an INSERT statement?

   - A) To specify the columns to be inserted

   - B) To return the number of rows affected by the insert

   - C) To return values generated by sequences or default expressions

   - D) To rollback changes made by the insert

9. Which of the following is NOT a valid PL/SQL trigger timing point?

   - A) BEFORE

   - B) AFTER

   - C) INSTEAD OF

   - D) BETWEEN

10. In PL/SQL, which construct is used to temporarily store and manipulate subsets of data?

    - A) CURSOR

    - B) TRIGGER

    - C) VIEW

    - D) COLLECTION

** PL/SQL Programming Constructs** (Continued)

6. Which of the following is NOT a valid PL/SQL loop construct?

   - A) FOR loop

   - B) WHILE loop

   - C) REPEAT loop

   - D) LOOP statement

7. In PL/SQL, how is a record declared?

   - A) Using the RECORD keyword

   - B) Using the DECLARE keyword

   - C) Using the ROWTYPE attribute

   - D) Using the CURSOR keyword

8. What is the purpose of the CASE statement in PL/SQL?

   - A) To handle exceptions

   - B) To declare variables

   - C) To control the flow of execution based on multiple conditions

   - D) To define triggers

9. Which of the following is NOT a valid PL/SQL exception handler?

   - A) WHEN OTHERS THEN

   - B) WHEN ZERO_DIVIDE THEN

   - C) WHEN NO_DATA_FOUND THEN

   - D) WHEN VALUE_ERROR THEN

10. In PL/SQL, what is the maximum number of nested blocks allowed?

    - A) 5

    - B) 10

    - C) 255

    - D) Unlimited

**Advanced PL/SQL Topics** (Continued)

6. What is the primary purpose of using autonomous transactions in PL/SQL?

   - A) To execute DDL statements

   - B) To define triggers

   - C) To manage user sessions

   - D) To maintain data consistency within a transaction

7. Which pragma is used to associate an exception code with a user-defined exception name?

   - A) EXCEPTION_INIT

   - B) PRAGMA_EXCEPTION

   - C) DECLARE_EXCEPTION

   - D) EXCEPTION_HANDLE

8. What is the purpose of the DBMS_OUTPUT.PUT_LINE procedure in PL/SQL?

   - A) To insert a new line into a table

   - B) To display output in the console

   - C) To execute SQL statements

   - D) To define triggers

9. In PL/SQL, what is the primary purpose of using bulk binds?

   - A) To improve performance by reducing context switches

   - B) To execute DML statements

   - C) To handle exceptions

   - D) To define triggers

10. Which of the following is a valid use case for using the PRAGMA RESTRICT_REFERENCES pragma in PL/SQL?

    - A) To declare a variable

    - B) To define a cursor

    - C) To enforce restrictions on a function's side effects

    - D) To create a trigger

Answers:

**SQL & PL/SQL Basics**

1. B) Structured Query Language

2. B) A extension of SQL with procedural features

3. B) To store data temporarily

4. A) Better performance

5. C) It is used for defining database structures

6. D) They must be declared before use.

7. C) To encapsulate a sequence of SQL statements

8. B) To manage user sessions

9. A) Result: 15

10. D) BEGIN - EXCEPTION - END

**Advanced PL/SQL Concepts**

1. B) To save changes made by DML statements permanently

2. C) EXECUTE IMMEDIATE

3. C) To return values generated by sequences or default expressions

4. D) BETWEEN

5. A) CURSOR

6. D) TRIGGER

7. C) INSTEAD OF

8. A) FOR loop

9. C) Using the ROWTYPE attribute

10. C) To control the flow of execution based on multiple conditions

**PL/SQL Programming Constructs**

1. D) LOOP statement

2. C) Using the ROWTYPE attribute

3. C) To control the flow of execution based on multiple conditions

4. D) WHEN VALUE_ERROR THEN

5. C) 255

6. A) To execute DDL statements

7. A) EXCEPTION_INIT

8. B) To display output in the console

9. A) To improve performance by reducing context switches

10. C) To enforce restrictions on a function's side effects

**Advanced PL/SQL Topics**

1. D) To maintain data consistency within a transaction

2. A) EXCEPTION_INIT

3. B) To display output in the console

4. A) To improve performance by reducing context switches

5. C) To enforce restrictions on a function's side effects